| 2020-01-01 | laptop | 210 |
| 2020-01-01 | pc | 100 |
| 2020-01-02 | laptop | 150 |
| 2020-01-02 | pc | 200 |
| 2020-01-03 | laptop | 150 |
| 2021-01-01 | pc | 110 |
| 2021-01-01 | laptop | 220 |
2021-01-02 | pc 210 |
2021-01-02 | laptop | 200 |
2021-01-03 | pc | 260|
Надо вывести среднее значение price по дням с начала года, т.е каждый день месяца суммировать с первым и разделить на два. CREATE TABLE tbl( date DATETIME, name VARCHAR(25), price INT);
INSERT INTO tbl VALUES
('2020-01-01', 'laptop', 210),
('2020-01-01', 'pc', 100),
('2020-01-02', 'laptop', 150),
('2020-01-02', 'pc', 200),
('2020-01-03', 'laptop', 150),
('2021-01-01', 'pc', 110),
('2021-01-01', 'laptop', 220),
('2021-01-02', 'pc', 210),
('2021-01-02', 'laptop', 200),
('2021-01-03', 'pc', 260);
SELECT T2.date, T2.name, ( T2.price + T1.price ) / 2
FROM tbl T2
JOIN tbl T1 ON T1.name = T2.name
AND T1.date = LAST_DAY(T2.date - INTERVAL 1 MONTH) + INTERVAL 1 DAY
ORDER BY 1, 2
select date, abs(sum-first_month_value)/2 from (select
sum(price) as sum,
count(date) as count,
date,
first_value(sum(price)) over() as first_month_value
from t
group by
date
order by date) as t;