SQL解决去重问题(在线超10min中的人数等)
一、描述
问题描述
比如有一些数据如下,记录了每个同学一天内的球类运动情况
tom 篮球 14min
tom 足球 25min
tom 篮球 20min
tom 足球 31min
jery 足球 25min
lilly 篮球 21min
问题:每项运动中坚持超过20min的学生数
二、求解
1、思路1
将时间大于20min的置做1,否则置为0,按照name,运动分组,对1和0求和,如果结果不等于0,则记作1
select ball
,count(*) cnt
from (
select name
,ball
,sum(if(tm>20, 1, 0)) times
from usr_tbl
group by name, ball
having sum(if(tm>20, 1, 0)) > 0
) t1
group by ball;
2、思路2
这种做法是不是很蠢,看似像是没有问题,其实很繁琐,其实用count和distinct结合,很容易就解决了
select ball
,count(distinct if(tm>20, name, null)) cnt
from usr_tbl
group by ball;
三、牛客的SQL33:直播各科出勤率
问题:请你统计每个科目的出勤率(attend_rate(%),结果保留两位小数),出勤率=出勤(在线时长10分钟及以上)人数 / 报名人数,输出结果按course_id升序排序
解决:
-- 1. 每个科目报名人数
with result1 as (
select course_id
,count(1) course_cnt
from behavior_tb
where if_sign = 1
group by course_id
),
-- 2. 每个科目出勤人数
result2 as (
select course_id
,count(distinct case when timestampdiff(minute, in_datetime, out_datetime)>=10 then user_id else null end) online_cnt
from attend_tb
group by course_id
)
select result1.course_id
,course_name
,round((online_cnt*100)/course_cnt,2) attend_rate
from result1
join result2
using(course_id)
join course_tb
using(course_id)