横扫SQL面试——PV、UV问题

📊 横扫SQL面试：UV/PV问题

🌟 什么是UV/PV？

在数据领域，UV（Unique Visitor，独立访客） 和 PV（Page View，页面访问量） 是最基础也最重要的指标：

• 👥 UV：统计时间段内的唯一用户数（按用户ID去重）
• 📄 PV：统计时间段内的总访问次数（不去重）

🏆 UV/PV问题为什么重要？

1. 业务价值：直接反映网站/APP的用户规模和活跃度

2. 面试高频：90%的数据岗位面试都会涉及，掌握后能轻松应对留存率、转化率等复杂指标

3. 异常分析：UV突然下降可能原因（渠道故障/数据丢失等）

4. 衍生指标：

   • 人均PV = PV / UV
   • 访问深度 = PV / 会话数
   • 跳出率 = 只访问一页的会话 / 总会话数

---

🚀 实战练习

1.计算每日uv、pv
访问记录表access_log，包含字段id（自增主键）、user_id（用户ID）、access_date（访问日期）、page_id（页面ID）。

• 计算出每天的UV和PV。

• 计算出某个特定页面（假设页面ID为100）的UV和PV。

• 计算每个用户最近7天的平均PV

---

2. 对比新老用户的PV贡献占比

用户访问日志表user_visits，包含字段：user_id(用户ID)，visit_time(访问时间)，page_url(访问页面)。

用户信息表users，包含字段：user_id(用户ID)，register_date(注册日期)。

• 区分新用户(注册后7天内访问)和老用户(注册7天后访问)
• 计算新老用户各自的PV总量

• 计算新老用户PV占总PV的比例

最终查询也可：

完整代码：

-- 第一步：创建CTE (Common Table Expression) 计算每个访问记录的用户类型
WITH user_visit_stats AS (SELECT v.user_id,           -- 用户IDv.visit_time,        -- 访问时间u.register_date,     -- 注册日期CASE -- 判断用户类型：注册后7天内访问的为新用户，否则为老用户WHEN DATEDIFF(v.visit_time, u.register_date) <= 7 THEN '新用户'ELSE '老用户'END AS user_type     -- 用户类型标记FROM user_visits v       -- 访问记录表JOIN users u ON v.user_id = u.user_id  -- 关联用户信息表
),-- 第二步：按用户类型分组统计PV总量
pv_summary AS (SELECT user_type,          -- 用户类型COUNT(*) AS pv_count -- 计算每种用户类型的PV总量FROM user_visit_stats   -- 使用上一步的结果GROUP BY user_type      -- 按用户类型分组
),-- 第三步：计算所有用户的总PV量
total_pv AS (SELECT SUM(pv_count) AS total  -- 汇总所有PVFROM pv_summary                -- 使用上一步的分组统计结果
)-- 最终查询：计算每种用户类型的PV占比
SELECT p.user_type,p.pv_count,ROUND(p.pv_count * 100.0 / (SELECT SUM(pv_count) FROM pv_summary), 2) AS pv_percentage
FROM pv_summary p
ORDER BY p.pv_count DESC;SELECT p.user_type,                   -- 用户类型p.pv_count,                    -- 该类型的PV数量ROUND(p.pv_count * 100.0 / t.total, 2) AS pv_percentage  -- 计算占比(百分比)
FROM pv_summary p                  -- 用户类型分组统计
CROSS JOIN total_pv t              -- 与总PV量交叉连接(确保每行都能计算占比)
ORDER BY p.pv_count DESC;          -- 按PV数量降序排列

---

• 计算每日新老用户的PV占比趋势

完整代码:

