在一张大桌子上将一个月分成单独的几天

Question

我一直在试图找出一种有效的方法，将一个月的点击数据分解为图表的各个日子，但到目前为止，我整理的大多数查询都需要 20-30 秒，因为我很难想到一种没有子表 \ 子查询的方法。 到目前为止，我想出的最好的是：

SELECT
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/09' AND '2018/04/10') THEN 1 ELSE 0 END) 9th,
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/08' AND '2018/04/09') THEN 1 ELSE 0 END) 8th,
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/07' AND '2018/04/08') THEN 1 ELSE 0 END) 7th,
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/06' AND '2018/04/07') THEN 1 ELSE 0 END) 6th,
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/05' AND '2018/04/06') THEN 1 ELSE 0 END) 5th,
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/04' AND '2018/04/05') THEN 1 ELSE 0 END) 6th,
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/03' AND '2018/04/04') THEN 1 ELSE 0 END) 4th,
    SUM(CASE WHEN ( TIME BETWEEN '2018/04/02' AND '2018/04/03') THEN 1 ELSE 0 END) 3rd
FROM 
( 
    SELECT TIME, BIN_IP FROM CLICKS_IN WHERE USER_GROUP = 4 AND TIME BETWEEN '2018/04/02' AND '2018/04/10' 
)a;

解释：

id  select_type    table    partitions   type   possible_keys      key  key_len  ref      rows   filtered   Extra
1      SIMPLE    CLICKS_IN     NULL      ref   USER_GROUP,TIME  USER_GROUP  2   const   1614964    26.65    Using where

或其变体，但考虑到潜在的记录计数(每天可能获得 100k+ 点击(，它仍然非常低效。 此外，实际代码使用预准备语句，因此无需指出，为了清楚起见，我将值放在这里。

编辑：我发现使用以下方法的速度要快得多，但我担心当我在系统上有更多用户组时，它会遇到问题。

SELECT SUM(TIME >= '2018/04/09' AND TIME < '2018/04/10') as 9th,
       SUM(TIME >= '2018/04/08' AND TIME < '2018/04/09') as 8th,
       SUM(TIME >= '2018/04/06' AND TIME < '2018/04/08') as 7th,
       SUM(TIME >= '2018/04/05' AND TIME < '2018/04/07') as 6th,
       SUM(TIME >= '2018/04/04' AND TIME < '2018/04/06') as 5th,
       SUM(TIME >= '2018/04/03' AND TIME < '2018/04/05') as 4th,
       SUM(TIME >= '2018/04/02' AND TIME < '2018/04/04') as 3th       
FROM CLICKS_IN USE INDEX (TIME)
WHERE TIME BETWEEN '2018/04/02' AND '2018/04/10'
AND USER_GROUP = 4

Answer 1

SELECT SUM(TIME >= '2018-04-09' AND TIME < '2018-04-10') as 9th,
       SUM(TIME >= '2018-04-08' AND TIME < '2018-04-09') as 8th
FROM CLICKS_IN 
WHERE USER_GROUP = 4 
AND TIME >= '2018-04-02' 
AND TIME < '2018-04-11'

并确保在time和user_group列上有索引。然后它应该在几毫秒内运行。

Answer 2

您可以在每天作为行返回的情况下执行此操作。在调用 PHP 代码中可以更有效地将其从行旋转到列。

SELECT
    DAYOFMONTH(TIME) as `day`,
    COUNT(*) as `numclicks`
FROM `CLICKS_IN`
WHERE USER_GROUP = 4 AND TIME BETWEEN '2018/04/02' AND '2018/04/10'
GROUP BY DAYOFMONTH(TIME)
ORDER BY DAYOFMONTH(TIME)