mySQL，按日期分组数据

Question

你好:这个问题似乎已经有了很多不同的版本，但是我还没能根据我的需求来调整它们(至少在某种程度上是行不通的)。

我有一张这样的桌子：

CREATE TABLE `AdServing`
(
  `imgID`           int UNSIGNED NOT NULL,
  `ServedTimeStamp` timestamp NOT NULL,
  `UserID`          int(10) UNSIGNED NOT NULL,
  `ServedIP`        VARBINARY(16) NULL,
  `Clicked`         Boolean NULL,
)

一些样本数据：

1   2015-08-04 14:59:48     1   192.168.0.4     1
1   2015-08-14 23:06:25     1   192.168.0.3     1
1   2015-08-16 15:00:09     1   192.168.0.3     1
1   2015-08-16 15:01:15     1   192.168.0.3     1
1   2015-08-16 15:01:43     1   192.168.0.3     1
1   2015-08-17 15:00:51     1   192.168.0.4     1
2   2015-07-26 17:55:21     1   192.168.0.2     0
3   2015-07-26 17:57:15     1   192.168.0.3     1
4   2015-07-26 17:57:15     1   192.168.0.4     1

我想要创建一个图表，显示广告的数量随着时间的推移-从第一次显示的日期，直到最近的显示日期。就像这样：

​

​

对于一些用户来说，这可能只需要一个星期。对其他人来说，可能是4到5个月。对于另一个用户来说，这可能跨越一到两年的时间。

我认为，不管时间周期如何，图表的时间轴上可能会有6到7个数据点--因此，对于只有一周数据的用户来说，间隔可能是1天，或者对于拥有几个月数据的用户来说是1周.我不确定确定这一点的最佳策略是什么--如果一个用户只有一个月的数据，那么如何以一种有意义的方式将天划分为6/7组呢？

但目前的主要问题是查询数据库，将表值提取到这些分组中。我想说的是这样的：

DateGrouping  |  Servings
-------------------------
1             |  2
2             |  5
3             |  3
4             |  7
5             |  2
6             |  4
7             |  3

对于当前用户的数据，...where 'DateGrouping‘将是任何适当的间隔，也就是说，对于一个星期的值，1-7表示单个天。“服务”是每隔一段时间提供的广告数量。同样地，假设每隔一天在这里，那将是在每一个记录的24小时期间服务的广告数量(这可能是连续的，也可能不是连续的)。

我试着修改这个查询，所以答案：https://stackoverflow.com/a/30335592/2577646

并想出了这个：

SELECT x.DateRange, COALESCE(TotalWithinRange, 0) AS TotalWithinRange
FROM (
  SELECT '1' AS DateRange 
  UNION SELECT '2'
  UNION SELECT '3'
  UNION SELECT '4'
  UNION SELECT '5'
  UNION SELECT '6'
  UNION SELECT '7') x
LEFT JOIN (  
   SELECT
      CASE when (`ServedTimeStamp` BETWEEN Max(`ServedTimeStamp`) - INTERVAL 1 DAY AND Max(`ServedTimeStamp`)) then '1'
       when (`ServedTimeStamp` BETWEEN Max(`ServedTimeStamp`) - INTERVAL 2 DAY AND Max(`ServedTimeStamp`) - INTERVAL 1 DAY) then '2'
       when (`ServedTimeStamp` BETWEEN Max(`ServedTimeStamp`) - INTERVAL 3 DAY AND Max(`ServedTimeStamp`) - INTERVAL 2 DAY) then '3'
       when (`ServedTimeStamp` BETWEEN Max(`ServedTimeStamp`) - INTERVAL 4 DAY AND Max(`ServedTimeStamp`) - INTERVAL 3 DAY) then '4'
       when (`ServedTimeStamp` BETWEEN Max(`ServedTimeStamp`) - INTERVAL 5 DAY AND Max(`ServedTimeStamp`) - INTERVAL 4 DAY) then '5'
       when (`ServedTimeStamp` BETWEEN Max(`ServedTimeStamp`) - INTERVAL 6 DAY AND Max(`ServedTimeStamp`) - INTERVAL 5 DAY) then '6'
       when (`ServedTimeStamp` BETWEEN Max(`ServedTimeStamp`) - INTERVAL 7 DAY AND Max(`ServedTimeStamp`) - INTERVAL 6 DAY) then '7'
  END AS DateRange,
  COUNT(*) as TotalWithinRange
FROM `AdServing`
WHERE `UserID` = 123
GROUP BY 1 ) y ON x.DateRange = y.DateRange

但mySQL抱怨道：“不能在‘DateRange’上分组”--我不完全理解。我对SQL非常陌生。还有很多其他的东西。

不管怎么说，我会非常感谢你的帮助。为这个冗长的问题道歉。

Answer 1

一个通用的版本，因为我今天有点忙：

SELECT MIN(dtField), MAX(dtField) INTO @first, @last 
FROM theTable 
WHERE [conditions]
;

SET @interval := DATEDIFF(@last, @first) / [number of datapoints you want];

SELECT DATEDIFF(dtField, @first) DIV @interval AS dateRange
, COUNT(1) AS TotalWithinRange
FROM theTable
WHERE [same conditions as the first query]
GROUP BY dateRange
;

编辑1: Oops，没有间隔上的除数。

编辑2:注，这假设至少7天。如果您希望获得更好的粒度，可以使用额外的函数将其缩小到秒级的精度。