mysql运营_Mysql日常运营使用SQL

#用户的留存率或者留存人数查询

/*解析：1.这个必需要有三个字段：user_id,注册时间和活跃时间,放在一个表里(或者链接查询好做子查询)2.通过datediff(活跃时间,注册时间)=1/2/6等等来判断是否活跃3.通过case when 来判断2中的数据，是的话返回1，不是返回0，再求和，可以得到他的留存数据*/

select c.cdt,count(distinct c.user_id) 注册人数,sum(case when datediff(c.dt,c.cdt)=1 then 1 else 0 end) 次日留存人数,sum(case when datediff(c.dt,c.cdt)=2 then 1 else 0 end) 三日留存人数,sum(case when datediff(c.dt,c.cdt)=6 then 1 else 0 end) 七日留存人数

from (select a.user_id,a.dt,b.cdt

from

(select distinct user_id,from_unixtime(ctime/1000,'%Y-%m-%d') dt

from hhz_user_login_rec) a

right join (select user_id,from_unixtime(ctime/1000,'%Y-%m-%d') cdt

from hhz_user

where from_unixtime(ctime/1000,'%Y-%m-%d') ='2019-10-11') b

on a.user_id =b.user_id) c

group by c.cdt

#2.查询连续签到天数(最大连续签到天数为max就行),原理：按人和签到时间排序+窗口函数rank,

#如果有跳过签到的话，签到时间-rank值就会变大，其中连续签到时候的 时间-rank值是相等的

select a.数字-a.顺序,a.uid,count(*) 连续签到天数

from (SELECT uid,datediff(tdate,'1970-01-01') 数字,rank() over(partition by uid order by tdate asc) '顺序'

from card

) a

group by a.数字-a.顺序,a.uid

#3.对于师傅收徒的徒弟和师傅的级别进行查询

/*1.mysql只能join61个表，所有只能有61个left join2.利用每个一个join后的user_id是否存在来进行判断,存在就加1(使用case when来进行判断)3.类似的行数过多的可以使用复制到excel来下拉增加，再复制到navicate中就行了*/

elect a.级别,count(a.user_id) 人数

from

