@[toc]
测试数据
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 近的商品销售记录，只需要改变排序的过滤条件即可。
结果如下所示：
