Блог

У меня есть данные о том, как набор клиентов ежедневно тратит деньги, со следующей структурой в BigQuery:

 CREATE TABLE if not EXISTS daily_spend (
  user_id int,
  created_at DATE,
  value float
);

INSERT INTO daily_spend VALUES
(1, '2021-01-01', 0),
(1, '2021-01-02', 1),
(1, '2021-01-04', 1),
(1, '2021-01-05', 2),
(1, '2021-01-07', 5),
(2, '2021-01-01', 5),
(2, '2021-01-03', 0),
(2, '2021-01-04', 1),
(2, '2021-01-06', 2);
 

Я хотел бы заполнить данные для каждого пользователя, поставив 0 в дни, когда пользователи не тратили никаких денег, включая только дни между их первым и последним днями, когда они тратили деньги.

Таким образом, выходная таблица в этом примере будет иметь следующие значения:

 (1, '2021-01-01', 0),
(1, '2021-01-02', 1),
(1, '2021-01-03', 0),
(1, '2021-01-04', 1),
(1, '2021-01-05', 2),
(1, '2021-01-06', 0),
(1, '2021-01-07', 5),
(2, '2021-01-01', 5),
(2, '2021-01-02', 0),
(2, '2021-01-03', 0),
(2, '2021-01-04', 1),
(2, '2021-01-05', 0),
(2, '2021-01-06', 2)
 

Каков самый простой способ сделать это в BigQuery?

Используйте ниже

 select user_id, created_at, ifnull(value, 0) value
from (
  select user_id, min(created_at) min_date, max(created_at) max_date
  from daily_spend
  group by user_id
), unnest(generate_date_array(min_date, max_date)) created_at
left join daily_spend
using(user_id, created_at)         
 

Если применить к образцам данных в вашем вопросе — вывод будет