对价格区间内的订单进行计数，按周数分组

Question

我必须从MySQL数据库中获取一些统计信息，需要获取订单大小范围内的订单量，然后需要从表created_at中的orders中按周进行分组

我希望这是有意义的。

这是我能想到的，但我在高级MySQL方面的经验很少。

SELECT 
   x.Kurv, COALESCE(ordre, 0) AS ordre
FROM (
  SELECT "0 - 100" AS Kurv
  UNION SELECT "100 - 200"
  UNION SELECT "200 - 300"
  UNION SELECT "300 - 400"
  UNION SELECT "400 - 500"
  UNION SELECT "500 - 600"
  UNION SELECT "over 600" ) x
LEFT JOIN 
  (SELECT
  CASE when base_total_ex_tax >= 0 and base_total_ex_tax <= 100 then "0 - 100"
       when base_total_ex_tax > 100 and base_total_ex_tax <= 200 then "100 - 200"
       when base_total_ex_tax > 200 and base_total_ex_tax <= 300 then "200 - 300"
       when base_total_ex_tax > 300 and base_total_ex_tax <= 400 then "300 - 400"
       when base_total_ex_tax > 400 and base_total_ex_tax <= 500 then "400 - 500"
       when base_total_ex_tax > 500 and base_total_ex_tax <= 600 then "500 - 600"
       else "over 600"
  END AS Kurv,
  COUNT(*) as ordre
FROM orders
WHERE
    created_at > '2017-01-01 00:00:00'
    && 
    status_id != 'canceled'
GROUP BY 1) 
    y ON x.Kurv = y.Kurv

它输出范围和顺序很好，我只需要添加week组。

提前谢谢。

Answer 1

你可以在CONCAT(YEAR(date), '/', WEEK(date))上找到week。然后你就可以在上面分组了：

SELECT  CONCAT(YEAR(date), '/', WEEK(date)) as wk
,       CASE
        WHEN amount <= 100 THEN '0 - 100'
        WHEN amount <= 200 THEN '100 - 200'
        ELSE '> 200'
        END as kurve
,       COUNT(*)
FROM    orderstable
GROUP BY
        wk
,       kurve

Example at rextester.

如果你想列出所有的kurve和weeks，甚至是那些没有订单的，你可以把所有的kurve(就像你已经做过的那样)和all weeks添加到左连接的右边。这通常更容易在客户端实现。

Answer 2

希望，我正确地理解了你的问题。

请检查下面的查询

      SELECT 
   Y.WEEK_VAL , x.Kurv, COALESCE(ordre, 0) AS ordre
FROM (
  SELECT "0 - 100" AS Kurv
  UNION SELECT "100 - 200"
  UNION SELECT "200 - 300"
  UNION SELECT "300 - 400"
  UNION SELECT "400 - 500"
  UNION SELECT "500 - 600"
  UNION SELECT "over 600" ) x
LEFT JOIN 
  (SELECT WEEK(created_at) WEEK_VAL, 
  CASE when 5 >= 0 and 5 <= 100 then "0 - 100"
       when 5 > 100 and 5 <= 200 then "100 - 200"
       when 5 > 200 and 5 <= 300 then "200 - 300"
       when 5 > 300 and 5 <= 400 then "300 - 400"
       when 5 > 400 and 5 <= 500 then "400 - 500"
       when 5 > 500 and 5 <= 600 then "500 - 600"
       else "over 600"
  END AS Kurv,
  COUNT(*) as ordre
FROM orders GROUP BY WEEK(created_at) ,  CASE when 5 >= 0 and 5 <= 100 then "0 - 100"
       when 5 > 100 and 5 <= 200 then "100 - 200"
       when 5 > 200 and 5 <= 300 then "200 - 300"
       when 5 > 300 and 5 <= 400 then "300 - 400"
       when 5 > 400 and 5 <= 500 then "400 - 500"
       when 5 > 500 and 5 <= 600 then "500 - 600"
       else "over 600"
  END) 
    y ON x.Kurv = y.Kurv