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

Question

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

Я пытаюсь посчитать разные идентификаторы в течение дня, который прямо вперед, но как посчитать их для 7 дней назад от текущей даты в окне, 30 дней назад в текущем окне.

Я сдвигаюсь назад на 7/30 строк из текущей строки, но я думаю, что она не будет точной, это может быть больше / меньше 7/30 дней, потому что я думаю, что она просто скользит по количеству строк, так что у вас есть идея справиться с этим?

--mockup data SnowSQL
with test  (date,id,a,b,c,d) 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') )
--subquery
select 1 as part,
(
  select sum(x)
  from (
select count(distinct id) as x
from test
where
 date = '2019-07-16'
group by a,b,c,d) ) as dau,
(
  select sum(x)
  from (
select count(distinct id) as x
from test
where
 date between '2019-07-16'::date-interval '7 days' and '2019-07-16'
//    <= '2019-07-15'
//and date >= '2019-07-15'::date-interval '7 days'
group by a,b,c,d)) as w,
(select sum(x)
  from (
select count(distinct id) as x
from test
where
 date <= '2019-07-16'
and date >= '2019-07-16'::date-interval '30 days'
group by a,b,c,d)) as m
union
--window function
select 2,sum(dau),sum(w),sum(m)
from(
select 
date,
a,
b,
c,
d,
count(distinct id) over (Partition by date,a,b,c,d Order by date)as dau,
count(distinct id) over (Partition by a,b,c,d Order by date rows between 7 preceding  and current row) as w,
count(distinct id) over (Partition by a,b,c,d Order by date rows between 30 preceding  and current row) as m
from test
group by
date,
a,
b,
c,
d,
id)
where date='2019-07-16'
group by date
;

1-я часть выполняет подсчет в течение периода времени подзапросом

Вторая часть делает подсчет, используя функцию скольжения

Ожидаемый результат: оба должны получить одинаковое количество за один и тот же период времени.

2

sql snowflake-cloud-data-platform window-functions snowflake-datawarehouse snowflake snowsql

Источник

user11347137 20 авг '19 в 20:33

1 ответ

Другие вопросы по тегам sql snowflake-cloud-data-platform window-functions snowflake-datawarehouse snowflake snowsql

user43992 07 сен '19 в 09:25 2019-09-07 09:25 · Answer 1 · 2019-09-07 09:25

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

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, в то время как более поздний не имеет этого дня.. так что это зависит от того, как вам нужны данные.

user1518867 20 авг '19 в 21:56 2019-08-20 21:56 · Answer 2 · 2019-08-20 21:56

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

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

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

user11347137 11 дек '19 в 01:08 2019-12-11 01:08 · Answer 3 · 2019-12-11 01:08

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