大数据面试之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(PARTITIONBY user_id ORDERBY 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
        GROUPBY 
            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

)：

CREATETABLE user_activity (
    user_id BIGINT,
    login_date DATE);

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

2.1 次日留存

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

-- 假设计算2022-01-01日的次日留存-- 获取2022-01-01日活跃用户列表WITH active_users AS(SELECTDISTINCT user_id
    FROM user_activity
    WHERE login_date ='2022-01-01'),-- 计算这些用户在2022-01-02日的留存情况
next_day_activity AS(SELECTDISTINCT user_id
    FROM user_activity
    WHERE login_date ='2022-01-02')-- 次日留存用户数SELECTCOUNT(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)ASFLOAT)/COUNT(DISTINCT active_users.user_id)*100.0AS 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(SELECTDISTINCT 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日留存用户数SELECTCOUNT(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)ASFLOAT)/COUNT(DISTINCT base_activity.user_id)*100.0AS 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(SELECTDISTINCT login_date, user_id
  FROM user_activity
  WHERE login_date BETWEEN'${pdate-8}'AND'${pdate}'-- 这里假设N=8，所以是8天后)-- N日留存用户数SELECT 
  a.login_date 
  ,count(DISTINCTCASEWHEN datediff(b.login_date, a.login_date)=1THEN a.cid ELSENULLEND)AS`次日留存`,count(DISTINCTCASEWHEN datediff(b.login_date, a.login_date)=3THEN a.cid ELSENULLEND)AS`三日留存`,count(DISTINCTCASEWHEN datediff(b.login_date, a.login_date)=7THEN a.cid ELSENULLEND)AS`七日留存`FROM n_day_activity a
LEFTJOIN n_day_activity b 
ON a.login_date < b.login_date AND a.user_id =b.user_id
GROUPBY 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(PARTITIONBY product_id ORDERBY 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和数量。

CREATETABLE orders (
    order_id INT,
    items ARRAY<STRUCT<item_id: INT, quantity: INT>>);-- 插入示例数据INSERTINTO orders VALUES(1, array(named_struct('item_id',1001,'quantity',2), named_struct('item_id',1002,'quantity',3)));INSERTINTO 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_id

和

quantity

属性。

5：行转列

在Hive SQL中，行转列通常涉及到将某些行的值转换为列的形式。这通常通过使用

collect_list

或

collect_set

等聚合函数与

explode

函数结合来实现。以下是一个简单的行转列案例。

假设有一个用户购买记录表

user_purchases

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

表结构如下：

CREATETABLE user_purchases (
    user_id INT,
    purchase_date DATE,
    product_id INT);

插入一些示例数据：

INSERTINTO 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
GROUPBY
    user_id;

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

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

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

CASE

语句来为每个可能的商品ID创建列。但是，这在商品ID数量不确定或很大时是不可行的。

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

SELECT
    user_id,MAX(CASEWHEN rn =1THEN product_id ELSENULLEND)as product_1,MAX(CASEWHEN rn =2THEN product_id ELSENULLEND)as product_2,-- 添加更多CASE语句以处理更多列FROM(SELECT
        user_id,
        product_id,
        row_number()OVER(PARTITIONBY user_id ORDERBY purchase_date)as rn
    FROM
        user_purchases
) t
GROUPBY
    user_id;

首先使用

row_number()

窗口函数为每个用户的购买记录分配一个行号。然后，使用

CASE

语句和

MAX

聚合函数来为每个可能的列位置选择产品ID。这种方法仅适用于知道或可以限制商品数量的场景。如果商品数量是动态的或非常大，那么这种方法就不适用了，可能需要在应用层或其他工具中进行此类转换。