基于单日期列生成from_date/to_date行集——客户审计表场景

现有customer_audit表用于记录customer表的INSERT和UPDATE操作，表结构及数据如下：

需求说明 #

需要针对address1和address2字段，分别展示它们的所有历史值，并基于timestamp列计算对应的生效起始日期（from_date）和失效日期（to_date）。以customer_id=100为例，预期输出如下：

实现SQL #

我通过调整mcwolf的查询语句完成了需求，针对address1的SQL代码如下：

select customer_id, name, 'address1' as attribute_name, address1 as "attribute_value", min(from_date) as "valid_from", max(to_date) as "valid_to"
from (
select customer_id, 
       name, 
       address1,
       "mod_timestamp" from_date,
         COALESCE(lead("mod_timestamp") over change_window, to_date('99991231', 'yyyymmdd')) to_date,
       ROW_NUMBER () OVER ( PARTITION BY customer_id, address1 ORDER BY mod_timestamp) as rownum
from customer_audit
WINDOW change_window as (PARTITION BY customer_id order by mod_timestamp)   
order by customer_id, mod_timestamp ) a
group by customer_id, name, attribute_name, "attribute_value"
order by customer_id, "valid_from";

若需要同时处理address2字段，可复制上述查询并将所有address1替换为address2，最后用UNION ALL合并两个查询结果，即可得到完整的双字段历史记录。

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