时间戳

首页 > 解决方案 > 如何计算数量订单的最大值、最小值、平均值和中值

mysql - 如何计算数量订单的最大值、最小值、平均值和中值

问题描述

我有一个名为 order_match 的表，其中包含 order_buyer_Id 作为交易的 id，createdby 作为买家的 id，createdAt 作为交易发生的日期，数量作为每个订单的数量。

在这种情况下，我想计算每个买家（createdby）的订单（order_buyer_Id），然后找出最大和最小数量。

这是示例数据：

+----------------+-----------+------------+--------+
| order_buyer_id | createdby | createdAt  |quantity|
+----------------+-----------+------------+--------+
|          19123 |        19 | 2017-02-02 |0.4     |
|         193241 |        19 | 2017-02-02 |0.5       
|         123123 |        20 | 2017-02-02 |1       |
|          32242 |        20 | 2017-02-02 |4
|          32434 |        20 | 2017-02-02 |3       |
+----------------+-----------+------------+---------

如果我运行查询，预期的结果是：

+-----+-----+---------+--------+
| max | min | average | median |
+-----+-----+---------+--------+
|   4 | 0.4 |    1,78 |      1 |
+-----+-----+---------+---------

这是小提琴

http://www.sqlfiddle.com/#!9/d89772/15

这是我的查询

SELECT MAX(quantity) AS max,
       MIN(quantity) AS min,
       AVG(quantity) AS average,
       AVG(CASE WHEN rn IN (FLOOR((@tr+1)/2), FLOOR((@tr+2)/2)) THEN quantity END) AS median
FROM (
  SELECT count, 
         @rn := @rn + 1 AS rn,
         @tr := @rn AS tr
  FROM (
    SELECT COUNT(*) AS count
    FROM order_match
    GROUP BY order_buyer_Id
    order by quantity
  ) o
  CROSS JOIN (SELECT @rn := 0) init
) c

标签： mysqlmysql-workbench

解决方案

您收到错误是因为quantity不在您的subquery.

您可以再次加入您的表格以获取，quantity或者您可以在您中包含数量select （基于您的样本数据，即使group by数量给出相同的结果）

SELECT MAX(quantity) AS max,
       MIN(quantity) AS min,
       AVG(quantity) AS average,
       AVG(CASE WHEN rn IN (FLOOR((@tr+1)/2), FLOOR((@tr+2)/2)) THEN quantity END) AS median
FROM (
  SELECT count, quantity,
         @rn := @rn + 1 AS rn,
         @tr := @rn AS tr
  FROM (
    SELECT COUNT(*) AS count,Quantity
    FROM order_match
    GROUP BY order_buyer_Id,Quantity
    order by quantity
  ) o
  CROSS JOIN (SELECT @rn := 0) init
) c

SQL 小提琴

推荐阅读