SQL - создание переменной задержки с помощью оконной функции

#sql

Вопрос:

У меня есть ежедневные данные на уровне города с некоторыми подсчетами. Я должен агрегировать эти данные на ежемесячном уровне (1-й день каждого месяца), а затем создавать переменные задержки на основе последней 1 недели с 1-го числа месяца.

Я использовал следующий код для создания переменных задержки за последний 1 месяц с использованием (после агрегирования данных на ежемесячном уровне (с 1-го числа месяца)

 sum(count) over (partition by City order by month_date rows between 1 preceding  and 1 preceding) as last_1_month_count

Есть ли способ агрегировать данные на ежемесячном уровне и создавать переменные задержки на основе последних 7,14,21,28 дней с помощью функции window?

1. Выберите одну СУБД (серверы MySQL и SQL слишком разные). Укажите точную версию СУБД. Предоставьте образцы данных в виде интерактивной скрипки (или СОЗДАЙТЕ ТАБЛИЦУ ВСТАВЬТЕ В скрипты). Предоставьте желаемый результат для этих данных с подробными пояснениями.

2. создайте переменные задержки на основе последних 7,14,21,28 дней с помощью функции window Используйте ДИАПАЗОН вместо СТРОК.

Ответ №1:

вы можете использовать это L

 select 
    CITY
    , month(Date)
    , year(date)
    , sum(count)

from table1
where date < Datediff(days , 7 , getdate())
group by 
    City
    , month(Date)
    , year(date)

Ответ №2:

Я думаю, вы ищете что-то вроде этого. Первый cte суммирует количество городов с точностью до дня, недели, месяца, года. Второй суммирует подсчеты до недели, месяца, года. Для группировки продаж по неделям, начиная с 1-го дня, используется функция DAY вместе с ГОДОМ и МЕСЯЦЕМ. Поскольку возвращает значение DAY и integer, группы отдельных недель могут быть созданы путем деления на 7, то есть DAY(day_dt) / 7.

Один из способов получить данные о продажах за предыдущую неделю — присоединить к себе сводку продаж за неделю cte, где неделя смещена на -1. Поскольку на предыдущей неделе может быть 0 продаж, кажется более безопасным использовать ЛЕВОЕ СОЕДИНЕНИЕ, чем использовать ЛАГ imo

 with 
day_sales_cte(city, day_dt, yr, mo, wk, sum_count) as (
    select city, day_dt, year(day_dt), month(day_cte), day(day_dt)/7, sum([count]) sum_counts
    from city_level_data
    group by city, day_dt, year(day_dt), month(day_cte), day(day_dt)/7)
wk_sales_cte(city, yr, mo, wk, sum_count) as (
    select city, yr, mo, wk, sum(sum_counts) sum_counts
    from sales_cte
    group by city, yr, mo, wk)
select ws.*, ws2.sum_sales prior_wk_sales
from wk_sales_cte ws
     left join wk_sales_cte ws2 on ws.city=ws2.city
                                   and ws.yr=ws2.yr
                                   and ws.mo=ws2.mo
                                   and ws.wk=ws.wk-1;