(SELECT

u1.user_id,

1+casewhenu2.user_idisnotnullthen1else0end+

casewhenu3.user_idisnotnullthen1else0end+

casewhenu4.user_idisnotnullthen1else0end+

casewhenu5.user_idisnotnullthen1else0end+

casewhenu6.user_idisnotnullthen1else0end+

casewhenu7.user_idisnotnullthen1else0end+

casewhenu8.user_idisnotnullthen1else0end+

casewhenu9.user_idisnotnullthen1else0end+

casewhenu10.user_idisnotnullthen1else0end+

casewhenu11.user_idisnotnullthen1else0end+

casewhenu12.user_idisnotnullthen1else0end+

casewhenu13.user_idisnotnullthen1else0end+

casewhenu14.user_idisnotnullthen1else0end+

casewhenu15.user_idisnotnullthen1else0end+

casewhenu16.user_idisnotnullthen1else0end+

casewhenu17.user_idisnotnullthen1else0end+

casewhenu18.user_idisnotnullthen1else0end+

casewhenu19.user_idisnotnullthen1else0end+

casewhenu20.user_idisnotnullthen1else0end+

casewhenu21.user_idisnotnullthen1else0end+

casewhenu22.user_idisnotnullthen1else0end+

casewhenu23.user_idisnotnullthen1else0end+

casewhenu24.user_idisnotnullthen1else0end+

casewhenu25.user_idisnotnullthen1else0end+

casewhenu26.user_idisnotnullthen1else0end+

casewhenu27.user_idisnotnullthen1else0end+

casewhenu28.user_idisnotnullthen1else0end+

casewhenu29.user_idisnotnullthen1else0end+

casewhenu30.user_idisnotnullthen1else0end+

casewhenu31.user_idisnotnullthen1else0end+

casewhenu32.user_idisnotnullthen1else0end+

casewhenu33.user_idisnotnullthen1else0end+

casewhenu34.user_idisnotnullthen1else0end+

casewhenu35.user_idisnotnullthen1else0end+

casewhenu36.user_idisnotnullthen1else0end+

casewhenu37.user_idisnotnullthen1else0end+

casewhenu38.user_idisnotnullthen1else0end+

casewhenu39.user_idisnotnullthen1else0end+

casewhenu40.user_idisnotnullthen1else0end+

casewhenu41.user_idisnotnullthen1else0end+

casewhenu42.user_idisnotnullthen1else0end+

casewhenu43.user_idisnotnullthen1else0end+

casewhenu44.user_idisnotnullthen1else0end+

casewhenu45.user_idisnotnullthen1else0end+

casewhenu46.user_idisnotnullthen1else0end+

casewhenu47.user_idisnotnullthen1else0end+

casewhenu48.user_idisnotnullthen1else0end+

casewhenu49.user_idisnotnullthen1else0end+

casewhenu50.user_idisnotnullthen1else0end+

casewhenu51.user_idisnotnullthen1else0end+

casewhenu52.user_idisnotnullthen1else0end+

casewhenu53.user_idisnotnullthen1else0end+

casewhenu54.user_idisnotnullthen1else0end+

casewhenu55.user_idisnotnullthen1else0end+

casewhenu56.user_idisnotnullthen1else0end+

casewhenu57.user_idisnotnullthen1else0end+

casewhenu58.user_idisnotnullthen1else0end+

casewhenu59.user_idisnotnullthen1else0end+

casewhenu60.user_idisnotnullthen1else0end+

casewhenu61.user_idisnotnullthen1else0end 级别

FROM

hhz_user u1

LEFT JOIN hhz_user u2 ON u1.parent_user_id = u2.user_id

LEFT JOIN hhz_user u3 ON u2.parent_user_id = u3.user_id

LEFT JOIN hhz_user u4 ON u3.parent_user_id = u4.user_id

LEFT JOIN hhz_user u5 ON u4.parent_user_id = u5.user_id

LEFT JOIN hhz_user u6 ON u5.parent_user_id = u6.user_id

LEFT JOIN hhz_user u7 ON u6.parent_user_id = u7.user_id

LEFT JOIN hhz_user u8 ON u7.parent_user_id = u8.user_id

LEFT JOIN hhz_user u9 ON u8.parent_user_id = u9.user_id

LEFT JOIN hhz_user u10 ON u9.parent_user_id = u10.user_id

LEFT JOIN hhz_user u11 ON u10.parent_user_id = u11.user_id

LEFT JOIN hhz_user u12 ON u11.parent_user_id = u12.user_id

LEFT JOIN hhz_user u13 ON u12.parent_user_id = u13.user_id

LEFTJOINhhz_useru14ONu13.parent_user_id=u14.user_id

LEFTJOINhhz_useru15ONu14.parent_user_id=u15.user_id

LEFTJOINhhz_useru16ONu15.parent_user_id=u16.user_id

LEFTJOINhhz_useru17ONu16.parent_user_id=u17.user_id

LEFTJOINhhz_useru18ONu17.parent_user_id=u18.user_id

LEFTJOINhhz_useru19ONu18.parent_user_id=u19.user_id

LEFTJOINhhz_useru20ONu19.parent_user_id=u20.user_id

LEFTJOINhhz_useru21ONu20.parent_user_id=u21.user_id

LEFTJOINhhz_useru22ONu21.parent_user_id=u22.user_id

LEFTJOINhhz_useru23ONu22.parent_user_id=u23.user_id

LEFTJOINhhz_useru24ONu23.parent_user_id=u24.user_id

LEFTJOINhhz_useru25ONu24.parent_user_id=u25.user_id

LEFTJOINhhz_useru26ONu25.parent_user_id=u26.user_id

LEFTJOINhhz_useru27ONu26.parent_user_id=u27.user_id

LEFTJOINhhz_useru28ONu27.parent_user_id=u28.user_id

LEFTJOINhhz_useru29ONu28.parent_user_id=u29.user_id

LEFTJOINhhz_useru30ONu29.parent_user_id=u30.user_id

LEFTJOINhhz_useru31ONu30.parent_user_id=u31.user_id

LEFTJOINhhz_useru32ONu31.parent_user_id=u32.user_id

LEFTJOINhhz_useru33ONu32.parent_user_id=u33.user_id

LEFTJOINhhz_useru34ONu33.parent_user_id=u34.user_id

LEFTJOINhhz_useru35ONu34.parent_user_id=u35.user_id

LEFTJOINhhz_useru36ONu35.parent_user_id=u36.user_id

LEFTJOINhhz_useru37ONu36.parent_user_id=u37.user_id

LEFTJOINhhz_useru38ONu37.parent_user_id=u38.user_id

LEFTJOINhhz_useru39ONu38.parent_user_id=u39.user_id

LEFTJOINhhz_useru40ONu39.parent_user_id=u40.user_id

LEFTJOINhhz_useru41ONu40.parent_user_id=u41.user_id

LEFTJOINhhz_useru42ONu41.parent_user_id=u42.user_id

LEFTJOINhhz_useru43ONu42.parent_user_id=u43.user_id

LEFTJOINhhz_useru44ONu43.parent_user_id=u44.user_id

LEFTJOINhhz_useru45ONu44.parent_user_id=u45.user_id

LEFTJOINhhz_useru46ONu45.parent_user_id=u46.user_id

LEFTJOINhhz_useru47ONu46.parent_user_id=u47.user_id

LEFTJOINhhz_useru48ONu47.parent_user_id=u48.user_id

LEFTJOINhhz_useru49ONu48.parent_user_id=u49.user_id

LEFTJOINhhz_useru50ONu49.parent_user_id=u50.user_id

LEFTJOINhhz_useru51ONu50.parent_user_id=u51.user_id

LEFTJOINhhz_useru52ONu51.parent_user_id=u52.user_id

LEFTJOINhhz_useru53ONu52.parent_user_id=u53.user_id

LEFTJOINhhz_useru54ONu53.parent_user_id=u54.user_id

LEFTJOINhhz_useru55ONu54.parent_user_id=u55.user_id

LEFTJOINhhz_useru56ONu55.parent_user_id=u56.user_id

LEFTJOINhhz_useru57ONu56.parent_user_id=u57.user_id

LEFTJOINhhz_useru58ONu57.parent_user_id=u58.user_id

LEFTJOINhhz_useru59ONu58.parent_user_id=u59.user_id

LEFTJOINhhz_useru60ONu59.parent_user_id=u60.user_id

LEFTJOINhhz_useru61ONu60.parent_user_id=u61.user_id) a

group by a.级别