CODEWITHSUNDEEP
sqlgoogle-bigquerywindow-functions
DarioB
DarioB

Reputation: 1609

ROWS BETWEEN CURRENT ROW PRECEDING AND EXACT MATCH FOLLOWING

considering the following dummy dataset

ID EVENT VALUE SORT_KEY
1 submitted 10 1
1 action 20 2
1 closed 30 3
1 action 30 4
2 submitted 10 1 
2 action 10 2
2 action 10 3
2 closed 10 4
2 action 10 5
3 action 29 1
3 submitted 20 2
3 action 10 3
3 closed 10 4
3 action 10 5
4 action 10 1

I want to sum all actions per id between submitted (included) and closed. I don't care about events outside these boundaries. I wonder if it is possible to build a window function partitioning by the id and following until a match expression.

Desired result:

ID EVENT VALUE_SUM
1 submitted 60
2 submitted 40
3 submitted 40

A query calculating this would look like something like the following:

SELECT 
    id
  , event
  , SUM(value) OVER (PARTITION BY id ROWS BETWEEN CURRENT ROW PRECEDING AND event='closed' FOLLOWING) as value_sum
FROM my_table
WHERE event = 'submitted'

I am aware that it is maybe possible to do this with multiple joins with itself, but due to the size of the data, and optimisation reason I wonder if it is possible to do this with a window function. Thanks.

Upvotes: 0

Views: 3155

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Below is for BigQuery SQL No JOINs but still just quick sketch so might still be options for refactoring/optimizing 

#standardSQL
SELECT id, SUM(VALUE) AS val
FROM (
  SELECT id, EVENT, VALUE, 
    SUM(boundary) OVER(PARTITION BY ID ORDER BY SORT_KEY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) grp
  FROM (
    SELECT *, 
      COUNTIF(EVENT IN ('submitted', 'closed')) OVER(PARTITION BY ID, EVENT ORDER BY SORT_KEY) boundary
    FROM `project.dataset.table` t
  )
)
WHERE grp = 1
OR (grp = 2 AND EVENT = 'closed')
GROUP BY ID

You can test / play with it using dummy data as in your question: 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 ID, 'submitted' EVENT, 10 VALUE, 1 SORT_KEY UNION ALL
  SELECT 1, 'action', 20, 2 UNION ALL
  SELECT 1, 'closed', 30, 3 UNION ALL
  SELECT 1, 'action', 30, 4 UNION ALL
  SELECT 2, 'submitted', 10, 1 UNION ALL 
  SELECT 2, 'action', 10, 2 UNION ALL
  SELECT 2, 'action', 10, 3 UNION ALL
  SELECT 2, 'closed', 10, 4 UNION ALL
  SELECT 2, 'action', 10, 5 UNION ALL
  SELECT 3, 'action', 29, 1 UNION ALL
  SELECT 3, 'submitted', 20, 2 UNION ALL
  SELECT 3, 'action', 10, 3 UNION ALL
  SELECT 3, 'closed', 10, 4 UNION ALL
  SELECT 3, 'action', 10, 5 UNION ALL
  SELECT 4, 'action', 10, 1 
)
SELECT id, SUM(VALUE) AS val
FROM (
  SELECT id, EVENT, VALUE, 
    SUM(boundary) OVER(PARTITION BY ID ORDER BY SORT_KEY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) grp
  FROM (
    SELECT *, 
      COUNTIF(EVENT IN ('submitted', 'closed')) OVER(PARTITION BY ID, EVENT ORDER BY SORT_KEY) boundary
    FROM `project.dataset.table` t
  )
)
WHERE grp = 1
OR (grp = 2 AND EVENT = 'closed')
GROUP BY ID
ORDER BY ID

and result is 

Row id  val  
1   1   60   
2   2   40   
3   3   40

Upvotes: 4

Related Questions