如何优化SQL查询结果结构，按部门-年份-月份层级展示统计数据？

问题背景 #

需要按department、年份、月份统计用户事件数，现有SQL能正常返回数据，但存在两个问题：

1. 返回结果结构零散，无法直接得到按部门→年份→月份层级嵌套的格式
2. 月份排序混乱（如示例中2022年的月份顺序是Aug、Dec、Nov、Oct，不符合时间顺序）

现有SQL语句 #

SELECT
    u.department,
    YEAR(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y')) AS year,
    DATE_FORMAT(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y'), '%b') AS month,
    COUNT(*) AS event_count
FROM
    userlibrary tlc
LEFT JOIN
    users u ON tlc.userfullname = u.fullname
WHERE
    u.type = ?
    AND YEAR(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y')) BETWEEN ? AND ?
GROUP BY
    u.department,
    year,
    month
ORDER BY
    u.department,
    year,
    month;

对应的Node.js调用代码：

const [results, fields] = await connection.query(sql, [
    type,
    `${startYear}-${from}`,
    `${endYear}-${end}`,
]);

当前返回结果 #

[
    {
        "department": null,
        "year": 2022,
        "month": "Aug",
        "event_count": 27
    },
    {
        "department": null,
        "year": 2022,
        "month": "Dec",
        "event_count": 4
    },
    {
        "department": null,
        "year": 2022,
        "month": "Nov",
        "event_count": 29
    },
    {
        "department": null,
        "year": 2022,
        "month": "Oct",
        "event_count": 49
    }
]

期望结果格式 #

{
    "null": {
        "2022": {
            "Aug": 27,
            "Oct": 49,
            "Nov": 29,
            "Dec": 4
        },
        "2023": {
            "Feb": 3,
            "Apr": 25,
            "Aug": 7
        }
    }
}

解决方案 #

1. 修复月份排序问题（SQL层优化） #

当前月份排序混乱是因为ORDER BY month按月份缩写的字母顺序排序，而非时间顺序。需要新增月份数字字段用于排序，同时保留月份缩写用于展示：

优化后的SQL：

SELECT
    u.department,
    YEAR(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y')) AS year,
    DATE_FORMAT(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y'), '%b') AS month,
    MONTH(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y')) AS month_num,
    COUNT(*) AS event_count
FROM
    userlibrary tlc
LEFT JOIN
    users u ON tlc.userfullname = u.fullname
WHERE
    u.type = ?
    AND YEAR(STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y')) BETWEEN ? AND ?
GROUP BY
    u.department,
    year,
    month,
    month_num
ORDER BY
    u.department,
    year,
    month_num;

2. 转换为嵌套结构（Node.js层处理） #

SQL无法直接返回多层嵌套的JSON结构，需在Node.js中对扁平化结果进行处理：

// 转换查询结果为嵌套结构
const structuredResult = {};

results.forEach(item => {
    // 处理department为null的情况，用字符串"null"作为键
    const deptKey = item.department === null ? "null" : item.department;
    const yearKey = item.year.toString();
    
    // 初始化部门层级
    if (!structuredResult[deptKey]) {
        structuredResult[deptKey] = {};
    }
    // 初始化年份层级
    if (!structuredResult[deptKey][yearKey]) {
        structuredResult[deptKey][yearKey] = {};
    }
    // 赋值月份对应的事件数
    structuredResult[deptKey][yearKey][item.month] = item.event_count;
});

console.log(JSON.stringify(structuredResult, null, 2));

额外优化：避免重复解析日期 #

原SQL多次重复解析日期字符串，可通过CTE提前解析，提升性能：

WITH parsed_time AS (
    SELECT
        tlc.userfullname,
        STR_TO_DATE(SUBSTRING_INDEX(tlc.time, ',', 1), '%d/%m/%y') AS event_date
    FROM userlibrary tlc
)
SELECT
    u.department,
    YEAR(pt.event_date) AS year,
    DATE_FORMAT(pt.event_date, '%b') AS month,
    MONTH(pt.event_date) AS month_num,
    COUNT(*) AS event_count
FROM parsed_time pt
LEFT JOIN users u ON pt.userfullname = u.fullname
WHERE
    u.type = ?
    AND YEAR(pt.event_date) BETWEEN ? AND ?
GROUP BY
    u.department,
    year,
    month,
    month_num
ORDER BY
    u.department,
    year,
    month_num;

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