查询客户交易历史状态：按更新日期统计最早/最晚交易日期

需求分析 #

针对交易表中的每个updated_date，需要输出每个客户的两个关键日期：

• 全局最早交易日期：该客户在所有交易记录里的最早TRANSACTION_DATE（不受当前updated_date限制）
• 截至当前更新日期的最新交易日期：该客户在小于等于当前updated_date的所有记录里，最新的TRANSACTION_DATE

正确SQL实现 #

基础版（保留所有交易记录的对应统计）

SELECT
    updated_date,
    CRM_CUSTOMER_ID,
    -- 客户所有交易中的最早日期
    MIN(TRANSACTION_DATE) OVER (PARTITION BY CRM_CUSTOMER_ID) AS EARLIEST_TRANSACTION_DATE,
    -- 截至当前updated_date，该客户的最新交易日期
    MAX(TRANSACTION_DATE) OVER (PARTITION BY CRM_CUSTOMER_ID, updated_date) AS LATEST_TRANSACTION_DATE
FROM (
    SELECT
        updated_date,
        CRM_CUSTOMER_ID,
        SVC_TRANSACTION_DATE_C AS TRANSACTION_DATE
    FROM PRD_SANITISE.PRD_SALESFORCE.SAN_SFDC_TRANSACTION_HEADER
) t
ORDER BY updated_date, CRM_CUSTOMER_ID;

去重版（每个updated_date+客户仅显示一条汇总）

如果同一个updated_date下同一个客户有多条交易记录，只需要一条汇总结果的话，可以用以下写法：

WITH transaction_data AS (
    SELECT
        updated_date,
        CRM_CUSTOMER_ID,
        SVC_TRANSACTION_DATE_C AS TRANSACTION_DATE
    FROM PRD_SANITISE.PRD_SALESFORCE.SAN_SFDC_TRANSACTION_HEADER
),
aggregated_data AS (
    SELECT
        updated_date,
        CRM_CUSTOMER_ID,
        MIN(TRANSACTION_DATE) OVER (PARTITION BY CRM_CUSTOMER_ID) AS EARLIEST_TRANSACTION_DATE,
        MAX(TRANSACTION_DATE) OVER (PARTITION BY CRM_CUSTOMER_ID, updated_date) AS LATEST_TRANSACTION_DATE,
        ROW_NUMBER() OVER (PARTITION BY updated_date, CRM_CUSTOMER_ID ORDER BY TRANSACTION_DATE DESC) AS row_num
    FROM transaction_data
)
SELECT
    updated_date,
    CRM_CUSTOMER_ID,
    EARLIEST_TRANSACTION_DATE,
    LATEST_TRANSACTION_DATE
FROM aggregated_data
WHERE row_num = 1
ORDER BY updated_date, CRM_CUSTOMER_ID;

原尝试问题说明 #

你之前用的ROW_NUMBER() OVER (PARTITION BY th.updated_date, tr.CRM_CUSTOMER_ID ORDER BY th.updated_date DESC)之所以没效果，是因为分区把updated_date和客户绑定了，只能在单个更新日期的客户组内排序，无法获取客户的全局最早交易日期，也没法直接统计截至当前更新日期的最晚交易日期。改用MIN和MAX窗口函数搭配对应分区，就能精准满足需求。

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