hive之lag函数

从博客上发现两个面试题，其中有个用到了lag函数。整理学习

LAG 函数是 Hive 中常用的窗口函数，用于访问同一分区内 前一行（或前 N 行）的数据。它在分析时间序列数据、计算相邻记录差异等场景中非常有用。

一、语法 LAG(column, offset, default) OVER ( PARTITION BY partition_expression ORDER BY sort_expression [ASC|DESC] )

column：需要访问的列。

offset：向前回溯的行数（默认为 1）。

default：当没有前 N 行时的默认值（默认为 NULL）。

PARTITION BY：按指定字段分区，每个分区独立计算。

ORDER BY：定义分区内的排序方式。

二、代码示例 1. 示例数据表 sales sale_daterevenueuser_id2023-01-0110012023-01-0215012023-01-0320012023-01-015022023-01-02802 2. 创建表并插入数据 CREATE TABLE sales ( sale_date STRING, revenue INT, user_id INT ); INSERT INTO sales VALUES ('2023-01-01', 100, 1), ('2023-01-02', 150, 1), ('2023-01-03', 200, 1), ('2023-01-01', 50, 2), ('2023-01-02', 80, 2); 3. 使用 LAG 计算每日环比增长 select user_id --用户 ,sale_date --销售日期 ,revenue --收入 ,lag(revenue,1) over(partition by user_id order by sale_date ) as prev_revenue --前一天的收入 ,revenue - LAG(revenue, 1) OVER (PARTITION BY user_id ORDER BY sale_date) AS growth --每日环比增长 from sales 三、使用场景 1、时间序列分析

计算每日/月销售额的环比增长（如示例所示）。

检测异常波动（如某天收入骤降 90%）。

 异常波动示例：假设有一张销售表 sales，需要检测 单用户单日销售额相比前一日波动超过 50% 的异常情况。

CREATE TABLE sales_yc ( user_id INT, sale_date STRING, revenue DOUBLE ) stored as orc INSERT INTO sales_yc VALUES (1, '2023-01-01', 100.0), (1, '2023-01-02', 150.0), -- 正常增长 50% (1, '2023-01-03', 30.0), -- 异常下降 80% (2, '2023-01-01', 200.0), (2, '2023-01-02', 450.0); -- 异常增长 125% with tmp as ( select user_id ,sale_date ,revenue ,lag(revenue,1,0) over(partition by user_id order by sale_date) prev_revenue from sales_yc ) select user_id ,sale_date ,revenue ,if(prev_revenue = 0,null,round((revenue - prev_revenue) / prev_revenue * 100,2))||'%' change_percent from tmp where abs((revenue - prev_revenue) / prev_revenue) > 0.5 and prev_revenue <> 0

user_idsale_daterevenueprev_revenuechange_percent12023-01-0330.0150.0-80.0-- 下降 80%22023-01-02450.0200.0125.0-- 增长 125% 2、填充缺失值

若数据缺失，可用前一行值填充： 

SELECT sale_date, COALESCE(revenue, LAG(revenue) OVER (ORDER BY sale_date)) AS imputed_revenue FROM sales; 3、用户行为分析

计算用户两次操作的时间间隔：

CREATE TABLE user_events ( user_id STRING, event_time STRING, event_type STRING ) stored as orc INSERT overwrite table user_events VALUES ('u1', '2023-01-01 08:00:00', 'login'), ('u1', '2023-01-01 08:05:30', 'click'), ('u1', '2023-01-01 08:15:45', 'purchase'), ('u2', '2023-01-01 09:00:00', 'login'), ('u2', '2023-01-01 09:30:00', 'logout'), ('u1', '2023-01-15 08:15:45', 'Add shopp'); with tmp as ( select user_id ,event_time ,event_type ,LAG(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time ) AS prev_event_time -- 获取前一次操作时间（按用户分区，时间排序） from user_events ) select user_id ,event_time ,event_type ,prev_event_time ,ROUND((UNIX_TIMESTAMP(event_time) - UNIX_TIMESTAMP(prev_event_time)) / 60,2) prev_event_time -- 计算时间间隔（转换为分钟，保留2位小数） from tmp; 4.库存管理

跟踪库存变化时，对比当前库存与前一日的差异。

CREATE TABLE stock ( product_id STRING, stock_date STRING, -- 日期格式需为 yyyy-MM-dd quantity INT ) stored as orc INSERT INTO stock VALUES ('1', '2023-01-01', 100), ('1', '2023-01-02', 80), ('1', '2023-01-03', 120), ('2', '2023-01-01', 200), ('2', '2023-01-03', 180); SELECT product_id, stock_date, quantity, -- 获取前一日库存（按商品分区，按日期排序） LAG(quantity, 1) OVER ( PARTITION BY product_id ORDER BY stock_date ) AS prev_quantity, -- 计算差异（当前库存 - 前一日库存） quantity - LAG(quantity, 1) OVER ( PARTITION BY product_id ORDER BY stock_date ) AS diff FROM stock; --筛选出库/入库记录 SELECT * FROM ( -- 上述计算差异的 SQL ) tmp WHERE diff IS NOT NULL; 四、面试题

已知有数据A如下，请分别根据A生成B和C。

数据A

+-----+-------+ | id | name | +-----+-------+ | 1 | aa | | 2 | aa | | 3 | aa | | 4 | d | | 5 | c | | 6 | aa | | 7 | aa | | 8 | e | | 9 | f | | 10 | g | +-----+-------+

数据B 

+-----+-----------------+ | id | name | +-----+-----------------+ | 7 | aa|aa|aa|aa|aa | | 4 | d | | 5 | c | | 8 | e | | 9 | f | | 10 | g | +-----+-----------------+ +-----+-----------+ | id | name | +-----+-----------+ | 3 | aa|aa|aa | | 4 | d | | 5 | c | | 7 | aa|aa | | 8 | e | | 9 | f | | 10 | g | +-----+-----------+

1、题目一 要求对name相同的数据进行合并处理，name相同的合并到一起用’|'进行拼接，id取组内最大值

2、题目二 要求对相邻name相同的数据进行合并，name相同的合并到一起用’|'进行拼接，id取组内最大值

问题1实现逻辑：

with tmp as ( select id,name,max(id) over(partition by name) new_id from data_a ) select new_id,concat_ws('|',collect_list(name)) from tmp group by new_id order by new_id

问题2实现逻辑：

select max(id) as id,concat_ws("|",collect_list(name)) as name from( select id,name,sum(is_continus) over(order by id) as continus_gp from( select id,name,if(name=lag(name) over( order by id),0,1) as is_continus from data_a )tmp ) a group by continus_gp

注：sql面试题目来源 SQL面试题——京东SQL面试题 合并数据-CSDN博客