网站用户行为实时分析案例

场景描述

某网站需要对用户行为数据进行实时统计。

业务需求

实时分析浏览网站的用户的行为数据，分析数据包括：按小时/天统计UV（独立访客数）、PV（页面浏览量）、访问时长、留存率等。

解决方案

使用物化视图的增量刷新及分区物化视图功能。

应用示例

用户行为日志表。

     CREATE TABLE user_behavior_log (
user_id BIGINT,
event_type VARCHAR(50),
page_url VARCHAR(200),
duration INT,  -- 停留时长(秒)
event_time TIMESTAMP
) WITH (orientation = COLUMN);
 
 
  

小时级统计物化视图，利用分区物化视图进行分区上卷计算。

     CREATE MATERIALIZED VIEW mv_user_stats_hourly
REFRESH FAST EVERY (interval '10 min')
ENABLE QUERY REWRITE
WITH (orientation = COLUMN)
PARTITION BY date_trunc('hour', event_time)
AS
SELECT
DATE_TRUNC('hour', event_time) as stat_hour,
event_type,
page_url,
COUNT(*) as pv,  -- 页面访问次数
COUNT(DISTINCT user_id) as uv,  -- 独立访客数
AVG(duration) as avg_duration,  -- 平均停留时长
COUNT(CASE WHEN duration > 60 THEN 1 END) as long_stay_count  -- 停留>1分钟
FROM user_behavior_log
GROUP BY DATE_TRUNC('hour', event_time), event_type, page_url;
 
 
  

留存率统计。

     CREATE TABLE user_behavior_log (
CREATE MATERIALIZED VIEW mv_user_retention
REFRESH FAST EVERY (interval '1 hour')
ENABLE QUERY REWRITE
AS
SELECT
first_date,
days_diff,
user_count,
ROUND(100.0 * user_count / (SELECT COUNT(*) FROM first_visit WHERE first_date = retention_calc.first_date), 2) as retention_rate
FROM retention_calc;
 
 
  