在指定日期范围的HISTORY表中查询金额增长记录的SQL实现问题

我来帮你搞定这个问题，你的核心需求是找出2019年1月1日到7月1日期间金额有增长的记录，而且需要把该时间段之前的最后一条记录和期间内的记录对比纳入考量对吧？先梳理下你之前思路里的几个问题，再给出完整的解决方案：

之前思路的问题点 #

1. 字段名笔误：原表字段是DATE，你误写为createddate；原表关联字段是ID，你写成了userid，这会直接导致子查询关联失败
2. 最后一条记录筛选逻辑有漏洞：用MAX(date) over(partition by id)后筛选date=max_date，如果某个ID在2019-01-01前有多个记录在同一天，会返回多条结果，而我们只需要时间最晚的那一条
3. 缺少跨时间段的金额对比：没有把“时间段前的最后一条记录”和“时间段内的记录”做金额对比，这是你明确要求补充的场景

完整解决方案 #

我们用窗口函数精准获取每个ID的历史最后一条记录和期间内的记录，覆盖两种增长场景：

• 场景1：时间段内自身存在多条记录，且后一条金额高于前一条
• 场景2：时间段内的第一条记录金额高于该ID在2019-01-01之前的最后一条记录金额

完整SQL代码

WITH time_range AS (
    -- 定义时间范围常量，方便后续维护
    SELECT 
        TO_DATE('2019-01-01', 'yyyy-mm-dd') AS start_date,
        TO_DATE('2019-07-01', 'yyyy-mm-dd') AS end_date
),
-- 获取每个ID在目标时间段内的所有记录，按时间升序编号
period_records AS (
    SELECT 
        h.ID,
        h.DATE,
        h.AMOUNT,
        ROW_NUMBER() OVER (PARTITION BY h.ID ORDER BY h.DATE ASC) AS period_row_num
    FROM HISTORY h
    CROSS JOIN time_range tr
    WHERE h.DATE BETWEEN tr.start_date AND tr.end_date
),
-- 获取每个ID在目标时间段之前的最后一条记录（如果存在）
prev_last_records AS (
    SELECT 
        h.ID,
        h.DATE AS prev_last_date,
        h.AMOUNT AS prev_last_amount
    FROM (
        SELECT 
            h.*,
            ROW_NUMBER() OVER (PARTITION BY h.ID ORDER BY h.DATE DESC) AS prev_row_num
        FROM HISTORY h
        CROSS JOIN time_range tr
        WHERE h.DATE < tr.start_date
    ) h
    WHERE h.prev_row_num = 1 -- 只取时间最晚的那一条记录
),
-- 筛选场景1：时间段内存在后一条金额高于前一条的ID
growth_in_period AS (
    SELECT DISTINCT p1.ID
    FROM period_records p1
    JOIN period_records p2 
        ON p1.ID = p2.ID 
        AND p1.period_row_num < p2.period_row_num 
        AND p1.AMOUNT < p2.AMOUNT
),
-- 筛选场景2：时间段第一条记录金额高于之前最后一条记录的ID
growth_from_prev AS (
    SELECT DISTINCT p.ID
    FROM period_records p
    JOIN prev_last_records pr 
        ON p.ID = pr.ID
    WHERE p.period_row_num = 1 -- 取时间段内的第一条记录
        AND p.AMOUNT > pr.prev_last_amount
)
-- 合并两种场景，统计所有符合条件的ID数量
SELECT COUNT(DISTINCT COALESCE(gip.ID, gfp.ID)) AS growth_id_count
FROM growth_in_period gip
FULL OUTER JOIN growth_from_prev gfp 
    ON gip.ID = gfp.ID;

代码解释

1. time_range：把时间范围定义为常量，后续修改只需要调整这里，更易维护
2. period_records：给每个ID在目标时间段内的记录按时间升序编号，方便快速定位第一条记录，以及对比前后记录的金额变化
3. prev_last_records：给每个ID在目标时间段之前的记录按时间降序编号，取编号为1的就是时间最晚的最后一条记录
4. growth_in_period：关联时间段内的记录，筛选出存在金额增长的ID
5. growth_from_prev：将时间段内的第一条记录和之前的最后一条记录对比，筛选出金额增长的ID
6. 最后用FULL OUTER JOIN合并两种场景的ID，去重后统计总数

如果需要获取具体的增长记录（而非仅ID数量），可以调整最后一步的查询，关联回原表获取详细信息。

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