DATEDIFF显示过去30天，60天和90天的购买情况

Your boss loved the pivot query you wrote in the first project. He showed the accountant, and she said she wants to see a similar query, but with sales in the last 30, 60, and 90 days.

你的老板喜欢你在第一个项目中写的数据透视查询。他向会计师展示了,她说她希望看到类似的查询,但是在过去的30,60和90天都有销售。

MySQL has a function that can be used to compare two dates - DATEDIFF: SELECT DATEDIFF(now(), DateOfPurchase) FROM Purchases MySQL subtracts the DateOfPurchase from "now" and returns an integer representing the number of days between those two dates.

MySQL有一个可用于比较两个日期的函数 - DATEDIFF:SELECT DATEDIFF(now(),DateOfPurchase)FROM Purchases MySQL从“now”中减去DateOfPurchase并返回一个整数,表示这两个日期之间的天数。

You can use this function to write your accountant's requested pivot query. The results might look similar to the following:

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

 Album         D30  D60  D90

 OK Computer    2    5    3 
 Sea Change     8    6    2

Note that each time period has separate data. D60 data should be for purchases from 31 to 60 days ago, not everything purchased in the last 60 days. Similarly, D90 data should be for purchases from 61 to 90 days ago, not everything purchased in the last 90 days.

请注意,每个时间段都有单独的数据。 D60数据应该是31至60天前的购买,而不是过去60天内购买的所有商品。同样,D90数据应该是61至90天前的购买,而不是过去90天内购买的所有商品。

Do not create a separate table or a view.

不要创建单独的表或视图。

Save your query as dba1lesson10project2.sql and then hand in the project.

将查询保存为dba1lesson10project2.sql,然后交给项目。

I have entered Purchase data into my Purchases table that are 30 60 and 90 days apart.

我已将购买数据输入到我的购买表中,相隔30 60和90天。

My table looks like

我的桌子看起来像

 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

So, I'm only going to have 1 purchase per 30 day period to show. My previous project was to show purchases per quarter, which is why the 2007 data is in there. I've been speaking with my instructor regarding this, and he said my code for this would be similar to my code for my last project.

所以,我每30天只会购买一次。我以前的项目是每季度购买一次,这就是2007年数据的原因。我一直和我的老师谈论这件事,他说我的代码与我上一个项目的代码类似。

I have tried

我试过了

 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;

Which returns:

 DATEDIFF(now(), DateOfPurchase   D30   D60   D90

              2365                 0     0     0

I have tried a couple different variants of this code, which pretty much return the same result. Other than the number in the first column being the same.

我尝试了几个不同的代码变体,几乎返回相同的结果。除了第一列中的数字是相同的。

The code to my last project is below, if that helps at all.

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

 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;

I'm also aware I will have to do the table joins for this project also. I'm just trying to get the correct output for D30 D60 and D90 before I do that.

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

1 个解决方案

#1

You want something like this

你想要这样的东西

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

Note: Since you're pivoting only 90 days worth of data from current day you better filter out everything else with WHERE clause and do it in an index-friendly manner.

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

Sample output:

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

Here is SQLFiddle demo

这是SQLFiddle演示

#1