select count(q2.device_id)/count(q1.device_id) as avg_ret from (select distinct device_id,date from question_practice_detail) as q1 left outer join (select distinct device_id,date from question_practice_detail) as q2 on q1.device_id=q2.device_id AND q1.date=DATE_SUB(q2.date,INTERVAL 1 DAY) 现在运营想...

/* select device_id, university, min(gpa) as gpa from user_profile group by university order by university; 错误：限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的； 程序异常退出, 请检查代码"是否有数组越界等异常"或者"是否有语法错误" SQL_ERROR_INFO: "Expression #1 of SELE...

/*1 substring_index(str,delim,count) str:要处理的字符串 delim:分隔符 count:计数 如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容 如果count是负数，那么就是从右往左数，第N个分隔符的右边的全部内容 如果需要中间内容，需要嵌套使用 注意：如果count为1(而不是0)，表示从第一个位置开始【数据库的记录都是从1开始，没有从0开始】。*/ # substring_index 可以嵌套使用 select # substring_index(profile,",",3) as age, 165cm,...

# （1）SUBSTRING_INDEX的写法 SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number FROM user_submit GROUP BY gender; # （2）LIKE的写法【mark一下，原来有IF(profile LIKE '%female','female','male') 这样的方式】 SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number FROM user_submit GROU...

# 限定条件 2021年8月，比如使用特定的取值函数，year(date)=2021,month(date)=8; # 例如使用date_formate函数进行转换日期格式 # date_formate(date,'%Y-%M')='202108' select distinct day(date) as day, count(question_id) as question_cnt from question_practice_detail where year(date)=2021 and month(dat...

# # 方法1：使用CASE WHEN搜索 # select device_id,gender, # case # when age between 20 and 24 then '20-24岁' # when age>=25 then '25岁及以上' # when age<20 then '20岁以下' # else '其他' # end as age_cut # from user_profile; # 方法二 select device_id, gender, if(age &gt...

# # 简单case 函数 # # CASE 测试表达式 # # WHEN 简单表达式1 THEN 结果表达式1 # # when 简单表达式2 then 结果表达式2 # # ... # # when 简单表达式n then 结果表达式n # # [else 结果表达式n+1] # # end # --简单Case函数 # CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END # --Case搜索函数 # CASE WHEN sex = '1' THEN '男' WHEN sex = '2' THEN '女' ELSE '...

# 第一步 # 首先要把In 和 out提取出来 作为一个cnt  in 加1， out 减1 # 先将提取出的表Union 合并   # 选择union all 因为可能会存在同一个视频观看时间相同的情况# 如果有要筛选的条件 先筛选完再合并 # 第二步 # 将第一步的总表创建一个临时表 记为t1 # 使用滑动窗口 计算出每个时刻的总在线人数  # 易错点：sum(cnt) over(partition by artical_id order by time, cnt desc) as sumcnt# cnt desc# 需要注意的是, 当同一时刻有人退出又有人进入时, 先计算进入的. 则还...

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)TIMESTAMPDIFF函数返回datetime_expr2 - datetime_expr1的结果，其中datetime_expr1和datetime_expr2可以是DATE或DATETIME类型值解析：TIMESTAMPDIFF(格式，开始时间，结束时间)unit: 日期比较返回的时间差单位，常用可选值如下:SECOND：秒MINUTE：分钟HOUR：小时DAY：天WEEK：星期MONTH：月QUARTER：季度YEAR：年datetime_expr1: 要比较的日期1datetime_e...

/* 请你统计每款的动销率（pin_rate，有销售的SKU数量/在售SKU数量）与售罄率（sell-through_rate，GMV/备货值，备货值=吊牌价*库存数），按style_id升序排序，以上例子的输出结果如下： style_id pin_rate(%) sell-through_rate(%) A 8.33 7.79 B 14.81 11.94 C 10.26 8.75 */ /* 一、动销率 Stock Keeping Unit (库存量单位)。在很多业务场景下，是计算库存进出计量，物理上不可分割的最小存货单元，表示某款商品的具体配置（规格、颜色等）。 有销售的SKU数量 /...

# # 请你统计每个科目的平均观看时长（观看时长定义为离开直播间的时间与进入直播间的时间之差，单位是分钟），输出结果按平均观看时长降序排序，结果保留两位小数。 # [使用]：timestampdiff(minute,in_datetime,out_datetime) select course_name, round( avg(timestampdiff(minute,in_datetime,out_datetime)) ,2) as avg_Len from course_tb a, attend_tb b where a.course_id =b. cours...

select a.course_id ,course_name, # 取出分组后的 in_datetime的time 要早于它开始的时间 count(distinct user_id) as online_num # 下面的having 已经筛选除了 # 上面的只需要计数 from course_tb a, attend_tb b where a.course_id = b.course_id and '19:00' BETWEEN DATE_FORMAT(in_datetime,'%H:%i') and DATE_FORMAT(out_datetime,'%H:%i') ...

# 请你统计回答过教育类问题的用户里有多少用户回答过职场类问题，以上例子的输出结果如下： select count(distinct author_id) as num from answer_tb where issue_id like 'C%' and author_id in (select author_id from answer_tb where issue_id like 'E%' ); select count(distinct author_id) num from issue_tb t1 join a...

# 统计在有用户互动的最近一个月（按包含当天在内的近30天算，比如10月31日的近30天为10.2~10.31之间的数据）中 # 每类视频的转发量和转发率（保留3位小数）。 # 注：转发率＝转发量÷播放量。结果按转发率降序排序。 # 1.有交互的视频的最近一个月（就是有播放量的视频的最大日期 减去 30天） # 2.每类视频在有用户互动的最近一个月（并不是对于各类视频计算最大值，而是整体的日期最大值） # 有播放量的视频的最大日期 max(start_time) select tag , SUM(if_retweet) retweet_cnt, # round ( SUM(if_...