大数据面试之Hive SQL经典面试题

1:连续登录n天的用户

假设有一个名为 user_login 的 Hive 表,包含用户登录记录,字段包括 user_id(用户ID)和 login_date(登录日期)。

要找出连续登录了 n 天的用户,可以使用 Hive SQL 编写类似以下的查询语句:

WITH login_sequence AS (
    SELECT 
        user_id,
        login_date,
        DATE_ADD(login_date,-ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date)) AS sequence_date
    FROM 
        user_login
)
SELECT 
    user_id
FROM 
    (
        SELECT 
            user_id,
            MIN(login_date) AS min_login_date,
            MAX(login_date) AS max_login_date
        FROM 
            login_sequence
        GROUP BY 
            user_id, sequence_date
    ) t
WHERE 
    DATEDIFF(max_login_date, min_login_date) + 1 >= n

上面的查询语句做了以下几件事情:

  1. 使用窗口函数 ROW_NUMBER() 为每个用户的登录日期分配一个连续的序列号,并使用 DATE_ADD() 函数计算序列日期。
  2. 使用 login_sequence 子查询来计算每个用户的登录日期序列。
  3. 使用子查询对每个用户的登录日期序列进行分组,计算最早和最晚的登录日期。
  4. 使用 DATEDIFF() 函数计算最早和最晚登录日期之间的天数,并筛选出连续登录天数大于等于 n 的用户。

2:留存问题

假设有一个用户每日登录活动表user_activity,包含用户ID(user_id)和登录日期(login_date):

CREATE TABLE user_activity (
    user_id BIGINT,
    login_date DATE
);

为了计算次日留存、3日留存、7日留存等,可以采用以下方法:

2.1 次日留存

计算某一天的用户在次日仍然活跃的用户数。

-- 假设计算2022-01-01日的次日留存
-- 获取2022-01-01日活跃用户列表
WITH active_users AS (
    SELECT DISTINCT user_id
    FROM user_activity
    WHERE login_date = '2022-01-01'
),

-- 计算这些用户在2022-01-02日的留存情况
next_day_activity AS (
    SELECT DISTINCT user_id
    FROM user_activity
    WHERE login_date = '2022-01-02'
)

-- 次日留存用户数
SELECT COUNT(DISTINCT active_users.user_id) AS retained_users
FROM active_users
JOIN next_day_activity ON active_users.user_id = next_day_activity.user_id;

-- 次日留存率
SELECT CAST(COUNT(DISTINCT active_users.user_id) AS FLOAT) / COUNT(DISTINCT active_users.user_id) * 100.0 AS retention_rate
FROM active_users
JOIN next_day_activity ON active_users.user_id = next_day_activity.user_id;

2.2 N日留存

计算N日留存则需要扩展上述逻辑,考虑用户在N天后的活跃情况。假设要计算3日留存:

-- 获取基准日期,比如'2022-01-01'日的活跃用户
WITH base_activity AS (
    SELECT DISTINCT user_id
    FROM user_activity
    WHERE login_date = '2022-01-01'
),

-- 计算这些用户在后续N天内的活跃情况
n_day_activity AS (
    SELECT user_id, login_date
    FROM user_activity
    WHERE login_date BETWEEN '2022-01-02' AND '2022-01-04' -- 这里假设N=3,所以是三天后
)

-- N日留存用户数
SELECT COUNT(DISTINCT base_activity.user_id) AS retained_users
FROM base_activity
JOIN n_day_activity ON base_activity.user_id = n_day_activity.user_id;

-- N日留存率
SELECT CAST(COUNT(DISTINCT base_activity.user_id) AS FLOAT) / COUNT(DISTINCT base_activity.user_id) * 100.0 AS retention_rate
FROM base_activity
JOIN n_day_activity ON base_activity.user_id = n_day_activity.user_id;

2.3 高级用法

-- 计算这些用户在后续N天内的活跃用户,并去重
n_day_activity AS (
  SELECT DISTINCT login_date, user_id
  FROM user_activity
  WHERE login_date BETWEEN '${pdate-8}' AND '${pdate}' -- 这里假设N=8,所以是8天后
)

-- N日留存用户数
SELECT 
  a.login_date 
  ,count(DISTINCT CASE WHEN datediff(b.login_date, a.login_date)=1 THEN a.cid ELSE NULL END) AS `次日留存`
  ,count(DISTINCT CASE WHEN datediff(b.login_date, a.login_date)=3 THEN a.cid ELSE NULL END) AS `三日留存`
  ,count(DISTINCT CASE WHEN datediff(b.login_date, a.login_date)=7 THEN a.cid ELSE NULL END) AS `七日留存`
FROM n_day_activity a
LEFT JOIN n_day_activity b 
ON a.login_date < b.login_date AND a.user_id =b.user_id
GROUP BY a.login_date

3:Top N问题

以下是一个示例 Hive SQL 查询,用于找出某个指标(比如销售额)最高的前 N 个记录:

假设有一个名为 sales_data 的表,包含销售数据,字段包括 product_id(产品ID)和 sales_amount(销售额)。

SELECT 
    product_id,
    sales_amount,
    row_num
FROM (
    SELECT 
        product_id,
        sales_amount,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sales_amount DESC) AS row_num
    FROM 
        sales_data
) t
WHERE 
    row_num <= N;

上面的查询使用了窗口函数 ROW_NUMBER(),对销售额进行降序排序,并为每行分配一个序列号。然后,在外部查询中,筛选出序列号小于等于 N 的记录,即销售额最高的前 N 个记录。

4:Explode问题

posexplode函数用于将数组类型的列转换为多行记录,同时输出数组元素及其对应的索引。以下是一个使用posexplode函数的实际案例:

