腾讯大数据面试SQL-文章阅读量统计：UV和PV
⚠️ 待修正
一、题目背景
这道题来自腾讯新闻/腾讯视频等内容业务的数据分析岗面试。PV（页面浏览量）和UV（独立访客数）是内容平台最基础的指标体系——PV衡量内容的总曝光量，UV衡量覆盖了多少独立用户。两者的比值 PV/UV 代表人均阅读次数，是衡量内容"粘性"的关键指标。
业务场景：腾讯新闻的编辑每天要看"昨日文章PV/UV排行榜"，决定今天头条推什么。运营团队则通过 PV/UV 比值判断文章是否"标题党"——PV很高但UV一般，说明同一批用户反复点进来又出去。
二、题目
现有一张文章阅读日志表 t9_article_read_log，记录了用户每次阅读文章的行为明细。请统计每篇文章的 PV（页面浏览量） 和 UV（独立访客数），并按照PV降序排列。
- PV（Page View）：文章被阅读的总次数，同一用户多次阅读算多次
- UV（Unique Visitor）：文章的独立访客数，同一用户多次阅读只算1次
文章阅读日志表 t9_article_read_log：
+----------+------------+---------------------+
| user_id | article_id | read_time |
+----------+------------+---------------------+
| u01 | A001 | 2023-03-01 08:00:00 |
| u01 | A001 | 2023-03-01 09:00:00 |
| u01 | A002 | 2023-03-01 10:00:00 |
| u02 | A001 | 2023-03-01 11:00:00 |
| u02 | A003 | 2023-03-01 12:00:00 |
| u03 | A001 | 2023-03-01 13:00:00 |
| u03 | A002 | 2023-03-01 14:00:00 |
| u03 | A002 | 2023-03-01 15:00:00 |
| u04 | A001 | 2023-03-01 16:00:00 |
| u04 | A002 | 2023-03-02 08:00:00 |
| u05 | A003 | 2023-03-02 09:00:00 |
| u05 | A003 | 2023-03-02 10:00:00 |
| u05 | A003 | 2023-03-02 11:00:00 |
| u05 | A004 | 2023-03-02 12:00:00 |
+----------+------------+---------------------+
三、思路分析
这是一道典型的聚合统计基础题，难度不高但考察对业务指标（PV/UV）的理解和SQL基础聚合函数的运用：
- PV：使用
count(*)或count(article_id)统计总行数 - UV：使用
count(distinct user_id)统计去重后的用户数 - 按
article_id分组group by，按 PV 降序排列order by
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 直接按文章ID分组统计PV和UV
执行SQL
select article_id,
count(*) as pv, -- 页面浏览量
count(distinct user_id) as uv -- 独立访客数
from t9_article_read_log
group by article_id
order by pv desc
执行结果
+------------+-----+-----+
| article_id | pv | uv |
+------------+-----+-----+
| A001 | 5 | 4 |
| A002 | 4 | 3 |
| A003 | 4 | 2 |
| A004 | 1 | 1 |
+------------+-----+-----+
2. 补充：计算每篇文章的PV占比
执行SQL
select article_id,
pv,
uv,
round(pv / sum(pv) over (), 4) as pv_rate
from (
select article_id,
count(*) as pv,
count(distinct user_id) as uv
from t9_article_read_log
group by article_id
) t
order by pv desc
执行结果
+------------+-----+-----+---------+
| article_id | pv | uv | pv_rate |
+------------+-----+-----+---------+
| A001 | 5 | 4 | 0.3571 |
| A002 | 4 | 3 | 0.2857 |
| A003 | 4 | 2 | 0.2857 |
| A004 | 1 | 1 | 0.0714 |
+------------+-----+-----+---------+
五、常见坑点
坑1：COUNT vs COUNT DISTINCT 别搞混
PV用 COUNT(*)（每次阅读算一次），UV用 COUNT(DISTINCT user_id)（每个用户只算一次）。这是两个SQL关键词的差异，也是两个业务指标的本质区别。面试中如果PV和UV都写了COUNT(*)，基本直接挂。
坑2：DISTINCT 的性能代价
COUNT(DISTINCT user_id) 在大数据量下会触发全量去重，Shuffle数据量大时性能较差。如果数据量很大且允许近似值，可以考虑 approx_count_distinct()（Spark SQL）或 HyperLogLog 算法。
六、举一反三
- 人均PV（PV/UV）：加一列
round(pv / uv, 2) as avg_pv_per_user，衡量每用户平均阅读次数 - 按时间维度统计：GROUP BY 加上
date字段，按天看PV/UV趋势和周末效应 - 新老用户分层：关联用户表，按新用户/老用户分组统计PV/UV，对比两者的阅读行为差异
- 阅读深度：如果日志表有
read_duration（阅读时长），按阅读时长分段统计PV分布
七、知识点总结
| 考点 | 说明 |
|---|---|
| COUNT(*) = PV | 统计总行数，即页面总浏览量 |
| COUNT(DISTINCT user_id) = UV | 去重统计独立用户数 |
| GROUP BY + ORDER BY | 按文章分组并按PV降序排列 |
| SUM() OVER() 占比 | 窗口函数一次扫描算全表总和，得到占比 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t9_article_read_log (
user_id string COMMENT '用户ID',
article_id string COMMENT '文章ID',
read_time string COMMENT '阅读时间'
) COMMENT '文章阅读日志表';
-- 数据插入
INSERT INTO t9_article_read_log VALUES
('u01', 'A001', '2023-03-01 08:00:00'),
('u01', 'A001', '2023-03-01 09:00:00'),
('u01', 'A002', '2023-03-01 10:00:00'),
('u02', 'A001', '2023-03-01 11:00:00'),
('u02', 'A003', '2023-03-01 12:00:00'),
('u03', 'A001', '2023-03-01 13:00:00'),
('u03', 'A002', '2023-03-01 14:00:00'),
('u03', 'A002', '2023-03-01 15:00:00'),
('u04', 'A001', '2023-03-01 16:00:00'),
('u04', 'A002', '2023-03-02 08:00:00'),
('u05', 'A003', '2023-03-02 09:00:00'),
('u05', 'A003', '2023-03-02 10:00:00'),
('u05', 'A003', '2023-03-02 11:00:00'),
('u05', 'A004', '2023-03-02 12:00:00');
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
