Bigquery SQL для скользящего оконного агрегата

Привет у меня есть стол, который выглядит так

Date         Customer   Pageviews
2014/03/01   abc          5
2014/03/02   xyz          8
2014/03/03   abc          6

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

Я использую Google BigQuery

РЕДАКТИРОВАТЬ: Гордон - ваш комментарий о "Клиенте", на самом деле то, что мне нужно, немного сложнее, поэтому я включил клиента в таблицу выше. Я рассчитываю получить количество клиентов, у которых было>n просмотров страниц в 30-дневном окне каждую неделю. что-то вроде этого

Date        Customers>10 pageviews in 30day window
2014/02/01  10
2014/02/08  5
2014/02/15  6
2014/02/22  15

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

Date        count of pageviews in 30day window
2014/02/01  50
2014/02/08  55
2014/02/15  65
2014/02/22  75
Источник

2 ответа

Решение

Как насчет этого:

SELECT changes + changes1 + changes2 + changes3 changes28days, login, USEC_TO_TIMESTAMP(week)
FROM (
  SELECT changes,
         LAG(changes, 1) OVER (PARTITION BY login ORDER BY week) changes1,
         LAG(changes, 2) OVER (PARTITION BY login ORDER BY week) changes2,
         LAG(changes, 3) OVER (PARTITION BY login ORDER BY week) changes3,
         login,
         week
  FROM (
    SELECT SUM(payload_pull_request_changed_files) changes, 
           UTC_USEC_TO_WEEK(created_at, 1) week,
           actor_attributes_login login,
    FROM [publicdata:samples.github_timeline]
    WHERE payload_pull_request_changed_files > 0
    GROUP BY week, login
))
HAVING changes28days > 0

Для каждого пользователя подсчитывается, сколько изменений он отправил за неделю. Затем с помощью LAG() мы можем заглянуть в следующий ряд, сколько изменений они представили за -1, -2 и -3 недели. Затем мы просто добавляем эти 4 недели, чтобы увидеть, сколько изменений было представлено за последние 28 дней.

Теперь вы можете обернуть все в новый запрос, чтобы отфильтровать пользователей с изменениями>X и сосчитать их.

Источник

Я создал следующую таблицу "Times":

Table Details: Dim_Periods
Schema
Date    TIMESTAMP   
Year    INTEGER         
Month   INTEGER         
day         INTEGER         
QUARTER INTEGER     
DAYOFWEEK   INTEGER     
MonthStart  TIMESTAMP   
MonthEnd    TIMESTAMP   
WeekStart   TIMESTAMP   
WeekEnd TIMESTAMP   
Back30Days  TIMESTAMP   -- the date 30 days before "Date"
Back7Days   TIMESTAMP   -- the date 7 days before "Date"

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

SELECT Date,Count(*) as MovingCNT
FROM

(SELECT Date,
                Back7Days 
                    FROM DWH.Dim_Periods  
                 where Date < timestamp(current_date()) AND
                             Date >= (DATE_ADD (CURRENT_TIMESTAMP(), -5, 'month'))
                )P
                CROSS JOIN EACH
    (SELECT repository_url,repository_created_at
    FROM publicdata:samples.github_timeline
                ) L
        WHERE timestamp(repository_created_at)>= Back7Days 
              AND timestamp(repository_created_at)<= Date

GROUP EACH BY Date

Обратите внимание, что он также может использоваться для агрегатов "Месяц на дату", "Неделя на дату", "30 дней назад" и т. Д. Однако производительность не самая лучшая, и запрос может занять некоторое время для больших наборов данных из-за декартовой системы. присоединяйтесь. Надеюсь, это поможет

Источник
Другие вопросы по тегам