目录
题目
准备数据
分析数据
题目
您正在运行一个电子商务网站,该网站正在寻找不平衡的订单。不平衡订单的订单最大数量严格大于每个订单(包括订单本身)的平均数量。
订单的平均数量计算为(订单中所有产品的总数量)/(订单中不同产品的数量)。订单的最大数量是订单中任何单个产品的最高数量。
编写SQL查询以查找所有不平衡订单的订单id。
准备数据
Create table If Not Exists OrdersDetails (order_id int, product_id int, quantity int);Truncate table OrdersDetails;insert into OrdersDetails (order_id, product_id, quantity) values ('1', '1', '12');insert into OrdersDetails (order_id, product_id, quantity) values ('1', '2', '10');insert into OrdersDetails (order_id, product_id, quantity) values ('1', '3', '15');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '1', '8');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '4', '4');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '5', '6');insert into OrdersDetails (order_id, product_id, quantity) values ('3', '3', '5');insert into OrdersDetails (order_id, product_id, quantity) values ('3', '4', '18');insert into OrdersDetails (order_id, product_id, quantity) values ('4', '5', '2');insert into OrdersDetails (order_id, product_id, quantity) values ('4', '6', '8');insert into OrdersDetails (order_id, product_id, quantity) values ('5', '7', '9');insert into OrdersDetails (order_id, product_id, quantity) values ('5', '8', '9');insert into OrdersDetails (order_id, product_id, quantity) values ('3', '9', '20');insert into OrdersDetails (order_id, product_id, quantity) values ('2', '9', '4');
分析数据
第一步:得出每个订单的平均值和最大值
selectorder_id,avg(quantity) avg,max(quantity) max
from OrdersDetails
group by order_id
;
第二步:满足最大数量大于平均值的最大值
with t1 as (selectorder_id,avg(quantity) avg,max(quantity) maxfrom OrdersDetailsgroup by order_id
)select order_id from t1
where max > (select max(avg) from t1);