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

Question

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

在这种情况下，我想计算每个买家(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

Answer 1

由于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 FIDDLE

Answer 2

SELECT t.max,t.min,t.average,0.00 AS 'Median'
FROM
(SELECT MAX(quantity) AS max,
MIN(quantity) AS min,
SUM(quantity)/COUNT(distinct created_by) AS average
FROM order_match)t
union 
SELECT 0.00 AS 'max',0.00 AS 'min',0.00 AS 'Average',
((2*t1.average/3)+t1.mode) AS 'Median' 
FROM (SELECT count(FLOOR(quantity)),IFNULL(FLOOR(quantity),min(quantity)) AS 'mode'  
FROM order_match GROUP BY quantity HAVING 
count(FLOOR(quantity))>1)t1