如何在SQL中对过去12个月进行计数和分组

Question

示例

要查询的interviewed表：

id	访谈日期
1	2020-12-31
2	2021-01-25
3	2021-025
4	2021-02-13
5	2022-05-25
6	2022-05-26

由于您没有指定rdms，我使用MySQL作为我的方法，因为每个rdms可能都有另一种语法，甚至会一起错过WINDOW functions。

在你的描述中，第二个是你谈论的是过去12个月的数据，但你的数据中没有任何数据，这就是为什么我在没有WHERE'子句的查询中添加了，onyl来证明它在中有效

该思想首先选择所有年份/月份的计数，然后使用窗口函数的累积SUM函数。

CREATE TABLE interview (
`id` INTEGER,
`interviewed_on_date`date
);
INSERT INTO interview
(`id`, `interviewed_on_date`)
VALUES
('1', '2020-12-31'),
('2', '2021-01-25'),
('3', '2021-01-25'),
('4', '2021-02-13'),
('5', '2022-05-25'),
('6', '2022-05-26');

WITH CTE AS (SELECT
DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
COunt(*) count_
FROM interview
GROUP BY 1)
SELECT myyeamon,
sum(count_) OVER (ORDER BY  myyeamon) count_
FROM CTE

myyeamon|count_：-------|-----：2020-12|12021-01|32021-02|42022-05|6

<blockquote\

WITH CTE AS (SELECT
DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
COunt(*) count_
FROM interview
WHERE `interviewed_on_date` BETWEEN curdate() - INTERVAL - 12 MONTH AND curdate()
GROUP BY 1)
SELECT myyeamon,
sum(count_) OVER (ORDER BY  myyeamon) count_
FROM CTE

>

myyeamon|count_：-------|-----：

db<gt；小提琴这里

这是，当你只想要2022年

WITH CTE AS (SELECT
DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
COunt(*) count_
FROM interview
WHERE YEAR(`interviewed_on_date`) = 2022
GROUP BY 1)
SELECT myyeamon,
sum(count_) OVER (ORDER BY  myyeamon) count_
FROM CTE

myyeamon|count_：-------|-----：2022-05|2

db<gt；小提琴这里

要获得完整的号码，但只需要一个条目，您需要LIMIT

WITH CTE AS (SELECT
DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
COunt(*) count_
FROM interview
GROUP BY 1)
SELECT myyeamon,
sum(count_) OVER (ORDER BY  myyeamon) count_
FROM CTE
ORDER BY myyeamon DESC
LIMIT 1

myyeamon|count_：-------|-----：2022-05|6

db<gt；小提琴这里

SELECT YEAR(interviewed_on_date), MONTH(interviewed_on_date), -- PK of result table.
FORMAT(MAX(interviewed_on_date), 'yyyy-MM'), -- Label column.
COUNT(*) -- Value column.
FROM interviewed
GROUP BY YEAR(interviewed_on_date), MONTH(interviewed_on_date)

MAX是从每组行中选择任何老代表的一种简单方法——每组中的每一行都有相同的年份和日期。