窗口函数之LEAD,LAG, FIRST_VALUE, LAST_VALUE

最新推荐文章于 2024-11-20 23:45:00 发布

原创最新推荐文章于 2024-11-20 23:45:00 发布 · 1.4k 阅读

2 ·

CC 4.0 BY-SA版权

文章标签：

#hive #sql

Hive 专栏收录该内容

3 篇文章

订阅专栏

这篇博客深入探讨了Hive中的四个关键窗口函数：LEAD, LAG, FIRST_VALUE和LAST_VALUE。通过实例展示了它们如何在数据分组中获取排序后的首尾值以及上下行的值，帮助理解这些函数的用法。" 104195650,7772391,大数据面试指南：笔试与面试策略,"['大数据', '面试技巧', '编程题', '技术面试', '职业规划']

这篇博客给大家介绍 Hive 中最常用的四个窗口函数，分别是：LEAD, LAG, FIRST_VALUE 以及 LAST_VALUE。

FIRST_VALUE：取分组内排序后，截止到当前行，第一个值。
LAST_VALUE：取分组内排序后，截止到当前行，最后一个值。
LEAD (col, n, default)：用于统计窗口内往下滴 n 行值。第一个参数为列名，第二个参数为往下滴 n 行（默认为 1 ），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG (col, n, default)：与 lead 函数相反，用于统计窗口内网上第 n 行值。

下面我们举例来看看：

测试数据集如下所示：

FIRST_VALUE 与 LAST_VALUE

select 
	user_id,
	user_type,
	row_number() over(partition by user_type order by sales) as row_num,
	first_value(user_id) over(partition by user_type order by sales desc) as max_sales_user,
	first_value(user_id) over(partition by user_type order by sales asc) as min_sales_user,
	last_value(user_id) over(partition by user_type order by sales desc) as curr_last_min_user,
	last_value(user_id) over(partition by user_type) order by sales asc) as curr_last_max_user
from
	order_detail;

查询结果为：

+----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+
| user_id  | user_type  | row_num  | max_sales_user  | min_sales_user  | curr_last_min_user  | curr_last_max_user  |
+----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+
| wutong   | new        | 7        | wutong          | qibaqiu         | wutong              | wutong              |
| lilisi   | new        | 6        | wutong          | qibaqiu         | qishili             | lilisi              |
| qishili  | new        | 5        | wutong          | qibaqiu         | qishili             | lilisi              |
| wanger   | new        | 4        | wutong          | qibaqiu         | wanger              | wanger              |
| zhangsa  | new        | 3        | wutong          | qibaqiu         | zhangsa             | zhangsa             |
| liiu     | new        | 2        | wutong          | qibaqiu         | qibaqiu             | liiu                |
| qibaqiu  | new        | 1        | wutong          | qibaqiu         | qibaqiu             | liiu                |
| liwei    | old        | 3        | liwei           | lisi            | liwei               | liwei               |
| wangshi  | old        | 2        | liwei           | lisi            | wangshi             | wangshi             |
| lisi     | old        | 1        | liwei           | lisi            | lisi                | lisi                |
+----------+------------+----------+-----------------+-----------------+---------------------+---------------------+--+

LEAD 与 LAG

select
	user_id, device_id,
	lead(device_id) over(order by sales) as default_after_one_line,
	lag(device_id) over(order by sales) as default_before_one_line,
	lead(device_id,2) over (order by sales) as after_two_line,
    lag(device_id,2,'abc') over (order by sales) as before_two_line
from 
    order_detail;

查询结果如下：

+----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+
| user_id  |  device_id  | default_after_one_line  | default_before_one_line  | after_two_line  | before_two_line  |
+----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+
| qibaqiu  | fds         | fdsfagwe                | NULL                     | 543gfd          | abc              |
| liiu     | fdsfagwe    | 543gfd                  | fds                      | f332            | abc              |
| lisi     | 543gfd      | f332                    | fdsfagwe                 | dfsadsa323      | fds              |
| wangshi  | f332        | dfsadsa323              | 543gfd                   | hfd             | fdsfagwe         |
| zhangsa  | dfsadsa323  | hfd                     | f332                     | 65ghf           | 543gfd           |
| liwei    | hfd         | 65ghf                   | dfsadsa323               | fds             | f332             |
| wanger   | 65ghf       | fds                     | hfd                      | dsfgg           | dfsadsa323       |
| qishili  | fds         | dsfgg                   | 65ghf                    | 543gdfsd        | hfd              |
| lilisi   | dsfgg       | 543gdfsd                | fds                      | NULL            | 65ghf            |
| wutong   | 543gdfsd    | NULL                    | dsfgg                    | NULL            | fds              |
+----------+-------------+-------------------------+--------------------------+-----------------+------------------+--+