如何获取账户当日最新值，无有效值时取历史最大值？

原始数据表 #

需求 #

传入当前日期（示例为'2023-06-08'），实现：

• 每个账户取当日的最新值；
• 若当日VALUE为0，则取该账户截止到当日的历史最大值，并取该最大值对应的最新日期。

尝试的SQL（未得到期望结果） #

set @curdate = '2023-06-08 ';
select DATE,ACCOUNT,
       CASE WHEN DATE = @curdate THEN VALUE 
            WHEN VALUE = 0 AND DATE = @curdate THEN MAX(VALUE)
            ELSE 0
       END AS MAX_VALUE
from table ;

期望输出 #

正确SQL解决方案 #

方案1（通用SQL，兼容多数数据库） #

WITH account_daily AS (
    -- 获取每个账户当日的记录
    SELECT 
        DATE,
        ACCOUNT,
        VALUE
    FROM your_table
    WHERE DATE = '2023-06-08'
),
account_history_max AS (
    -- 获取每个账户截止到当日的历史最大值，以及对应最新日期
    SELECT 
        ACCOUNT,
        MAX(VALUE) AS max_val,
        MAX(DATE) AS max_date
    FROM your_table
    WHERE DATE <= '2023-06-08'
      AND VALUE > 0
    GROUP BY ACCOUNT
)
SELECT 
    CASE 
        WHEN ad.VALUE != 0 THEN ad.DATE 
        ELSE ahm.max_date 
    END AS DATE,
    ad.ACCOUNT,
    CASE 
        WHEN ad.VALUE != 0 THEN ad.VALUE 
        ELSE ahm.max_val 
    END AS VALUE
FROM account_daily ad
JOIN account_history_max ahm ON ad.ACCOUNT = ahm.ACCOUNT;

方案2（MySQL 8.0+专用，窗口函数简化） #

SET @curdate = '2023-06-08';

WITH ranked_history AS (
    SELECT 
        DATE,
        ACCOUNT,
        VALUE,
        -- 按VALUE降序、DATE降序排名，取每个账户的最优历史记录
        ROW_NUMBER() OVER (
            PARTITION BY ACCOUNT 
            ORDER BY VALUE DESC, DATE DESC
        ) AS rn
    FROM your_table
    WHERE DATE <= @curdate
),
daily_record AS (
    SELECT * FROM your_table WHERE DATE = @curdate
)
SELECT
    CASE 
        WHEN dr.VALUE != 0 THEN dr.DATE 
        ELSE rh.DATE 
    END AS DATE,
    dr.ACCOUNT,
    CASE 
        WHEN dr.VALUE != 0 THEN dr.VALUE 
        ELSE rh.VALUE 
    END AS VALUE
FROM daily_record dr
LEFT JOIN ranked_history rh 
    ON dr.ACCOUNT = rh.ACCOUNT 
    AND rh.rn = 1;

错误说明 #

你之前的SQL存在三个核心问题：

1. 未按账户分组，MAX(VALUE)无法计算每个账户的历史最大值；
2. CASE逻辑错误，无法区分当日值为0的场景，也未关联历史数据；
3. 全表查询返回所有行，不符合"每个账户一条结果"的要求。

上述方案通过CTE拆分逻辑，先处理当日数据，再获取历史最优数据，最后关联得到符合需求的结果。

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