大数据学习之数据仓库代码题总结上

原创

bxia的厨房_公众号

修改于 2024-03-05 19:04:33

1501

修改于 2024-03-05 19:04:33

文章被收录于专栏：大数据总结大数据总结

一、开窗总结

1.1、窗口函数-窗口函数使用方式总结

【窗口函数】sum(sales)OVER (

【分区】PARTITION BY dealer_id

【排序】ORDER BY stat_date

【窗口表达式】ROWS BETWEEN x AND y)

1.2、窗口函数-window_expression 用于确定窗边界

名词	含义
xx preceding	往前，x的取值
xx following	往后，y的取值
current row	当前行，x、y均可
unbounded preceding	从前面的起点，x取值
unbounded following	到后面的终点，y取值

-∞~0
over (
  PARTITION BY dealer_id 
  ORDER BY stat_date 
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
-x~0
over (
  PARTITION BY dealer_id 
  ORDER BY stat_date 
  ROWS BETWEEN 1 PRECEDING and CURRENT ROW
)
-x~-x
over (
  PARTITION BY dealer_id 
  ORDER BY stat_date 
  ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
0~-∞
over (
  PARTITION BY dealer_id 
  ORDER BY stat_date 
  ROWS BETWEEN CURRENT ROW and UNBOUNDED FOLLOWING
)


LAG(MONEY, 1, 1) OVER (); --取分组内上一行的营业额，如果没有上一行则取1
LEAD(MONEY, 1, 7)OVER ()；

avg(sales) over ();

ROW_NUMBER() over(partition by dealer_id order by sales desc) rk01,
RANK() over(partition by dealer_id order by sales desc) rk02,
DENSE_RANK() over(partition by dealer_id order by sales desc) rk03, 
PERCENT_RANK() over(partition by dealer_id order by sales desc) rk04

1.3、窗口函数-窗口聚合函数有哪些？5个

窗口函数	函数功能说明
AVG()	AVG 窗口函数返回输入表达式值的平均值，忽略 NULL 值。
COUNT()	COUNT 窗口函数计算输入行数。COUNT(*) 计算目标表中的所有行，包括Null值；COUNT(expression) 计算特定列或表达式中具有非 NULL 值的行数。
MAX()	MAX窗口函数返回表达式在所有输入值中的最大值，忽略 NULL 值。
MIN()	MIN窗口函数返回表达式在所有输入值中的最小值，忽略 NULL 值。
SUM()	SUM窗口函数返回所有输入值的表达式总和，忽略 NULL 值。

1.4、窗口函数-窗口数据排序函数有哪些？6个

窗口函数	函数功能说明
ROW_NUMBER()	根据具体的分组和排序，为每行数据生成一个起始值等于1的唯一序列数
RANK()	对组中的数据进行排名，如果名次相同，则排名也相同，但是下一个名次的排名序号会出现不连续。
DENSE_RANK()	dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续。当出现名次相同时，则排名序号也相同。而下一个排名的序号与上一个排名序号是连续的。
PERCENT_RANK()	计算给定行的百分比排名。可以用来计算超过了百分之多少的人;排名计算公式为：(当前行的rank值-1)/(分组内的总行数-1)
CUME_DIST()	计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值
NTILE()	已排序的行划分为大小尽可能相等的指定数量的排名的组，并返回给定行所在的组的排名。如果切片不均匀，默认增加第一个切片的分布，不支持ROWS BETWEEN

1.5、窗口函数-值窗口函数有哪些？4个

窗口函数	函数功能说明
LAG()	与lead相反，用于统计窗口内往上第n行值。第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL.
LEAD()	用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL.
FIRST_VALUE	取分组内排序后，截止到当前行，第一个值
LAST_VALUE	取分组内排序后，截止到当前行，最后一个值

二、笔试题考查

2.1、复购/留存率统计问题

新增用户留存率=新增用户中登录用户数/新增用户数×100%（统计周期为天）。
新增用户数：在某个时间段（一般为第一整天）新登录应用的用户数。
登录用户数：登录应用后至当前时间，至少登录过一次的用户数。
第N日留存率：指的是新增用户日之后的第N日依然登录的用户占新增用户的比例

考虑一个在线学习平台，有用户注册和学习行为的数据。以下是相关的两个表格：

请编写 SQL 查询，计算从注册当天开始的每个用户在注册后第1天、第3天、第7天的学习留存率。留存率的计算方式是在注册后的特定天数内继续学习的用户数除以当天注册的用户总数。结果应包含日期、留存天数和留存率。

2.2、计算直播同时在线人数最大值

考虑一个直播平台，有用户进入直播间和离开直播间的记录。以下是相关的表格：

直播记录表 (live_records)：

| user_id | enter_time? ?       | leave_time? ? ? ? ? |
|---------|---------------------|---------------------|
| 1       | 2022-01-01 10:00:00 | 2022-01-01 10:30:00 |
| 2       | 2022-01-01 10:05:00 | 2022-01-01 10:20:00 |
| 3       | 2022-01-01 10:10:00 | 2022-01-01 10:25:00 |
| 1       | 2022-01-01 10:15:00 | 2022-01-01 10:40:00 |
| 2       | 2022-01-01 10:25:00 | 2022-01-01 11:00:00 |
| 3       | 2022-01-01 10:30:00 | 2022-01-01 11:10:00 |
| 1       | 2022-01-01 10:35:00 | 2022-01-01 11:20:00 |
| 2       | 2022-01-01 10:45:00 | 2022-01-01 11:30:00 |
| 3       | 2022-01-01 11:00:00 | 2022-01-01 11:40:00 |

问题：

请编写 Hive SQL 查询，计算直播平台在任意时刻的同时在线人数，并找到同时在线人数的最大值及对应的时间。

预期结果示例：

| time                | max_online_users |
|---------------------|------------------|
| 2022-01-01 10:00:00 | 1                |
| 2022-01-01 10:05:00 | 2                |
| 2022-01-01 10:10:00 | 3                |
| 2022-01-01 10:15:00 | 3                |
| 2022-01-01 10:20:00 | 3                |
| 2022-01-01 10:25:00 | 3                |
| 2022-01-01 10:30:00 | 3                |
| 2022-01-01 10:35:00 | 3                |
| 2022-01-01 10:40:00 | 2                |
| ...                 | ...              |

注意事项：

在 SQL 查询中使用合适的聚合和窗口函数。
考虑用户进入和离开的时间范围，以计算同时在线人数。
结果应按时间顺序排序。

2.3、hive中怎么统计array中非零的个数？

假设有一个包含数组的表格，表名为 array_table，结构如下：

| number_array          |
|-----------------------|
| [0, 2, 0, 4, 0, 6]    |
| [1, 0, 3, 0, 5, 0]    |
| [0, 0, 0, 0, 0, 0]    |
| [7, 8, 9, 10]         |

问题：请编写 Hive SQL 查询，统计每个 number_array 中非零元素的个数，并按数量进行排序

2.4、在线流量统计场景

考虑一个在线流量统计场景，有一个包含用户上网行为的表 internet_behavior，包含用户ID (user_id)、上网时间 (access_time)、上网流量 (traffic_usage) 等字段。请编写 Hive SQL 查询，对用户的上网行为进行聚合，将同一个用户的多个上网行为数据进行聚合，如果两次上网时间间隔小于10分钟，就进行聚合。

输入数据：

| user_id | access_time         | traffic_usage |
|---------|---------------------|---------------|
| 1       | 2022-01-01 10:00:00 | 100           |
| 2       | 2022-01-01 10:05:00 | 150           |
| 1       | 2022-01-01 10:10:00 | 120           |
| 2       | 2022-01-01 10:15:00 | 80            |
| 1       | 2022-01-01 10:20:00 | 200           |
| 1       | 2022-01-01 10:35:00 | 150           |
| 3       | 2022-01-01 10:40:00 | 180           |
| 3       | 2022-01-01 10:50:00 | 120           |

输出数据：

2.5、SQL重叠交叉区间问题分析？

考虑一个销售信息表 sales，其中包含了品牌销售的打折信息。表结构如下：

CREATE TABLE sales (
    brand_id INT,
    discount_start_date DATE,
    discount_end_date DATE
);

INSERT INTO sales VALUES
(1, '2022-01-01', '2022-01-05'),
(2, '2022-01-02', '2022-01-04'),
(1, '2022-01-03', '2022-01-06'),
(3, '2022-01-05', '2022-01-08'),
(2, '2022-01-06', '2022-01-10');

品牌销售信息表 sales 记录了每个品牌的打折销售期间，包括品牌ID (brand_id)、打折开始日期 (discount_start_date) 和打折结束日期 (discount_end_date)。

现在的任务是计算每个品牌的总的打折销售天数。然而，由于数据中存在时间交叠的情况，我们需要合理处理这种情况，避免重复计算销售天数。因此，你需要设计一条 SQL 查询来解决这个问题。

请编写一条 SQL 查询，计算每个品牌总的打折销售天数，要求正确处理时间交叠的情况，并给出你的解决方案。

2.6、计算员工薪水中位数（中等）

考虑一个包含员工薪水信息的表 employee_salary，结构如下

CREATE TABLE employee_salary (
    employee_id INT,
    salary INT
);
INSERT INTO employee_salary VALUES
(1, 50000),
(2, 60000),
(3, 75000),
(4, 80000),
(5, 90000);

表中包含每位员工的员工ID (employee_id) 和薪水 (salary)。

现在的任务是编写一条 SQL 查询，计算员工薪水的中位数。由于计算中位数需要对数据进行排序和分析，这个问题在 SQL 中相对复杂。你可以使用窗口函数和子查询等技术来解决。

2.7、给定数字的频率查询中位数（中等）

假设有一个包含数字及其频率信息的表 number_frequency，结构如下：

CREATE TABLE number_frequency (
    number INT,
    frequency INT
);

INSERT INTO number_frequency VALUES
(1, 3),
(2, 2),
(3, 5),
(4, 1);

编写一条 SQL 查询，计算给定数字频率表的中位数。

2.8、计算每月销售额的累计百分比

假设有一个包含销售信息的表 sales，结构如下：

CREATE TABLE sales (
    sale_date DATE,
    product_id INT,
    amount DECIMAL(10, 2)
);
INSERT INTO sales VALUES
('2022-01-01', 1, 100.00),
('2022-01-02', 1, 150.00),
('2022-01-03', 1, 200.00),
('2022-02-01', 1, 120.00),
('2022-02-02', 1, 180.00),
('2022-02-03', 1, 220.00),
('2022-01-01', 2, 50.00),
('2022-01-02', 2, 80.00),
('2022-01-03', 2, 120.00),
('2022-02-01', 2, 70.00),
('2022-02-02', 2, 100.00),
('2022-02-03', 2, 130.00);

表中包含销售日期 (sale_date)、产品ID (product_id) 和销售金额 (amount)。

请编写一条 Hive SQL 查询，计算每个产品每月销售额的累计百分比。输出结果应包含销售日期、产品ID、销售金额和对应的累计百分比。

2.9、计算档位问题

假设有一个包含员工信息的表 employee，结构如下：

CREATE TABLE employee (
    emp_id INT,
    emp_name STRING,
    salary DECIMAL(10, 2)
);

INSERT INTO employee VALUES
(1, 'Alice', 50000.00),
(2, 'Bob', 60000.00),
(3, 'Charlie', 75000.00),
(4, 'David', 80000.00),
(5, 'Eva', 90000.00),
(6, 'Frank', 100000.00);

表中包含员工ID (emp_id)、员工姓名 (emp_name) 和工资 (salary)。

请编写一条 Hive SQL 查询，将员工按工资分为四个档次，并输出每个员工的ID、姓名、工资和所在的工资档次。

**2.10、lead&lag练习

CREATE TABLE sales_orders (
    order_id INT,
    order_date DATE,
    product_id INT,
    quantity INT
);
INSERT INTO sales_orders VALUES
(1, '2022-01-01', 101, 50),
(2, '2022-01-02', 102, 30),
(3, '2022-01-03', 101, 40),
(4, '2022-01-04', 103, 20),
(5, '2022-01-05', 102, 60);

表中包含销售订单的订单ID (order_id)、订单日期 (order_date)、产品ID (product_id) 和订单数量 (quantity)。

请编写一条 Hive SQL 查询，以获取每个订单的以下信息：

订单ID (order_id)
订单日期 (order_date)
产品ID (product_id)
订单数量 (quantity)
下一个订单的订单日期 (lead_order_date)
下一个订单的订单数量 (lead_quantity)
上一个订单的订单日期 (lag_order_date)
上一个订单的订单数量 (lag_quantity)

**2.11、连续登陆天数

考虑一个包含用户登录信息的表 user_log，结构如下：

CREATE TABLE user_log (
    user_id INT,
    login_date DATE
);
INSERT INTO user_log VALUES
(1, '2022-01-01'),
(1, '2022-01-02'),
(1, '2022-01-03'),
(1, '2022-01-05'),
(2, '2022-01-01'),
(2, '2022-01-02'),
(2, '2022-01-03'),
(2, '2022-01-05'),
(2, '2022-01-06'),
(3, '2022-01-01'),
(3, '2022-01-02'),
(3, '2022-01-04');

表中包含用户ID (user_id) 和登录日期 (login_date)。

请编写一条 SQL 查询，计算每个用户的最长连续登录天数。
请编写一条 SQL 查询，计算连续登录天数3天的用户。
请编写一条 SQL 查询，计算连续登录天数3天的用户(非连续2天以上未登录为连续登陆的定义)。

三、结尾总结

数据仓库笔试题核心是sql、sql的核心是开窗，开窗最常考的是lead、lag、rank等，以上的面试题足够应付各类问题。

祝各位拿到字节、快手、小红书的offer

10年互联网研发经验，数据平台总监及投放增长研发负责人，负责数据平台期间，完成埋点改造、数据工具建设、离线及实时数仓建设，业务日增数据量约500T；负责增长平台期间，完成自助归因平台的建设，涉及归因、回传优化、回传等功能，支撑日投放金额近千万，同时在RTA/RTB也有深度实践。目前，在做特征工程和算法模型相关的工作。

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

sql