【Hive SQL 每日一题】分组排名取值

测试数据

DROP TABLE IF EXISTS sales_data;
CREATE TABLE sales_data (
    id STRING,
    sales_amount INT,
    sales_date STRING
);

INSERT INTO sales_data (id, sales_amount, sales_date) VALUES
('A', 100, '2024-01-01'),
('A', 150, '2024-01-02'),
('A', 90, '2024-01-03'),
('B', 200, '2024-01-01'),
('B', 50, '2024-01-02'),
('B', 300, '2024-01-03'),
('C', 120, '2024-01-01'),
('C', 180, '2024-01-02'),
('C', 140, '2024-01-03');

这是一张记录商品每日销售记录的表，结构以及数据展示如下：

id	sales_amount	sales_date
A	100	2024-01-01
A	150	2024-01-02
A	90	2024-01-03
B	200	2024-01-01
B	50	2024-01-02
B	300	2024-01-03
C	120	2024-01-01
C	180	2024-01-02
C	140	2024-01-03

字段说明

• id：商品ID；

• sales_amount：销售额；

• sales_date：销售日期。

求各个商品销售额最高的记录

select
    id,
    sales_amount,
    sales_date
from
    (select
        id,
        sales_amount,
        sales_date,
        rank() over(partition by id order by sales_amount desc) rk
    from
        sales_data)t1
where
    rk = 1;

这个需求使用窗口函数能够轻松完成，分成两步：

• 利用窗口函数分组降序排列；

• 通过排名过滤获取最高的销售记录。

结果如下所示：

求各个商品距今最近及最远的销售记录

select
    id,
    sales_amount,
    sales_date
from
    (select
        id,
        sales_amount,
        sales_date,
        max(sales_date) over(partition by id) max_sales_date,
        min(sales_date) over(partition by id) min_sales_date
    from
        sales_data)t1
where
    sales_date = max_sales_date or sales_date = min_sales_date;

这个需求同样可以利用开窗解决，分为两步：

• 先使用开窗获取销售记录最近以及最远日期；

• 通过日期过滤获取最终结果。

结果如下所示：

求各个商品距今第二近的销售记录

select
    id,
    sales_amount,
    sales_date
from
    (select
        id,
        sales_amount,
        sales_date,
        row_number() over(partition by id order by diff_days desc) rn
    from
        (select
            id,
            sales_amount,
            sales_date,
            datediff(current_date(),sales_date) diff_days
        from
            sales_data)t1 )t2
where
    rn = 2;

本题的关键在于如何理解“第二近的销售记录”，假设某商品有如下销售记录：

• 在 2022-01-01 销售额为 100 元；

• 在 2022-01-10 销售额为 150 元；

• 在 2022-01-15 销售额为 130 元；

• 此外没有其它销售记录。

那么距今第二近的销售记录显然是第二条，也就是在 2022-01-10 的销售数据。

那么明白了需求，想想该如何去实现呢？

• 首先，计算出每条销售记录距今的销售日期差值；

• 然后，利用窗口函数对日期差值进行降序排序，取排名为 2 的数据，这样就能得到结果啦。

使用该方法不仅能获取到距今第 2 近的商品销售记录，还可以获取第 n 近的商品销售记录，只需要改变排序的过滤条件即可。

结果如下所示：