如何按merchant_id捕获文本内容的历史变更节点？

原始表结构与数据 #

CREATE TABLE term_changes (load_id int, merchant_id int, load_date date, terms varchar(50));
INSERT INTO term_changes (load_id, merchant_id, load_date, terms) VALUES  
(1, 1, '2023-01-05', 'Roses are red'), 
(2, 2, '2023-01-05', 'Roses are blue'), 
(3, 1, '2023-01-06', 'Roses are red'), 
(4, 2, '2023-01-06', 'Roses are blue'), 
(5, 1, '2023-01-07', 'Roses are violet'), 
(6, 2, '2023-01-07', 'Roses are blue'), 
(7, 1, '2023-01-08', 'Roses are violet'), 
(8, 2, '2023-01-08', 'Roses are yellow');

原有SQL及返回结果 #

原有尝试的SQL：

WITH t1 AS (SELECT load_id, merchant_id, load_date, MD5(terms) AS terms            
            FROM term_changes            
            ORDER BY merchant_id, load_id),
t2 AS (SELECT load_id,               
               merchant_id,               
               load_date,               
               terms,               
               LAG(load_id, 1) OVER (PARTITION BY merchant_id                                    
                                     ORDER BY load_id) AS prev_load_id       
       FROM t1)
SELECT *
FROM   t2        
JOIN t1 ON t1.load_id = t2.prev_load_id                   
AND t1.merchant_id = t2.merchant_id                   
AND t1.terms != t2.terms

返回结果：

期望输出 #

按merchant_id分组，保留每个商家的初始值及变更后的值，且用load_id排序（单日内可能有多条记录）：

解决方案SQL #

WITH ranked_terms AS (
    SELECT 
        load_id,
        merchant_id,
        load_date,
        terms,
        LAG(terms) OVER (PARTITION BY merchant_id ORDER BY load_id) AS prev_terms
    FROM term_changes
)
SELECT load_id, merchant_id, load_date, terms
FROM ranked_terms
WHERE prev_terms IS NULL 
   OR prev_terms != terms
ORDER BY merchant_id, load_id;

逻辑说明 #

1. 用LAG(terms)窗口函数，按商家分组、load_id排序，获取每条记录的上一条terms值
2. 筛选两类记录：
   • prev_terms IS NULL：每个商家的第一条记录（初始值）
   • prev_terms != terms：与上一条记录内容不同的变更记录
3. 最后按merchant_id和load_id排序，得到符合需求的结果

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