PostgreSQL 从同一张表中获取值

首先，您需要修复表模式设计中的缺陷，并声明:

• "DATE"类型的日期(而不是VARCHAR(50))
• INT类型的周值(而不是VARCHAR(5))
• 类型为DECIMAL的流量值(而不是FLOAT)

CREATE TABLE tab(
    DATE      DATE,
    Tname     VARCHAR(50),
    Week      INT,
    Traffic   DECIMAL(4,2)
);

一旦你执行了它，你可以通过以下方法来解决这个问题:

• 为您一周中的每一天创建排名值，在日期上使用EXTRACT
• 通过对上一步创建的排名进行划分，并根据Week_Numbers进行排序，提取前一周LAG的流量价值.

WITH cte AS (
    SELECT date, Tname, Week, Traffic,
           ROUND(AVG(Traffic) OVER(PARTITION BY Week), 2) AS AVGTraffic,
           EXTRACT(ISODOW FROM date) - 1                  AS week_day
    FROM tab
)
SELECT date, Tname, Week, 
       LAG(Traffic) OVER(PARTITION BY week_day ORDER BY Week) AS prevweek_traffic,
       AVGTraffic
FROM cte
ORDER BY Week, week_day

如果您意识到您的周(...、第17周、第18周、第20周、第21周等)之间可能有空洞，并且特别想要前一周的值(可能会丢失)，则可以在LAG函数上添加一个筛选器，该筛选器判断一周和前一周是否连续:

...
CASE WHEN LAG(Week) OVER(PARTITION BY week_day ORDER BY Week) = Week-1
     THEN LAG(Traffic) OVER(PARTITION BY week_day ORDER BY Week) 
END
...

(仅代替LAG(Traffic) OVER(...))

Output:

查看演示here.

该查询允许您的数据中存在任何类型的漏洞，如果这是必需的.

注:不需要最后ORDER BY条款.它在那里只是为了可视化的目的.