获取上个月，周，日的重复日期条目的总和-Java 学习之路

我有一个包含3列的表： id ， updated_at ， click_sum .

许多行具有完全相同的 updated_at 值，这使得很难简单地检索数据 order by updated_at 并在图表中显示总和 . 由于相同日期有多个总和，这会使图表变得复杂 .

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

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

可选地，只有那些形成上个月，周或日_272682_的日期不仅仅是 NOW() - 1 月的日期 .

我构建的当前查询非常大，并不能很好地工作 . 它按日期分组（没有重复日期出现）和 SUM() s正确点击，但定义从何时（上个月，周，日）日期似乎不正常 .

查询：（ $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

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);

回复于 2024-04-28T08:21:28+08:00

获取上个月，周，日的重复日期条目的总和

1 回答

----------

相关问题