SQL技术需求：提取最近日期与上一对应日期的数据值

需求：从SQL数据表中提取最近日期与上一最近日期对应的数据值，最新日期为2022/08/26（作为当前值），上一最近日期为2022/08/19（作为历史值），按SEGMENT、MODEL维度，将FC1-FC4字段的当前值与历史值合并为指定格式展示。

输入表结构及数据 #

期望输出表结构及数据 #

测试数据SQL #

Create table ##input1
(date_value date,
segment varchar(30),
model varchar(20),
FC1 int,
FC2 int,
FC3 int,
FC4 int)

insert into ##input1 values
('8/26/2022','Halo ','MJK12','54','19','43','134'),
('8/26/2022','Halo ','JKIO34','70','9','67','117'),
('8/26/2022','Kalo','JK123','75','107','6','6'),
('8/26/2022','Belo','OPWE1','5','101','106','102'),
('8/26/2022','Halo ','KLWE1','35','35','108','9'),
('8/19/2022','Halo ','MJK12','58','19','43','138'),
('8/19/2022','Halo ','JKIO34','74','9','78','121'),
('8/19/2022','Kalo','JK123','79','119','6','8'),
('8/19/2022','Belo','OPWE1','8','101','111','104'),
('8/19/2022','Halo ','KLWE1','39','35','108','11'),
('8/12/2022','Halo ','MJK12','60','18','46','139'),
('8/12/2022','Halo ','JKIO34','76','8','81','122'),
('8/12/2022','Kalo','JK123','81','118','9','9'),
('8/12/2022','Belo','OPWE1','10','100','114','105'),
('8/12/2022','Halo ','KLWE1','41','34','111','12')

Create table ##output1
(segment varchar(20),
model varchar(30),
[FC1-current] int,
[FC1-previous] int,
[FC2-current] int,
[FC2-previous] int,
[FC3-current] int,
[FC3-previous] int,
[FC4-current] int,
[FC4-previous] int)

insert into ##output1 values
('Halo ','MJK12','54','58','19','19','43','43','134','138'),
('Halo ','JKIO34','70','74','9','9','67','78','117','121'),
('Kalo','JK123','75','79','107','119','6','6','6','8'),
('Belo','OPWE1','5','8','101','101','106','111','102','104'),
('Halo ','KLWE1','35','39','35','35','108','108','9','11')

尝试的查询语句 #

WITH CTE AS
(
 SELECT *,DENSE_RANK()OVER(ORDER BY "DATE_VALUE" DESC) AS RNum
     FROM input
    )
    SELECT c1.rnum, C1.SEGMENT,C1.MODEL,C1.FC1 as FC1current,c2.FC1 as FC1old,C1.FC2 as FC2current,c2.FC2 as FC2old,C1.FC3 as FC3current,c2.FC3 as FC3old,C1.FC4 as FC4current,c2.FC4 as FC4old,C1.FC5 as FC5current,c2.FC5 as FC5old,
     C1.FC6 as FC6current,c2.FC6 as FC6old

FROM CTE C1 LEFT JOIN CTE C2 ON C1.RNum  = C2.RNum + 1 AND trim(C1.segment)=trim(C2.segment) AND trim(C1.model)=trim(C2.model)
    WHERE C1.RNum IN (1,2) 

正确实现查询 #

原查询存在三个问题：未按SEGMENT、MODEL分区排名，导致全局排名逻辑错误；引用了不存在的FC5、FC6字段；输出字段名和筛选逻辑不符合需求。以下是修正后的查询：

WITH ranked_data AS (
    SELECT 
        *,
        DENSE_RANK() OVER (PARTITION BY SEGMENT, MODEL ORDER BY date_value DESC) AS rnum
    FROM ##input1
)
SELECT 
    r1.SEGMENT,
    r1.MODEL,
    r1.FC1 AS [FC1-current],
    r2.FC1 AS [FC1-previous],
    r1.FC2 AS [FC2-current],
    r2.FC2 AS [FC2-previous],
    r1.FC3 AS [FC3-current],
    r2.FC3 AS [FC3-previous],
    r1.FC4 AS [FC4-current],
    r2.FC4 AS [FC4-previous]
FROM ranked_data r1
LEFT JOIN ranked_data r2 
    ON r1.SEGMENT = r2.SEGMENT 
    AND r1.MODEL = r2.MODEL 
    AND r1.rnum = r2.rnum + 1
WHERE r1.rnum = 1;

逻辑说明 #

1. 分区排名：使用PARTITION BY SEGMENT, MODEL确保每个SEGMENT+MODEL分组内，按日期倒序生成排名，最新数据为rnum=1，上一期数据为rnum=2。
2. 关联匹配：通过相同SEGMENT、MODEL以及排名差1的条件，将最新数据和上一期数据关联。
3. 筛选输出：仅保留rnum=1的最新数据行，合并对应字段并使用需求指定的列名。

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