按时间戳分组的SQL未按日统计销量的异常问题排查

DevOps 小满

2026-5-26

解决按日统计产品销售总量的SQL问题

嘿，我懂你遇到的问题了——你当前的SQL是按完整的时间戳（包含时分秒）来分组的，所以哪怕是同一天的不同时间点，都会被拆成单独的分组，这就导致你没法拿到每日的销售总量。

核心解决方案

要实现按日统计，你需要把created_at字段转换为纯日期格式（忽略时分秒），然后按转换后的日期分组。不同数据库的日期转换函数略有不同，下面是几种常见数据库的实现方式：

MySQL/MariaDB 版本

SELECT 
    DATE(created_at) AS sale_date, 
    SUM(quantity) AS product_sales 
FROM sales_products 
WHERE created_at > '2018-04-19' 
GROUP BY DATE(created_at) 
ORDER BY sale_date DESC 
LIMIT 100;

PostgreSQL 版本

SELECT 
    DATE_TRUNC('day', created_at)::DATE AS sale_date, 
    SUM(quantity) AS product_sales 
FROM sales_products 
WHERE created_at > '2018-04-19' 
GROUP BY DATE_TRUNC('day', created_at) 
ORDER BY sale_date DESC 
LIMIT 100;

SQL Server 版本

SELECT 
    CAST(created_at AS DATE) AS sale_date, 
    SUM(quantity) AS product_sales 
FROM sales_products 
WHERE created_at > '2018-04-19' 
GROUP BY CAST(created_at AS DATE) 
ORDER BY sale_date DESC 
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

Oracle 版本

SELECT 
    TRUNC(created_at) AS sale_date, 
    SUM(quantity) AS product_sales 
FROM sales_products 
WHERE created_at > TO_DATE('2018-04-19', 'YYYY-MM-DD') 
GROUP BY TRUNC(created_at) 
ORDER BY sale_date DESC 
FETCH FIRST 100 ROWS ONLY;