案例:

假设有一个名为orders的表,其中包含一个订单详情,每个订单由一个order_id标识,并且有一个items列存储每个订单所购买的商品数组,数组中每个元素都是一个元组,包含商品ID和数量。

CREATE TABLE orders (
    order_id INT,
    items ARRAY<STRUCT<item_id: INT, quantity: INT>>
);

-- 插入示例数据
INSERT INTO orders VALUES (1, array(named_struct('item_id', 1001, 'quantity', 2), named_struct('item_id', 1002, 'quantity', 3)));
INSERT INTO orders VALUES (2, array(named_struct('item_id', 1003, 'quantity', 1)));

-- 使用posexplode函数将数组元素扩展为行
SELECT 
    order_id, 
    pos, 
    item.item_id, 
    item.quantity
FROM 
    orders
LATERAL VIEW posexplode(items) exploded_items AS pos, item

执行此查询后,结果将是:

order_id | pos | item_id | quantity
---------|-----|---------|---------
1        | 0   | 1001    | 2
1        | 1   | 1002    | 3
2        | 0   | 1003    | 1

posexplode(items)items数组中的每个元素与其在数组中的位置一同展开到多行记录中。新产生的列pos表示元素在原始数组中的索引,item则是从数组中展开出来的结构体,可以进一步引用其内部的item_idquantity属性。

5:行转列

在Hive SQL中,行转列通常涉及到将某些行的值转换为列的形式。这通常通过使用collect_listcollect_set等聚合函数与explode函数结合来实现。以下是一个简单的行转列案例。

假设有一个用户购买记录表user_purchases,包含用户ID、购买日期和购买的商品ID。希望将每个用户的购买记录从行格式转换为列格式,以展示每个用户购买的每个商品。

表结构如下:

CREATE TABLE user_purchases (
    user_id INT,
    purchase_date DATE,
    product_id INT
);

插入一些示例数据:

INSERT INTO user_purchases VALUES
(1, '2023-01-01', 101),
(1, '2023-01-02', 102),
(2, '2023-01-01', 101),
(2, '2023-01-03', 103);

现在,假设想要将每个用户的所有购买商品ID转换为列的形式。由于商品的数量可能不同,不能直接硬编码列名。但是,可以使用Hive的聚合函数和条件语句来近似实现这个效果。以下是一个可能的解决方案,它使用collect_list来聚合每个用户的商品ID,并使用concat_ws来将商品ID连接成一个字符串:

SELECT
    user_id,
    concat_ws(',', collect_list(product_id)) as purchased_products
FROM
    user_purchases
GROUP BY
    user_id;

这个查询将返回每个用户及其购买的所有商品ID,商品ID之间用逗号分隔。结果如下:

user_id | purchased_products
--------|-------------------
1       | 101,102
2       | 101,103

请注意,这种方法并没有真正地将行转换为独立的列,而是将多个行的值合并到了一个字符串中。在Hive中,由于schema是静态的,将任意数量的行转换为固定数量的列是不直接支持的。如果知道商品ID的最大数量,并且这个数量是固定的,可以使用条件聚合和CASE语句来为每个可能的商品ID创建列。但是,这在商品ID数量不确定或很大时是不可行的。

确实需要将行转换为独立的列,并且商品的数量是固定的,可以这样做:

SELECT
    user_id,
    MAX(CASE WHEN rn = 1 THEN product_id ELSE NULL END) as product_1,
    MAX(CASE WHEN rn = 2 THEN product_id ELSE NULL END) as product_2,
    -- 添加更多CASE语句以处理更多列
FROM (
    SELECT
        user_id,
        product_id,
        row_number() OVER (PARTITION BY user_id ORDER BY purchase_date) as rn
    FROM
        user_purchases
) t
GROUP BY
    user_id;

首先使用row_number()窗口函数为每个用户的购买记录分配一个行号。然后,使用CASE语句和MAX聚合函数来为每个可能的列位置选择产品ID。这种方法仅适用于知道或可以限制商品数量的场景。如果商品数量是动态的或非常大,那么这种方法就不适用了,可能需要在应用层或其他工具中进行此类转换。

  • 6
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
Hive SQL优化面试题通常涉及以下几个方面: 1. 查询优化:了解如何通过索引、分区等技术来优化Hive SQL查询性能。可以提到使用合适的索引、分区和分桶来减少数据扫描量,提高查询效率。 2. 性能调优:理解常见的性能瓶颈和优化策略,例如避免全表扫描、减少数据倾斜、合理设置并行度等。可以提到使用合适的数据类型、避免不必要的数据转换、使用合适的连接方式等来提高性能。 3. EXPLAIN关键字的使用:掌握使用EXPLAIN关键字来分析查询执行计划,了解查询的执行顺序和涉及的操作,从而找到潜在的性能问题并进行优化。 4. 解答优化相关的题目:在面试中可能会遇到一些关于查询优化和性能调优的具体问题,例如如何优化某个特定的查询语句,如何处理大数据量的查询等。在回答时可以结合自己的实际经验和知识来给出合理的解决方案。 综上所述,Hive SQL优化面试题主要涉及查询优化、性能调优、使用EXPLAIN关键字分析查询计划以及解答具体的优化问题。掌握这些知识和技巧可以帮助提升在Hive SQL领域的技能和竞争力。 #### 引用[.reference_title] - *1* *3* [Hive SQL大厂面试题必考大全](https://blog.csdn.net/m0_47256162/article/details/131687792)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [Hive SQL面试题(附答案)](https://blog.csdn.net/a934079371/article/details/122227602)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insertT0,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值