mysql - 对我的多个日期范围进行分组查询

Question

我需要使用多个日期范围的计数和求和来查询数据，我正在寻找比我现在正在做的查询更快的查询。
我有一个包含日期和金额的交易表。我需要按今天、昨天、本周、上周、本月、上个月的日期范围提供一个包含交易计数和总金额的表格。目前我正在做子查询，有没有更好的方法？

select  
(select count(date) from transactions where date between ({{today}})) as count_today,  
(select sum(amount) from transactions where date between ({{today}})) as amount_today,  
(select count(date) from transactions where date between ({{yesterday}})) as count_yesterday,  
(select sum(amount) from transactions where date between ({{yesterday}})) as amount_yesterday,  
(select count(date) from transactions where date between ({{thisweek}})) as count_thisweek,  
(select sum(amount) from transactions where date between ({{thisweek}})) as amount_thisweek,  
etc...

有没有更好的办法？

Answer 1

虽然您有一个明确的解决方案，但我还有另一个可能会使用 MySQL 变量进一步简化您的查询，这样您就不必输入错误/计算日期等...

无需预先声明变量，您可以将它们作为选择语句内联，然后将它们当作另一个表中的列来使用。由于它是作为单行创建的，因此没有笛卡尔结果。首先是查询，然后我将描述其计算。

select
      sum( if( t.date >= @today AND t.date < @tomorrow, 1, 0 )) as TodayCnt,
      sum( if( t.date >= @today AND t.date < @tomorrow, amount, 0 )) as TodayAmt,
      sum( if( t.date >= @yesterday AND t.date < @today, 1, 0 )) as YesterdayCnt,
      sum( if( t.date >= @yesterday AND t.date < @today, amount, 0 )) as YesterdayAmt,
      sum( if( t.date >= @FirstOfWeek AND t.date < @EndOfWeek, 1, 0 )) as WeekCnt,
      sum( if( t.date >= @FirstOfWeek AND t.date < @EndOfWeek, amount, 0 )) as WeekAmt
   from
      transations t,
   ( select @today := curdate(),
               @yesterday := date_add( @today, interval -1 day ),
               @tomorrow := date_add( @today, interval 1 day ),
               @FirstOfWeek := date_add( @today, interval +1 - dayofweek( @today) day ),
               @EndOfWeek := date_add( @FirstOfWeek, interval 7 day ),
               @minDate := least( @yesterday, @FirstOfWeek ) ) sqlvars
   where
          t.date >= @minDate
      AND t.date < @EndOfWeek

现在，日期。由于@variables 是按顺序准备的，因此您可以将其视为设置变量的内联程序。由于它们是预查询，因此它们首先完成，并在前面所述的其余查询期间可用。因此，首先，我正在处理任何只获取日期部分而不考虑时间的“curdate()”。从中减去 1 天(加 -1)得到昨天的开始。添加 1 天以获得明天。然后，一周的第一天是当前日期 +1 - 一周中的实际日期(您很快就会看到)。从一周的第一天开始加上 7 天，得到一周的结束。最后，获取昨天(可能存在于前一周末)或本周初之间的最小日期。

Now look at today for example... Feb 23rd.
Sun  Mon  Tue  Wed  Thu  Fri  Sat  Sun
21   22   23   24   25   26   27   28

Today = 23
Yesterday = 22
Tomorrow = 24
First of week = 23 + 1 = 24 - 3rd day of week = 21st
End of Week = 21st + 7 days = 28th.

为什么我要截断日期剥离时间？简化 >= AND < 的 SUM() 条件。如果我说某个日期 = 今天，如果您的交易带有时间戳怎么办。然后，您必须提取日期部分才能符合条件。通过这种方法，我可以说“今天”的计数和金额是 >= 2 月 23 日午夜 12 点且 < 2 月 24 日午夜 12 点的任何日期。这是所有时间，包括 2 月 23 日到晚上 11:59:59，因此少于 2 月 24 日(明天)。

昨天的类似考虑包括所有的 UP TO 但不包括“今天”是什么。周范围也是如此。

最后，WHERE 子句寻找最早的日期作为范围，因此它不必遍历整个交易数据库直到结束。

最后，如果您想要前一周/期间的计数和总数，无论如何，您可以推断和更改

 @today := '2015-01-24'

并且计算将如同查询在该日期运行一样。

类似地，如果您想要改变一个月，您可以计算一个月的第一天到下一个月的第一天，以获得每月总计。

希望您喜欢这个灵活的解决方案。