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

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

SELECT
  ty.*,
  ly.Date as Date_LY,
  ly.Sessions as Sessions_LY
FROM
  `testjoin` AS ty
FULL JOIN
  `testjoin` as ly
  ON
  ly.Date = DATE_SUB(ty.Date, INTERVAl 1 YEAR)
  AND ly.Channel = ty.Channel

Данные:

Date        Channel Sessions
01/01/2017  Email   5
02/02/2017  Email   10
01/01/2018  Email   11
02/02/2018  Email   17
01/01/2017  Organic 10
02/02/2017  Organic 15
01/01/2018  Organic 20

Желаемый результат:

Date    Channel Sessions    Sessions_LY
01/01/2017  Email   5   null
02/02/2017  Email   10  null
01/01/2018  Email   11  5
02/02/2018  Email   17  10
01/01/2017  Organic 10  null
02/02/2017  Organic 15  null
01/01/2018  Organic 20  10
02/02/2018  Organic null    15

Фактический результат:

Date        Channel Sessions    Sessions_LY
01/01/2017  Organic 10  
02/02/2017  Email   10  
02/02/2017  Organic 15  
01/01/2017  Email   5   
01/01/2018  Email   11  5
01/01/2018  Organic 20  10
02/02/2018  Email   17  10
                        15
                        11
                        20
                        17

sql google-bigquery join bigquery-standard-sql
person Carl K    schedule 15.05.2018    source источник
comment
У вас была возможность попробовать любой из приведенных ниже ответов?   -  person Mikhail Berlyant    schedule 18.05.2018
comment
@MikhailBerlyant, спасибо за внимание. Я играл с ответами, поскольку фактический набор данных имеет много лет и 16 измерений, поэтому сложность запроса была довольно высокой. Все еще работаю над тем, чтобы заставить его работать, но я думаю, что у меня есть все части благодаря вам, ребята, здесь.   -  person Carl K    schedule 31.05.2018

Ответы (3)


arrow_upward
1
arrow_downward

Я думаю, вы хотите, чтобы cross join генерировал строки, а left join вводил значения:

SELECT d.Date, c.Channel, ty.Sessions, ty_prev.Sessions
FROM (SELECT DISTINCT ty.Date
      FROM testjoin ty
     ) d CROSS JOIN
     (SELECT DISTINCT ty.channel FROM testjoin ty) c LEFT JOIN
     testjoin ty
     ON ty.Date = d.Date AND ty.Channel = c.Channel LEFT JOIN
     testjoin ty_prev
     ON ty_prev.Date = d.date - interval 1 year and ty.Channel = c.Channel;
person Gordon Linoff    schedule 15.05.2018

arrow_upward
0
arrow_downward

Играйте с datepart в соответствии с вашими потребностями

with t (date, channel, sessions) as ( select '01/01/2017', 'Email', 5 union all select '02/02/2017', 'Email', 10 union all select '01/01/2018', 'Email', 11 union all select '02/02/2018', 'Email', 17 union all select '01/01/2017', 'Organic', 10 union all select '02/02/2017', 'Organic', 15 union all select '01/01/2018', 'Organic', 20 ) select *, lag(sessions) over (partition by d.channel, datepart(mm, d.date) order by d.channel, datepart(mm, d.date)) l from (select * from ((SELECT DISTINCT t.Date FROM t) d CROSS JOIN (SELECT DISTINCT t.channel FROM t) c)) d left join t on d.Date = t.Date and d.channel = t.channel order by d.channel, datepart(yyyy,d.date), datepart(mm, d.date)

person san    schedule 15.05.2018

arrow_upward
0
arrow_downward

Все в вашем вопросе указывает на то, что у вас есть только текущий (2018) и предыдущий (2017), поэтому ниже основано на этом предположении и предназначено для стандартного SQL BigQuery.

#standardSQL
WITH temp AS (
  SELECT PARSE_DATE('%m/%d/%Y', Date) Date, Channel, Sessions
  FROM `project.dataset.your_table` 
), all_days AS ( 
  SELECT Date, Channel FROM temp UNION DISTINCT
  SELECT DATE_ADD(Date, INTERVAL 1 YEAR), Channel 
    FROM temp WHERE EXTRACT(YEAR FROM Date) = 2017
), all_data AS (
  SELECT Date, Channel, Sessions, FORMAT_DATE('%m%d', Date) day
  FROM all_days
  LEFT JOIN temp USING(Date, Channel)
)
SELECT Date, Channel, Sessions, 
  LAG(Sessions) OVER(PARTITION BY day, Channel ORDER BY Date) Sessions_LY
FROM all_data

Вы можете протестировать/поиграть с выше, используя фиктивные данные из вашего вопроса, как показано ниже.

#standardSQL
WITH `project.dataset.your_table` AS (
  SELECT '01/01/2017' Date, 'Email' Channel, 5 Sessions UNION ALL
  SELECT '02/02/2017', 'Email', 10 UNION ALL
  SELECT '01/01/2018', 'Email', 11 UNION ALL
  SELECT '02/02/2018', 'Email', 17 UNION ALL
  SELECT '01/01/2017', 'Organic', 10 UNION ALL
  SELECT '02/02/2017', 'Organic', 15 UNION ALL
  SELECT '01/01/2018', 'Organic', 20 
), temp AS (
  SELECT PARSE_DATE('%m/%d/%Y', Date) Date, Channel, Sessions
  FROM `project.dataset.your_table` 
), all_days AS ( 
  SELECT Date, Channel FROM temp UNION DISTINCT
  SELECT DATE_ADD(Date, INTERVAL 1 YEAR), Channel 
    FROM temp WHERE EXTRACT(YEAR FROM Date) = 2017
), all_data AS (
  SELECT Date, Channel, Sessions, FORMAT_DATE('%m%d', Date) day
  FROM all_days
  LEFT JOIN temp USING(Date, Channel)
)
SELECT Date, Channel, Sessions, 
  LAG(Sessions) OVER(PARTITION BY day, Channel ORDER BY Date) Sessions_LY
FROM all_data
ORDER BY 2, 1

результат

Row Date        Channel     Sessions    Sessions_LY  
1   2017-01-01  Email       5           null     
2   2017-02-02  Email       10          null     
3   2018-01-01  Email       11          5    
4   2018-02-02  Email       17          10   
5   2017-01-01  Organic     10          null     
6   2017-02-02  Organic     15          null     
7   2018-01-01  Organic     20          10   
8   2018-02-02  Organic     null        15
person Mikhail Berlyant    schedule 15.05.2018