hive函数的解析及练习

将 SQL 函数从“语法”转化为“解决商业问题的能力”，关键在于建立起“业务场景-函数逻辑”的映射思维。这就像手里有了一把瑞士军刀，现在我们要学习在什么情况下用哪一把刀。

为了让你能系统性地掌握，我们将 Hive 中最核心的函数分为三个梯队：计算与逻辑类、时间处理类、高阶分析类。每一类我都将结合具体的商业场景进行拆解。

🛠️ HQL 高级开发与复杂逻辑实现（实战拆解）

窗口函数

窗口函数是 Hive SQL 的灵魂，用于解决“组内排序”、“累计计算”等问题。

• 排名类：
  • row_number() over(partition by dept order by salary desc)：用于TopN问题（如：每个部门工资最高的前 3 名）。
  • rank()vsdense_rank()：处理并列排名的区别（1, 1, 3 vs 1, 1, 2）。
• 偏移类：
  • lag(visit_date, 1) over(partition by uid order by visit_date)：获取用户上一次登录时间。
  • 实战：计算“用户上次登录时间”与“本次登录时间”的差值，如果差值 > 30天，则标记为“流失召回用户”。
  • lead()：获取下一次登录时间，用于计算留存。
• 聚合类：
  • sum(sales) over(partition by month order by day rows between unbounded preceding and current row)：用于计算累计求和（如：本月截止到今天的总销售额）。

复杂数据类型处理

• 场景：APP 启动日志通常是一行 JSON，里面包含一个events数组（用户在本次启动中触发了点击、浏览等多个事件）。
• 处理：
  • 使用get_json_object解析基础字段。
  • 使用explode(split(events, ','))将数组炸开，一行变多行，才能统计每个具体事件的点击量。
  • lateral view：配合explode使用，防止数据膨胀导致的主表数据丢失问题。

行列转换

• 行转列：
  • 场景：将“科目-分数”表转换为“姓名-语文-数学-英语”表。
  • 技术：max(case when subject='math' then score else 0 end)配合group by name。
• 列转行：
  • 场景：将“姓名-语文-数学”表转换为“科目-分数”长表。
  • 技术：stack(2, 'math', math_score, 'chinese', chinese_score)或者array+explode。

常用函数

• collect_set(col)：将分组内的某列数据去重合并成一个数组。
  • 实战：找出“购买了商品 A 同时也购买了商品 B”的用户。先按用户分组，collect_set(item_id)，然后判断数组中是否同时包含 A 和 B。
• concat_ws(',', collect_set(tag))：将用户的多个标签合并成一个逗号分隔的字符串，形成用户画像标签串。

第一梯队：基础逻辑与计算（解决“指标定义”问题）

商业分析的第一步往往是数据清洗和指标分类。

1. 条件逻辑函数

核心函数：CASE WHEN,COALESCE,IF,NVL
应用场景：用户分层、异常值处理、指标口径定义。

商业案例：定义高价值用户
假设你需要统计不同等级用户的订单量。业务定义：金额 > 1000 为“高价值”，> 500 为“中价值”，其余为“普通”。

SELECT order_id, user_id, amount, CASE WHEN amount >= 1000 THEN '高价值' WHEN amount >= 500 THEN '中价值' ELSE '普通' END AS user_level FROM dwd_order_detail;

进阶用法（行转列统计）：
如果要统计各等级用户的总数，可以配合SUM使用（这是面试和实战的高频考点）：

SELECT SUM(CASE WHEN amount >= 1000 THEN 1 ELSE 0 END) AS high_val_cnt, SUM(CASE WHEN amount >= 500 AND amount < 1000 THEN 1 ELSE 0 END) AS mid_val_cnt FROM dwd_order_detail;

2. 空值处理

核心函数：COALESCE(value1, value2, ...)
商业案例：营销活动中的替补策略。
如果用户有“优惠券金额”，则用优惠券；如果没有，看是否有“积分抵扣”；如果都没有，显示 0。

SELECT user_id, COALESCE(coupon_amt, point_deduction, 0) AS final_discount FROM marketing_info;

第二梯队：时间与日期函数（解决“周期分析”问题）

电商、金融、物流等所有行业都离不开时间维度分析。

1. 日期解析与计算

核心函数：date_format,datediff,date_add,last_day
应用场景：复购率计算、留存分析、账期统计。

商业案例：计算用户复购间隔
计算用户两次下单之间相隔了多少天。这需要用到LAG(后面会讲) 配合datediff。

商业案例：判断是否月底冲刺
统计每个月的最后一天的订单量，看是否存在“月底冲业绩”现象。

