HiveSQL中last_value和first_value函数的应用

概述

本日做一个数据分析，其中有一列数据有些有数据有些没数据，因此我们需要把每数据的举行增补进来因此我们需要利用last_value 函数和over 结合利用，但是遇到一个比力奇葩的标题不能按照预期举行处理。
新说原因： 由于我们要处理的列，不是 null 而是空字符导致函数失效
遇到这个标题我思考的几个思绪：

• last_value 函数第一次用不熟悉，猜疑自己利用方法不对，因此官方文档， 百度各种找资料，最后确认自己明白的没标题
  
• 由于我们是用公司内部平台猜疑平台有标题，最后确认没标题
  
• 猜疑低版本的标题，咨询平台是hive 2.0，发现这个函数是0.1.1开始支持
  
• 自己造数据举行测试，发现没标题，自己造的数据用null，忽然想起来是null和空字符串的标题导致的，经过验证确实是
  

函数说明：

first_value:函数用于返回当前第一个值。可开启true下令，跳过null值
last_value:函数用于返回当前最后个值。可开启true下令，跳过null值
LAST_VALUE 是 SQL 中的一个窗口函数，用于获取窗口内最后一个值。窗口函数在数据库查询中用于在某个特定的窗口范围内盘算聚合、分析等操作。

1. LAST_VALUE(column_name) OVER (PARTITION BY partition_expression ORDER BY sort_expression [ASC | DESC] ROWS BETWEEN start AND end)

复制代码

其中：

• column_name：要获取最后值的列名。
  
• PARTITION BY partition_expression：可选，指定分区表达式，用于将结果分成多个分区，类似于 GROUP BY。
  
• ORDER BY sort_expression：指定用于排序的列或表达式。
  
• ASC | DESC：可选，用于指定排序次序，默以为升序。
  
• ROWS BETWEEN start AND end：可选，用于指定窗口的范围。
  

last_value

数据：

1. 1   a      a      null  202301     202301
2. 1   b      b      null  null       202302
3. 1   null   c      null  null       202303
4. 1   d      null   null  null       202304
5. 2   a      a      null  202301     202301
6. -- 预期实现
7. 1   d      c      null  202301     202304
8. 2   a      a      null  202301     202301

复制代码

1. select last_value(age) over(partition by a order by b,c desc)

复制代码

1. SELECT *
2. FROM
3. (SELECT id
4.        ,last_value(name,TRUE)    OVER (PARTITION BY id ORDER BY up_time) name
5.        ,last_value(age,TRUE)     OVER (PARTITION BY id ORDER BY up_time) age
6.        ,last_value(address,TRUE) OVER (PARTITION BY id ORDER BY up_time) address
7.        ,last_value(ct_time,TRUE) OVER (PARTITION BY id ORDER BY up_time) ct_time
8.        ,up_time
9.        ,row_number() over (partition by id order by up_time desc ) as rank
10. FROM
11.     (select *
12.      from
13.          (select 1 as id,'a'  as name ,'a'  as age,null as address,202301 as ct_time,202301   as up_time
14.           union all
15.           select 1 as id,'b'  as name ,'b'  as age,null as address,null   as ct_time,  202302 as up_time
16.           union all
17.           select 1 as id,null as name,'c'   as age,null as address,null   as ct_time,  202303 as up_time
18.           union all
19.           select 1 as id,'d'  as name ,null as age,null as address,null   as ct_time,  202304 as up_time
20.           union all
21.           select 2 as id,'a'  as name ,'a'  as age,null as address,202301 as ct_time,  202301 as up_time
22.          ) t
23.     )
24. )
25. WHERE rank=1
26. ;
28. SELECT *
29. FROM
30.     (SELECT id
31.           ,last_value(name,TRUE)    OVER (PARTITION BY id ORDER BY up_time ROWS BETWEEN unbounded preceding and unbounded following) name
32.           ,last_value(age,TRUE)     OVER (PARTITION BY id ORDER BY up_time ROWS BETWEEN unbounded preceding and unbounded following) age
33.           ,last_value(address,TRUE) OVER (PARTITION BY id ORDER BY up_time ROWS BETWEEN unbounded preceding and unbounded following) address
34.           ,last_value(ct_time,TRUE) OVER (PARTITION BY id ORDER BY up_time ROWS BETWEEN unbounded preceding and unbounded following) ct_time
35.           ,up_time
36.           ,row_number() over (partition by id order by up_time desc ) as rank
37.      FROM
38.          (select *
39.           from
40.               (select 1 as id,'a'  as name ,'a'  as age,null as address,202301 as ct_time,202301   as up_time
41.                union all
42.                select 1 as id,'b'  as name ,'b'  as age,null as address,null   as ct_time,  202302 as up_time
43.                union all
44.                select 1 as id,null as name,'c'   as age,null as address,null   as ct_time,  202303 as up_time
45.                union all
46.                select 1 as id,'d'  as name ,null as age,null as address,null   as ct_time,  202304 as up_time
47.                union all
48.                select 2 as id,'a'  as name ,'a'  as age,null as address,202301 as ct_time,  202301 as up_time
49.               ) t
50.          )
51.     )
52. WHERE rank=1
53. ;
54. 在上述sql中，使用last_value函数对每一个列按照主键id分组，取一个最新值，如果遇见null值，使用参数true进行忽略，最后再使用窗口函数row_number进行分组排序取最大一条数据即可实现数据合并。

复制代码

first_value

1. CREATE TABLE data (id INT, value INT);
3. INSERT INTO data (id, value) VALUES
4.     (1, NULL),
5.     (2, NULL),
6.     (3, NULL),
7.     (4, NULL),
8.     (5, 99),
9.     (6, NULL),
10.     (7, NULL),
11.     (8, 101),
12.     (9, NULL),
13.     (10, NULL);

复制代码

1. select
2.        id,
3.        value,
4.        --1、取第一个值。null也正常取值
5.        first_value(value) over(order by id),
6.        --2、第一行到当前行，取第一个非null的值
7.        first_value(value,true) over(order by id ),
8.        --3、取当前行，到最后行，第一个非null值。
9.        --业务需求：取当前行之后的，最近的非null值
10.        first_value(value,true)
11.        over(order by id rows between current row and unbounded following)
12. from data;

复制代码

1. SELECT
2. role_gid,
3. nd, --值为1、2、3、、、、28
4.     level_raw,
5.     battle_level_raw,
6.     coalesce(
7.     battle_level_raw,
8.         --取后面行第一个值（最接近）
9.         --函数含义:分组排序取组内第一个值.
10.         需求是:当前行的后面行的第一个值.true是忽略null值   否则跟原始值一样
11.     first_value(battle_level_raw,true  )  
12.         OVER(PARTITION BY role_gid ORDER BY nd  
13.         ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING) ,
14.         取前面行最后值（最接近）
15.        --函数含义:分组排序取组内最后一个值.
16.         需求是:当前行的前面行的最后值.true是忽略null值   否则跟原始值一样
17.     last_value(battle_level_raw ,true )
18.         OVER(PARTITION BY role_gid ORDER BY nd  
19.         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
20.     ) battle_level_notnull
21.     from  nd_game_sd_sjmy.tmp_dws_user_adventure_role_bat_detail_di  ;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。