计算销售额中的中位数方法，hivesql实现方法案例

一座野山

已于 2024-07-21 15:35:47 修改

阅读量610

点赞数 23

分类专栏： Linux hive bigdata 文章标签： linux hive hadoop

于 2024-07-21 15:32:37 首次发布

本文链接：https://blog.youkuaiyun.com/2301_81547508/article/details/140588660

版权

Linux 同时被 3 个专栏收录

37 篇文章

订阅专栏

bigdata

19 篇文章

订阅专栏

hive

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_id 、median 升序排列。

其中：

product_id 表示商品 ID；
median 表示该商品的中位数值。

需求实现

需求实现之前，我们需要明确中位数的概念，在日常生活中，我们是如何计算中位数的？

这里给定一个列表 [4,5,6,7,8]，请你计算该列表的中位数，那么该如何进行呢？

首先，求中位数需要将数值按照从小到大的顺序排列，然后根据中位数列表的长度 n 不同有两种结果：

如果列表长度 n 为偶数，那么中位数就有两个，为第 n/2 个和第 n/2+1 个；
如果列表长度 n 为奇数，那么中位数就只有一个，为第 (n+1)/2 个。

这里给定的示例列表长度为 5，是一个奇数，故它的中位数为第 (5+1)/2 个，所以这个列表的中位数为 6。

那么，学会了如何计算中位数，下面就说说如何在 SQL 中实现。

方法1——升序计算方法

select
    product_id,
    amount median
from
    (select
        product_id,
        amount,
        row_number() over(partition by product_id order by amount) rn,
        count(1) over(partition by product_id) cnt
    from
        orders)t1
where
    rn in (cnt/2,cnt/2+1,(cnt+1)/2)
order by
    product_id,median;

计算结果

这个方法就是上面提到的中位数计算逻辑：

分组按从小到大进行排名；
分组统计总个数；
判断排名是否处于中位数的结果中。

计算方法2——正反排序法

select
    product_id,
    amount median
from
    (select
        product_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) cnt
    from
        orders)t1
where
    rn_asc >= cnt/2
    and
    rn_desc >= cnt/2
order by
    product_id,median;

计算结果如下：

这个方法的计算逻辑有所不同：

分组按从小到大进行排名；
分组按从大到小进行排名；
分组统计总个数 cnt；
判断正反排名是否都满足。

那么为什么这种方法可以取到中位数呢？我们一起来看看子查询 t1 的结果：

Total MapReduce CPU Time Spent: 11 seconds 310 msec
OK
1       110.0   4       4       7
1       100.0   3       5       7
1       150.0   7       1       7
1       90.0    2       6       7
1       80.0    1       7       7
1       130.0   6       2       7
1       120.0   5       3       7
2       230.0   6       1       6
2       220.0   5       2       6
2       210.0   4       3       6
2       180.0   1       6       6
2       190.0   2       5       6
2       200.0   3       4       6
3       90.0    5       2       6
3       50.0    1       6       6
3       60.0    2       5       6
3       70.0    3       4       6
3       80.0    4       3       6
3       100.0   6       1       6
4       350.0   6       1       6
4       340.0   5       2       6
4       330.0   4       3       6
4       320.0   3       4       6
4       310.0   2       5       6
4       300.0   1       6       6
5       270.0   5       1       5
5       260.0   4       2       5
5       250.0   3       3       5
5       240.0   2       4       5
5       230.0   1       5       5
Time taken: 35.427 seconds, Fetched: 30 row(s)

从 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);

select
    product_id,
    percentile_approx(amount,0.5) median
from
    orders
group by
    product_id;