SELECT order_id, order_date, last_day(order_date) -- 返回该月最后一天，如 2023-10-31 FROM dwd_order_detail WHERE order_date = last_day(order_date);

2. 月份/周处理

核心函数：trunc(date, 'MM')
商业案例：将不同日期的订单归集到对应的“月份桶”中。

SELECT trunc(order_date, 'MM') as month_group, count(*) FROM dwd_order_detail GROUP BY trunc(order_date, 'MM');

第三梯队：高阶分析函数（解决“复杂排名与趋势”问题）

这是区分初级和高级分析师的分水岭。

1. 排名函数

核心函数：ROW_NUMBER(),RANK(),DENSE_RANK()
区别：

• ROW_NUMBER: 1, 2, 3, 4 (强制排序，无重复)
• RANK: 1, 2, 2, 4 (跳跃排序，有并列)
• DENSE_RANK: 1, 2, 2, 3 (不跳跃排序，有并列)

商业案例：Top N 问题（每个品类销量前3的商品）
这是最经典的面试题和业务题。

SELECT category, product_name, sales_amt FROM ( SELECT category, product_name, sales_amt, ROW_NUMBER() OVER(PARTITION BY category ORDER BY sales_amt DESC) as rn FROM dws_product_sales ) t WHERE t.rn <= 3;

2. 偏移分析函数

核心函数：LAG(col, n),LEAD(col, n)
应用场景：环比增长、用户行为路径分析、留存。

商业案例：计算每日 GMV 环比增长率
你需要知道今天的销售额比昨天涨了还是跌了。

SELECT stat_date, gmv, LAG(gmv, 1) OVER(ORDER BY stat_date) as yesterday_gmv, -- 取上一行的数据 ROUND( (gmv - LAG(gmv, 1) OVER(ORDER BY stat_date)) / LAG(gmv, 1) OVER(ORDER BY stat_date) , 2) as growth_rate FROM ads_daily_gmv;

3. 聚合开窗

核心函数：SUM() OVER(...),AVG() OVER(...)
应用场景：计算累计值（如：年初至今销售额）、移动平均线（如：近7天平均活跃用户）。

商业案例：计算“累计销售额”
老板要看截止到每一天的年度累计业绩。

SELECT stat_date, daily_sales, SUM(daily_sales) OVER(ORDER BY stat_date) as cumulative_sales -- 从第一行累加到当前行 FROM ads_daily_sales;

第四梯队：复杂数据处理（解决“埋点与日志”问题）

核心函数：explode,split,lateral view
应用场景：处理用户搜索词、购物车列表、标签集合。

商业案例：分析用户搜索关键词热度
假设日志表中有一行数据记录了用户一次搜索的多个词："手机, 华为, 5G"。需要将其拆分为三行进行统计。

SELECT keyword, COUNT(*) as cnt FROM search_log LATERAL VIEW explode(split(keywords, ',')) tmp AS keyword -- 将字符串按逗号分割并炸开 GROUP BY keyword;

实战训练题（建议动手写）

为了巩固这些知识，我为你设计了三道从易到难的训练题。你可以尝试自己写 SQL，或者在脑海中构思逻辑。

题目一：连续登录问题（经典留存）

场景：有一张用户登录表user_login(user_id,login_date)。
问题：找出所有连续登录 3 天及以上的用户。
提示：

1. 先去重（一天登录多次算一次）。
2. 使用ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date)生成序号。
3. 利用“日期”减去“序号”，如果结果相同，说明是连续的（这是著名的date_sub解法）。

题目二：漏斗分析（路径转化）

场景：电商购物行为表user_action(user_id,action_type,action_time)。action_type包括 'view' (浏览), 'cart' (加购), 'buy' (购买)。
问题：计算“浏览 -> 加购 -> 购买”的转化率。即：有多少人浏览了，其中多少人加了购物车，其中多少人买了。
提示：

1. 需要按user_id分组。
2. 利用MAX(CASE WHEN ...)标记该用户当天是否发生过某行为。
3. 统计各行为发生的人数，进行除法计算。

题目三：分组 Top N 与去重（综合）

场景：订单表orders(order_id,user_id,category,pay_amount,pay_time)。
问题：统计2023年10月，每个品类下支付金额最高的前2名用户。
提示：

1. WHERE过滤时间。
2. GROUP BY category, user_id计算每个用户在每个品类的总支付金额。
3. 使用子查询或 CTE (WITH语句)。
4. 外层使用DENSE_RANK() OVER(PARTITION BY category ORDER BY sum_amt DESC)。
5. 最外层WHERE rank <= 2。

你可以先尝试思考一下这几道题的逻辑，如果有具体的 SQL 写法想要验证，随时发给我，我帮你 Code Review！