如何按每月第一天分组日期并统计当月唯一ID数量？

问题背景 #

我通过Java程序连接H2嵌入式数据库，其中有一张ASSEGNAZIONI_IN表，包含DATA（DATE类型）和ID（VARCHAR(16)类型）两列，表中数据如下：

需求 #

• 获取每月第一天以及当月的唯一ID数量
• 按时间升序排序结果

期望输出：

2023-03-01  4
2023-04-01  2
2023-05-01  3
2023-10-01  2

尝试的查询及问题 #

我最初使用的SQL语句：

SELECT
    CONCAT(
        YEAR(ASSEGNAZIONI_IN.DATA),
        '-',
        MONTH(ASSEGNAZIONI_IN.DATA)
    ) AS MESE,
    COUNT(DISTINCT ASSEGNAZIONI_IN.ID)
FROM
    ASSEGNAZIONI_IN 
GROUP BY
    MESE

得到的结果：

2023-10     2
2023-3      4
2023-4      2
2023-5      3

存在两个问题：

1. 按字符串排序导致顺序错误（比如2023-10排在了最前面）
2. 日期格式不符合需求，需要显示每月第一天而非仅年月

最终解决方案 #

感谢@ArtBindu的帮助，最终使用的查询语句如下：

SELECT 
    CAST(
        CONCAT(
            YEAR(DATA),
            '-',
            MONTH(DATA),
            '-01') AS DATE) AS FIRSTOFMONTH,
    COUNT(DISTINCT ID)
FROM
    ASSEGNAZIONI_IN
GROUP BY
    FIRSTOFMONTH
ORDER BY
    FIRSTOFMONTH ASC

该查询通过以下方式解决问题：

• 拼接出当月第一天的字符串后，转换为DATE类型，既保证了日期格式符合要求，又能基于日期类型正确排序
• 直接按转换后的日期字段分组和排序，确保结果按时间升序排列
• 用COUNT(DISTINCT ID)准确统计当月唯一ID的数量

内容的提问来源于stack exchange，提问作者Xyon4