横扫SQL口试——PV、UV标题

📊 横扫SQL口试：UV/PV标题

🌟 什么是UV/PV？

在数据范畴，UV（Unique Visitor，独立访客） 和 PV（Page View，页面访问量） 是最底子也最紧张的指标：

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

🏆 UV/PV标题为什么紧张？

• 业务代价：直接反映网站/APP的用户规模和生动度
  
• 口试高频：90%的数据岗位口试都会涉及，把握后能轻松应对留存率、转化率等复杂指标
  
• 非常分析：UV忽然降落大概缘故原由（渠道故障/数据丢失等）
  
• 衍生指标：
  
  
  
  • 人均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的比例
  

终极查询也可：

完备代码：

1. -- 第一步：创建CTE (Common Table Expression) 计算每个访问记录的用户类型
2. WITH user_visit_stats AS (
3.     SELECT
4.         v.user_id,           -- 用户ID
5.         v.visit_time,        -- 访问时间
6.         u.register_date,     -- 注册日期
7.         CASE
8.             -- 判断用户类型：注册后7天内访问的为新用户，否则为老用户
9.             WHEN DATEDIFF(v.visit_time, u.register_date) <= 7 THEN '新用户'
10.             ELSE '老用户'
11.         END AS user_type     -- 用户类型标记
12.     FROM user_visits v       -- 访问记录表
13.     JOIN users u ON v.user_id = u.user_id  -- 关联用户信息表
14. ),
16. -- 第二步：按用户类型分组统计PV总量
17. pv_summary AS (
18.     SELECT
19.         user_type,          -- 用户类型
20.         COUNT(*) AS pv_count -- 计算每种用户类型的PV总量
21.     FROM user_visit_stats   -- 使用上一步的结果
22.     GROUP BY user_type      -- 按用户类型分组
23. ),
25. -- 第三步：计算所有用户的总PV量
26. total_pv AS (
27.     SELECT SUM(pv_count) AS total  -- 汇总所有PV
28.     FROM pv_summary                -- 使用上一步的分组统计结果
29. )
31. -- 最终查询：计算每种用户类型的PV占比
32. SELECT
33.     p.user_type,
34.     p.pv_count,
35.     ROUND(p.pv_count * 100.0 / (SELECT SUM(pv_count) FROM pv_summary), 2) AS pv_percentage
36. FROM pv_summary p
37. ORDER BY p.pv_count DESC;
39. SELECT
40.     p.user_type,                   -- 用户类型
41.     p.pv_count,                    -- 该类型的PV数量
42.     ROUND(p.pv_count * 100.0 / t.total, 2) AS pv_percentage  -- 计算占比(百分比)
43. FROM pv_summary p                  -- 用户类型分组统计
44. CROSS JOIN total_pv t              -- 与总PV量交叉连接(确保每行都能计算占比)
45. ORDER BY p.pv_count DESC;          -- 按PV数量降序排列

复制代码

---

• 盘算逐日新老用户的PV占比趋势
  

完备代码:

1. -- 第一步：创建CTE标记每日每条访问记录的用户类型
2. WITH daily_user_types AS (
3.     SELECT
4.         DATE(v.visit_time) AS visit_date,  -- 将访问时间转为日期格式（去掉时分秒）
5.         v.user_id,                         -- 用户ID
6.         CASE
7.             -- 判断用户类型：注册后7天内访问的为新用户，否则为老用户
8.             WHEN DATEDIFF(v.visit_time, u.register_date) <= 7 THEN '新用户'
9.             ELSE '老用户'
10.         END AS user_type                   -- 用户类型标记
11.     FROM user_visits v                     -- 访问记录表
12.     JOIN users u ON v.user_id = u.user_id  -- 关联用户信息表
13. ),
15. -- 第二步：按日期和用户类型分组统计PV量
16. daily_pv AS (
17.     SELECT
18.         visit_date,       -- 访问日期
19.         user_type,        -- 用户类型
20.         COUNT(*) AS pv_count  -- 计算每日每类用户的PV总量
21.     FROM daily_user_types -- 使用上一步的结果
22.     GROUP BY visit_date, user_type  -- 按日期和用户类型分组
23. ),
25. -- 第三步：计算每日的总PV量（不分用户类型）
26. daily_totals AS (
27.     SELECT
28.         visit_date,              -- 访问日期
29.         SUM(pv_count) AS daily_total  -- 计算每日所有用户的总PV量
30.     FROM daily_pv                -- 使用上一步的分组统计结果
31.     GROUP BY visit_date          -- 按日期分组
32. )
34. -- 最终查询：计算每日每类用户的PV占比
35. SELECT
36.     d.visit_date,                      -- 访问日期
37.     d.user_type,                       -- 用户类型
38.     d.pv_count,                        -- 该类型的PV数量
39.     ROUND(d.pv_count * 100.0 / t.daily_total, 2) AS percentage  -- 计算占比(百分比)
40. FROM daily_pv d                       -- 每日用户类型分组统计
41. JOIN daily_totals t ON d.visit_date = t.visit_date  -- 关联每日总PV量（按日期匹配）
42. 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：

• 高流高转：在流量降序分列的前 20% 的旅馆中，筛选出有付出订单（cnt > 0）的旅馆，而且这些旅馆的转化率在降序分列的前 20%。
  
• 高流低转：在流量降序分列的前 20% 的旅馆中，筛选出转化率升序分列的前 20% 的旅馆（包罗没有付出订单的旅馆，即 cnt = 0 的情况）。
  
• 低流高转：在流量升序分列的前 20% 的旅馆中，筛选出有付出订单（cnt > 0）的旅馆，而且这些旅馆的转化率在降序分列的前 20%。
  

---

这道题有点优雅：

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

复制代码