SQL实现补全全年月份下已停用账单编码的YTD交易行

业务规则 #

• 输入表InputTable存储LocationID维度下不同账单编码（billing code）的月度交易记录，字段包含Year、LocationID、Month、invoiceID、code、amt1、amt ytd
• 补数规则：当年任意月份存在交易的账单编码，需在当年12个YTD统计周期的结果中全量保留
  • 编码停用无交易的月份，amt1字段填0
  • 编码停用无交易的月份，amt ytd字段取该编码最后一次产生交易时的YTD累计值
• 样例验证：编码F全年有交易无需补行；编码G仅1月、6月有交易，补行后1-5月amt ytd取1月累计值10，7-12月amt ytd取6月累计值11，无交易月份amt1均为0

原有代码问题 #

原有SQL存在字段拼写错误（如ocationID漏写前缀、混用未在输入表定义的post_date_month/base_amount字段）、关联逻辑错误（无条件左连产生笛卡尔积）、未实现月份骨架补全和YTD值向前填充的核心逻辑，无法输出正确结果。

原有错误代码如下：

WITH temp1 AS
(select   year,invoiceID,locationID,count(1)
FROM     InputTable
GROUP BY year, invoiceID,ocationID HAVING   count(1)=1)
,
temp2 AS
(  SELECT *  FROM   InputTable
  WHERE  concat(year,'_',invoiceID,'_',locationID) in 
      (  SELECT   concat(b.year, '_',b.invoiceID,'_',b.locationID)  FROM    InputTable b
                   GROUP BY b.year,  b.invoiceID, b.locationID
                   HAVING   count (1)>1 
      ) 
) 
SELECT DISTINCT 
  c.year,
                CASE  WHEN c.code=d.code THEN c.post_date_month  ELSE c.post_date_month END AS post_date_month,
                c.invoiceID,
                CASE WHEN c.code=d.code THEN c.locationID  ELSE d.locationID END AS locationID,
                CASE WHEN c.code=d.code THEN c.code  ELSE d.code END AS code,
                CASE  WHEN c.code=d.code THEN c.base_amount ELSE d.base_amount END AS base_amount,
                CASE  WHEN c.code=d.code THEN c.base_amount_ytd  ELSE d.base_amount_ytd  END AS base_amount_ytd
FROM            (
                                SELECT DISTINCT b.*
                                FROM            temp1 a
                                INNER JOIN      InputTable b
                                ON              a.year=b.year   AND  a.invoiceID=b.invoiceID AND a.locationID=b.locationID
                ) c
LEFT JOIN       temp2 d
ON              1=1
;

正确实现方案 #

实现逻辑：

1. 生成1-12月的标准月份序列，作为全年统计周期的基准
2. 提取所有当年产生过交易的（年、LocationID、invoiceID、账单编码）维度组合，与月份序列做笛卡尔积，生成每个账单编码全年12个月的完整数据骨架
3. 骨架左关联原交易表，提取已有月份的交易金额，无交易月份amt1直接置0
4. 用窗口函数按维度分区、按月份排序，向前填充最后一次非空的YTD累计值，得到最终结果

可直接运行的SQL代码（适配Hive、Spark SQL、BigQuery、MySQL 8.0+等支持窗口函数的引擎）：

WITH 
-- 生成1-12月标准月份序列，有公共日期维度表可直接替换该部分
month_series AS (
    SELECT 1 AS Month UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
    UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
),
-- 提取所有当年有交易的维度组合
dim_code AS (
    SELECT DISTINCT Year, LocationID, invoiceID, code
    FROM InputTable
),
-- 生成每个账单编码全年12个月的完整数据骨架
full_skeleton AS (
    SELECT 
        d.Year,
        d.LocationID,
        m.Month,
        d.invoiceID,
        d.code
    FROM dim_code d
    CROSS JOIN month_series m
),
-- 关联原表提取真实交易数据
joined_data AS (
    SELECT 
        s.Year,
        s.LocationID,
        s.Month,
        s.invoiceID,
        s.code,
        COALESCE(t.amt1, 0) AS amt1,
        t.`amt ytd` AS raw_amt_ytd
    FROM full_skeleton s
    LEFT JOIN InputTable t
        ON s.Year = t.Year
        AND s.LocationID = t.LocationID
        AND s.Month = t.Month
        AND s.invoiceID = t.invoiceID
        AND s.code = t.code
)
-- 向前填充最后一次交易的YTD值，输出最终结果
SELECT 
    Year,
    LocationID,
    Month,
    invoiceID,
    code,
    amt1,
    LAST_VALUE(raw_amt_ytd IGNORE NULLS) OVER (
        PARTITION BY Year, LocationID, invoiceID, code
        ORDER BY Month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS `amt ytd`
FROM joined_data
ORDER BY Year, LocationID, invoiceID, code, Month;

注意事项：

• 若使用的引擎不支持LAST_VALUE(... IGNORE NULLS)语法（如低版本MySQL），可通过会话变量顺序遍历填充、或给非空YTD值打分组标记后关联填充的方式实现同等效果
• 若数仓有公共日期维度表，可直接替换手动生成月份序列的CTE，过滤对应年份的1-12月即可

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