Как рассчитать скользящую сумму для группы данных
У меня есть набор исторических данных, скажем, с 2000 года, хранящихся в таблице A. Структура данных таблицы просто состоит из двух столбцов, даты dt и элемента.
dt, item
1/20/2000, A1
1/20/2000, A2
1/20/2000, A3
....
6/1/2024, A1
---
Как рассчитать историческое количество каждого предмета за последние 2 года?
Другими словами, я хотел бы видеть такие результаты:
202406, A1, Count of A1 until 6/1/2024 since 1/1/2000
202406, A2, Count of A2 until 6/1/2024 since 1/1/2000
202406, A3, Count of A3 until 6/1/2024 since 1/1/2000
202405, A1, Count of A1 until 5/1/2024 since 1/1/2000
202405, A2, Count of A2 until 5/1/2024 since 1/1/2000
202405, A3, Count of A3 until 5/1/2024 since 1/1/2000
...
202206, A1, Count of A1 until 6/1/2022 since 1/1/2000
202206, A2, Count of A2 until 6/1/2022 since 1/1/2000
202206, A3, Count of A3 until 6/1/2022 since 1/1/2000
Я могу легко сгруппировать и подсчитать элементы за определенный месяц, используя это:
select 202406, item, count(item)
from table A
where datetime < 6/1/2024
group by left(convert(varchar,dt,112),6), item
Я также могу перечислить 24 прошедших месяца с сегодняшнего дня.
select dt2=format(dateadd(month,2-n,getdate()),'yyyyMM')
from
(
select top (24) n=row_number() over (order by (select null))
from sys.object)
)
Но я не могу объединить два приведенных выше оператора SQL, чтобы получить желаемый результат.
В принципе, мне просто хотелось бы получить что-то вроде этого
select x, item, count(item) from tablea where month(dt) <x group by x, item
где x работает с текущего месяца-24 до текущего месяца.
Можно ли этого легко добиться с помощью одного простого оператора SQL вместо использования хранимой процедуры?
Вы также можете выполнить перекрестное применение или скалярный запрос: from tablea a cross apply (select count(*) from tablea a2 where a2.item = a.item and a2.dt >= dateadd(month, -24, a.dt) and a2.dt < a.dt) as month24(itemcount) Настройте конечные точки соответствующим образом. Я не совсем уверен, какие 24 месяца вам нужны. Однако этот подход будет работать с пробелами.
Вы используете SQL-сервер? Я предлагаю вам добавить тег sql-server, чтобы лучше охватить целевую аудиторию. Также опубликуйте информацию о своей версии (select @@version). Некоторые новые функции, такие как GENERATE_SERIES() и DATETRUNC(), могут быть предпочтительнее при создании списка дат. Решение может быть что-то вроде select D.MonthEnd, H.Item, count(*) as ItemCount from HistoryTable H join (date-generator-logic) D ON H.dt <= D.MonthEnd group by D.MonthEnd, H.Item order by D.MonthEnd, H.Item. Обычно лучше всего реализовывать логику даты с использованием типов даты и применять форматирование только к конечному результату.
@TN: Да, я использую SQL Server 2018.
@shawnt00: Спасибо за подсказку... но не могли бы вы рассказать подробнее? Я не совсем уверен, как связать месяц в выбранном элементе из таблицы, где месяц элемента в таблице меньше месяца x, x за период с предыдущих 24 месяцев до текущего месяца.
@ user1205746 — SQL-сервера 2018 не существует. Что вам говорит команда select @@version.
@TN: К сожалению… извините… Должно быть написано «2019». Microsoft SQL Server 2019 (RTM-CU26)
По сути, я просто хотел бы получить это «выберите x, элемент, счетчик (элемент) из таблицы, где месяц (dt) <x группа по x, элемент с x, работающим с текущего месяца-24 до текущего месяца. Можно ли этого достичь за 1 простой оператор вместо использования хранимой процедуры?
Еще пара вопросов: (1) Кончаются ли ваши диапазоны дат в начале месяца («202406» включает даты до 1 июня 2024 г.) или в конце месяца («202406» включает даты до 30 июня 2024 г.). )? (2) Для значений «Количество A1 до 01.06.2024 с 01.01.2000» в ваших ожидаемых результатах вы просто ищете целочисленное количество?
К вашему сведению... вы говорите о своей альтернативе как о добавлении хранимой процедуры, но хранимая процедура не дает вам никаких других опций, чем здесь... она просто инкапсулирует некоторые операторы.
Ответы 2
Возможно, вам поможет следующий пример.
WITH months (n, ldom) AS (
SELECT -23 AS n, EOMONTH(GETDATE(), -23) AS ldom
UNION ALL
SELECT n + 1, EOMONTH(GETDATE(), n + 1)
FROM months
WHERE n < 0
)
SELECT
ldom,
agg.item,
agg.cnt
FROM months
OUTER APPLY (
SELECT items.item, COUNT(*) AS cnt
FROM items
WHERE items.dt <= months.ldom
GROUP BY items.item
) agg
Здесь CTE months получает последние 24 месяца от текущей даты. Это можно получить разными способами. Например, в SQl Server 2022 через GENERATE_SERIES.
Спасибо! Это, вероятно, тоже сработает. Я бы хотел отметить оба ответа как ответ. Решений вопроса всегда больше, но, к сожалению, только одно решение может быть помечено как ответ.
Вы можете добиться того, чего хотите:
- Инкапсуляция измененной версии генератора дат в подвыборке или CTE.
- Соедините сгенерированные даты с вашими данными, используя условие неравенства дат.
- Группируйте по дате и элементу при расчете и подсчете.
- Отформатируйте результаты по желанию.
with Months as (
select eomonth(getdate(), 1 - N.n) as endDate
from (
select top (24) row_number() over (order by (select null)) as n
from (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) N1(n) -- up to 10
cross join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) N2(n) -- up to 100
) N
)
select
M.endDate,
convert(char(6), M.endDate, 112) as endMonth, -- yyyymmdd truncated
A.item,
count(*) as itemCount
from TableA A
join Months M
on A.dt <= M.endDate
group by M.endDate, A.item
order by M.endDate, A.item;
Генератор дат использует источник VALUES вместо sys.objects, потому что последнему не хватило строк в среде fiddle. Он также вычисляет истинную дату окончания месяца вместо формата «ГГГГММ». Я также скорректировал диапазон на один месяц вниз, чтобы он заканчивался текущим месяцем.
Полученные результаты
См. эту db<>fiddle для демонстрации
Спасибо за очень элегантное решение! Это тот кусок, который я не знал как правильно связать! Решение краткое и легкое для понимания. Еще раз спасибо!
count() over (partition by item order by dt rows between 23 preceding and current row)Вы захотите изменить свой запрос наgroup by dt, itemПри этом предполагается, что в данных нет месячных пробелов. К сожалению, SQL Server пока не поддерживает использованиеrange between.