当前位置：首页 > news >正文

mysql常见指标计算笔记

news 2026/8/12 14:19:15

文章目录

1、用户拉新情况相关指标
- PV（页面浏览量/访问量）
- UV（独立访客数）
- PVUV（人均页面浏览量）
2、用户留存率指标
- 2.1 留存率计算
3、用户行为
4、用户定位RFM模型

1、用户拉新情况相关指标

可以分析每日浏览深度(按日期分组)，每时浏览深度(按时间分组)

PV（页面浏览量/访问量）

定义：用户对页面的每一次浏览/刷新都计为1次PV（同一用户多次访问同一页面会重复计数）。
计算公式：
PV = 所有用户的页面浏览次数之和
示例：用户A浏览页面X 3次，用户B浏览页面X 2次 → 页面X的PV = 3 + 2 = 5

UV（独立访客数）

定义：统计周期内（如1天），独立的用户数（同一用户多次访问只计1次，以设备/账号/IP等唯一标识区分）。
计算公式：
UV = 去重后的独立用户标识数量
示例：用户A用手机访问页面X 3次，用户B用电脑访问页面X 2次 → 页面X的UV = 2（2个独立用户）

PVUV（人均页面浏览量）

定义：平均每个独立用户的页面浏览次数，反映用户的访问深度。
计算公式：
PVUV = PV ÷ UV
示例：页面X的PV=5，UV=2 → PVUV = 5 ÷ 2 = 2.5

SELECTdates,count(IF(behavior_type='pv',1,NULL))ASPV,count(DISTINCTuser_id)ASUV,round((count(IF(behavior_type='pv',1,NULL))/count(DISTINCTuser_id)),2)ASPVUVFROMuserbehaviorGROUPBYdates;

2、用户留存率指标

2.1 留存率计算

（1）计算次日留存率方法1

-- 次日留存率：SQL164witht1as(selectuid,min(day_time)asfirst_dayfrom(selectuid,date(in_time)asday_timefromtb_user_log)r1groupbyuidhavingdate_format(first_day,'%Y-%m')='2021-11'),t2as(selectt1.uid,first_day,day_timeassecond_dayfromt1leftjoin(selectuid,date(in_time)asday_timefromtb_user_logunionselectuid,date(out_time)asday_timefromtb_user_log)bont1.uid=b.uidanddatediff(day_time,first_day)=1)selectfirst_day,# round(sum(if(second_day is null, 0 ,1))/count(uid),2) as uv_left_rateround(count(second_day)/count(uid),2)asuv_left_ratefromt2groupbyfirst_dayorderbyfirst_day-- 次日留存率：CREATETABLEdf_retention_1(datesCHAR(10),retention_1FLOAT);INSERTINTOdf_retention_1SELECTub1.dates,count(ub2.user_id)/count(ub1.user_id)ASretention_1FROM(SELECTDISTINCTuser_id,datesFROMuserbehavior)ASub1LEFTJOIN(SELECTDISTINCTuser_id,datesFROMuserbehavior)ASub2ONub2.user_id=ub1.user_idANDub2.dates=date_add(ub1.dates,INTERVAL1DAY)GROUPBYub1.dates;

（2）计算次日留存率方法2

SELECT-- 统计基准日（用户首次登录的日期，按这个日期分组算留存）first_login.login_dateAS统计日期,-- 1. 次日留存率：登录日期差 = 1 天CONCAT(ROUND((COUNT(DISTINCTCASEWHENDATEDIFF(second_login.login_date,first_login.login_date)=1THENsecond_login.user_idEND)/COUNT(DISTINCTfirst_login.user_id))*100,2),"%")AS次日留存率,-- 2. 三日留存率：登录日期差 = 2 天（当日为0，次日1，第三日就是2）CONCAT(ROUND((COUNT(DISTINCTCASEWHENDATEDIFF(second_login.login_date,first_login.login_date)=2THENsecond_login.user_idEND)/COUNT(DISTINCTfirst_login.user_id))*100,2),"%")AS三日留存率FROM-- 表1：所有用户每日登录去重（1人1天1条）(SELECTDISTINCTuser_id,login_dateFROMuser_login_table)first_login-- 左连接：关联用户后续登录记录（满足「后续日期>基准日期」，覆盖所有留存可能）LEFTJOIN(SELECTDISTINCTuser_id,login_dateFROMuser_login_table)second_loginONfirst_login.user_id=second_login.user_id-- 同一用户关联ANDsecond_login.login_date>first_login.login_date-- 核心条件：后续登录日期>基准日GROUPBYfirst_login.login_date-- 按基准日分组，每日的留存单独算ORDERBYfirst_login.login_dateDESC;-- 按日期倒序，优先看最新数据