查询关联值发生变更的账户的所有历史账户记录

嘿，针对你这个时变账户表的查询需求，我来给你梳理清晰的解决方案。先明确下业务规则：当账户的关联值（比如到期日）发生变更时，旧记录会被设置结束日期，同时生成一条次日生效的新记录；未变更的账户只会有一条结束日期为12/31/9000的最新记录（比如例子里的44444444），这类账户需要排除。

我们的目标是：找出所有发生过关联值变更的账户，并返回这些账户的全部历史记录。

方案一：用窗口函数直接标记并筛选 #

这种方法通过一次表扫描，用窗口函数标记每个账户是否存在变更，然后直接筛选出目标记录。假设你的表名为time_variant_accounts，核心字段包括account_id（账户ID）、effective_date（生效日期）、end_date（结束日期）、expiry_date（关联值，比如到期日）：

WITH account_change_flags AS (
    SELECT 
        account_id,
        effective_date,
        end_date,
        expiry_date,
        -- 获取当前记录的上一条关联值
        LAG(expiry_date) OVER (PARTITION BY account_id ORDER BY effective_date) AS prev_expiry_date,
        -- 标记该账户是否存在任何一次关联值变更
        MAX(CASE 
            WHEN LAG(expiry_date) OVER (PARTITION BY account_id ORDER BY effective_date) != expiry_date 
            THEN 1 ELSE 0 
        END) OVER (PARTITION BY account_id) AS has_change
    FROM time_variant_accounts
)
-- 筛选出有变更的账户的所有记录
SELECT account_id, effective_date, end_date, expiry_date
FROM account_change_flags
WHERE has_change = 1;

逻辑说明： #

• LAG(expiry_date) OVER (...)：按账户分组、生效日期排序，获取当前记录的上一条记录的关联值。
• MAX(...) OVER (PARTITION BY account_id)：对每个账户，只要存在任意一条记录的关联值和上一条不同，就标记该账户为has_change=1。
• 最后筛选has_change=1的记录，就能得到所有有变更账户的全量历史。

方案二：先定位变更账户，再关联取全记录 #

如果你的表数据量极大，这种分步的方式可能更高效——先找出所有发生过变更的账户ID，再关联原表获取这些账户的全部记录：

WITH changed_accounts AS (
    -- 找出所有存在关联值变更的账户ID
    SELECT DISTINCT account_id
    FROM (
        SELECT 
            account_id,
            expiry_date,
            LAG(expiry_date) OVER (PARTITION BY account_id ORDER BY effective_date) AS prev_expiry_date
        FROM time_variant_accounts
    ) t
    -- 排除第一条记录（没有上一条），只保留关联值发生变化的记录
    WHERE prev_expiry_date IS NOT NULL 
      AND prev_expiry_date != expiry_date
)
-- 关联原表，获取这些账户的全部记录
SELECT a.*
FROM time_variant_accounts a
INNER JOIN changed_accounts c ON a.account_id = c.account_id;

逻辑说明： #

• 子查询先找出所有“当前记录关联值和上一条不同”的账户（去重后得到变更账户列表）。
• 再通过内关联，把原表中这些账户的所有记录都拉出来，自然排除了从未变更的账户（比如44444444）。

这两种方案都能满足你的需求，你可以根据自己表的数据量和性能要求选择合适的方式~

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