网站页面和图片设计,顺德中小企业网站建设,wordpress下载站,做旅游网站多少钱文章目录 测试数据需求说明需求实现方法1 —— 升序计算法方法2 —— 正反排序法 补充 测试数据
-- 创建 orders 表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (order_id INT,product_id INT,order_date STRING,amount DOUBLE
);-- 插入 orders 数据
INSERT INTO ord… 文章目录 测试数据需求说明需求实现方法1 —— 升序计算法方法2 —— 正反排序法 补充 测试数据
-- 创建 orders 表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (order_id INT,product_id INT,order_date STRING,amount DOUBLE
);-- 插入 orders 数据
INSERT INTO orders VALUES
(1, 1, 2024-01-01, 100.0),
(2, 1, 2024-01-02, 150.0),
(3, 2, 2024-01-03, 200.0),
(4, 3, 2024-01-04, 50.0),
(5, 4, 2024-01-05, 300.0),
(6, 5, 2024-01-06, 250.0),
(7, 1, 2024-01-07, 80.0),
(8, 2, 2024-01-08, 220.0),
(9, 3, 2024-01-09, 60.0),
(10, 4, 2024-01-10, 310.0),
(11, 5, 2024-01-11, 230.0),
(12, 1, 2024-01-12, 90.0),
(13, 2, 2024-01-13, 210.0),
(14, 3, 2024-01-14, 70.0),
(15, 4, 2024-01-15, 320.0),
(16, 5, 2024-01-16, 240.0),
(17, 1, 2024-01-17, 110.0),
(18, 2, 2024-01-18, 190.0),
(19, 3, 2024-01-19, 80.0),
(20, 4, 2024-01-20, 330.0),
(21, 5, 2024-01-21, 260.0),
(22, 1, 2024-01-22, 120.0),
(23, 2, 2024-01-23, 230.0),
(24, 3, 2024-01-24, 90.0),
(25, 4, 2024-01-25, 340.0),
(26, 5, 2024-01-26, 270.0),
(27, 1, 2024-01-27, 130.0),
(28, 2, 2024-01-28, 180.0),
(29, 3, 2024-01-29, 100.0),
(30, 4, 2024-01-30, 350.0);需求说明
求出每个商品的订单金额中位数。
结果示例
product_idmedian1110.02200.02210.0……
结果按 product_id 、median 升序排列。
其中
product_id 表示商品 IDmedian 表示该商品的中位数值。
需求实现
需求实现之前我们需要明确中位数的概念在日常生活中我们是如何计算中位数的
这里给定一个列表 [4,5,6,7,8]请你计算该列表的中位数那么该如何进行呢
首先求中位数需要将数值按照从小到大的顺序排列然后根据中位数列表的长度 n 不同有两种结果 如果列表长度 n 为偶数那么中位数就有两个为第 n/2 个和第 n/21 个 如果列表长度 n 为奇数那么中位数就只有一个为第 (n1)/2 个。
这里给定的示例列表长度为 5是一个奇数故它的中位数为第 (51)/2 个所以这个列表的中位数为 6。
那么学会了如何计算中位数下面就说说如何在 SQL 中实现。
方法1 —— 升序计算法
selectproduct_id,amount median
from(selectproduct_id,amount,row_number() over(partition by product_id order by amount) rn,count(1) over(partition by product_id) cntfromorders)t1
wherern in (cnt/2,cnt/21,(cnt1)/2)
order byproduct_id,median;输出结果如下所示 这个方法就是上面提到的中位数计算逻辑 分组按从小到大进行排名 分组统计总个数 判断排名是否处于中位数的结果中。
方法2 —— 正反排序法
selectproduct_id,amount median
from(selectproduct_id,amount,row_number() over(partition by product_id order by amount) rn_asc,row_number() over(partition by product_id order by amount desc) rn_desc,count(1) over(partition by product_id) cntfromorders)t1
wherern_asc cnt/2andrn_desc cnt/2
order byproduct_id,median;输出结果如下 这个方法的计算逻辑有所不同 分组按从小到大进行排名 分组按从大到小进行排名 分组统计总个数 cnt 判断正反排名是否都满足 cnt/2。
那么为什么这种方法可以取到中位数呢我们一起来看看子查询 t1 的结果 从 t1 子查询中可以看到如果总个数为奇数时那么该组中的中位数有且仅有一个因为它无论时正序还是逆序排列中位数的排名都不会发生改变这种情况时那么是不是满足条件 rn_asc rn_desc 我们就能够找出长度为奇数组中的中位数。
如果总个数为偶数时根据中位数的特性该组的中位数一定是两个那么如何设置条件呢其实我们可以从正反序的排名中入手同组中当满足 rn_asc cnt/2 且 rn_desc cnt/2 条件时它就能够找出长度为偶数中的中位数。
将奇偶条件结合可以省略直接写成 rn_asc cnt/2 and rn_desc cnt/2
补充
在 Hive 中有两个内置的聚合函数可以用于求近似中位数分别是 percentile(col,0.5) percentile_approx(col,0.5)
其中第一个参数 col 为我们要求中位数的列第二个参数固定为 0.5。
它们的区别是percentile 中指定的列必须是整型不能是浮点型。如果是浮点型数据则使用 percentile_approx 函数它们在用法上并没有差别。
注意 这两个函数无法严格的计算出中位数它们计算的只是一个近似值意味着和真正的中位数是存在一定差异的特别是在数据量较少或数据分布不均的情况下。
如果不需要拿到准确的中位数值只需要获取到这组数据中相对的中位数那么则可以使用这两个内置函数主要看业务指标口径是否需要达到精准。
使用示例
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (order_id INT,product_id INT,amount DOUBLE
);INSERT INTO orders (order_id, product_id, amount) VALUES
(1, 1, 100.0),
(2, 1, 150.0),
(3, 1, 666.6),
(4, 3, 180.0),
(5, 3, 250.0),
(6, 3, 320.0);selectproduct_id,percentile_approx(amount,0.5) median
fromorders
group byproduct_id;输出结果如下
