按plant、segment、model维度统计承诺日期平均变动及去重订单数

需在segment、plant、model三个维度的聚合粒度下，计算当前承诺日期（currentprmdate）相对7天前承诺日期（prmdate7）、14天前承诺日期（prmdate14）、28天前承诺日期（prmdate28）的平均天数差，同时统计该聚合粒度下的去重订单号（ordernumber）数量。
具体计算规则：

• 7天平均交期偏移：currentprmdate与prmdate7天数差的平均值
• 14天平均交期偏移：currentprmdate与prmdate14天数差的平均值
• 28天平均交期偏移：currentprmdate与prmdate28天数差的平均值
• 固定分组维度：segment、plant、model

输入表结构与样例值 #

期望输出结构与样例结果 #

-- 输入表建表语句
create table input
(ordernumber varchar(40),
segment varchar(20),
plant varchar(10),
model varchar(15),
currentprmdate date,
prmdate7 date,
prmdate14 date,
prmdate28 date)

-- 输入表样例数据插入语句
insert into input values
('V89121','vinots','Chikoo','HJ781','5/6/2021','5/5/2021','5/1/2021','5/7/2021'),
('LM12781','vinots ','Chikoo','HJ781','5/17/2021','5/11/2021','5/15/2021','5/10/2021'),
('JK9812','vinots','Chikoo','HJ781','5/3/2021','4/28/2021','4/25/2021','4/20/2021'),
('LP18921','Vimar','Jolie','MK241','4/3/2021','3/27/2021','3/21/2021','3/20/2021'),
('BN1231','Vimar','Jolie','MK241','6/10/2021','6/5/2021','6/3/2021','6/1/2021'),
('LO1231','Vimar','Jolie','MK241','7/15/2022','7/11/2022','7/13/2022','7/7/2022')

-- 输出表建表语句
create table output
(segment varchar(20),
plant varchar(20),
model varchar(40),
avg7day float,
avg14day float,
avg28day float,
distinctorders int)

-- 输出表样例结果插入语句
insert into output values
('vinots','Chikoo','HJ781','4','5','6.3','3'),
('Vimar','Jolie','MK241','5.3','7.3','10.3','3')

INSERT INTO output
SELECT
    TRIM(segment) AS segment,
    TRIM(plant) AS plant,
    TRIM(model) AS model,
    ROUND(AVG(DATEDIFF(currentprmdate, prmdate7)),1) AS avg7day,
    ROUND(AVG(DATEDIFF(currentprmdate, prmdate14)),1) AS avg14day,
    ROUND(AVG(DATEDIFF(currentprmdate, prmdate28)),1) AS avg28day,
    COUNT(DISTINCT ordernumber) AS distinctorders
FROM input
GROUP BY TRIM(segment), TRIM(plant), TRIM(model);

注意：样例插入数据中LM12781对应的segment字段值尾部带多余空格，用TRIM()函数处理后可保证分组结果匹配预期；不同SQL引擎的日期差函数不同，如PostgreSQL直接用日期相减currentprmdate - prmdate7、SQL Server用DATEDIFF(day, prmdate7, currentprmdate)，替换对应函数即可。

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