如何通过MySQL计算带有日期间隔的统计信息

Question

我想用日期和动态间隔(周、月、年)来计算一些统计数据(带有一个MySQL后端)。下面是一个小小的例子：

Mysql表: tracker_click

| ID   | SITE_ID | CREATED_AT          |
| ---- |---------| --------------------|
| 153  | 2       | 2013-07-22 15:43:25 |
| 154  | 2       | 2013-07-25 16:45:46 |
| 2501 | 2       | 2013-09-15 17:45:48 |

我希望通过一个查询按周获得最后一个月的点击总数，并在最后一年按月获得相同的点击次数。

过去一个月的每周我想要的一个例子是：

| click number   | SITE_ID | BEGIN_DATE           | END_DATE            |
|----------------|---------|----------------------|---------------------|
| 25             |    2    |  2013-07-01 00:00:00 | 2013-07-08 00:00:00 |
| 19             |    2    |  2013-08-09 00:00:00 | 2013-08-16 00:00:00 |
| 53             |    2    |  2013-0717- 00:00:00 | 2013-08-24 00:00:00 |

我不知道是否有一个解决方案来精确地处理这个数组，只有一个查询而没有任何其他进程。

谢谢

Answer 1

这将使您获得最后一个月的计数(即最后4个星期)，包括每个站点id的计数为0的周。如果您有一个站点表来获取站点id，这意味着可以将子查询的交叉连接替换为对表的简单交叉连接。

这将生成从0到5的一系列数字，并从当前日期减去该周数，格式为给出结果周的星期日和星期六，并检查结果周是否介于当前日期和当前日期减去1个月之间(这样做的话，只需减去4周以处理可变长度的月份)。

SELECT Weeks.aWeek_start, Weeks.aWeek_end, all_site_id.site_id, COUNT(tracker_click.id)
FROM
(
    SELECT STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i WEEK), '%Y%U Sunday 00:00:00'), '%X%V %W %H:%i:%s') AS aWeek_start,
            STR_TO_DATE(DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i WEEK), '%Y%U Saturday 23:59:59'), '%X%V %W %H:%i:%s') AS aWeek_end
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5)units
    WHERE DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i WEEK), '%Y%U') BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y%U') AND DATE_FORMAT(NOW(), '%Y%U')
) Weeks
CROSS JOIN
(
    SELECT DISTINCT site_id
    FROM tracker_click
) AS all_site_id
LEFT OUTER JOIN tracker_click
ON tracker_click.CREATED_AT BETWEEN Weeks.aWeek_start AND Weeks.aWeek_end
AND tracker_click.site_id = all_site_id.site_id
GROUP BY Weeks.aWeek_start, Weeks.aWeek_end, all_site_id.site_id

类似的查询可以在一年中的几个月内完成。

SELECT Months.aMonth_start, Months.aMonth_end, all_site_id.site_id, COUNT(tracker_click.id)
FROM
(
    SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL units.i MONTH), '%Y/%m/01 00:00:00') AS aMonth_start,
            DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL units.i MONTH)), '%Y/%m/%d 23:59:59') AS aMonth_end
    FROM (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11)units
) Months
CROSS JOIN
(
    SELECT DISTINCT site_id
    FROM tracker_click
) AS all_site_id
LEFT OUTER JOIN tracker_click
ON tracker_click.CREATED_AT BETWEEN Months.aMonth_start AND Months.aMonth_end
AND tracker_click.site_id = all_site_id.site_id
GROUP BY Months.aMonth_start, Months.aMonth_end, all_site_id.site_id

Answer 2

SELECT count(*), site_id, extract(WEEK from created_at) as start_date, 
date_add(extract(WEEK from created_at), 1, week) as end_date
FROM click_tracker
GROUP BY site_id, extract(WEEK from created_at)

此外，还可以添加where子句来筛选结果。