Блог

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

Набор данных, с которым я работаю, будет выглядеть следующим образом

И я бы хотел, чтобы это выглядело следующим образом:

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

Я пытаюсь выполнить это в консоли Google BigQuery.

Вы можете использовать union all и lead() :

 select item, store, start, end, sold
from t
union all
select item, store, dateadd(end, interval 1 day), dateadd(next_start, interval -1 day)
from (select item, store, end, lead(start) over (partition item, store start) as next_start
      from t
     ) t
where next_start  dateadd(end, interval 1 day);
  

Вы можете использовать оконные функции и insert ... select синтаксис для создания «отсутствующих» строк:

 insert into mytable (item, store, start, end, sold)
select 
    item,
    store, 
    dateadd(end, interval 1 day),
    dateadd(lead_start, interval -1 day),
     0
from (
    select 
        t.*,
        lead(start) over(partition by item, store order by start) lead_start
    from mytable t
) t
where lead_start > dateadd(end, interval 1 day)
  

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

 #standardSQL
SELECT * FROM `project.dataset.table` UNION ALL
SELECT * FROM (
  SELECT item, store,
    DATE_ADD(`end`, INTERVAL 1 DAY) new_start,
    DATE_SUB(LEAD(start) OVER(PARTITION BY item, store ORDER BY start), INTERVAL 1 DAY) new_end, 0
  FROM `project.dataset.table` 
)
WHERE new_start <= new_end
  

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

 Row item    store   start       end         sold     
1   5       1       2020-01-01  2020-01-15  22   
2   5       1       2020-01-16  2020-01-31  0    
3   5       1       2020-02-01  2020-02-20  14      
  

В случае, если вы хотите вставить эти «отсутствующие» строки в свою таблицу — вы должны использовать только second select — который

 INSERT INTO `project.dataset.table`
SELECT * FROM (
  SELECT item, store,
    DATE_ADD(`end`, INTERVAL 1 DAY) new_start,
    DATE_SUB(LEAD(start) OVER(PARTITION BY item, store ORDER BY start), INTERVAL 1 DAY) new_end, 0
  FROM `project.dataset.table` 
)
WHERE new_start <= new_end