基于每日余额计算用户利润：SQL日期连接去重补空问题

核心思路 #

要实现用最新可用余额填补无交易日期的空缺，并计算每日利润，关键步骤是：

1. 生成目标月份（2024年2月）的完整日期序列
2. 为每个日期匹配最近且不晚于该日期的交易余额
3. 基于匹配到的余额计算利润（余额÷3）

解决方案代码 #

方法1：关联子查询（简洁直观）

先生成2024年2月的所有日期，再通过子查询获取每个日期对应的最新余额：

-- 生成2024年2月完整日期表
WITH Feb2024Dates AS (
    SELECT 
        DATEADD(day, value, '2024-02-01') AS Datum
    FROM 
        GENERATE_SERIES(0, DATEDIFF(day, '2024-02-01', '2024-02-29'))
)
-- 计算每日利润
SELECT 
    fd.Datum,
    -- 获取当前日期及之前的最新余额
    (SELECT TOP 1 Balance 
     FROM vTnxDaily 
     WHERE DateOfDay <= fd.Datum 
     ORDER BY DateOfDay DESC) AS LatestBalance,
    -- 计算利润，保留2位小数
    ROUND((SELECT TOP 1 Balance 
           FROM vTnxDaily 
           WHERE DateOfDay <= fd.Datum 
           ORDER BY DateOfDay DESC)/3.0, 2) AS Profit
FROM Feb2024Dates fd
ORDER BY fd.Datum;

方法2：窗口函数（大数据量更高效）

通过交叉连接匹配所有符合条件的余额，再用ROW_NUMBER()筛选每个日期的最新记录，避免重复子查询：

-- 生成2024年2月完整日期表
WITH Feb2024Dates AS (
    SELECT 
        DATEADD(day, value, '2024-02-01') AS Datum
    FROM 
        GENERATE_SERIES(0, DATEDIFF(day, '2024-02-01', '2024-02-29'))
),
-- 匹配所有日期对应的历史余额并排序
DateBalancePairs AS (
    SELECT 
        fd.Datum,
        v.Balance,
        -- 按日期分组，余额按交易日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY fd.Datum ORDER BY v.DateOfDay DESC) AS rn
    FROM Feb2024Dates fd
    LEFT JOIN vTnxDaily v ON v.DateOfDay <= fd.Datum
)
-- 仅保留每个日期的最新余额记录并计算利润
SELECT 
    Datum,
    Balance AS LatestBalance,
    ROUND(Balance/3.0, 2) AS Profit
FROM DateBalancePairs
WHERE rn = 1
ORDER BY Datum;

为什么之前的方法有问题？ #

• LEFT JOIN直接匹配：只有存在交易记录的日期能匹配到余额，无交易日期会返回NULL，无法自动填充最新余额
• t.Datum >= v.DateOfDay连接：会返回当前日期之前的所有交易记录，导致同一日期出现多条重复行，必须通过排序筛选最新的一条

结果验证 #

根据你的样本数据，最终结果会符合预期：

• 2024-02-01 ~ 2024-02-11：使用余额$420，利润$140.00
• 2024-02-12 ~ 2024-02-19：使用余额$80，利润$26.67
• 2024-02-20 ~ 2024-02-29：使用余额$250，利润$83.33

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