PostgreSQL技术问题：如何同时获取周平均流量及D-7日流量数据

• 需求：按周编号统计每周流量平均值，同时获取每条记录对应日期**前7天（Traffic(D-7)）**的流量数据。例如日期为2023年5月13日时，需输出2023年5月6日的流量。
• 当前进展：已实现周流量平均值统计，但无法同时返回D-7的流量数据。

create table a
(
date  varchar(50),
Tname varchar(50),
Week varchar(5),
Traffic float
)

insert into  a values ('5/1/2023', 'ID1', '18', 7.98)
insert into  a values ('5/2/2023', 'ID1', '18', 4.44)
insert into  a values ('5/3/2023', 'ID1', '18', 5.66)
insert into  a values ('5/4/2023', 'ID1', '18', 10.01)
insert into  a values ('5/5/2023', 'ID1', '18', 9.41)
insert into  a values ('5/6/2023', 'ID1', '18', 6.71)
insert into  a values ('5/7/2023', 'ID1', '18', 8.24)
insert into  a values ('5/8/2023', 'ID1', '19', 8.97)
insert into  a values ('5/9/2023', 'ID1', '19', 6.74)
insert into  a values ('5/10/2023', 'ID1', '19', 6.45)
insert into  a values ('5/11/2023', 'ID1', '19', 9.33)
insert into  a values ('5/12/2023', 'ID1', '19', 8.08)
insert into  a values ('5/13/2023', 'ID1', '19', 8.36)


SELECT date, Tname, Week,
AVG(Traffic) OVER(PARTITION BY Week) AS AVTraffic
FROM a
ORDER BY week

要同时获取周平均和D-7流量，需要通过日期关联匹配7天前的数据。由于原表中date是字符串类型，需先转换为日期格式再做运算，这里使用自连接实现：

SELECT 
    a.date,
    a.Tname,
    a.Week,
    -- 保留周流量平均值统计
    AVG(a.Traffic) OVER(PARTITION BY a.Week) AS AVTraffic,
    -- 获取当前日期前7天的流量
    b.Traffic AS Traffic_D7
FROM a
-- 自连接匹配7天前的记录
LEFT JOIN a b 
    ON CONVERT(date, a.date, 101) = DATEADD(day, 7, CONVERT(date, b.date, 101))
ORDER BY a.Week, a.date;

说明 #

1. CONVERT(date, a.date, 101)：将字符串格式的日期转换为标准日期类型（101对应mm/dd/yyyy格式）。
2. DATEADD(day, 7, CONVERT(date, b.date, 101))：计算b表日期加7天后的日期，与a表当前日期关联，从而得到D-7的流量数据。
3. 使用LEFT JOIN确保即使没有7天前的数据（比如最早的7条记录），也能保留原记录，Traffic_D7会显示为NULL。

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