大表月度点击数据拆分为单日的高效SQL查询方案求助

看来你现在卡在了用SUM(CASE)拆分单日数据时查询耗时过长的问题上，还想避开子表/子查询的方案。我给你几个更高效的思路：

1. 改用GROUP BY日期的纵向统计（推荐） #

这种写法不仅简洁，数据库优化器的处理效率也远高于多个SUM(CASE)的组合，而且更适配大多数图表工具的数据源格式：

SELECT 
    DATE(TIME) AS click_date,
    COUNT(*) AS daily_clicks
FROM your_table
-- 用左闭右开的范围更准确，避免漏掉当天最后一秒的数据
WHERE TIME >= '2018-04-08' AND TIME < '2018-04-11'
GROUP BY DATE(TIME)
ORDER BY click_date;

这个查询只会扫描一次符合时间范围的数据，然后按日期分组统计，比你原来多次计算SUM(CASE)的方式减少了重复扫描的开销。

2. 给TIME字段添加索引 #

如果你的TIME字段还没有索引，这是提升查询速度最关键的一步。创建索引后，数据库可以快速定位到目标时间范围内的数据，避免全表扫描：

CREATE INDEX idx_click_time ON your_table(TIME);

有了这个索引，不管是上面的GROUP BY查询还是你原来的SUM(CASE)查询，都会快很多。

3. 如果确实需要横向列（每日作为单独列） #

如果你因为图表需求必须把日期转为横向的列（比如8th、9th这样的列名），可以用动态SQL自动生成CASE语句，避免手动写一堆重复代码，同时依然能利用索引：

SET @sql = NULL;
SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SUM(CASE WHEN DATE(TIME) = ''', DATE(TIME), ''' THEN 1 ELSE 0 END) AS `', DATE_FORMAT(TIME, '%dth'), '`'
    )
) INTO @sql
FROM your_table
WHERE TIME >= '2018-04-08' AND TIME < '2018-04-11';

SET @sql = CONCAT('SELECT ', @sql, ' FROM your_table WHERE TIME >= ''2018-04-08'' AND TIME < ''2018-04-11'';');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

这种动态生成的方式既保持了你想要的横向结果，又不用手动维护每个日期的CASE逻辑，同时依托索引过滤数据。

为什么你的原查询慢？ #

你原来的写法中，每个SUM(CASE)都需要对符合条件的数据集进行一次遍历计算，相当于多次扫描相同的数据。而GROUP BY的方式只需要扫描一次数据并分组统计，开销要小得多。

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