安装第二套mysql_2020-11-10-Mysql(练习题第二套)

遗留问题 where cast(event_day as DATE ) between cast('20200320' as DATE ) and date_add ('20200320' ,interval 7 day ); 这种语法为什么是错误的:发现问题:date_add 后有空格

//select cast('20200102' as DATE ); 能够将这种字符串转换成日期格式

create table tb_tmp_1d(

cuid varchar(10), //用户唯一标识

os_nmae varchar(7), /平台

soft_version varchar(10), //版本

event_day varchar(8) //日期

);

insert into tb_tmp_1d values('esd23jd','android','10.10.0','20200101');

insert into tb_tmp_1d values('ewerjjd','android','10.10.0','20200320');

insert into tb_tmp_1d values('esftyjd','apple','10.10.1','20200321');

insert into tb_tmp_1d values('wet23jd','android','10.11.1','20200322');

insert into tb_tmp_1d values('esd23jd','apple','10.12.0','20200323');

insert into tb_tmp_1d values('esiopd','android','10.13.1','20200324');

insert into tb_tmp_1d values('ewe23jd','apple','10.13.1','20200326');

insert into tb_tmp_1d values('esd23jd','android','10.13.0','20200421');

insert into tb_tmp_1d values('qqd23jd','apple','10.13.0','20200521');

insert into tb_tmp_1d values('esd67jd','android','10.13.0','20200621');

insert into tb_tmp_1d values('e34623jd','apple','10.14.0','20200721');

insert into tb_tmp_1d values('esduijd','apple','10.31.0','20200821');

insert into tb_tmp_1d values('edeu23jd','android','11.10.0','20200921');

insert into tb_tmp_1d values('edeu2216d','android','11.11.0','20200922');

1.

UV(unique visitor)

是指自然人登录自己账号访问量

KPI是指UV

PV (Page View)

是指网页的浏览量

vv(Visitor view)

是指每次登录网站的访问次数

//分析是否要对字段分类以及怎么分类 grouping sets() 聚合不同粒度 ?

2.

写出20200320的次日 次7日的留存分析

//怎么表示留存? 考虑使用每日占比来表示 //这里的次日指的是哪一日?

//分析首先统计20200320的登录人数

//然后统计次日的登录人数(要去重,考虑同一标识码登录的情况)

//问题:怎么表示次7日,考虑使用窗口函数 count() 统计每日的登录人数

//接着使用 lag()获取上一日的登录情况 ? 这里使用比较复杂 采用order by 进行比较

#使用窗口函数??怎么写

select

count (distinct cuid ) over( order by event_day rows between 1 preceding and current row ) as result

from tb_tmp_1d

group by event_day

select

count(cuid)

from tb_tmp_1d

where event_day = '20200320'

#采用分解步骤,求取结果

select

t2.event_day,

(t2.number1 / t2.number) * 100

from

(

select

t1.event_day,

t1.number,

lag(t1.number,1,0) over () as number1

from

(

select

event_day,

count(distinct cuid) as number

from tb_tmp_1d

group by event_day) t1 ) t2;

create table tb_accounts_df(

cuid varchar(10),

uids varchar(10),

even_day varchar(10)

);

insert into tb_accounts_df values ('esd23jd','10001','20201010');

insert into tb_accounts_df values ('ewerjjd','10002','20200908');

insert into tb_accounts_df values ('esftyjd','10003','20201209');

insert into tb_accounts_df values ('wet23jd','10008','122100');

insert into tb_accounts_df values ('esd23jd','10003','202012');

insert into tb_accounts_df values ('esiopd','100123','23434');

insert into tb_accounts_df values ('ewe23jd','311097','5663');

insert into tb_accounts_df values ('esd23jd','3456','432');

insert into tb_accounts_df values ('qqd23jd','98097','4352');

insert into tb_accounts_df values ('esd67jd','1000676','4567');

insert into tb_accounts_df values ('e34623jd','1000890','7823');

insert into tb_accounts_df values ('esduijd','1000078','878');

insert into tb_accounts_df values ('edeu23jd','1000088','685');

insert into tb_accounts_df values ('edeu23jd','1000088','685');

insert into tb_accounts_df values ('edeuwrqrq','1000088','685');

insert into tb_accounts_df values ('edffra23jd','1000088','645345');

insert into tb_accounts_df values ('edttw23jd','1000088','6356465');

insert into tb_accounts_df values ('edyur3jd','1000088','646435');

insert into tb_accounts_df values ('edeueewrd','1000088','652525');

3.求取活跃的用户量和活跃的登录用户量

#明确什么是用户量? 指示某区域的留存量 登录量指示某一日总留存中有多少用户登录

//第二张表是全部的用户标识,,分别count() 但是有歧义我不知道一共有多少用户 我只知道总共的用户标识和区域的活跃用户

select

os_nmae,

count(d.cuid),

count(s.cuid)

from

tb_tmp_1d d

right join

tb_accounts_df s

on d.cuid = s.cuid

where event_day = '20200101'

group by os_nmae

4.求某一周的周活跃用户量的平均值

//分析:某一周怎么获取 是否去重 问题:分组和去重?什么时候使用 使用 date_add()方法

使用 between and

select

substr(count(1)/7,1,1)

from

tb_tmp_1d

// #的写法为什么出错了????

#where cast(event_day as DATE ) between cast('20200320' as DATE )and date_add ('20200320' ,interval 7 day );

where event_day between 20200320 and 20200327;