mysql - 将所有日期转换为日期范围

Question

我的数据库中有一个名为 sale 的表:

SaleID (PK)
Quantity
Date
Time
TotalPrice
TransactionID (FK)

然后我在同一个数据库中有一个测试表，其结构如下:

SaleID (PK)
DateFrom
DateTo
TotalPrice
AveragePrice

在销售表中，我有 60,000 个日期。在我的测试表中，我希望 date from 和 date to 是一周。例如:

DateFrom - 1st January 2016.
DateTo - 7th January 2016.
AveragePrice - 20,000

它可以让我查看每周平均销售额。有什么办法可以将 7 天范围内的所有日期都从销售中取出并将它们插入测试中吗？我想不出可以使这项工作的查询。我知道如何做一个星期的查询:

SELECT * FROM sale where date between 'January 1 2016' and 'January 7 2016'

但是我不确定如何插入 2016 年 1 月 1 日作为 FromDate 和 2016 年 1 月 7 日作为 ToDate 并每隔 7 天执行一次。可能吗？

Answer 1

这个不行。 2014 年的最后一周假设是第 1 周

SELECT 
      YEAR(`Date`),
      WEEKOFYEAR(`Date`),
      AVG(TotalPrice)
FROM Sales
GROUP BY  YEAR(`Date`),
          WEEKOFYEAR(`Date`)

2016 年 1 月 1 日将有一周少于 7 天

如果你想要每周工作 7 天，你可以使用:

SQL Fiddle Demo

SELECT 
      SUBDATE(`Date`, WEEKDAY(`Date`)) MondayWeek,
      AVG(`TotalPrice`)
FROM Sales
GROUP BY SUBDATE(`Date`, WEEKDAY(`Date`))

我包含另一个查询以查看它是如何工作的

SELECT `Date`, WEEKDAY(`Date`), YEAR(`Date`), WEEKOFYEAR(`Date`),  yearWeek(`Date`)
FROM Sales
order by `Date`;

输出

|       Date | WEEKDAY(`Date`) | YEAR(`Date`) | WEEKOFYEAR(`Date`) | YEARWEEK(`Date`) |
|------------|-----------------|--------------|--------------------|------------------|
| 2014-12-24 |               2 |         2014 |                 52 |           201451 |
| 2014-12-25 |               3 |         2014 |                 52 |           201451 |
| 2014-12-26 |               4 |         2014 |                 52 |           201451 |
| 2014-12-27 |               5 |         2014 |                 52 |           201451 |
| 2014-12-28 |               6 |         2014 |                 52 |           201452 |
| 2014-12-29 |               0 |         2014 |  <===>           1 |           201452 | ***
| 2014-12-30 |               1 |         2014 |                  1 |           201452 |
| 2014-12-31 |               2 |         2014 |                  1 |           201452 |
| 2015-01-01 |               3 |         2015 |  <===>           1 |           201452 | ***
| 2015-01-02 |               4 |         2015 |                  1 |           201452 |
| 2015-01-03 |               5 |         2015 |                  1 |           201452 |
| 2015-01-04 |               6 |         2015 |                  1 |           201501 | ***
| 2015-01-05 |               0 |         2015 |                  2 |           201501 |
| 2015-01-06 |               1 |         2015 |                  2 |           201501 |
| 2015-01-07 |               2 |         2015 |                  2 |           201501 |
| 2015-01-08 |               3 |         2015 |                  2 |           201501 |
| 2015-01-09 |               4 |         2015 |                  2 |           201501 |
| 2015-01-10 |               5 |         2015 |                  2 |           201501 |
| 2015-01-11 |               6 |         2015 |                  2 |           201502 |
| 2015-01-12 |               0 |         2015 |                  3 |           201502 |
| 2015-01-13 |               1 |         2015 |                  3 |           201502 |
| 2015-01-14 |               2 |         2015 |                  3 |           201502 |
| 2015-01-15 |               3 |         2015 |                  3 |           201502 |
| 2015-01-16 |               4 |         2015 |                  3 |           201502 |