Is there a convenient way to reset a running count created by an analytic function?

user_1481331 0
Viewed 503

Here is what I am looking for:

create table test.test(
col1 boolean,
act_date date)

Sample query:

select 
    col1,
    act_date,
    row_number() over (partition by col1 order by act_date) rnum,
    rank() over (partition by col1 order by act_date) rrnum,
    dense_rank() over (partition by col1 order by act_date) drrnum
from test.test
order by act_date

col1   act_date   rnum   dnum   drnum   whatIwant
t      2018-08-12  1      1      1       1
f      2018-08-13  1      1      1       1
f      2018-08-14  2      2      2       2
t      2018-08-15  2      2      2       1
t      2018-08-16  3      3      3       2
t      2018-08-17  4      4      4       3
f      2018-08-18  3      3      3       1
f      2018-08-19  4      4      4       2
t      2018-08-20  5      5      5       1
t      2018-08-21  6      6      6       2
t      2018-08-22  7      7      7       3
f      2018-08-23  5      5      5       1
f      2018-08-24  6      6      6       2
f      2018-08-25  7      7      7       3
t      2018-08-26  8      8      8       1
t      2018-08-27  9      9      9       2
f      2018-08-28  8      8      8       1
t      2018-08-29  10     10     10      1
t      2018-08-30  11     11     11      2
t      2018-08-31  12     12     12      3

FWIW, my ultimate goal is to isolate the rows for which three or more consecutive rows are false. I would choose from the output where whatIwant >= 3. If there is a different way of accomplishing this task without using analytic functions I'm all ears.

FWIW, my data is in google bigquery.

google-bigquery window-functions sql
1 Answers

Below example for BigQuery Standard SQL 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT TRUE col1, '2018-08-12' act_date UNION ALL 
  SELECT FALSE, '2018-08-13' UNION ALL 
  SELECT FALSE, '2018-08-14' UNION ALL 
  SELECT TRUE, '2018-08-15' UNION ALL 
  SELECT TRUE, '2018-08-16' UNION ALL 
  SELECT TRUE, '2018-08-17' UNION ALL 
  SELECT FALSE, '2018-08-18' UNION ALL 
  SELECT FALSE, '2018-08-19' UNION ALL 
  SELECT TRUE, '2018-08-20' UNION ALL 
  SELECT TRUE, '2018-08-21' UNION ALL 
  SELECT TRUE, '2018-08-22' UNION ALL 
  SELECT FALSE, '2018-08-23' UNION ALL 
  SELECT FALSE, '2018-08-24' UNION ALL 
  SELECT FALSE, '2018-08-25' UNION ALL 
  SELECT TRUE, '2018-08-26' UNION ALL 
  SELECT TRUE, '2018-08-27' UNION ALL 
  SELECT FALSE, '2018-08-28' UNION ALL 
  SELECT TRUE, '2018-08-29' UNION ALL 
  SELECT TRUE, '2018-08-30' UNION ALL 
  SELECT TRUE, '2018-08-31' 
)
SELECT col1, act_date, 
  ROW_NUMBER() OVER(PARTITION BY grp ORDER BY act_date) whatIwant
FROM (
  SELECT col1, act_date, 
    COUNTIF(col1 != prev_value) OVER(ORDER BY act_date) grp
  FROM (
    SELECT col1, act_date, 
      LAG(col1) OVER(ORDER BY act_date) prev_value
    FROM `project.dataset.table`
  )
)
-- ORDER BY act_date

with result 

Row col1    act_date    whatIwant    
1   true    2018-08-12  1    
2   false   2018-08-13  1    
3   false   2018-08-14  2    
4   true    2018-08-15  1    
5   true    2018-08-16  2    
6   true    2018-08-17  3    
7   false   2018-08-18  1    
8   false   2018-08-19  2    
9   true    2018-08-20  1    
10  true    2018-08-21  2    
11  true    2018-08-22  3    
12  false   2018-08-23  1    
13  false   2018-08-24  2    
14  false   2018-08-25  3    
15  true    2018-08-26  1    
16  true    2018-08-27  2    
17  false   2018-08-28  1    
18  true    2018-08-29  1    
19  true    2018-08-30  2    
20  true    2018-08-31  3
user_52219440
Related