ToB企服应用市场:ToB评测及商务社交产业平台

标题: 大数据面试之Hive SQL经典面试题 [打印本页]
作者: 写过一篇    时间: 2024-7-22 02:38
标题: 大数据面试之Hive SQL经典面试题
1：一连登录n天的用户

假设有一个名为 user_login 的 Hive 表，包罗用户登录记录，字段包罗 user_id（用户ID）和 login_date（登录日期）。
要找出一连登录了 n 天的用户，可以使用 Hive SQL 编写类似以下的查询语句：
  1. WITH login_sequence AS (
  2.     SELECT
  3.         user_id,
  4.         login_date,
  5.         DATE_ADD(login_date,-ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date)) AS sequence_date
  6.     FROM
  7.         user_login
  8. )
  9. SELECT
  10.     user_id
  11. FROM
  12.     (
  13.         SELECT
  14.             user_id,
  15.             MIN(login_date) AS min_login_date,
  16.             MAX(login_date) AS max_login_date
  17.         FROM
  18.             login_sequence
  19.         GROUP BY
  20.             user_id, sequence_date
  21.     ) t
  22. WHERE
  23.     DATEDIFF(max_login_date, min_login_date) + 1 >= n
复制代码
上面的查询语句做了以下几件事情：
2：留存问题

假设有一个用户逐日登录活动表user_activity，包罗用户ID(user_id)和登录日期(login_date)：
  1. CREATE TABLE user_activity (
  2.     user_id BIGINT,
  3.     login_date DATE
  4. );
复制代码
为了盘算次日留存、3日留存、7日留存等，可以采取以下方法：
2.1 次日留存

盘算某一天的用户在次日仍然活跃的用户数。
  1. -- 假设计算2022-01-01日的次日留存
  2. -- 获取2022-01-01日活跃用户列表
  3. WITH active_users AS (
  4.     SELECT DISTINCT user_id
  5.     FROM user_activity
  6.     WHERE login_date = '2022-01-01'
  7. ),
  9. -- 计算这些用户在2022-01-02日的留存情况
  10. next_day_activity AS (
  11.     SELECT DISTINCT user_id
  12.     FROM user_activity
  13.     WHERE login_date = '2022-01-02'
  14. )
  16. -- 次日留存用户数
  17. SELECT COUNT(DISTINCT active_users.user_id) AS retained_users
  18. FROM active_users
  19. JOIN next_day_activity ON active_users.user_id = next_day_activity.user_id;
  21. -- 次日留存率
  22. SELECT CAST(COUNT(DISTINCT active_users.user_id) AS FLOAT) / COUNT(DISTINCT active_users.user_id) * 100.0 AS retention_rate
  23. FROM active_users
  24. JOIN next_day_activity ON active_users.user_id = next_day_activity.user_id;
复制代码
2.2 N日留存

盘算N日留存则需要扩展上述逻辑，考虑用户在N天后的活跃环境。假设要盘算3日留存：
  1. -- 获取基准日期，比如'2022-01-01'日的活跃用户
  2. WITH base_activity AS (
  3.     SELECT DISTINCT user_id
  4.     FROM user_activity
  5.     WHERE login_date = '2022-01-01'
  6. ),
  8. -- 计算这些用户在后续N天内的活跃情况
  9. n_day_activity AS (
  10.     SELECT user_id, login_date
  11.     FROM user_activity
  12.     WHERE login_date BETWEEN '2022-01-02' AND '2022-01-04' -- 这里假设N=3，所以是三天后
  13. )
  15. -- N日留存用户数
  16. SELECT COUNT(DISTINCT base_activity.user_id) AS retained_users
  17. FROM base_activity
  18. JOIN n_day_activity ON base_activity.user_id = n_day_activity.user_id;
  20. -- N日留存率
  21. SELECT CAST(COUNT(DISTINCT base_activity.user_id) AS FLOAT) / COUNT(DISTINCT base_activity.user_id) * 100.0 AS retention_rate
  22. FROM base_activity
  23. JOIN n_day_activity ON base_activity.user_id = n_day_activity.user_id;
复制代码
2.3 高级用法

  1. -- 计算这些用户在后续N天内的活跃用户，并去重
  2. n_day_activity AS (
  3.   SELECT DISTINCT login_date, user_id
  4.   FROM user_activity
  5.   WHERE login_date BETWEEN '${pdate-8}' AND '${pdate}' -- 这里假设N=8，所以是8天后
  6. )
  8. -- N日留存用户数
  9. SELECT
  10.   a.login_date
  11.   ,count(DISTINCT CASE WHEN datediff(b.login_date, a.login_date)=1 THEN a.cid ELSE NULL END) AS `次日留存`
  12.   ,count(DISTINCT CASE WHEN datediff(b.login_date, a.login_date)=3 THEN a.cid ELSE NULL END) AS `三日留存`
  13.   ,count(DISTINCT CASE WHEN datediff(b.login_date, a.login_date)=7 THEN a.cid ELSE NULL END) AS `七日留存`
  14. FROM n_day_activity a
  15. LEFT JOIN n_day_activity b
  16. ON a.login_date < b.login_date AND a.user_id =b.user_id
  17. GROUP BY a.login_date
复制代码
3：Top N问题