-- 第一步：创建CTE标记每日每条访问记录的用户类型
WITH daily_user_types AS (SELECT DATE(v.visit_time) AS visit_date,  -- 将访问时间转为日期格式（去掉时分秒）v.user_id,                         -- 用户IDCASE -- 判断用户类型：注册后7天内访问的为新用户，否则为老用户WHEN DATEDIFF(v.visit_time, u.register_date) <= 7 THEN '新用户'ELSE '老用户'END AS user_type                   -- 用户类型标记FROM user_visits v                     -- 访问记录表JOIN users u ON v.user_id = u.user_id  -- 关联用户信息表
),-- 第二步：按日期和用户类型分组统计PV量
daily_pv AS (SELECT visit_date,       -- 访问日期user_type,        -- 用户类型COUNT(*) AS pv_count  -- 计算每日每类用户的PV总量FROM daily_user_types -- 使用上一步的结果GROUP BY visit_date, user_type  -- 按日期和用户类型分组
),-- 第三步：计算每日的总PV量（不分用户类型）
daily_totals AS (SELECT visit_date,              -- 访问日期SUM(pv_count) AS daily_total  -- 计算每日所有用户的总PV量FROM daily_pv                -- 使用上一步的分组统计结果GROUP BY visit_date          -- 按日期分组
)-- 最终查询：计算每日每类用户的PV占比
SELECT d.visit_date,                      -- 访问日期d.user_type,                       -- 用户类型d.pv_count,                        -- 该类型的PV数量ROUND(d.pv_count * 100.0 / t.daily_total, 2) AS percentage  -- 计算占比(百分比)
FROM daily_pv d                       -- 每日用户类型分组统计
JOIN daily_totals t ON d.visit_date = t.visit_date  -- 关联每日总PV量（按日期匹配）
ORDER BY d.visit_date, d.user_type;   -- 按日期和用户类型排序

---

3. 识别"高价值用户"（UV高且PV高）

用户访问日志表user_visits，包含字段：user_id(用户ID)，visit_time(访问时间)，page_url(访问页面)。

• 找出访问天数多且访问页面多——前20%的"高价值用户"

使用 NTILE 分桶

---

留个作业：（有难度哈）

基于流量与转化率的酒店分类筛选

现有一张名为 hotel 的表，用于记录酒店的相关数据，表结构如下：

字段名	数据类型	说明
id	唯一标识	酒店的唯一编号
pv	整数	酒店的展现量（PV，Page View）
cnt	整数	酒店的支付订单量

要求根据上述表中的数据，筛选出以下三类酒店的 id：

1. 高流高转：在流量降序排列的前 20% 的酒店中，筛选出有支付订单（cnt > 0）的酒店，并且这些酒店的转化率在降序排列的前 20%。
2. 高流低转：在流量降序排列的前 20% 的酒店中，筛选出转化率升序排列的前 20% 的酒店（包括没有支付订单的酒店，即 cnt = 0 的情况）。
3. 低流高转：在流量升序排列的前 20% 的酒店中，筛选出有支付订单（cnt > 0）的酒店，并且这些酒店的转化率在降序排列的前 20%。

---

这道题有点优雅：

-- 使用 WITH 子句创建一个名为 hotel_stats 的公共表表达式（CTE）
with hotel_stats as (-- 从 hotel 表中选择所需的列，并计算一些统计信息select id,-- 计算酒店的总数，使用窗口函数对整个结果集进行计数count(*) over() as all_hotel_num,-- 对酒店按照展现量（pv）降序排名，使用窗口函数 row_number()row_number() over(order by pv desc) as rk_pv,   -- 展现量-- 判断酒店是否有支付订单，有则标记为 1，否则标记为 0case when cnt > 0 then 1 else 0 end as has_order,  -- 有订单-- 对酒店按照转化率（cnt / pv）降序排名，使用窗口函数 row_number()row_number() over(order by case when pv = 0 then 0 else cnt / pv end desc) as rk_change  -- 转化率from hotel
)
-- 从 hotel_stats 子查询中选择所需的列，并根据排名情况对酒店进行分类
select id,-- 根据排名和是否有订单的情况，对酒店进行分类case -- 高流高转：流量排名在前 20% 且有订单  且转化率排名在前 20%when rk_pv / all_hotel_num <= 0.2 and has_order = 1 and rk_change / all_hotel_num <= 0.2 then '高流高转'-- 高流低转：流量排名在前 20% 且转化率排名在后 20%when rk_pv / all_hotel_num <= 0.2 and rk_change / all_hotel_num >= 0.8 then '高流低转'-- 低流高转：流量排名在后 20% 且有订单且转化率排名在前 20%when rk_pv / all_hotel_num >= 0.8 and has_order = 1 and rk_change / all_hotel_num <= 0.2 then '低流高转'-- 其他情况标记为未知else '未知' end as lable
from hotel_stats;