mysql - 计算 30 天 bin 中的行数

Question

我表中的每一行都有一个日期时间戳，我希望从现在开始查询数据库，以计算过去 30 天、之前 30 天等的行数。直到有一个 30 天的 bin 返回到表的开头。

我已经使用 Python 并进行了多次查询，成功地执行了此查询。但我几乎可以肯定，它可以在一个 MySQL 查询中完成。

Answer 1

没有存储过程，没有临时表，只有一个查询，以及给定日期列索引的高效执行计划:

select

  subdate(
    '2012-12-31',
    floor(dateDiff('2012-12-31', dateStampColumn) / 30) * 30 + 30 - 1
  ) as "period starting",

  subdate(
    '2012-12-31',
    floor(dateDiff('2012-12-31', dateStampColumn) / 30) * 30
  ) as "period ending",

  count(*)

from
  YOURTABLE
group by floor(dateDiff('2012-12-31', dateStampColumn) / 30);

这里发生的事情应该很明显，除了这个咒语:

floor(dateDiff('2012-12-31', dateStampColumn) / 30)

该表达式出现了几次，它的计算结果为 dateStampColumn 之前 30 天的周期数。 dateDiff 返回天数差值，将其除以 30 得到 30 天的差值，并将其全部提供给 floor() 以将其四舍五入为整数。一旦我们有了这个数字，我们就可以对它进行GROUP BY，然后我们进一步做一些数学运算，将这个数字转换回该时间段的开始和结束日期。

如果您愿意，请将 '2012-12-31' 替换为 now()。下面是一些示例数据:

CREATE TABLE YOURTABLE
    (`Id` int, `dateStampColumn` datetime);

INSERT INTO YOURTABLE
    (`Id`, `dateStampColumn`)
VALUES
    (1, '2012-10-15 02:00:00'),
    (1, '2012-10-17 02:00:00'),
    (1, '2012-10-30 02:00:00'),
    (1, '2012-10-31 02:00:00'),
    (1, '2012-11-01 02:00:00'),
    (1, '2012-11-02 02:00:00'),
    (1, '2012-11-18 02:00:00'),
    (1, '2012-11-19 02:00:00'),
    (1, '2012-11-21 02:00:00'),
    (1, '2012-11-25 02:00:00'),
    (1, '2012-11-25 02:00:00'),
    (1, '2012-11-26 02:00:00'),
    (1, '2012-11-26 02:00:00'),
    (1, '2012-11-24 02:00:00'),
    (1, '2012-11-23 02:00:00'),
    (1, '2012-11-28 02:00:00'),
    (1, '2012-11-29 02:00:00'),
    (1, '2012-11-30 02:00:00'),
    (1, '2012-12-01 02:00:00'),
    (1, '2012-12-02 02:00:00'),
    (1, '2012-12-15 02:00:00'),
    (1, '2012-12-17 02:00:00'),
    (1, '2012-12-18 02:00:00'),
    (1, '2012-12-19 02:00:00'),
    (1, '2012-12-21 02:00:00'),
    (1, '2012-12-25 02:00:00'),
    (1, '2012-12-25 02:00:00'),
    (1, '2012-12-26 02:00:00'),
    (1, '2012-12-26 02:00:00'),
    (1, '2012-12-24 02:00:00'),
    (1, '2012-12-23 02:00:00'),
    (1, '2012-12-31 02:00:00'),
    (1, '2012-12-30 02:00:00'),
    (1, '2012-12-28 02:00:00'),
    (1, '2012-12-28 02:00:00'),
    (1, '2012-12-30 02:00:00');

结果:

period starting     period ending   count(*)
2012-12-02          2012-12-31      17
2012-11-02          2012-12-01      14
2012-10-03          2012-11-01      5

期间端点包括在内。

在 SQL Fiddle 中玩这个.

结果中不会包含任何具有零个匹配行的 30 天期间，这有一点潜在的愚蠢之处。如果您可以将其与周期表结合起来，则可以将其消除。然而，MySQL 没有像 PostgreSQL 的 generate_series() 那样的东西。 , 所以你必须在你的应用程序中处理它或尝试 this clever hack .