0


【Hive SQL 每日一题】找出各个商品销售额的中位数

文章目录

测试数据

-- 创建 orders 表DROPTABLEIFEXISTS orders;CREATETABLE orders (
    order_id INT,
    product_id INT,
    order_date STRING,
    amount DOUBLE);-- 插入 orders 数据INSERTINTO 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 表示商品 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(partitionby product_id orderby amount) rn,count(1)over(partitionby product_id) cnt
    from
        orders)t1
where
    rn in(cnt/2,cnt/2+1,(cnt+1)/2)orderby
    product_id,median;

输出结果如下所示:

在这里插入图片描述

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

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

方法2 —— 正反排序法

select
    product_id,
    amount median
from(select
        product_id,
        amount,
        row_number()over(partitionby product_id orderby amount) rn_asc,
        row_number()over(partitionby product_id orderby amount desc) rn_desc,count(1)over(partitionby product_id) cnt
    from
        orders)t1
where
    rn_asc >= cnt/2and
    rn_desc >= cnt/2orderby
    product_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

函数,它们在用法上并没有差别。

注意:这两个函数无法严格的计算出中位数,它们计算的只是一个近似值,意味着和真正的中位数是存在一定差异的,特别是在数据量较少或数据分布不均的情况下。

如果不需要拿到准确的中位数值,只需要获取到这组数据中相对的中位数,那么则可以使用这两个内置函数,主要看业务指标口径是否需要达到精准。

使用示例

DROPTABLEIFEXISTS orders;CREATETABLE orders (
    order_id INT,
    product_id INT,
    amount DOUBLE);INSERTINTO 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
groupby
    product_id;

输出结果如下:

在这里插入图片描述

标签: hive sql hadoop

本文转载自: https://blog.csdn.net/weixin_46389691/article/details/140431548
版权归原作者 月亮给我抄代码 所有, 如有侵权,请联系我们删除。

“【Hive SQL 每日一题】找出各个商品销售额的中位数”的评论:

还没有评论