时间戳

首页 > 解决方案 > 如何在 Big Query 中配置的 SQL 中计算递归总和

sql - 如何在 Big Query 中配置的 SQL 中计算递归总和

问题描述

我有查询问题，我正在使用 Google BigQuery（如果它不同，只是为了给你上下文）。我需要两个值，即 Value_A 和 Value_B。Value_A 是前 X 值，而 Value_B 是前 X 值的其余部分。这是我的输入表

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

在这种情况下，X 的值为 6，但我需要可配置。

在日期 20 中，Value_A 是 Value 中前 6 个数据的总和（即日期 14 到 20），而 Value_B 是其余顶部数据的总和（即日期 14 和以下）。

在日期 19 中，Value_A 是 Value 中 6 个顶部数据的总和（日期 13 到 19），Value_B 是其余顶部数据的总和（日期 13 和以下）。

这是我的输出

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

标签： sqlrecursiongoogle-bigquery

解决方案

以下是 BigQuery 标准 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

如果适用于您的问题的样本数据，如下例所示 

#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

结果是 

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

推荐阅读