Объединить две таблицы с разными датами на основе значения столбца?

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

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

Пример: для Account1 мне нужны новые данные, начиная с 1 января; для Account2 мне нужны новые данные от 30 декабря; для учетной записи 3 мне нужны новые данные от 31 декабря

Old Table  
------------------------------------   
Account         Date         Sales  
------------------------------------
Account1        12-29-18     10  
Account1        12-30-18     10  
Account1        12-31-18     5  
Account2        12-29-18     10    
Account3        12-29-18     20  
Account3        12-30-18     10

New Table
------------------------------------   
Account         Date         Sales  
------------------------------------
Account1        12-29-18     10  
Account1        12-30-18     10  
Account1        12-31-18     5  
Account1        01-01-19     20  
Account2        12-30-18     15  
Account2        12-31-18     20  
Account2        01-01-19     10  
Account3        12-30-18     10  
Account3        12-31-18     20  
Account3        01-01-19     5  

Output
------------------------------------   
Account         Date         Sales  
------------------------------------
Account1        12-29-18     10  
Account1        12-30-18     10  
Account1        12-31-18     5  
Account1        01-01-19     20  
Account2        12-29-18     10
Account2        12-30-18     15  
Account2        12-31-18     20  
Account2        01-01-19     10
Account3        12-29-18     20  
Account3        12-30-18     10
Account3        12-31-18     20  
Account3        01-01-19     5

sql google-bigquery union
person Zach Goodman    schedule 03.01.2019    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Ниже приведен стандартный SQL BigQuery.

  #standardSQL
  SELECT account, date, 
    ARRAY_AGG(sales ORDER BY data LIMIT 1)[OFFSET(0)] sales
  FROM (
    SELECT 'old' data, * FROM `project.dataset.old_table` UNION ALL 
    SELECT 'new' data, * FROM `project.dataset.new_table` 
  )
  GROUP BY account, date

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

  #standardSQL
  WITH `project.dataset.old_table` AS (
    SELECT 'Account1' account, '12-29-18' date, 10 sales UNION ALL  
    SELECT 'Account1', '12-30-18', 10 UNION ALL  
    SELECT 'Account1', '12-31-18', 5 UNION ALL  
    SELECT 'Account2', '12-29-18', 10 UNION ALL    
    SELECT 'Account3', '12-29-18', 20 UNION ALL  
    SELECT 'Account3', '12-30-18', 10 
  ),  `project.dataset.new_table` AS (
    SELECT 'Account1' account, '12-29-18' date, 10 sales UNION ALL
    SELECT 'Account1', '12-30-18', 10 UNION ALL
    SELECT 'Account1', '12-31-18', 5 UNION ALL
    SELECT 'Account1', '01-01-19', 20 UNION ALL
    SELECT 'Account2', '12-30-18', 15 UNION ALL
    SELECT 'Account2', '12-31-18', 20 UNION ALL
    SELECT 'Account2', '01-01-19', 10 UNION ALL
    SELECT 'Account3', '12-30-18', 10 UNION ALL
    SELECT 'Account3', '12-31-18', 20 UNION ALL
    SELECT 'Account3', '01-01-19', 5 
  )
  SELECT account, date, 
    ARRAY_AGG(sales ORDER BY data LIMIT 1)[OFFSET(0)] sales
  FROM (
    SELECT 'old' data, * FROM `project.dataset.old_table` UNION ALL 
    SELECT 'new' data, * FROM `project.dataset.new_table` 
  )
  GROUP BY account, date
  ORDER BY account, PARSE_DATE('%m-%d-%y', date)

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

Row account     date        sales    
1   Account1    12-29-18    10   
2   Account1    12-30-18    10   
3   Account1    12-31-18    5    
4   Account1    01-01-19    20   
5   Account2    12-29-18    10   
6   Account2    12-30-18    15   
7   Account2    12-31-18    20   
8   Account2    01-01-19    10   
9   Account3    12-29-18    20   
10  Account3    12-30-18    10   
11  Account3    12-31-18    20   
12  Account3    01-01-19    5
person Mikhail Berlyant    schedule 03.01.2019
comment
Это сделало работу, большое спасибо! У меня есть пара вопросов, если бы вы могли уточнить для меня. Что именно делает функция ARRAY_AGG? Я просмотрел его, но не могу понять. Кроме того, почему упорядочение данных ограничивается «старыми» данными? Разве упорядочивание по данным, а затем ограничение его использования не будет использовать «новые» данные, поскольку «новые» в алфавитном порядке будут сверху после заказа? - person Zach Goodman; 09.01.2019
comment
Да, конечно. Комментарий снова, чтобы убедиться, что вы видите комментарий редактирования выше - person Zach Goodman; 09.01.2019
comment
ARRAY_AGG с GROUP BY объединяет все sales по account, date, затем упорядочивает массив по данным и оставляет только верхний элемент. наконец, OFFSET(0) берет этот первый (и единственный) элемент из массива - person Mikhail Berlyant; 09.01.2019