📊 橫掃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;