如何用窗口函数的RANGE子句获取上一年度利润总和

需要从给定数据集中，获取每个国家上一年度的利润总和：

原始数据集与初始SQL #

WITH tbl (year, country, product, profit) AS (
   VALUES
     (2000, 'Finland', 'Computer'  , 1500)
   , (2000, 'Finland', 'Phone'     ,  100)
   , (2001, 'Finland', 'Phone'     ,   10)
   , (2000, 'India'  , 'Calculator',   75)
   , (2000, 'India'  , 'Calculator',   75)
   , (2000, 'India'  , 'Computer'  , 1200)
   )
select country, year, profit
     , lag(profit) over (partition by country order by year) as sum_profit_previous_year
from tbl;

当前执行结果 #

初始SQL仅能获取前一行的利润值，无法得到上一年度的利润总和：

┌─────────┬──────┬────────┬──────────────────────────┐
│ country ┆ year ┆ profit ┆ sum_profit_previous_year │
╞═════════╪══════╪════════╪══════════════════════════╡
│ India   ┆ 2000 ┆     75 ┆                          │
│ India   ┆ 2000 ┆     75 ┆                       75 │
│ India   ┆ 2000 ┆   1200 ┆                       75 │
│ Finland ┆ 2000 ┆   1500 ┆                          │
│ Finland ┆ 2000 ┆    100 ┆                     1500 │
│ Finland ┆ 2001 ┆     10 ┆                      100 │
└─────────┴──────┴────────┴──────────────────────────┘

预期结果 #

仅芬兰2001年的行需显示上一年（2000年）的利润总和1600，其余无上年数据的行该字段为空：

┌─────────┬──────┬────────┬──────────────────────────┐
│ country ┆ year ┆ profit ┆ sum_profit_previous_year │
╞═════════╪══════╪════════╪══════════════════════════╡
│ India   ┆ 2000 ┆     75 ┆                          │
│ India   ┆ 2000 ┆     75 ┆                          │
│ India   ┆ 2000 ┆   1200 ┆                          │
│ Finland ┆ 2000 ┆   1500 ┆                          │
│ Finland ┆ 2000 ┆    100 ┆                          │
│ Finland ┆ 2001 ┆     10 ┆                   1600   │
└─────────┴──────┴────────┴──────────────────────────┘

提问 #

在BigQuery或Postgres中，实现该需求的正确RANGE子句写法是什么？

直接使用lag()无法实现需求，因为它仅提取前一行数据。我们可以通过先聚合年度利润再关联，或窗口函数结合RANGE子句两种方式实现：

方法一：通用写法（Postgres/BigQuery均适用） #

先计算每个国家每年的利润总和，再通过关联将上一年的总和匹配到当前年的所有行：

WITH tbl (year, country, product, profit) AS (
   VALUES
     (2000, 'Finland', 'Computer'  , 1500)
   , (2000, 'Finland', 'Phone'     ,  100)
   , (2001, 'Finland', 'Phone'     ,   10)
   , (2000, 'India'  , 'Calculator',   75)
   , (2000, 'India'  , 'Calculator',   75)
   , (2000, 'India'  , 'Computer'  , 1200)
),
yearly_profit AS (
  SELECT country, year, SUM(profit) AS total_profit
  FROM tbl
  GROUP BY country, year
)
SELECT 
  t.country, 
  t.year, 
  t.profit,
  yp.total_profit AS sum_profit_previous_year
FROM tbl t
LEFT JOIN yearly_profit yp 
  ON t.country = yp.country 
  AND t.year = yp.year + 1;

方法二：窗口函数+RANGE子句 #

Postgres写法 #

利用Postgres支持的RANGE BETWEEN语法，直接在窗口中计算上一年的利润总和：

WITH tbl (year, country, product, profit) AS (
   VALUES
     (2000, 'Finland', 'Computer'  , 1500)
   , (2000, 'Finland', 'Phone'     ,  100)
   , (2001, 'Finland', 'Phone'     ,   10)
   , (2000, 'India'  , 'Calculator',   75)
   , (2000, 'India'  , 'Calculator',   75)
   , (2000, 'India'  , 'Computer'  , 1200)
)
SELECT 
  country, 
  year, 
  profit,
  CASE 
    WHEN year > MIN(year) OVER (PARTITION BY country) 
    THEN SUM(profit) OVER (
      PARTITION BY country 
      ORDER BY year 
      RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
    ) 
  END AS sum_profit_previous_year
FROM tbl;

RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING表示仅包含当前年份减1的所有行，结合SUM()即可得到上一年的利润总和。

BigQuery写法 #

BigQuery的RANGE语法支持时间间隔，写法如下：

WITH tbl AS (
  SELECT * FROM UNNEST([
    STRUCT(2000 AS year, 'Finland' AS country, 'Computer' AS product, 1500 AS profit),
    STRUCT(2000, 'Finland', 'Phone', 100),
    STRUCT(2001, 'Finland', 'Phone', 10),
    STRUCT(2000, 'India', 'Calculator', 75),
    STRUCT(2000, 'India', 'Calculator', 75),
    STRUCT(2000, 'India', 'Computer', 1200)
  ])
)
SELECT 
  country, 
  year, 
  profit,
  CASE 
    WHEN year > MIN(year) OVER (PARTITION BY country) 
    THEN SUM(profit) OVER (
      PARTITION BY country 
      ORDER BY year 
      RANGE BETWEEN INTERVAL 1 YEAR PRECEDING AND INTERVAL 1 YEAR PRECEDING
    ) 
  END AS sum_profit_previous_year
FROM tbl;

内容的提问来源于stack exchange，提问作者David542