Блог

У меня есть таблица, которая содержит действия пользователя (например, просмотр страницы, нажатие кнопки и т. Д.). Каждая строка содержит идентификатор пользователя, дату (created_on) и имя действия. Я хотел бы создать запрос, который для каждой даты создавал бы вложенное поле с различными действиями, выполненными до этой даты включительно. Например, у меня есть таблица с именем user_actions :

 -------------------------------------
| user_id |    date    |   action   |
-------------------------------------
|    1    | 2018-04-01 |    click   |
|    2    | 2018-04-01 |     view   |
|    1    | 2018-04-02 |     view   |
|    2    | 2018-04-02 |     view   |
|    2    | 2018-04-03 |      buy   |
-------------------------------------

would result in

-------------------------------------
| user_id |    date    |   actions  |
-------------------------------------
|    1    | 2018-04-01 |     click  |
|    2    | 2018-04-01 |     view   |
|    1    | 2018-04-02 |     click  |
|    2    | 2018-04-02 |     view   |
|         |            |     view   |
|    2    | 2018-04-03 |     view   |
|    2    |            |      buy   |
-------------------------------------
  

Во второй таблице actions представляет собой вложенное повторяющееся поле. Я знаю, что для одного момента времени я могу использовать что-то похожее на следующее:

 SELECT
    user_id,
    date,
    ARRAY(action)
FROM
    user_actions
GROUP BY
    1,2
  

Однако я не уверен, как расширить это, чтобы обеспечить одинаковые вычисления для каждой даты в исходной таблице и смотреть только на время перед date полем.

Любая помощь будет с благодарностью принята. Спасибо!

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

Ниже для BigQuery Standrad SQL

 #standardSQL
SELECT user_id, date, 
  ARRAY(
    SELECT DISTINCT action FROM UNNEST(actions) action
  ) actions
FROM (
  SELECT user_id, date, ARRAY_AGG(action) OVER(win) actions
  FROM `project.dataset.table`
  WINDOW win AS (
    PARTITION BY user_id ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
)
  

Вы можете протестировать, поиграть с приведенными выше примерами данных из вашего вопроса, как в примере ниже

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 user_id, '2018-04-01' date, 'click' action UNION ALL
  SELECT 2, '2018-04-01', 'view' UNION ALL
  SELECT 1, '2018-04-02', 'view' UNION ALL
  SELECT 2, '2018-04-02', 'view' UNION ALL
  SELECT 2, '2018-04-03', 'buy' 
)
SELECT user_id, date, 
  ARRAY(
    SELECT DISTINCT action FROM UNNEST(actions) action
  ) actions
FROM (
  SELECT user_id, date, ARRAY_AGG(action) OVER(win) actions
  FROM `project.dataset.table`
  WINDOW win AS (
    PARTITION BY user_id ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
)
-- ORDER BY date, user_id    
  

с результатом

Обновить

Приведенная ниже версия поддерживает более общий случай с несколькими действиями для одного и того же пользователя в течение одного дня (я понял, что это не относится к моему первоначальному ответу)

 #standardSQL
SELECT user_id, date, 
  ARRAY(
    SELECT DISTINCT action FROM UNNEST(SPLIT(actions)) action
  ) actions
FROM (
  SELECT user_id, date , STRING_AGG(actions) OVER(win) actions
  FROM (
    SELECT user_id, date, STRING_AGG(DISTINCT action) actions
    FROM `project.dataset.table`
    GROUP BY user_id, date
  )
  WINDOW win AS (
    PARTITION BY user_id ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
)
  

Вы можете протестировать его с приведенными ниже примерами данных (обратите внимание на строку extyra с activity = ‘play’ )

 #standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 user_id, DATE '2018-04-01' date, 'click' action UNION ALL
  SELECT 2, '2018-04-01', 'view' UNION ALL
  SELECT 1, '2018-04-02', 'view' UNION ALL
  SELECT 1, '2018-04-02', 'play' UNION ALL
  SELECT 2, '2018-04-02', 'view' UNION ALL
  SELECT 2, '2018-04-03', 'buy' 
)
SELECT user_id, date, 
  ARRAY(
    SELECT DISTINCT action FROM UNNEST(SPLIT(actions)) action
  ) actions
FROM (
  SELECT user_id, date , STRING_AGG(actions) OVER(win) actions
  FROM (
    SELECT user_id, date, STRING_AGG(DISTINCT action) actions
    FROM `project.dataset.table`
    GROUP BY user_id, date
  )
  WINDOW win AS (
    PARTITION BY user_id ORDER BY date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
)
-- ORDER BY date, user_id
  

с результатом