计数价格范围内的订单，按每周数量计数

Question

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

我希望这是有道理的。

这是我能想到的，但是我在高级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

输出范围和订单良好的

，我只需要添加一周组即可。

预先感谢。

Answer 1

您可以使用CONCAT(YEAR(date), '/', WEEK(date))找到一周。然后，您可以将其分组：

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

Rextester的示例。

如果您想列出所有库尔维斯和几周，即使是那些没有订单的人，则可以将所有库尔维斯（如已经完成的那样）添加到左联接的右侧。通常更容易做客户端。

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