选举系统中如何编写SQL查询实现按日期分组的累计投票统计？

核心思路 #

先按日期分组统计当日投票数量，再通过窗口函数（或子查询，针对旧版数据库）计算从起始日期到当前日期的累计投票总和，满足「前一日累计 + 当日票数」的需求。

针对支持窗口函数的数据库（MySQL 8.0+/PostgreSQL/SQL Server） #

MySQL 示例

SELECT
    -- 格式化日期为 dd/MM 格式
    DATE_FORMAT(STR_TO_DATE(created_at, '%d/%m/%Y'), '%d/%m') AS vote_date,
    -- 统计当日投票数
    COUNT(voters_id) AS daily_votes,
    -- 按日期排序后累加所有历史票数，得到累计总和
    SUM(COUNT(voters_id)) OVER (ORDER BY STR_TO_DATE(created_at, '%d/%m/%Y')) AS cumulative_votes
FROM voters
-- 按原始日期分组，避免不同年份的同日期数据冲突
GROUP BY STR_TO_DATE(created_at, '%d/%m/%Y'), DATE_FORMAT(STR_TO_DATE(created_at, '%d/%m/%Y'), '%d/%m')
ORDER BY STR_TO_DATE(created_at, '%d/%m/%Y');

PostgreSQL 示例

SELECT
    TO_CHAR(TO_DATE(created_at, 'DD/MM/YYYY'), 'DD/MM') AS vote_date,
    COUNT(voters_id) AS daily_votes,
    SUM(COUNT(voters_id)) OVER (ORDER BY TO_DATE(created_at, 'DD/MM/YYYY')) AS cumulative_votes
FROM voters
GROUP BY TO_DATE(created_at, 'DD/MM/YYYY'), TO_CHAR(TO_DATE(created_at, 'DD/MM/YYYY'), 'DD/MM')
ORDER BY TO_DATE(created_at, 'DD/MM/YYYY');

SQL Server 示例

SELECT
    FORMAT(CONVERT(DATE, created_at, 103), 'dd/MM') AS vote_date,
    COUNT(voters_id) AS daily_votes,
    SUM(COUNT(voters_id)) OVER (ORDER BY CONVERT(DATE, created_at, 103)) AS cumulative_votes
FROM voters
GROUP BY CONVERT(DATE, created_at, 103), FORMAT(CONVERT(DATE, created_at, 103), 'dd/MM')
ORDER BY CONVERT(DATE, created_at, 103);

针对不支持窗口函数的旧版数据库（如 MySQL 5.x） #

用子查询实现累计统计：

SELECT
    DATE_FORMAT(STR_TO_DATE(v1.created_at, '%d/%m/%Y'), '%d/%m') AS vote_date,
    COUNT(v1.voters_id) AS daily_votes,
    -- 子查询统计当前日期及之前的所有票数
    (SELECT COUNT(v2.voters_id) 
     FROM voters v2 
     WHERE STR_TO_DATE(v2.created_at, '%d/%m/%Y') <= STR_TO_DATE(v1.created_at, '%d/%m/%Y')) AS cumulative_votes
FROM voters v1
GROUP BY STR_TO_DATE(v1.created_at, '%d/%m/%Y'), DATE_FORMAT(STR_TO_DATE(v1.created_at, '%d/%m/%Y'), '%d/%m')
ORDER BY STR_TO_DATE(v1.created_at, '%d/%m/%Y');

输出结果 #

以上查询会返回符合需求的数据：

其中cumulative_votes就是「前一日累计 + 当日票数」的总和，可直接用于ChartJS的图表渲染。

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