SQL实现：按分组最小EndDate排序分组，组内按EndDate排序

需求说明 #

需要对CustomerData表的数据进行排序，规则如下：

• 先按每个GroupId分组的最小EndDate升序排列整个分组
• 每个分组内部的数据，再按自身的EndDate升序排列

表结构与测试数据 #

Create Table CustomerData
(
 Id Integer,
 GroupId Varchar(25),
 CName Varchar(25),
 Product Varchar(25),
 StartDate Date,
 EndDate Date,
 Premium Integer,
 GroupMo Varchar(25)
)

Insert Into CustomerData Values(1,'U001','Uday Dodiya','Motor Policy','2021-08-19','2022-08-18',2500,'9601629656')
Insert Into CustomerData Values(2,'K001','Kalpesh Shah','Health Policy','2021-08-02','2022-08-01',500,'9911629656')
Insert Into CustomerData Values(3,'U001','Brinda Dodiya','PA Policy','2021-08-02','2022-08-01',200,'9601629656')
Insert Into CustomerData Values(4,'Z001','Zalak Mer','PA Policy','2021-08-16','2022-08-15',2500,'9801629656')
Insert Into CustomerData Values(5,'H001','Harsh Rathod','WC Policy','2021-08-02','2022-08-01',4500,'7788995566')
Insert Into CustomerData Values(6,'H001','Het Dodiya','Motor Policy','2021-08-29','2022-08-28',2900,'7788995566')
Insert Into CustomerData Values(7,'U001','Gopal Dodiya','Other Policy','2021-08-31','2022-08-30',3000,'9601629656')
Insert Into CustomerData Values(8,'U001','Gopal Dodiya','Motor Policy','2021-08-10','2022-08-09',9600,'9601629656')
Insert Into CustomerData Values(9,'K001','Karina Shah','Health Policy','2021-08-06','2022-08-05',2500,'9911629656')  
Insert Into CustomerData Values(10,'S001','Sneha Mer','Motor Policy','2021-08-26','2022-08-25',3600,'8866554466')
Insert Into CustomerData Values(11,'U001','Uday Dodiya','PA Policy','2021-08-20','2022-08-19',3500,'9601629656')

解决方案 #

使用窗口函数MIN() OVER (PARTITION BY GroupId)计算每个分组的最小EndDate，将其作为分组排序的依据，再结合组内的EndDate完成排序：

SELECT 
    Id,
    GroupId,
    CName,
    Product,
    StartDate,
    EndDate,
    Premium,
    GroupMo
FROM CustomerData
ORDER BY 
    -- 按分组的最小EndDate升序排列分组
    MIN(EndDate) OVER (PARTITION BY GroupId),
    -- 组内按EndDate升序排列
    EndDate;

结果说明 #

执行上述SQL后，数据将按以下规则排序：

1. 分组排序：H001、K001、U001的最小EndDate均为2022-08-01，会排在最前面；接着是最小EndDate为2022-08-15的Z001；最后是最小EndDate为2022-08-25的S001。
2. 组内排序：每个分组内的记录按自身EndDate从小到大排列。

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