以下是一个示例 Hive SQL 查询，用于找出某个指标（比如销售额）最高的前 N 个记录：
假设有一个名为 sales_data 的表，包罗销售数据，字段包罗 product_id（产物ID）和 sales_amount（销售额）。
  1. SELECT
  2.     product_id,
  3.     sales_amount,
  4.     row_num
  5. FROM (
  6.     SELECT
  7.         product_id,
  8.         sales_amount,
  9.         ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sales_amount DESC) AS row_num
  10.     FROM
  11.         sales_data
  12. ) t
  13. WHERE
  14.     row_num <= N;
复制代码
上面的查询使用了窗口函数 ROW_NUMBER()，对销售额举行降序排序，并为每行分配一个序列号。然后，在外部查询中，筛选出序列号小于等于 N 的记录，即销售额最高的前 N 个记录。
4：Explode问题

posexplode函数用于将数组类型的列转换为多行记录，同时输出数组元素及其对应的索引。以下是一个使用posexplode函数的实际案例：
案例：
假设有一个名为orders的表，此中包罗一个订单详情，每个订单由一个order_id标识，并且有一个items列存储每个订单所购买的商品数组，数组中每个元素都是一个元组，包罗商品ID和数量。
  1. CREATE TABLE orders (
  2.     order_id INT,
  3.     items ARRAY<STRUCT<item_id: INT, quantity: INT>>
  4. );
  6. -- 插入示例数据
  7. INSERT INTO orders VALUES (1, array(named_struct('item_id', 1001, 'quantity', 2), named_struct('item_id', 1002, 'quantity', 3)));
  8. INSERT INTO orders VALUES (2, array(named_struct('item_id', 1003, 'quantity', 1)));
  10. -- 使用posexplode函数将数组元素扩展为行
  11. SELECT
  12.     order_id,
  13.     pos,
  14.     item.item_id,
  15.     item.quantity
  16. FROM
  17.     orders
  18. LATERAL VIEW posexplode(items) exploded_items AS pos, item
复制代码
执行此查询后，结果将是：
  1. order_id | pos | item_id | quantity
  2. ---------|-----|---------|---------
  3. 1        | 0   | 1001    | 2
  4. 1        | 1   | 1002    | 3
  5. 2        | 0   | 1003    | 1
复制代码
posexplode(items) 将items数组中的每个元素与其在数组中的位置一同睁开到多行记录中。新产生的列pos表示元素在原始数组中的索引，item则是从数组中睁开出来的结构体，可以进一步引用其内部的item_id和quantity属性。
5：行转列

在Hive SQL中，行转列通常涉及到将某些行的值转换为列的形式。这通常通过使用collect_list或collect_set等聚合函数与explode函数结合来实现。以下是一个简朴的行转列案例。
假设有一个用户购买记录表user_purchases，包罗用户ID、购买日期和购买的商品ID。盼望将每个用户的购买记录从行格式转换为列格式，以展示每个用户购买的每个商品。
表结构如下：
  1. CREATE TABLE user_purchases (
  2.     user_id INT,
  3.     purchase_date DATE,
  4.     product_id INT
  5. );
复制代码
插入一些示例数据：
  1. INSERT INTO user_purchases VALUES
  2. (1, '2023-01-01', 101),
  3. (1, '2023-01-02', 102),
  4. (2, '2023-01-01', 101),
  5. (2, '2023-01-03', 103);
复制代码
如今，假假想要将每个用户的所有购买商品ID转换为列的形式。由于商品的数量大概差别，不能直接硬编码列名。但是，可以使用Hive的聚合函数和条件语句来近似实现这个结果。以下是一个大概的办理方案，它使用collect_list来聚合每个用户的商品ID，并使用concat_ws来将商品ID毗连成一个字符串：
  1. SELECT
  2.     user_id,
  3.     concat_ws(',', collect_list(product_id)) as purchased_products
  4. FROM
  5.     user_purchases
  6. GROUP BY
  7.     user_id;
复制代码
这个查询将返回每个用户及其购买的所有商品ID，商品ID之间用逗号分隔。结果如下：
  1. user_id | purchased_products
  2. --------|-------------------
  3. 1       | 101,102
  4. 2       | 101,103
复制代码
请注意，这种方法并没有真正地将行转换为独立的列，而是将多个行的值归并到了一个字符串中。在Hive中，由于schema是静态的，将任意数量的行转换为固定数量的列是不直接支持的。假如知道商品ID的最大数量，并且这个数量是固定的，可以使用条件聚合和CASE语句来为每个大概的商品ID创建列。但是，这在商品ID数量不确定或很大时是不可行的。
确实需要将行转换为独立的列，并且商品的数量是固定的，可以这样做：
  1. SELECT
  2.     user_id,
  3.     MAX(CASE WHEN rn = 1 THEN product_id ELSE NULL END) as product_1,
  4.     MAX(CASE WHEN rn = 2 THEN product_id ELSE NULL END) as product_2,
  5.     -- 添加更多CASE语句以处理更多列
  6. FROM (
  7.     SELECT
  8.         user_id,
  9.         product_id,
  10.         row_number() OVER (PARTITION BY user_id ORDER BY purchase_date) as rn
  11.     FROM
  12.         user_purchases
  13. ) t
  14. GROUP BY
  15.     user_id;
复制代码
首先使用row_number()窗口函数为每个用户的购买记录分配一个行号。然后，使用CASE语句和MAX聚合函数来为每个大概的列位置选择产物ID。这种方法仅适用于知道或可以限定商品数量的场景。假如商品数量是动态的或非常大，那么这种方法就不适用了，大概需要在应用层或其他工具中举行此类转换。

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



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4