-- 需求：计算2021年里每个创作者每月的涨粉率及截止当月的总粉丝量 -- 拆解思路如下： -- 1、以互动表为左表，使用left join以video_id为关联条件，获取信息表中的用户 -- 2、使用left函数获取日期对应的月份 -- 3、使用case when函数对if_follow的不同结果进行赋值，再使用sum和count分别计算对应的粉丝量、播放量来计算对应的涨粉率 -- 4、需要用sum窗口函数来统计截止当月的粉丝量。因为语句中有group by，所以需要先根据group by语句sum每个月的粉丝数，然后再使用开窗函数对月进行累计 select author ,left(s...

-- 需求：统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数）。 -- 需求拆解如下： -- 1、使用max与date函数获取互动表中的最大日期； -- 2、使用date_sub来获取最大日期对应的最近一个月 -- 3、使用sum与avg分别计算转发量与转发率 ，再使用round函数对结果保留3位小数 -- 4、使用group by对类别进行分组 select tag ,sum(if_retweet) as retweet_cnt ,round(sum(if_retweet)/cou...

-- 需求：计算各类视频的平均播放进度，将进度大于60%的类别输出。 对需求进行拆解如下： -- 1、以互动表为左表，使用left join以video_id为关联条件，匹配右表中每个视频对应的类别和播放时长； -- 2、计算播放进度：①使用timestampdiff函数获取每个视频开始时间和结束时间之间对应的秒数；②再通过if函数将实际播放时长与时长进行判断，如果实际播放时长>时长，就记录为1，否则记录为0；③使用avg计算对应的播放进度；④使用round函数与concat函数将结果呈现为百分比形式 -- 3、使用group by对类别进行分组统计 -- 4、使用having语句，筛选...

-- 计算2021年里有播放记录的每个视频的完播率(结果保留三位小数)，并按完播率降序排序 -- 完播率=完成播放次数/总播放次数 -- 解题思路：先对需求进行拆解。 -- 1、以互动表为左表，使用left join 匹配信息表中每个视频对应的时长； -- 2、使用timestampdiff函数计算每个视频start_time和end_time对应的实际播放时长； -- 3、通过使用if来判断是否完播，再使用avg计算对应的完播率 -- 4、补充2021年的限制条件，以及对完播率保留3位小数 select t1.video_id ,round(avg(if(timestampdiff(seco...

/* 在mysql中，创建触发器语法如下： create trigger trigger_name tigger_time trigger_event on tbl_name for each row trigger_stmt 其中： trigger_name：标识触发器名称，用户自行指定； trigger_time：标识触发器时机，取值为before 或 after； trigger_event：标识触发事件，取值为insert/update/delete； tbl_name：标识建立触发器的表名，即在哪张表上建立触发器； trigger_stmt：触发器程序体，可以是一句sql语句，或者用...

/* 添加字段语法 alter table <表名> add column <新字段名> <数据类型> [约束条件][first|after 已存在的字段] */ alter table actor add column create_date datetime not null default '2020-10-01 00:00:00' after last_update

/* 在MySQL中，强制索引是通过在查询中指定索引来实现的。如果你想确保查询使用特定的索引，你可以在查询中使用FORCE INDEX子句。 例如，假设你有一个名为users的表，它有一个名为idx_username的索引，用于username字段。如果你想强制查询使用这个索引，你可以这样写你的查询： SELECT * FROM users FORCE INDEX (idx_username) WHERE username = 'john_doe'; 在这个例子中，FORCE INDEX (idx_username)告诉MySQL，即使查询 planner 可能会选择一个成本更低的索引，或者可...

/* 直接在视图的后面用小括号 create view actor_name_view (first_name_v,last_name_v) as select first_name ,last_name from actor */ -- 在select后面对列重命名为视图的字段名 create view actor_name_view as select first_name as first_name_v ,last_name as last_name_v from actor

select dept_no ,emp_no ,salary from ( -- 使用窗口函数对部门进行分组，对组内员工薪水进行降序排列 select dept_no ,t1.emp_no ,salary ,dense_rank() over(partition by dept_no order by salary desc) as rank_1 from dept_emp t1 left join salaries t2 on t1.emp_no = t2.emp_no ) t3 where rank_1 = 1 order by dept_no

select t1.emp_no ,t2.emp_no as manager from dept_emp t1 inner join dept_manager t2 on t1.dept_no = t2.dept_no where t1.emp_no <> t2.emp_no

-- 统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中，每类视频的转发量和转发率（保留3位小数） -- 转发率＝转发量÷播放量。结果按转发率降序排序。 select b.tag ,sum(if_retweet) as retweet_cut ,round(sum(if_retweet)/count(if_follow),3) as retweet_rate from tb_user_video_log a left join tb_video_info b on a.video_id = b.video_id where ...

-- 统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。 -- 忠实用户(近7天活跃过且非新晋用户)——最大日期的差值 与 用户最大活跃日期 差值 0-6 且用户最小活跃日期与最大日期差值大于6 -- 新晋用户(近7天新增)——最大活跃日期 与 用户最小活跃日期 差值 0-6 -- 沉睡用户(近7天未活跃但更早前活跃过)——最大活跃日期 与 用户最大活跃日期 差值 大于6 -- 流失用户(近30天未活跃但更早前活跃过)——最大活跃日期 与 用户最大活跃日期 差值 大于29 -- 假设今天就是数据中所有日期的最大值。近7天表示包含当天T的近7天，即闭区间[T-6,...

select a.dt,round(count(b.uid)/count(a.uid),2) as uv_left_rate from ( -- 用户的最小日期 select uid,min(date(in_time)) as dt from tb_user_log group by uid ) a left join ( -- 用户的全部日期 select uid,date(in_time) dt from tb_user_log union select uid,date(out_time) dt from tb_user_log ) b on a.uid = b.uid and a.dt...

-- “同一时刻有进入有离开时，先记录增加后记录减少” ，进入+1，退出-1，可以使用sum窗口函数，对文章进行分组，然后先日期升序，再对人数进行降序排列 select artical_id ,max(uv) as max_uv from ( -- 使用sum窗口函数，根据文章id进行分组，对dt进行升序排列，计算对应的diff值 select artical_id ,sum(diff) over(partition by artical_id order by dt,diff desc) as uv from ( -- 先计算uid对应的进入时间和离开时间，并且进入+1.离开-1 selec...