日期范围内的 MySQL 频率

Question

我有一个 MySQL 数据库:

Date     Customer_ID

我怎样才能把它变成:

Customer_ID | Count_Visits_Past_Week | Count_Visits_Past_Month | Count_Visits_Past_90days  | Count_Total

注意: Count_Total = 其他三个计数的总和

谢谢

Answer 1

第一步是确定指定日期范围的分界点。

这里有几个问题需要回答:您是否只想比较日期('yyyy-mm-dd')并忽略任何时间部分？

“过去一周”是指过去 7 天内，还是指自上周日以来至今，还是指最后一周，从周日到周六。

“上个月”是指上个月，从月初到月末吗？或者这是否意味着如果查询在当月 20 日运行，我们需要从上个月 20 日到今天的日期？还是昨天？

一旦我们知道了每个指定时间段相对于今天日期的开始和结束时间点，我们就可以构建计算这些日期的表达式。

例如，“过去一周”可以表示为最近 7 天的时间段:

DATE(NOW())-INTERVAL 1 WEEK    -thru-   DATE(NOW())

“上个月”可以表示为上个月直到今天的同一“一个月中的某一天”(例如 17 日):

DATE(NOW())-INTERVAL 1 MONTH   -thru-   DATE(NOW())

这实际上是第一步，确定每个指定期间的开始和结束日期。

<小时/>

一旦我们有了这个，我们就可以继续构建一个查询，该查询获取带有 date 的行“计数”。每个时期内的列。

“技巧”是在查询的 SELECT 列表中的表达式中使用条件测试。如果该行要包含在“计数”中，我们将使用这些条件测试返回 1，如果应排除该行，则返回 0 或 NULL。

我更喜欢使用SUM()聚合函数来获取“计数”。但也可以使用 COUNT()总计的。 (如果我们使用 COUNT() ，我们需要使用一个在要排除该行时返回 NULL 的表达式。我更喜欢返回 1 或 0；我认为这使调试更容易。

以下是“计数”查询的概述。

SELECT t.Customer_Id
     , SUM(IF( <some_condition>  ,1,0) AS Count_something
     , SUM(IF( <other_condition> ,1,0) AS Count_something_else
  FROM mytable t
 GROUP BY t.Customer_Id

何时 <some_condition>为 true，我们返回 1，否则返回 0。

要测试条件表达式，通常最简单的方法是避免进行聚合，而只返回各个行:

这样，我们就可以看到每个“计数”中将包含哪些单独的行。

例如:

SELECT t.Customer_ID
     , t.date
     , IF(t.date BETWEEN DATE(NOW())-INTERVAL 1 WEEK  AND DATE(NOW()),1,0) 
       AS visit_past_week
     , IF(t.date BETWEEN DATE(NOW())-INTERVAL 1 MONTH AND DATE(NOW()),1,0) 
       AS visit_past_month
  FROM mytable t
 ORDER BY t.date, t.Customer_Id

该查询不返回“计数”，它只返回表达式的结果，这在测试中很有用。当然，我们想要测试返回每个期间的开始和结束日期的表达式:

SELECT DATE(NOW()) - INTERVAL 1 WEEK  AS past_week_begin
     , DATE(NOW())                    AS past_week_end

<小时/>

通过这种方法，可以通过一次查询和一次遍历表将同一行包含在多个“计数”中。

请注意 SUM() 内的表达式下面的查询中的聚集利用了一种方便的速记方式，计算为 bool 值的表达式将在 TRUE 时返回 1，如果 false 则返回 0，或 NULL。

要使用 COUNT 聚合，我们需要确保要聚合的表达式在要“计数”该行时返回非 NULL，而在要从计数中排除该行时返回 NULL。因此，如果表达式返回的值为零，我们可以使用方便的 NULLIF 函数返回 NULL。

SELECT t.Customer_ID
     , COUNT(NULLIF( t.date BETWEEN DATE(NOW())-INTERVAL 1 WEEK  AND DATE(NOW()),0))
       AS Count_Visits_Past_Week
     , COUNT(NULLIF( t.date BETWEEN DATE(NOW())-INTERVAL 1 MONTH AND DATE(NOW()),0))
       AS Count_Visits_Past_Month
     , COUNT(NULLIF( t.date BETWEEN DATE(NOW())-INTERVAL 90 DAY  AND DATE(NOW()),0))
       AS Count_Visits_Past_90days
     , COUNT(NULLIF( t.date BETWEEN DATE(NOW())-INTERVAL 1 WEEK  AND DATE(NOW()),0))
     + COUNT(NULLIF( t.date BETWEEN DATE(NOW())-INTERVAL 1 MONTH AND DATE(NOW()),0))
     + COUNT(NULLIF( t.date BETWEEN DATE(NOW())-INTERVAL 90 DAY  AND DATE(NOW()),0))
       AS Count_Total
  FROM mytable t
 GROUP BY t.Customer_Id

注:NULLIF(a,b)是 IF a IS NULL THEN return b ELSE return a 的便捷简写

返回 Count_Total 有点奇怪，因为它有可能多次“计数”同一行......但它返回的值应该与各个计数的总和相匹配。