mysql - DATEDIFF 显示过去 30、60 和 90 天的购买情况

Question

您的老板喜欢您在第一个项目中编写的数据透视查询。他给会计师看了，她说她想看一个类似的查询，但包含过去 30、60 和 90 天的销售额。

MySQL 有一个函数可以用来比较两个日期 - DATEDIFF:从购买中选择 DATEDIFF(now(), DateOfPurchase)MySQL 从“现在”中减去 DateOfPurchase 并返回一个整数，表示这两个日期之间的天数。

您可以使用此函数编写您的会计师请求的数据透视查询。结果可能类似于以下内容:

 Album         D30  D60  D90

 OK Computer    2    5    3 
 Sea Change     8    6    2 

请注意，每个时间段都有单独的数据。 D60 数据应该是 31 到 60 天前的购买数据，而不是最近 60 天购买的所有数据。同样，D90 数据应该是 61 到 90 天前的购买数据，而不是最近 90 天内购买的所有数据。

不要创建单独的表或 View 。

将查询保存为dba1lesson10project2.sql，然后提交项目。

我已将购买数据输入到我的购买表中，这些数据分别为 30、60 和 90 天。

我的 table 看起来像

 Customer ID     DateOfPurchase     SongID

     1              2007-03-31        3
     3              2007-06-30        4
     4              2007-09-30        4
     5              2007-12-31        5
     2              2013-08-21        2
     4              2013-07-22        5
     5              2013-06-22        1

因此，我每 30 天只显示 1 次购买。我之前的项目是显示每个季度的购买量，这就是为什么 2007 年的数据在那里。我一直在和我的导师讨论这个问题，他说我的代码与我上一个项目的代码相似。

我试过了

 SELECT DATEDIFF(now(), DateOfPurchase),
 SUM(CASE WHEN DateOfPurchase <= 30 THEN 1 ELSE 0 END) AS 'D30',
 SUM(CASE WHEN DateOfPurchase >= 31 AND DateOfPurchase = 60 THEN 1 ELSE 0 END) AS 'D60',
 SUM(CASE WHEN DateOfPurchase >= 61 AND DateOfPurchase = 90 THEN 1 ELSE 0 END) AS 'D90'
 FROM Purchases;

哪个返回:

 DATEDIFF(now(), DateOfPurchase   D30   D60   D90

              2365                 0     0     0

我已经尝试了这段代码的几个不同变体，它们返回的结果几乎相同。除了第一列中的数字相同。

我上一个项目的代码如下，如果有帮助的话。

 SELECT A.Title,
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 1 THEN 1 ELSE 0 END) AS 'Q1',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 2 THEN 1 ELSE 0 END) AS 'Q2',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 3 THEN 1 ELSE 0 END) AS 'Q3',
 SUM(CASE WHEN QUARTER(DateOfPurchase) = 4 THEN 1 ELSE 0 END) AS 'Q4'
 FROM Albums AS A
 INNER JOIN Songs AS S
 ON A.AlbumID = S.AlbumID
 INNER JOIN Purchases as P
 ON S.SongID = P.SongID
 GROUP BY A.Title;

我也知道我还必须为这个项目做表连接。在我这样做之前，我只是想获得 D30 D60 和 D90 的正确输出。

Answer 1

你想要这样的东西

SELECT SUM(CASE WHEN DATEDIFF(CURDATE(), DateOfPurchase) 
                     BETWEEN  0 AND 30 THEN 1 ELSE 0 END) D30,
       SUM(CASE WHEN DATEDIFF(CURDATE(), DateOfPurchase) 
                     BETWEEN 31 AND 60 THEN 1 ELSE 0 END) D60,
       SUM(CASE WHEN DATEDIFF(CURDATE(), DateOfPurchase) 
                     BETWEEN 61 AND 90 THEN 1 ELSE 0 END) D90
  FROM Purchase
 WHERE DateOfPurchase BETWEEN CURDATE() - INTERVAL 90 DAY AND CURDATE()

注意:由于您仅从当天开始旋转 90 天的数据，因此您最好使用 WHERE 子句过滤掉所有其他内容，并以索引友好的方式进行方式。

示例输出:

| D30 | D60 | D90 ||-----|-----|-----||   1 |   1 |   1 |

这是 SQLFiddle 演示