Блог

Я работаю над улучшением набора данных, создавая столбец, который позволил бы мне отслеживать, сколько активных кварталов было у данной компании для данной строки. Компания является «активной», если она признает доход в течение этого квартала. Каждая строка моего набора данных представляет производительность за один месяц для одной компании.

Я смог успешно использовать оконную функцию для создания текущей суммы за активные месяцы:

 COUNTIF(Revenue IS NOT NULL) OVER 
(partition by Company_Name ORDER BY month_end ASC ROWS BETWEEN unbounded preceding and current row) AS cumulative_active_months
  

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

Это приблизительное представление о том, как выглядит моя таблица в настоящее время.

   Row   Month   Month_end    Fiscal_Quarter   Company_Name   Revenue   Active month count  
 ----- ------- ------------ ---------------- -------------- --------- -------------------- 
  1     Jul     2016-07-31   FY17-Q2          Foo            x,xxx     1                   
  2     Jul     2016-07-31   FY17-Q2          Bar            xxx,xxx   1                   
  3     Aug     2016-08-31   FY17-Q2          Foo            xx,xxx    2                   
  4     Aug     2016-08-31   FY17-Q2          Bar            xxx       2                   
  5     Sep     2016-09-30   FY17-Q2          Foo            xx        3                   
  6     Sep     2016-09-30   FY17-Q2          Bar            x,xxx     3                   
  7     Oct     2016-10-31   FY17-Q3          Foo            xx        4                   
  8     Oct     2016-10-31   FY17-Q3          Bar            Null      3                 
  

В идеале я хотел бы, чтобы моя таблица выглядела так.

   Row   Month   Month_end    Fiscal_Quarter   Company_Name   Revenue   Active month count   Active quarter count  
 ----- ------- ------------ ---------------- -------------- --------- -------------------- ---------------------- 
  1     Jul     2016-07-31   FY17-Q2          Foo            x,xxx     1                    1                     
  2     Jul     2016-07-31   FY17-Q2          Bar            xxx,xxx   1                    1                     
  3     Aug     2016-08-31   FY17-Q2          Foo            xx,xxx    2                    1                     
  4     Aug     2016-08-31   FY17-Q2          Bar            xxx       2                    1                     
  5     Sep     2016-09-30   FY17-Q2          Foo            xx        3                    1                     
  6     Sep     2016-09-30   FY17-Q2          Bar            x,xxx     3                    1                     
  7     Oct     2016-10-31   FY17-Q3          Foo            xx        4                    2                     
  8     Oct     2016-10-31   FY17-Q3          Bar            Null      3                    1                     
  

Если это подсчет активных месяцев:

 COUNTIF(Revenue IS NOT NULL) OVER (PARTITION BY Company_Name  ORDER BY month_end ASC) AS cumulative_active_months
  

Тогда это соответствующее количество для кварталов будет использоваться COUNT(DISTINCT) :

 COUNT(DISTINCT CASE WHEN Revenue IS NOT NULL THEN Fiscal_Quarter END)  OVER (PARTITION BY Company_Name ORDER BY month_end ASC) AS cumulative_active_quarters
  

К сожалению, BigQuery не поддерживает это, поэтому вы можете использовать подзапрос и накопительную сумму:

 select t.* except (seqnum),
       countif(seqnum = 1) over (partition by company_name order by month_end) as cnt
from (select t.*,
             (case when revenue is not null
                   then row_number() over (partition by Company_Name, Fiscal_Quarter order by month_end)
                   else 0
              end) as seqnum
      from t
     ) t;
  

Примечание: это не учитывает текущий квартал, пока не появится доход, что, я думаю, имеет смысл.