如何按条件计算合计值？

更新：为此，我添加了一个新列以允许对行进行排序。由于OP在代码中将day_name称为数字，因此我假设它们是指天-因此将“ id”列更改为“ daynum”，并因此添加了“ weeknum”列。

按周编号排序，然后按天编号等效于按日期排序。如果OP将日期字段作为要排序的关键列，请将我的引用更改ORDER BY weeknum, daynum为ORDER BY datefield。

这是我使用的数据设置（仅前两周）：

CREATE TABLE initial_table 
    (weeknum int, daynum int, value int, PRIMARY KEY (weeknum, daynum));

INSERT INTO initial_table (weeknum, daynum, value) VALUES
(1, 1,  22),
(1, 2,  23),
(1, 3,   4),
(1, 4,  23),
(1, 5,  14),
(1, 6, 132),
(1, 7, 211),
(2, 1, 155),
(2, 2, 190),
(2, 3,  33),
(2, 4, 222),
(2, 5,  75),
(2, 6,  92),
(2, 7, 107);

一个相对清晰的版本是让子查询或CTE查找所有类型的平均值，然后使外部查询选择合适的平均值，例如，

WITH AvgList AS
(SELECT weeknum,
        daynum, 
        value, 
    SUM (value) OVER (ORDER BY weeknum, daynum) AS CumulativeSum,
        AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) :: decimal(8,2) AS Avg5,
        AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) :: decimal(8,2) AS Avg1,
        AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) :: decimal(8,2) AS AvgOther
    FROM initial_table
)
SELECT weeknum,
       daynum, 
       value,
       CASE WHEN daynum = 5 THEN Avg5
            WHEN daynum = 1 THEN Avg1
            ELSE AvgOther END AS hello
FROM AvgList;

一种较短的方法是将它们包含在单个CASE表达式中

SELECT weeknum,
       daynum, 
       value,
       CASE WHEN daynum = 5 THEN AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) :: decimal(8,2)
            WHEN daynum = 1 THEN AVG (value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) :: decimal(8,2)
            ELSE  AVG(value) OVER (ORDER BY weeknum, daynum ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) :: decimal(8,2) END AS hello
FROM initial_table;

这是一个带有数据的db <>小提琴，还有两个选项。

仅供参考，这是SQL Server中的db <>小提琴，因为这是我首先编写的。