Row count to repeat in a pattern BigQuery

Question

I am trying to create a row count pattern that would look something like this

|Id  | Row_Count |
|--- | --------- |
|1   | 1        |
|2   | 2         |
|3   | 3         |
|4   | 4         |
|5   | 5         |
|6   | 1         |
|7   | 2         |
|8   | 3         |
|9   | 4         |
|10  | 5         |
|11  | 1         |
|12  | 2         |
|13  | 3         |
|14  | 4         |
|15  | 5         |
|16  | 6         |
|17  | 1         |
|18  | 2         |
|19  | 3         |
|20  | 4         |
|21  | 5          |

I would like to have the count go up to 5 twice then go up to 6 once then go up to 5 twice again then 6 and so on. I can’t seem to find a way to do that.

Answer 1

Use below (BigQuery Standard SQL)

SELECT *, 
  IF(MOD(id - 1, 16) + 1 = 16, 6, MOD(MOD(id - 1, 16), 5) + 1) row_count 
FROM your_table      

with output

you can test it with below

WITH your_table AS (
  SELECT id
  FROM UNNEST(GENERATE_ARRAY(1,50)) id
)
SELECT *, 
  IF(MOD(id - 1, 16) + 1 = 16, 6, MOD(MOD(id - 1, 16), 5) + 1) row_count 
FROM your_table     

Answer 2

How would this work if instead of ID I had a date field like 2013-01-01, 2013-01-02,…?

Use below approach

SELECT day,  
  IF(MOD(pos - 1, 16) + 1 = 16, 6, MOD(MOD(pos - 1, 16), 5) + 1) row_count 
FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY day) pos
  FROM your_table
)     

with output

you can test it using below

WITH your_table AS (
  SELECT day
  FROM UNNEST(GENERATE_DATE_ARRAY(CURRENT_DATE() - 50, CURRENT_DATE())) day
)
SELECT day,  
  IF(MOD(pos - 1, 16) + 1 = 16, 6, MOD(MOD(pos - 1, 16), 5) + 1) row_count 
FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY day) pos
  FROM your_table
)

Below is most generic query that you can adjust to whatever configuration you need - currently it covers 5+5+9, but can easily be adjusted to any!!!

SELECT day,  
  CASE
    WHEN DIV(MOD(pos - 1, 19), 5) < 2 THEN MOD(MOD(pos - 1, 19), 5) + 1
    ELSE MOD(MOD(pos - 1, 19) - 1, 9) + 1
  END AS row_count 
FROM (
  SELECT *, ROW_NUMBER() OVER(ORDER BY day) pos
  FROM your_table
)