hive分组求中位数 hive求中位数函数

NUM: 第34天 - 薪水求中位数

今天的sql是求中位数, 当然,我们第一眼看到这个问题的时候我们首先想到的是开窗函数row_number()根据公司名称分区,再将薪水排序,那么中位数怎么求呢?
**首先我们要理解什么是中位数?**** **
中位数就是中间的值,有两种可能,如果是奇数,中间值为1个,偶数则为2个,这时候我们肯定可以想到**" /2"** 的方式,这里还要count()开窗,根据公司分区, 不排序,求每个公司的汇总,那么具体怎么求呢? 关于中位数的求值都有哪几种方案呢? 这个问题是一个老生常谈的问题,那我们今天来总结一下吧
关于求中位数的三种方式:

1. 求出count后, 使用两个floor向下取整求出行号 --> rn in (floor((cnt+1)/2),floor((cnt +2)/2) );
2. 使用floor向下取整和ceil向上取整 --> rn in (floor((cnt+1)/2),ceil((cnt +1)/2));
3. 使用正排序和倒着排序,奇数相等,偶数为相减求绝对值=1 --> rn1 = rn2 OR abs(rn1 - rn2) = 1;

🎉建表并插入数据

CREATE TABLE t35
(
    id      bigint,
    company string,
    salary  bigint
);

INSERT INTO t35 (id, company, salary)
VALUES (1, 'A', 2341),
       (2, 'A', 341),
       (3, 'A', 15),
       (4, 'A', 15314),
       (5, 'A', 451),
       (6, 'A', 513),
       (7, 'B', 15),
       (8, 'B', 13),
       (9, 'B', 1154),
       (10, 'B', 1345),
       (11, 'B', 1221),
       (12, 'B', 234),
       (13, 'C', 2345),
       (14, 'C', 2645),
       (15, 'C', 2645),
       (16, 'C', 2652),
       (17, 'C', 65);

👓问题:查询每个公司的薪水中位数

查询结果

🧨SQL

-- 解题1:使用两个floor向下取整
WITH tmp AS
         (
             SELECT id
                  , company
                  , salary
                  , row_number() OVER (PARTITION BY company ORDER BY salary) rn
                  , count(1) OVER (PARTITION BY company ) cnt
             FROM t35
)
SELECT
    id
    ,company
    ,salary
FROM tmp WHERE rn in (`floor`((cnt+1)/2),`floor`((cnt +2)/2) );

-- 解题2:使用floor向下取整和ceil向上取整
WITH tmp AS
         (
             SELECT id
                  , company
                  , salary
                  , row_number() OVER (PARTITION BY company ORDER BY salary) rn
                  , count(1) OVER (PARTITION BY company ) cnt
             FROM t35
)
SELECT
    id
    ,company
    ,salary
FROM tmp WHERE rn in (`floor`((cnt+1)/2),`ceil`((cnt +1)/2));
-- 解题3: 使用abs求绝对值
WITH tmp AS
         (
             SELECT id
                  , company
                  , salary
                  , row_number() OVER (PARTITION BY company ORDER BY salary) rn1
                  , row_number() OVER (PARTITION BY company ORDER BY salary DESC ) rn2
             FROM t35
)
SELECT
    id
    ,company
    ,salary
FROM tmp WHERE rn1 = rn2 OR abs(rn1 - rn2) = 1;

关于sql的规范问题

1. 尽量把逗号放在前面,为什么?

1. 方便排查,不会遗漏逗号
2. 方便注释,可以单行直接注释,不用再改逗号
3. 排版看起来更紧密,我用datagrip快捷键ctrl + alt+ L可以快速缩进
4. 特别用datagrip进行快速复制一行的时候,只需要Ctrl + D不需要关注逗号，写起来很快

2. 使用关键字大写,养成好习惯
3. 参考 hive sql 官方conf地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual
4. 使用公用表达式 with as,提高代码可读性和运行效率