hive leg函数

从博客上发现两个面试题,其中有个用到了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_date revenue user_id
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

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_id sale_date revenue prev_revenue change_percent
1 2023-01-03 30.0 150.0 -80.0 — 下降 80%
2 2023-01-02 450.0 200.0 125.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博客

大数据

Go脚本同步Es Redis

2025-3-3 10:15:47

大数据

hive之LEAD 函数详解

2025-3-3 10:15:49

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