Выполнение текущего подсчета несекционированных данных в Google bigquery

Question

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

Date  | Priority
----------------
01/01 | Low
02/01 | Low
03/01 | Low
04/01 | Med
05/01 | Med
06/01 | Low
07/01 | High
08/01 | High
09/01 | Med
...

Я хочу добавить столбец, который отображает, сколько дней он находился в текущем приоритете, чтобы он выглядел так:

Date  | Priority | Days in state
--------------------------------
01/01 | Low      | 3
02/01 | Low      | 2
03/01 | Low      | 1
04/01 | Med      | 2
05/01 | Med      | 1
06/01 | Low      | 1
07/01 | High     | 2
08/01 | High     | 1
09/01 | Med      | 1
...

Я изо всех сил пытаюсь сделать это, потому что я не могу разделить данные в их нынешнем виде. При разделении по приоритету учитывается каждое вхождение этого приоритета в истории, а не только текущее "окно".

Я использовал IF(LAG(priority) OVER(ORDER BY date) = priority,1,0) чтобы отмечать изменения, но я не могу понять, что делать дальше.

Answer 1

Это своего рода проблема разрывов и островков. Для ваших целей самый простой способ - вычесть последовательность и использовать оконные функции:

select t.*,
       row_number() over (partition by status, grp order by date date desc) as days_to_next_state
from (select t.*,
             date_add(date, interval - seqnum day) as grp
      from (select t.*,
                   row_number() over (partition by status, order by date) as seqnum
            from t
           ) t
     ) t

Answer 2

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

#standardSQL
SELECT date, Priority, 
  1 + DATE_DIFF(MAX(date) OVER(PARTITION BY grp), date, DAY) Days_in_state
FROM (
  SELECT date, Priority, 
    COUNTIF(start_new_Priority) OVER(ORDER BY date)grp
  FROM (
    SELECT date, Priority, 
      IFNULL(Priority != LAG(Priority) OVER(ORDER BY date), TRUE) start_new_Priority
    FROM `project.dataset.table`
  )
)

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

Row date        Priority    Days_in_state    
1   2019-01-01  Low         3    
2   2019-01-02  Low         2    
3   2019-01-03  Low         1    
4   2019-01-04  Med         2    
5   2019-01-05  Med         1    
6   2019-01-06  Low         1    
7   2019-01-07  High        2    
8   2019-01-08  High        1    
9   2019-01-09  Med         1