mysql - 在 MySql 中对一周中的连续天进行分组

Question

我希望能够按周对销售表进行分组，但只能按前 x 天进行分组。

我可以轻松地按周分组

 SELECT SUM( order_total_price ) AS total, WEEK(order_time,1) AS week_number
 FROM orders
 WHERE YEAR( order_time ) = 2014
 GROUP BY WEEK( order_time, 1 ) 

所以我想获得一周中每一天的订单总数。我需要每天运行此查询 7 次。

这是一些示例数据。我选择了周一至周日的一系列总计

+-------------+-------------------+  
| order_time  | order_total_price |  
+-------------+-------------------+  
| 2014-03-03  |                20 |  
| 2014-03-04  |                25 |  
| 2014-03-05  |                30 |  
| 2014-03-06  |                15 |  
| 2014-03-07  |                20 |  
| 2014-03-08  |                15 |  
| 2014-03-09  |                30 |  
| 2014-03-10  |                20 |  
| 2014-03-11  |                15 |  
| 2014-03-12  |                10 |  
| 2014-03-13  |                25 |  
| 2014-03-14  |                30 |  
| 2014-03-15  |                25 |  
| 2014-03-16  |                10 |  
+-------------+-------------------+

这是我想要的结果

+----------+-------------+-------+   
| end_day  | week_number | total |   
+----------+-------------+-------+   
|        1 |         10  |    20 |  
|        2 |          10 |    45 |  
|        3 |          10 |    75 |  
|        4 |          10 |    90 |  
|        5 |          10 |   110 |  
|        6 |          10 |   125 |  
|        7 |          10 |   155 |  
|        1 |          11 |    20 |  
|        2 |          11 |    35 |   
|        3 |          11 |    45 |  
|        4 |          11 |    70 |  
|        5 |          11 |   100 |  
|        6 |          11 |   125 |  
|        7 |          11 |   135 |   
+----------+-------------+-------+  

end_day(1=Mon - 7=Sun) 是计算一周总计的日期。请注意总计是一周中那一天的总计。

Answer 1

SELECT
    sum(order_total_price) AS total,
    WEEK(order_time,1) AS week_number, date_format(order_time,'%w') as day_of_week
FROM orders
WHERE YEAR(order_time) = 2014
    AND date_format(order_time,'%w') >= 1
    AND date_format(order_time,'%w') <= 2
    -- date_format(date,format)
    -- %w: Day of the week (0=Sunday, 6=Saturday)
GROUP BY WEEK(order_time,1), date_format(order_time, '%w')