mariadb GROUP BY每年自定义日期间隔

ifmq2ha2  于 2022-11-08  发布在  其他
关注(0)|答案(1)|浏览(108)

情况:我需要日期之间的自定义间隔。当我尝试GROUP BY年份时遇到的问题以及我得到的结果总计到给定年份。我需要自定义每年从12月20日开始的时间间隔:上年00:00:00至12月19日用款时间:23:59:59,这是我的一些数据:

表格-历史:

id | date                | income  | spent  
--------------------------------------------
1  | 2019-12-21 17:15:00 | 600,00  | NULL
2  | 2019-12-23 12:55:00 | 183,00  | NULL
3  | 2019-12-30 20:05:00 | NULL    | 25,00
4  | 2020-01-01 15:35:00 | NULL    | 13,00
5  | 2020-01-01 20:25:00 | NULL    | 500,50
6  | 2020-12-10 10:25:00 | NULL    | 5,50
7  | 2021-05-22 12:45:00 | 1098,00 | NULL
8  | 2021-05-23 10:18:00 | NULL    | 186,00
9  | 2021-11-25 12:32:00 | NULL    | 10,00
10 | 2021-12-23 10:35:00 | NULL    | 10,00

预期结果:

Year | Summary Income | Summary Spent | Difference
--------------------------------------------------
2020 |         783,00 |        544,00 |     239,50
2021 |        1098,00 |        196,00 |     902,00
2022 |           0,00 |         10,00 |     -10,00

我已经设法在一个过程中的循环的帮助下得到了一个结果:

...
SET @Aa = (SELECT MIN(date) FROM History);
CREATE TEMPORARY TABLE Yr (Year VARCHAR(4), Income FLOAT(8,2), Spent FLOAT(8,2), differ FLOAT(8,2));
Yearly: LOOP
SET @Aa = @Aa + 1;
SET @From = CONCAT((@Aa - 1), '-12-20 00:00:00');
SET @To = CONCAT(@Aa, '-12-19 23:59:59');
SET @Count = (SELECT SUM(income) FROM History WHERE date >= @From AND date <= @To);
SET @diff = (SELECT SUM(spent)  FROM History WHERE date >= @From AND date <= @To);
INSERT INTO Yr (Year, Income, Spent, differ) VALUES (@Aa, @Count, @diff, (@Count - @diff));
IF (@Aa = (SELECT MAX(YEAR(date)) FROM History)) THEN LEAVE Yearly; END IF;
END LOOP;
SELECT * FROM Yr;
...

**问题:**我想知道是否可以使用精简的SQL查询而不使用循环来获得年度摘要的自定义间隔?

rslzwgfq

rslzwgfq1#

您可以在应用year函数之前简单地在日期上加上11天来获得此分组,例如:

SELECT YEAR(DATE_ADD(date, INTERVAL 11 DAY)) AS Year,
       SUM(income) AS income,
       SUM(spent) AS Spent,
       IFNULL(SUM(income),0)  - IFNULL(SUM(spent),0) AS difference
FROM History
GROUP BY YEAR(DATE_ADD(date, INTERVAL 11 DAY));

相关问题