基于Data Lake(Hue)的两类支付记录SQL查询需求

原始支付表结构及数据 #

查询1：返回与用户最新支付日期间隔≥6年的支付记录 #

需求 #

提取每个用户的所有支付记录中，支付日期与该用户最新支付日期相差6年及以上的条目。

SQL实现 #

WITH user_latest_pay AS (
    SELECT 
        `Person ID`,
        MAX(Payment_date) AS latest_pay_date
    FROM payment_table
    GROUP BY `Person ID`
)
SELECT 
    p.Payment_Type,
    p.`Person ID`,
    p.Payment_date,
    p.Payment_Amount
FROM payment_table p
JOIN user_latest_pay ulp ON p.`Person ID` = ulp.`Person ID`
WHERE DATEDIFF(ulp.latest_pay_date, p.Payment_date) >= 6 * 365
ORDER BY p.`Person ID`, p.Payment_date;

查询结果 #

查询2：返回「近6年无Normal支付，但有Reversal支付」的用户所有记录 #

需求 #

筛选符合以下条件的用户的全部支付记录：

1. 用户最近一次Normal支付距离今日已超过6年；
2. 用户在近6年内有Reversal支付记录。

SQL实现 #

WITH user_payment_summary AS (
    SELECT 
        `Person ID`,
        MAX(CASE WHEN Payment_Type = 'Normal' THEN Payment_date END) AS last_normal_pay,
        MAX(CASE WHEN Payment_Type = 'Reversal' THEN Payment_date END) AS last_reversal_pay
    FROM payment_table
    GROUP BY `Person ID`
),
eligible_users AS (
    SELECT 
        `Person ID`
    FROM user_payment_summary
    WHERE 
        -- 最近Normal支付距今≥6年
        last_normal_pay <= DATE_ADD(CURRENT_DATE(), INTERVAL -6 YEAR)
        -- 近6年有Reversal支付
        AND last_reversal_pay >= DATE_ADD(CURRENT_DATE(), INTERVAL -6 YEAR)
)
SELECT 
    p.Payment_Type,
    p.`Person ID`,
    p.Payment_date,
    p.Payment_Amount
FROM payment_table p
JOIN eligible_users eu ON p.`Person ID` = eu.`Person ID`
ORDER BY p.`Person ID`, p.Payment_date;

版本兼容说明 #

如果你的Hive版本不支持INTERVAL语法，可替换日期计算为：

-- 替换eligible_users中的WHERE条件
WHERE 
    last_normal_pay <= DATE_ADD(CURRENT_DATE(), -2190) -- 6*365天
    AND last_reversal_pay >= DATE_ADD(CURRENT_DATE(), -2190)

查询结果 #

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