SQL Query：筛选付款日期间隔大于或等于6年的记录

2026-10-24支付阿杰

结论

两个需求可以分别用窗口函数和条件聚合处理：

Query 1：计算每个 Person ID 的最新付款日期，筛选早于或等于该日期往前 6 年的记录。
Query 2：找出符合条件的人员，返回其最近一次 Normal 付款，以及最近 6 年内的 Reversal 记录。

题目给出的 Query 1 期望结果与”相对最新付款日期间隔至少 6 年”的条件并不一致。Person 1 的最新日期是 2022-01-09，往前 6 年的临界日期是 2016-01-09，所以 2017-01-01 不应返回。Person 2 的两笔记录间隔也不足 6 年。

下面的示例按严格的”完整日历 6 年”规则编写。

Query 1：按每个人的最新付款日期筛选

WITH payment_with_latest AS (
    SELECT
        t.*,
        CAST(t.payment_date AS DATE) AS payment_dt,
        MAX(CAST(t.payment_date AS DATE))
            OVER (PARTITION BY t.person_id) AS latest_payment_dt
    FROM payments t
)
SELECT
    payment_type,
    person_id,
    payment_date,
    payment_amount
FROM payment_with_latest
WHERE payment_dt <= ADD_MONTHS(latest_payment_dt, -72)
ORDER BY person_id, payment_dt;

ADD_MONTHS(latest_payment_dt, -72) 会从最新付款日期往前推 72 个月，也就是 6 个完整日历年。

按示例数据计算：

Person 1 的临界日期是 2016-01-09，只有 2015-01-01 符合条件。
Person 2 的临界日期是 2016-01-02，因此 2016-12-29 不符合条件。

严格按此规则，结果只有：

Normal | 1 | 2015-01-01 | £1.00

如果只检查 Normal 付款，可以增加一个条件：

WHERE payment_type = 'Normal'
  AND payment_dt <= ADD_MONTHS(latest_payment_dt, -72)

如果实际需求是”返回每个人除最新一笔以外的所有 Normal 付款”，那么问题与 6 年间隔无关，需要改用排序：

WITH ranked_payments AS (
    SELECT
        t.*,
        ROW_NUMBER() OVER (
            PARTITION BY person_id
            ORDER BY CAST(payment_date AS DATE) DESC
        ) AS rn
    FROM payments t
)
SELECT
    payment_type,
    person_id,
    payment_date,
    payment_amount
FROM ranked_payments
WHERE payment_type = 'Normal'
ORDER BY person_id, CAST(payment_date AS DATE);

Query 2：至少 6 年没有 Normal，但最近 6 年有 Reversal

根据期望结果，“相关记录”在这里指：

符合条件人员最近一次 Normal 付款；
该人员最近 6 年内的所有 Reversal 记录。

WITH prepared AS (
    SELECT
        t.*,
        CAST(t.payment_date AS DATE) AS payment_dt
    FROM payments t
),

eligible_people AS (
    SELECT
        person_id
    FROM prepared
    GROUP BY person_id
    HAVING
        MAX(
            CASE
                WHEN payment_type = 'Normal' THEN payment_dt
            END
        ) <= ADD_MONTHS(CURRENT_DATE, -72)
        AND
        MAX(
            CASE
                WHEN payment_type = 'Reversal'
                 AND payment_dt >= ADD_MONTHS(CURRENT_DATE, -72)
                 AND payment_dt <= CURRENT_DATE
                THEN 1
                ELSE 0
            END
        ) = 1
),

ranked_normal AS (
    SELECT
        p.*,
        ROW_NUMBER() OVER (
            PARTITION BY p.person_id
            ORDER BY p.payment_dt DESC
        ) AS rn
    FROM prepared p
    JOIN eligible_people e
      ON p.person_id = e.person_id
    WHERE p.payment_type = 'Normal'
),

result_rows AS (
    SELECT
        payment_type,
        person_id,
        payment_date,
        payment_amount,
        payment_dt
    FROM ranked_normal
    WHERE rn = 1

    UNION ALL

    SELECT
        p.payment_type,
        p.person_id,
        p.payment_date,
        p.payment_amount,
        p.payment_dt
    FROM prepared p
    JOIN eligible_people e
      ON p.person_id = e.person_id
    WHERE p.payment_type = 'Reversal'
      AND p.payment_dt >= ADD_MONTHS(CURRENT_DATE, -72)
      AND p.payment_dt <= CURRENT_DATE
)

SELECT
    payment_type,
    person_id,
    payment_date,
    payment_amount
FROM result_rows
ORDER BY person_id, payment_dt;

这段查询中：

第一个 MAX(CASE ...) 取出每个人最近一次 Normal 付款的日期。
<= ADD_MONTHS(CURRENT_DATE, -72) 用来判断该日期距今是否至少 6 年。
第二个条件检查最近 6 年内是否至少有一笔 Reversal。
ROW_NUMBER() 保证只返回最近一次 Normal，与题目给出的期望结果一致。

如果要返回符合条件人员的所有历史 Normal 记录，而不是只返回最近一次，可以删除 ranked_normal 部分的以下条件：

WHERE rn = 1

Hue 环境中的注意事项

Hue 是查询界面，具体支持哪些语法取决于它连接的执行引擎，例如 Hive、Impala 或 Spark SQL。上面的写法适用于常见的 Hive 风格 SQL，但还需注意以下几点：

某些引擎要求使用 CURRENT_DATE()，而不是 CURRENT_DATE。
如果列名确实是包含空格的 Person ID，需要加反引号：

`Person ID`

建议将该列名改为 person_id。

如果 payment_date 已经是 DATE 类型，可以去掉 CAST(... AS DATE)。
不建议用固定的 6 * 365 天来判断 6 年，因为闰年会造成边界误差。ADD_MONTHS(..., -72) 更符合”6 个日历年”的含义。
Payment_Amount 最好使用数值类型，例如 DECIMAL(12,2)。货币符号应单独存放，或在展示时添加。