HANA SQL实现日期表与余额表关联并填充缺失日期的客户每日余额

我明白你要解决的是按客户填充每日余额，用最近一次的有效余额值补全空缺日期的问题——这类时间序列数据补全需求确实没法靠简单的左连接搞定，得结合窗口函数来实现。下面我给你两种可行的HANA SQL方案，附带详细解释：

方法一：用 LAST_VALUE 窗口函数直接填充 #

这是最简洁的实现方式，核心思路是先生成「所有客户+所有日期」的完整数据集，再用窗口函数向前填充最近的非空余额值：

WITH CustomerDates AS (
    -- 第一步：生成每个客户对应所有日期的基础数据集
    SELECT 
        dt."Day",
        c."CustomerID"
    FROM "DateTable" dt
    CROSS JOIN (SELECT DISTINCT "CustomerID" FROM "BalanceTable") c
),
FilledBalances AS (
    SELECT 
        cd."Day",
        cd."CustomerID",
        -- 按客户分区、日期排序，取当前及之前最近的非空余额
        LAST_VALUE(bt."Balance" IGNORE NULLS) OVER (
            PARTITION BY cd."CustomerID" 
            ORDER BY cd."Day" 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS "Balance"
    FROM CustomerDates cd
    LEFT JOIN "BalanceTable" bt 
        ON cd."CustomerID" = bt."CustomerID" 
        AND cd."Day" = bt."BalDate"
)
SELECT 
    "Day",
    "CustomerID",
    "Balance"
FROM FilledBalances
ORDER BY "CustomerID", "Day";

关键逻辑解释： #

1. CustomerDates CTE：通过CROSS JOIN生成所有客户与日期的笛卡尔积，确保每个客户都能对应日期表的所有日期（这是左连接做不到的，左连接只会保留日期表中与余额表有匹配的客户行）。
2. LAST_VALUE 窗口函数：
   • PARTITION BY cd."CustomerID"：按客户分组处理每个客户的日期序列
   • ORDER BY cd."Day"：按日期顺序遍历
   • IGNORE NULLS：跳过余额为空的行，直接取最近的非空值
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：只考虑当前日期及之前的记录，避免提前引用未来的余额数据

方法二：先找最近余额日期再关联取值 #

如果你更习惯分步处理，也可以先找到每个客户每日对应的「最近有余额记录的日期」，再关联余额表获取对应值：

WITH CustomerDates AS (
    SELECT 
        dt."Day",
        c."CustomerID"
    FROM "DateTable" dt
    CROSS JOIN (SELECT DISTINCT "CustomerID" FROM "BalanceTable") c
),
LatestBalDate AS (
    SELECT 
        cd."Day",
        cd."CustomerID",
        -- 找到当前日期之前，该客户最后一次有余额记录的日期
        MAX(bt."BalDate") OVER (
            PARTITION BY cd."CustomerID" 
            ORDER BY cd."Day" 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS "LatestBalDate"
    FROM CustomerDates cd
    LEFT JOIN "BalanceTable" bt 
        ON cd."CustomerID" = bt."CustomerID" 
        AND bt."BalDate" <= cd."Day"
)
SELECT 
    lbd."Day",
    lbd."CustomerID",
    bt."Balance"
FROM LatestBalDate lbd
LEFT JOIN "BalanceTable" bt 
    ON lbd."CustomerID" = bt."CustomerID" 
    AND lbd."LatestBalDate" = bt."BalDate"
ORDER BY lbd."CustomerID", lbd."Day";

额外注意事项： #

• 如果某个客户在日期表的起始日期之前没有任何余额记录，填充后的结果会是NULL。如果需要默认值（比如0），可以用COALESCE("Balance", 0)替换最终SELECT里的"Balance"。
• 确保DateTable的日期区间覆盖了BalanceTable中所有客户的余额记录日期，避免出现超出区间的无数据情况。

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