SQL如何按天统计指定value_type类型的记录数量

首先你给出的预期输出存在笔误，按照示例数据计算，2020-05-15的upload记录为2条，2020-05-16的upload记录为1条，正确输出应为：

需求核心逻辑是将精确到毫秒的time字段截断到天维度，过滤出value_type为upload的记录后分组计数即可，不同数据库的对应写法如下：

MySQL #

SELECT 
    DATE(`time`) AS `day`,
    COUNT(*) AS upload_num
FROM 替换为实际表名
WHERE value_type = 'upload'
GROUP BY DATE(`time`)
ORDER BY `day`;

PostgreSQL #

SELECT 
    DATE_TRUNC('day', time)::DATE AS day,
    COUNT(*) AS upload_num
FROM 替换为实际表名
WHERE value_type = 'upload'
GROUP BY DATE_TRUNC('day', time)::DATE
ORDER BY day;

SQL Server #

SELECT 
    CAST(time AS DATE) AS day,
    COUNT(*) AS upload_num
FROM 替换为实际表名
WHERE value_type = 'upload'
GROUP BY CAST(time AS DATE)
ORDER BY day;

Oracle #

SELECT 
    TRUNC(time) AS day,
    COUNT(*) AS upload_num
FROM 替换为实际表名
WHERE value_type = 'upload'
GROUP BY TRUNC(time)
ORDER BY day;

所有写法的核心逻辑一致：先将timestamp类型的time字段转换为仅保留日期的格式，相同日期的记录会被划分到同一分组，过滤出符合条件的记录后计数，即可得到每日的upload记录总数。

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