How to calculate agregate values by condition?

here my base example

+----+-------+---------------------+
| id | value | Get avg data here ↓ |
+----+-------+---------------------+
|  1 |    22 |                     |
|  2 |    23 |                     |
|  3 |     4 |                     |
|  3 |    33 |                     |
|  4 |   222 |                     |
|  5 |    75 |                     |
|  6 |    92 |                     |
|  7 |   202 |                     |
+----+-------+---------------------+

Try smth like this, but nothing

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

Could you help me?

Answer

UPDATE: for these, I have added a new column to allow ordering of the rows. As the OP referred to day_name as a number in code, I have assumed that they refer to days – and thus changed the ‘id’ column to ‘daynum’, and accordingly added a column ‘weeknum’.

Sorting by weeknum then daynum is equivalent to sorting by date. If the OP instead has a datefield as the key column to sort by, change my references to ORDER BY weeknum, daynum to ORDER BY datefield.

Here is the data setup I used (first two weeks only):

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);

One relatively clear version would be to have a sub-query or CTE that finds all types of averages, then an outer query that selects the appropriate one e.g.,

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;

A shorter way is just to include them in a single CASE expression

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;

Here’s a db<>fiddle with data, and both options.

FYI here’s a db<>fiddle in SQL Server as that’s what I wrote it in first.