hivesql每天访问的pv,uv

HiveSQL每日PV与UV计算详解

基础概念解析

PV（Page View）：页面浏览量，指用户每打开一个页面即记录一次访问，同一用户多次访问同一页面会产生多个PV。
UV（Unique Visitor）：独立访客数，指在统计周期内（如一天）通过设备/账号唯一标识去重后的访客数量，同一用户多次访问仅计为1个UV。

数据准备与表结构设计

假设存在日志表 web_log，典型字段如下：
| 字段名 | 类型 | 说明 |
|————–|———–|————————–|
| log_id | BIGINT | 日志唯一ID |
| user_id | STRING | 用户唯一标识（如UUID） |
| event_time | BIGINT | 事件发生时间（Unix时间戳）|
| page_url | STRING | 访问的页面URL |
| ip_address | STRING | 用户IP地址 |
| referrer | STRING | 来源页面 |

注：user_id 是核心字段，用于UV计算；event_time 需转换为日期格式以实现按天统计。

HiveSQL实现逻辑

提取日期字段

SELECT 
  FROM_UNIXTIME(event_time, 'yyyy-MM-dd') AS visit_date,
  user_id,
  page_url
FROM web_log

计算每日PV

SELECT 
  visit_date,
  COUNT() AS pv
FROM (
  SELECT 
    FROM_UNIXTIME(event_time, 'yyyy-MM-dd') AS visit_date,
    page_url
  FROM web_log
) tmp
GROUP BY visit_date
ORDER BY visit_date DESC

计算每日UV

SELECT 
  visit_date,
  COUNT(DISTINCT user_id) AS uv
FROM (
  SELECT 
    FROM_UNIXTIME(event_time, 'yyyy-MM-dd') AS visit_date,
    user_id
  FROM web_log
) tmp
GROUP BY visit_date
ORDER BY visit_date DESC

合并PV与UV结果

SELECT 
  a.visit_date,
  a.pv,
  b.uv
FROM (
  SELECT 
    visit_date,
    COUNT() AS pv
  FROM web_log
  WHERE event_time >= (UNIX_TIMESTAMP() 86400) -最近一天
  GROUP BY visit_date
) a
JOIN (
  SELECT 
    visit_date,
    COUNT(DISTINCT user_id) AS uv
  FROM web_log
  WHERE event_time >= (UNIX_TIMESTAMP() 86400)
  GROUP BY visit_date
) b
ON a.visit_date = b.visit_date

性能优化方案

1. 分区表设计：按 visit_date 分区，避免全表扫描。

   CREATE TABLE web_log_partitioned (
     user_id STRING,
     event_time BIGINT,
     page_url STRING,
     ...
   )
   PARTITIONED BY (visit_date STRING)

2. ETL预处理：在数据导入时直接计算 visit_date，减少实时查询计算量。
3. 索引优化：对 user_id 和 event_time 创建组合索引，加速去重与过滤。

特殊场景处理

1. 时区转换：若 event_time 为UTC时间，需转为业务所在时区。

   FROM_UNIXTIME(event_time, 'yyyy-MM-dd') 
   -> 
   FROM_UNIXTIME(event_time + 3600  TIME_OFFSET, 'yyyy-MM-dd')

2. 缺失用户标识：若 user_id 为空，可通过 ip_address + device_id 生成临时ID。

   COALESCE(user_id, CONCAT(ip_address, '_', device_id)) AS temp_user_id

结果验证与测试

1. 小数据测试：抽取1天数据，与Python/Pandas计算结果比对。
2. 边界条件检查：验证凌晨0点前后的数据归属是否正确。
3. UV准确性：通过 COUNT(DISTINCT) 与抽样数据人工核对。

FAQs（常见问题解答）

Q1：如何统计指定时间范围（如最近7天）的PV和UV？

A：修改 WHERE 条件中的时间范围，并调整分组逻辑，示例：

WHERE event_time >= (UNIX_TIMESTAMP() 7  86400) -最近7天
GROUP BY visit_date
ORDER BY visit_date DESC

Q2：如何处理跨天请求（如用户在23:59访问，次日0:01再次访问）？

A：严格按 visit_date（由 event_time 转换）分组，跨天请求会自动归属到对应日期，若需合并会话，需结合 session_id