Skip to content
Advertisement

How to do calculate recursive sum in SQL in configurable in Big Query

I have query problem, I’m using Google BigQuery (Just to give you context if it is different). I need two value which is Value_A and Value_B. Value_A is top X value, and Value_B is the rest of top X value.Here’s my Input Table

Date   Value 
20     10
19     10
18     10
17     10
16     10
15     10
14     10
13     10
12     10
11     10
10     10
9      10
8      10
7      10
6      10
5      10
4      10
3      10
2      10
1      10

In this case, the value of X is 6, but I need to be configurable.

In date 20, Value_A is sum from the 6 top data in Value (which is date 14 to 20), and Value_B is sum of rest of top data (which is date 14 an below).

In date 19, Value_A is sum from the 6 top data in Value (which is date 13 to 19), and Value_B is sum of rest of top data (which is date 13 an below).

Here’s my output

Date   Value  Value_A    Value_B     
20     10     60         140
19     10     60         130
18     10     60         120
17     10     60         110
16     10     60         100
15     10     60          90
14     10     60          80   
13     10     60          70
12     10     60          60
11     10     60          50
10     10     60          40
9      10     60          30
8      10     60          20
7      10     60          10
6      10     60           0
5      10     50           0
4      10     40           0
3      10     30           0
2      10     20           0
1      10     10           0

Advertisement

Answer

Below is for BigQuery Standard SQL

#standardSQL
SELECT date, value, 
  SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) Value_A,
  IFNULL(SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING), 0) Value_B
FROM `project.dataset.table`
-- ORDER BY date DESC   

If to apply to sample data from your question, as in below example

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 20 date, 10 value UNION ALL
  SELECT 19, 10 UNION ALL
  SELECT 18, 10 UNION ALL
  SELECT 17, 10 UNION ALL
  SELECT 16, 10 UNION ALL
  SELECT 15, 10 UNION ALL
  SELECT 14, 10 UNION ALL
  SELECT 13, 10 UNION ALL
  SELECT 12, 10 UNION ALL
  SELECT 11, 10 UNION ALL
  SELECT 10, 10 UNION ALL
  SELECT 9, 10 UNION ALL
  SELECT 8, 10 UNION ALL
  SELECT 7, 10 UNION ALL
  SELECT 6, 10 UNION ALL
  SELECT 5, 10 UNION ALL
  SELECT 4, 10 UNION ALL
  SELECT 3, 10 UNION ALL
  SELECT 2, 10 UNION ALL
  SELECT 1, 10 
)
SELECT date, value, 
  SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING) Value_A,
  IFNULL(SUM(value) OVER(ORDER BY date DESC ROWS BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING), 0) Value_B
FROM `project.dataset.table`
ORDER BY date DESC 

result is

Row date    value   Value_A Value_B  
1   20      10      60      140  
2   19      10      60      130  
3   18      10      60      120  
4   17      10      60      110  
5   16      10      60      100  
6   15      10      60      90   
7   14      10      60      80   
8   13      10      60      70   
9   12      10      60      60   
10  11      10      60      50   
11  10      10      60      40   
12  9       10      60      30   
13  8       10      60      20   
14  7       10      60      10   
15  6       10      60      0    
16  5       10      50      0    
17  4       10      40      0    
18  3       10      30      0    
19  2       10      20      0    
20  1       10      10      0    
User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement