BigQuery中按规则生成DateFinal列的技术实现问询

需求说明 #

• 按Gruopid字段分组
• 当Orden=1时，DateFinal = DateStart + Days
• 当Orden>1时，DateFinal = 上一行的DateFinal + 当前行的Days

原尝试代码 #

WITH Table0 AS 
(SELECT 
  Gruopid,
  DateStart,
  Orden,
  Days
FROM `project.test`
ORDER BY Gruopid,Orden),

Table1 AS
(SELECT
  *,
  CASE 
    WHEN Orden =1 THEN DATE_ADD(DateStart, INTERVAL Days DAY) 
    ELSE NULL END AS DateFinal
FROM Table0)


SELECT 
  *,
  CASE
  WHEN Orden> 1 THEN DATE_ADD(LAG(DateFinal) OVER (PARTITION BY Gruopid ORDER BY Orden),INTERVAL Days DAY)
  ELSE NULL END AS DateFinal
FROM Table1

问题分析 #

原代码无法实现需求的核心原因：Table1中仅Orden=1的行有DateFinal值，后续行使用LAG(DateFinal)会获取到NULL，无法完成递推累加。

正确实现代码 #

WITH Table0 AS (
  SELECT 
    Gruopid,
    DateStart,
    Orden,
    Days,
    -- 计算组内按Orden排序的累计天数
    SUM(Days) OVER (PARTITION BY Gruopid ORDER BY Orden) AS TotalDays
  FROM `project.test`
)
SELECT
  Gruopid,
  DateStart,
  Orden,
  Days,
  DATE_ADD(DateStart, INTERVAL TotalDays DAY) AS DateFinal
FROM Table0
ORDER BY Gruopid, Orden;

代码说明 #

1. 使用窗口函数SUM(Days) OVER (PARTITION BY Gruopid ORDER BY Orden)，实现每个Gruopid分组内，按Orden排序的天数累加：
   • Orden=1时，TotalDays等于当前行的Days
   • Orden>1时，TotalDays等于从Orden=1到当前行的所有Days之和
2. 直接用初始的DateStart加上累计的TotalDays，即可得到符合要求的DateFinal值

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