Применение функции скользящего окна с помощью поля даты

Если я правильно понимаю вопрос, вы можете использовать аргументы диапазона в оконной функции следующим образом (для простоты я выгрузил значения вашей таблицы во временную таблицу):

select distinct [date], id
      from #test
order by [date] desc

select [date], 
        count(*) over (order by date desc rows between current row and 7 following ) count_7_day,
        count(*) over (order by date desc rows between current row and 30 following ) count_30_day
from (select distinct [date], id
      from #test )x
order by [date] desc

надеюсь, это поможет?

с датой в таблице

create table  test as(
select $1 as date, $2 as id, $3 as a, $4 as b, $5 as c, $6 as d
from values 
('2019-07-15','x_1','ps','e','US','NA'),
('2019-07-15','x_2','ps','e','US','NA'),
('2019-07-15','x_2','ps','e','CA','NA'),
('2019-07-16','x_2','ps','e','CA','NA'),
('2019-07-16','x_3','c','xb','CH','AS'),
('2019-07-17','x_4','ps','e','US','NA'),
('2019-07-17','x_5','c','ps4','CH','AS'),
('2019-07-17','x_6','c','ps4','CH','AS'),
('2019-07-17','x_7','c','ns','CH','AS'),
('2019-07-18','x_7','c','ns','CH','AS'),
('2019-07-18','x_7','c','ns','CH','AS'),
('2019-07-18','x_7','c','ns','CH','AS'),
('2019-07-18','x_7','c','ns','CH','AS'),
('2019-07-19','x_8','c','ps','CH','AS'),
('2019-07-20','x_8','c','ps','CH','AS'),
('2019-07-21','x_8','c','ps','CH','AS'),
('2019-07-22','x_8','c','ps','CH','AS'),
('2019-07-29','x_8','c','ps','CH','AS'),
('2019-08-01','x_8','c','ps','CH','AS'),
('2019-08-02','x_9','c','ps','CH','AS'),
('2019-08-03','y_1','c','ps','CH','AS'),
('2019-08-04','y_8','c','ps','CH','AS'),
('2019-08-05','z_8','c','ps','CH','AS'),
('2019-08-06','a_8','c','ps','CH','AS'),
('2019-08-07','b_8','c','ps','CH','AS'),
('2019-08-07','c_8','c','ns','CH','AS'),
('2019-08-07','e_8','ps','e','US','NA'),
('2019-08-08','f_8','c','xb','CH','AS'),
('2019-08-08','f_8','c','xb','CH','AS'),
('2019-08-08','f_8','c','xb','CH','AS'),
('2019-08-09','f_8','c','xb','CH','AS'),
('2019-08-09','f_8','c','xb','CH','AS'),
('2019-08-09','f_8','c','xb','CH','AS'),
('2019-08-10','gx_8','c','ps','CH','AS'),
('2019-08-11','v_8','c','ps','CH','AS') );

так, как я делал это в прошлом, когда я хотел изобразить разные дни в виде строк, поэтому результаты были в виде разных строк, чтобы разрешить цвет по столбцу дней, я решаю это следующим образом:

with distinct_data as (
    select distinct date, id
    from test
), days as ( -- set of days we want to range over
    select column1 as days from (values (1),(7),(30))
), windows_ends as ( -- last 60 days of date
    select dateadd('day',-SEQ8(),current_date) as win_date_end
    from table(generator(rowcount => 60))
), windows as (
    select d.days
        ,w.win_date_end
        ,dateadd('day',-d.days, w.win_date_end) as win_date_start
    from days as d
    join windows_ends as w
)
select w.days
    ,w.win_date_start
    ,w.win_date_end
    ,count(distinct(d.id)) as c_id
from distinct_data as d
join windows as w on d.date > w.win_date_Start and d.date <= w.win_date_end
group by 1,2,3
order by 3,1;

для моих результатов я отбросил строки, которые win_date_start были до начала моих данных, поэтому я не получу 30 дней, которые начались через 2 дня после запуска набора данных.

Вышеупомянутое позволяет динамическое количество дней (что я нашел действительно полезным), но в контексте наличия трех фиксированных результатов я просто добавил точку поворота как таковую.

with distinct_data as (
    select distinct date, id
    from test
), days as ( -- set of days we want to range over
    select column1 as days from (values (1),(7),(30))
), windows_ends as ( -- last 60 days of date
    select dateadd('day',-SEQ8(),current_date) as win_date_end
    from table(generator(rowcount => 60))
), windows as (
    select d.days
        ,w.win_date_end
        ,dateadd('day',-d.days, w.win_date_end) as win_date_start
    from days as d
    join windows_ends as w
), manyrows as (
  select w.days
      ,w.win_date_end
      ,count(distinct(d.id)) as c_id
  from distinct_data as d
  join windows as w on d.date > w.win_date_Start and d.date <= w.win_date_end
  group by 1,2
)
select win_date_end as date, "1" as d1, "7" as d7, "30" as d30
from manyrows
   pivot(sum(c_id) for days in (1,7,30)) as p
order by win_date_end;

но вы можете видеть, как результаты имеют конечный эффект для последних дат ... который я был бы склонен отфильтровать ..

также необходимо изменить фильтр 60 с настоящего момента

Я считаю, что это можно было бы сделать более эффективно, учитывая фиксированное 30-дневное окно, например

with distinct_data as (
    select distinct date
        ,id
    from test
), distinct_dates as (
    select distinct date
        ,dateadd('day',-30,date) as d30_s
    from distinct_data
), mixed_data as (
select d.date
    ,b.id
    ,datediff('days',b.date, d.date) as d_diff
    ,iff(d_diff < 1, id, NULL) as d1_id
    ,iff(d_diff < 7, id, NULL) as d7_id
    ,iff(d_diff < 30, id, NULL) as d30_id
from distinct_dates as d
join distinct_data as b on b.date >= d.d30_s and b.date <= d.date
)
select date
    ,count(distinct(d1_id)) as d1
    ,count(distinct(d7_id)) as d7
    ,count(distinct(d30_id)) as d30
from mixed_data
group by 1 order by 1;

но это дает только оконные суммы для текущих дней, поэтому первые два метода дают 7 дней и 30 дней для 2019-7-23, тогда как в последнем нет этого дня ... так что это зависит от того, как вам нужны данные.

Если мы используем текущую строку, мы не получим исключенный вывод, если только в вашем случае не будет только 1 строка, поэтому мы должны использовать слайд по диапазону, если эта функция доступна. Если нет, объедините свои данные с измерением даты и заполните данные для своего скользящего окна и используйте оператор case, чтобы получить ваши окна (если у вас несколько окон). Теперь, когда у нас есть необходимые данные, используйте обычные оконные функции для дела и примените к ним счет.