我有一个table Event_，带有两个列（event_id和事件的日期）

+----------+------------+
| event_id | event_date |
+----------+------------+
| 21       | 2017-01-29 |
| 27       | 2018-01-16 |
| 45       | 2017-01-27 |
| 49       | 2018-01-29 |
| 59       | 2017-01-24 |
| 71       | 2017-01-30 |
| 73       | 2017-01-14 |
| 79       | 2017-01-16 |
| 101      | 2019-01-03 |
| 102      | 2020-01-12 |
| 119      | 2017-01-30 |
| 122      | 2019-01-18 |
| 144      | 2020-01-24 |
| 159      | 2018-01-16 |
| 164      | 2017-01-03 |
| 171      | 2018-01-17 |
| 190      | 2018-01-02 |
| 193      | 2018-01-08 |
| 200      | 2019-01-07 |
| 230      | 2017-01-23 |
| 239      | 2017-01-06 |
| 256      | 2020-01-01 |
| 277      | 2017-01-13 |
| 291      | 2018-01-17 |
| 328      | 2017-01-10 |
| 332      | 2018-01-22 |
| 341      | 2018-01-12 |
| 348      | 2019-01-17 |
| 388      | 2017-01-05 |
+----------+------------+

DDL和DML

create table event_ (event_id int , event_date date );

insert into event_ (event_id,event_date) values(21,"2017-01-29"),(27,"2018-01-16"),(45,"2017-01-27"),(49,"2018-01-29"),(59,"2017-01-24"),(71,"2017-01-30"),(73,"2017-01-14"),(79,"2017-01-16"),(101,"2019-01-03"),(102,"2020-01-12"),(119,"2017-01-30"),(122,"2019-01-18"),(144,"2020-01-24"),(159,"2018-01-16"),(164,"2017-01-03"),(171,"2018-01-17"),(190,"2018-01-02"),(193,"2018-01-08"),(200,"2019-01-07"),(230,"2017-01-23"),(239,"2017-01-06"),(256,"2020-01-01"),(277,"2017-01-13"),(291,"2018-01-17"),(328,"2017-01-10"),(332,"2018-01-22"),(341,"2018-01-12"),(348,"2019-01-17"),(388,"2017-01-05");

我想在2017年至2020年之间每年进行month-wise分析。特别是，在这些年中的每个月中，我想确定一周中的最大事件数量。如果在给定年份中有多个工作日的特定月份最大活动数量，我想确定所有工作日的名称。

下面我提到了预期输出：

month     2017          2018            2019            2020
  1       Monday   Monday,Tuesday  Thursday    Friday,Sunday,Wednesday

所有的帮助将不胜感激。

分析解答

我们需要分两个步骤执行此操作。

  1. 组BY YEAR, MONTH, WEEKDAY，然后添加RANK（）
  2. 选择每月排名第1的日子，每年枢转

步骤1

SELECT
    YEAR(`event_date`) AS `year`,
    MONTH(`event_date`) AS `month`,
    WEEKDAY(`event_date`) AS `day_num`,
    DAYNAME(`event_date`) AS `day_name`,
    COUNT(*) AS `count`,
    RANK() OVER (PARTITION BY YEAR(`event_date`), MONTH(`event_date`) ORDER BY COUNT(*) DESC) AS `rank`
FROM `event_`
GROUP BY `year`, `month`, `day_num`, `day_name`
ORDER BY `year`, `month`, `rank`;

输出：

year month day_num day_name count rank
2017 1 0 Monday 4 1
2017 1 4 Friday 3 2
2017 1 1 Tuesday 3 2
2017 1 6 Sunday 1 4
2017 1 5 Saturday 1 4
2017 1 3 Thursday 1 4
2018 1 1 Tuesday 3 1
2018 1 0 Monday 3 1
2018 1 2 Wednesday 2 3
2018 1 4 Friday 1 4
2019 1 3 Thursday 2 1
2019 1 4 Friday 1 2
2019 1 0 Monday 1 2
2020 1 6 Sunday 1 1
2020 1 4 Friday 1 1
2020 1 2 Wednesday 1 1

第2步

WITH `ranked` (`year`, `month`, `day_num`, `day_name`, `rank`) AS (
    SELECT
        YEAR(`event_date`),
        MONTH(`event_date`),
        WEEKDAY(`event_date`),
        DAYNAME(`event_date`),
        RANK() OVER (PARTITION BY YEAR(`event_date`), MONTH(`event_date`) ORDER BY COUNT(*) DESC)
    FROM `event_`
    GROUP BY `year`, `month`, `day_num`, `day_name`
)
SELECT `month`,
    GROUP_CONCAT(IF(`year` = 2017, `day_name`, NULL) ORDER BY `day_num`) AS `2017`,
    GROUP_CONCAT(IF(`year` = 2018, `day_name`, NULL) ORDER BY `day_num`) AS `2018`,
    GROUP_CONCAT(IF(`year` = 2019, `day_name`, NULL) ORDER BY `day_num`) AS `2019`,
    GROUP_CONCAT(IF(`year` = 2020, `day_name`, NULL) ORDER BY `day_num`) AS `2020`
FROM `ranked`
WHERE `rank` = 1
GROUP BY `month`;

输出：

month 2017 2018 2019 2020
1 Monday Monday,Tuesday Thursday Wednesday,Friday,Sunday

这是db <>小提琴。