php - 获取上个月、上周、上天的重复日期条目的总和

Question

我有一个包含 3 列的表格:id、updated_at、click_sum。

许多行具有完全相同的 updated_at 值，这使得很难简单地检索数据、按 update_at 排序并在图表中显示总和。由于同一日期有多个总和，这会破坏图表。

我试图实现的是获得以下输出:

 update_at | click_sum
-----------+-----------
   date1   |    100
   date2   |     3
   date3   |    235
   date4   |    231

可选地，仅包含上个月、上一周或上一天的日期AND，而不仅仅是NOW() - 1个月的日期。

我当前构建的查询非常大，而且效果不太好。它按日期进行分组(不会出现重复的日期)，并且 SUM() 可以正确地计算点击次数，但定义日期的时间(上个月、上周、上天)似乎无法正常工作。

查询:($interval代表MONTH或DAY或SECOND或WEEK)

SELECT d.updated_at, SUM(d.clicks_sum) AS click_sum
FROM aggregated_clicks d
JOIN 
(
     SELECT c.id, MAX(StartOfChains.updated_at) AS ChainStartTime
     FROM aggregated_clicks c
     JOIN 
     (
         SELECT DISTINCT a.updated_at
         FROM aggregated_clicks a
         LEFT JOIN aggregated_clicks b ON (b.updated_at >= a.updated_at - INTERVAL 1 DAY AND b.updated_at < a.updated_at)
         WHERE b.updated_at IS NULL
      ) StartOfChains  ON c.updated_at >= StartOfChains.updated_at
     GROUP BY c.id
) GroupingQuery
ON d.id = GroupingQuery.id
WHERE GroupingQuery.ChainStartTime >= DATE_SUB(NOW(), INTERVAL 1 $interval)
GROUP BY GroupingQuery.ChainStartTime
ORDER BY GroupingQuery.ChainStartTime ASC

Answer 1

也许我对您的问题的性质(及其引用的表格)假设太多，但我认为这可以比您显示的查询更简单地完成。

计算最近完成的月份并不难。

首先要知道当前月份的第一个日期——使用这个:

date_sub(curdate(), interval (extract(day from curdate())-1) day)

要知道上个月的第一天，请使用:

date_sub(date_sub(curdate(), interval extract(day from (curdate())-1) day), interval 1 month)

因此，如果您只想获取中间几天的总和(即最近完成的月份)，请使用以下命令:

select updated_at, sum(click_sum) from aggregated_clicks
  where updated_at >= date_sub(date_sub(curdate(), interval extract(day from (curdate())-1) day), interval 1 month)
    and updated_at < date_sub(curdate(), interval (extract(day from curdate())-1) day)
  group by updated_at;

计算最近完成的一周也同样容易。此示例将假设一周为周日至周六。

由于 ODBC 标准定义日期数字的方式，很容易找到上周的结束时间(星期六):

date_sub(curdate(), interval dayofweek(curdate()) day)

该周的开始(星期日)是在此之前的六天:

date_sub(curdate(), interval (dayofweek(curdate())+6) day)

因此，如果您只想获取中间几天的总和(即最近完成的一周)，请使用以下命令:

select updated_at, sum(click_sum) from aggregated_clicks
  where updated_at >= date_sub(curdate(), interval (dayofweek(curdate())+6) day)
    and updated_at <= date_sub(curdate(), interval dayofweek(curdate()) day)
  group by updated_at;

当然，根据最近完成的一天进行计算非常简单。

要获取前一天的日期，请使用:

date_sub(curdate(), interval 1 day)

因此，如果您想要昨天的总和，请使用以下内容:

select updated_at, sum(click_sum) from aggregated_clicks
  where updated_at = date_sub(curdate(), interval 1 day)
  group by updated_at;

注意:我已使用 MySQL 5.1、YMMV 测试了这些查询。

----------

更新:由于日期列是日期时间，只需将查询中对 updated_at 的所有引用更改为 date(updated_at)，如下所示:

月份案例:

select date(updated_at), sum(click_sum) from aggregated_clicks
  where date(updated_at) >= date_sub(date_sub(curdate(), interval extract(day from (curdate())-1) day), interval 1 month)
    and date(updated_at) < date_sub(curdate(), interval (extract(day from curdate())-1) day)
  group by date(updated_at);

周案例:

select date(updated_at), sum(click_sum) from aggregated_clicks
  where date(updated_at) >= date_sub(curdate(), interval (dayofweek(curdate())+6) day)
    and date(updated_at) <= date_sub(curdate(), interval dayofweek(curdate()) day)
  group by date(updated_at);

昨天的案例:

select date(updated_at), sum(click_sum) from aggregated_clicks
  where date(updated_at) = date_sub(curdate(), interval 1 day)
  group by date(updated_at);