mysql常见指标计算笔记

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;-- 按日期倒序，优先看最新数据

3、用户行为

指标：用户购买行为
用户购买次数(按照用户分组统计)
用户复购率:购买次数超过两次的用户比上总购买用户数，计算思路为使用用户表t和重复购买的用户表sub进行左连接，计算sub表中的用户数，占连接后表的用户数的比例。

指标：用户转化率

用户PV，FAV，CART， BUY
计算方法：对于用户行为字段，按照用户和商品分组统计对应行为的数量

用户行为归一标准化
计算方法：对于上一步按照用户和商品分组统计过的指标，不管数量，只要大于0就有过这种行为统计为1

用户行为路径记录
计算方法：以用户和商品为唯一字段，记录行为路径

统计不同用户行为路径的数量，并按照漏斗分析来分类分组计数

4、用户定位RFM模型