ToB企服应用市场:ToB评测及商务社交产业平台

标题: SQL进阶day9————聚合与分组 [打印本页]

作者: 不到断气不罢休    时间: 2024-6-8 07:46
标题: SQL进阶day9————聚合与分组
 
目录
1聚合函数
1.1SQL类别高难度试卷得分的截断平均值
1.2统计作答次数
1.3 得分不小于平均分的最低分
2 分组查询
2.1平均活跃天数和月活人数
2.2 月总刷题数和日均刷题数
2.3未完成试卷数大于1的有效用户

1聚合函数

1.1SQL类别高难度试卷得分的截断平均值


我的错误代码:截断平均值是有专门的函数吗?
  1. select tag,difficulty,avg(score) clip_avg_score
  2. from examination_info ei join exam_record
  3. using(id)
  4. group by tag
  5. where tag = 'SQL' and difficulty='hard'
  6. and score not in (max(score),min(score))
复制代码
我的思路改正:用 (全部值 - 最大值 - 最小值) / (总数-2) ,但是缺点就是,如果最大值和最小值有多个,这个方法就很难筛选出来
  1. SELECT ei.tag,ei.difficulty,
  2.        ROUND((SUM(er.score)-MIN(er.score)-MAX(er.score)) / (COUNT(er.score)-2),1) AS clip_avg_score
  3. FROM examination_info ei join exam_record er
  4. on ei.exam_id = er.exam_id
  5. where ei.tag = "SQL"
  6. AND ei.difficulty = "hard";
复制代码
标准正确代码:
使用in子句将最大值和最小值排除掉,再求平均值

  1. # t1筛选出SQL高难度的数据
  2. WITH t1 as(
  3.     SELECT er.*,ei.tag,ei.difficulty
  4.     FROM exam_record er INNER JOIN
  5.     examination_info ei
  6.     ON er.exam_id = ei.exam_id
  7.     WHERE tag = "SQL" and difficulty = "hard"
  8. )
  9. # 在t1的基础上计算均值
  10. SELECT tag,difficulty,round(avg(score),1)
  11. FROM t1
  12. # 用in子句将最大值和最小值排除掉,再求平均值 not in
  13. WHERE score not in (
  14.     SELECT max(score)
  15.     FROM t1
  16.     UNION
  17.     SELECT min(score)
  18.     FROM t1
  19. )
复制代码
Q:为什么这里where换成and也不报错,因为前面有on?那么where和on有啥区别呢,可以只有一个吗?
A:

(1)where和having是在临时表生产之后,对临时表中的数据进行过滤用的。
如SQL语句:select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=’AAA’

(2) on是在生成中临时表之前就去作用的,它会在数据源那里就把不符合要求的数据给过滤掉,即是先把不符合条件的记录过滤后才进行统计,它就可以减少中间运算要处理的数据所以on运行的速度最快。
如SQL语句:select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=’AAA’)

(3)在两个表联接时才用on的,所以在一个表的时候,就剩下where跟having比较了。在这单表查询统计的情况下,如果要过滤的条件没有涉及到要计算字段,那它们的结果是一样的,但是where可以使用rushmore技术,而having就不能,在速度上后者要慢。
(4)  如果要涉及到计算的字段,where的作用时间是在计算之前就完成的,而having就是在计算后才起作用的,所以在这种情况下,两者的结果会不同。   
(5) 在多表联接查询时,on比where更早起作用。系统首先由on根据各个表之间的联接条件,把多个表合成一个临时表后,再由where进行过滤,然后再计算,计算完后再由having进行过滤。
1.2统计作答次数


我的报错代码:求已完成的试卷数时应该要分组一下,exam_id
  1. select count(er.id) total_pv,
  2. count(er.submit_time) complete_pv,
  3. count(t2.exam_id) complete_exam_cnt
  4. from exam_record er,(select count(exam_id) from exam_record
  5. group by exam_id) t2
复制代码
正确代码1:
主要在于已完成的试卷数的统计,因为这个带有条件,且需要统计聚合结果,可以使用 聚合函数与case when 结合。count中是可以加条件的
  1. select
  2. count(*)  total_pv,
  3. count(score)  complete_pv,
  4. count(distinct case when score is null then null else exam_id end)  complete_exam_cnt
  5. from exam_record
复制代码
复习case when
(1)case expr when v1 then r1 [when v2 then r2] ...[else rn] end
       例如:case 2 when 1 then 'one' when 2 then 'two' else 'more' end 返回two
       case后面的值为2,与第二条分支语句when后面的值相等相等,因此返回two
(2)case when v1 then r1 [when v2 then r2]...[else rn] end
       例如:case when 1<0 then 'T' else 'F' end返回F
       1<0的结果为false,因此函数返回值为else后面的F
正确代码2:
  1. select count(*) as total_pv,
  2. count(score) as complete_pv,
  3. count(distinct exam_id,score IS NOT NULL or null) as complete_exam_cnt
  4. # 是逗号,连接不是and连接
  5. from exam_record
复制代码
在select和count后面都可以加条件的,但是要明白内核:
count(distinct exam_id,score IS NOT NULL or null) as complete_exam_cnt (正确)
不能是
count(distinct exam_id and score IS NOT NULL or null) as complete_exam_cnt (错误,结果永远为2, 这个题只是碰巧结果为2,改一个数据就不对了)
1 用and:
(1)一般在where后筛选过滤,还是得到的满足条件的score

(2)如果在select后直接加条件判断:这里的score is not null 是判断


(3) 加上exam_id,进行and逻辑运算


(4) 在上一步的基础上去重,则只会剩下1和0


(5)所以这时候再进行count计算,结果恒为2 (两行数据)

 2 正确答案的执行逻辑:
(1)用,连接(从之前的逻辑判断,变为多列组合)

(2)这时候distinct 去重后,就不是只剩下两行数据 1 和 0,而是会把score为null也会考虑进去。

(3)结果应该是3,如果没有or NULL,就是5行了(null为0会被计数)

1.3 得分不小于平均分的最低分

 

我的代码:where后面的条件错了,但是思路大概这样。
  1. select score min_score_over_avg
  2. from exam_record er join examination_info ei
  3. on er.id = ei.id
  4. group by exam_id
  5. where score>=avg(score) and ei.tag = 'SQL'
  6. order by score asc
  7. limit 1
复制代码
修改我的代码:
  1. select er.score min_score_over_avg
  2. from exam_record er
  3. left join examination_info ei
  4. on er.exam_id = ei.exam_id # 不是按照id连接
  5. where  ei.tag = 'SQL'
  6. and score>= (SELECT avg(er.score) from exam_record er
  7. left join examination_info ei
  8. on er.exam_id=ei.exam_id
  9. where ei.tag='SQL')
  10. order by score asc
  11. limit 1
复制代码
(1)表连接是按照exam_id ,不是按照id连接 
(2)score>=某个值,这里不能直接score>=avg(score),而是应该通过表查询返回得到avg(score),然后在进行比较。
改进我的代码:
这里有表查询的部分重复了两次,可以用with...as句式将要多次使用的表命名,这样可以只写一次,多次调用。
此外,order by score asc  limit 1  可以换为min函数。
  1. with t as
  2. (SELECT score from exam_record er
  3. left join examination_info ei
  4. on er.exam_id=ei.exam_id
  5. where ei.tag='SQL')
  6. select min(score) min_score_over_avg
  7. from t
  8. where score>=
  9. (SELECT avg(t.score) from t)
复制代码
2 分组查询

2.1平均活跃天数和月活人数


我的代码:此处活跃指有交卷行为,用户平均月活跃天数avg_active_days啥意思?
  1. with t as
  2. (select *
  3. from exam_record
  4. where year(start_time)=2021)
  5. select month(start_time) 'month',
  6. count(submit_time) mau
  7. from t
  8. group by month(submit_time)
复制代码
正确代码:
  1. select DATE_FORMAT(start_time,"%Y%m") as month,
  2. round(count(distinct uid,date_format(start_time,"%Y%m%d"))/count(distinct uid),2) as avg_active_days,
  3. count(distinct uid) as mau
  4. from exam_record
  5. where submit_time is not NULL
  6. and YEAR(submit_time) =2021
  7. group by month;
复制代码
(1)202107是用date_format函数:DATE_FORMAT(start_time,"%Y%m") as month
(2)主要难的一点是天数的计算。
到底是count(distinct uid,date_format(start_time,"%Y%m%d"))
还是count(start_time)作为分子呢
关键是理解题目的意思是:天数。
假设一个uid 比如1001在2021-07-06这一天有二个记录,如果是count(start_time)那么就是天数是2,但是如果是count(distinct uid,date_format(start_time,"%Y%m%d"))天数就是1了
复盘探索:
(1)先找出2021年,活跃的用户ID和时间(具体到哪天)

(2)如果不考虑uid,直接按照活跃时间去重,那么不同用户在同一天活跃记录会被去重到只剩下1条,

(3)同理,如果只安装用户ID去重,那么同一用户在不同天的记录也会被去重到只有1条。这里查询的实际是月活跃的用户有哪些。

(4) 所以要去重的目的是,同一个用户,在同一天,重复提交活跃多次的记录。(因为这里是按天算,同一天同一个用户只算一次。)

(5)用户平均月活跃天数=月活跃天数 /月活跃用户
:count(distinct uid,date_format(start_time,"%Y%m%d"))/count(distinct uid)
月活跃天数:

月活跃用户: 

2.2 月总刷题数和日均刷题数


我的代码:分组好像报错,后面那个求总数我也不知道咋整
分组报错问题:MySQL提供了any_value()函数来抑制ONLY_FULL_GROUP_BY值被拒绝
  1. select
  2. date_format(submit_time,'%y%m') submit_month,
  3. count(score) month_q_cnt,
  4. count(score)/day(month(submit_time)) avg_day_q_cnt
  5. from practice_record
  6. group by date_format(submit_time,'%y%m%d')
  7. having year(submit_time)=2021
复制代码
(1)当月天数求错了,我是想先求出当前月,再求出当月天:这样操作结果是不对的。
    

应该用last_day函数求出最后一天,然后用day函数求出这个日期的天数。

复习【日期时间函数】
●   year(date)——获取年的函数
●   month(date)——获取月的函数
●   day(date)——获取日的函数
●   date_add(date,interval expr type)——对指定起始时间进行加操作
●   date_sub(date,interval expr type)——对指定起始时间进行减操作
●   datediff(date1,date2)——计算两个日期之间间隔的天数
●   date_format(date,format)——将日期和时间格式化
代码改正:
  1. select
  2. date_format(submit_time,'%y%m') submit_month,
  3. any_value(count(score)) month_q_cnt,
  4. any_value(round(count(score)/day(last_day(submit_time)),3)) avg_day_q_cnt
  5. from practice_record
  6. where year(submit_time)='2021'
  7. # where date_format(submit_time,'%y')='2021'
  8. group by submit_month
复制代码
该年的总体情况,可以用union all来连接,完整代码:
  1. select date_format(submit_time,'%Y%m') submit_month,
  2. any_value(count(question_id)) month_q_cnt,
  3. any_value(round(count(question_id)/day(LAST_DAY(submit_time)),3)) avg_day_q_cnt
  4. from practice_record
  5. where date_format(submit_time,'%Y')='2021'
  6. group by submit_month
  7. union all
  8. select '2021汇总' as submit_month,
  9. count(question_id) month_q_cnt,
  10. round(count(id)/31,3) avg_day_q_cnt
  11. from practice_record
  12. where date_format(submit_time,'%Y')='2021'
  13. order by submit_month;
复制代码
复习:
1、区别1:取结果的交集
1)union: 对两个结果集进行并集操作, 不包括重复行,相当于distinct, 同时进行默认规则的排序;
2)union all: 对两个结果集进行并集操作, 包括重复行, 即所有的结果全部显示, 不管是不是重复;
2、区别2:获取结果后的操作
1)union: 会对获取的结果进行排序操作
2)union all: 不会对获取的结果进行排序操作
3、总结
union all只是合并查询结果,并不会进行去重和排序操作,在没有去重的前提下,使用union all的执行效率要比union高。
2.3未完成试卷数大于1的有效用户


我的代码:罗里吧嗦答案还不对。。
  1. with t as
  2. (select uid,er.exam_id,start_time,submit_time,tag
  3. from exam_record er , examination_info ei
  4. where er.exam_id=ei.exam_id
  5. and date_format(start_time,'%Y')='2021')
  6. select uid,
  7. (select count(submit_time) from t
  8. where submit_time is NULL) incomplete_cnt,
  9. (select count(submit_time) from t
  10. where submit_time is not NULL) complete_cnt
  11. from t
  12. where (select count(submit_time) from t
  13. where submit_time is NULL)<5
  14. and (select count(submit_time) from t
  15. where submit_time is not NULL)>1
  16. order by incomplete_cnt
复制代码
我的代码改正:
  1. select uid,
  2. sum(case when submit_time is NULL then 1 else 0 end ) incomplete_cnt,
  3. sum(case when submit_time is NULL then 0 else 1 end ) complete_cnt
  4. from exam_record er join examination_info ei
  5. on er.exam_id=ei.exam_id
  6. where date_format(start_time,'%Y')='2021'
  7. group by uid
  8. having incomplete_cnt >1 and incomplete_cnt<5 and complete_cnt>1
  9. order by incomplete_cnt
复制代码
接下来是detail,作答过的试卷tag集合,是提交日期:类型;一直重复显示

我的完整代码:
  1. select uid,
  2. sum(case when submit_time is NULL then 1 else 0 end ) incomplete_cnt,
  3. sum(case when submit_time is NULL then 0 else 1 end ) complete_cnt,
  4. group_concat(DISTINCT concat_ws(':',date_format(start_time,"%Y-%m-%d"),tag) order by start_time Separator ';') detail
  5. from exam_record er join examination_info ei
  6. on er.exam_id=ei.exam_id
  7. where date_format(start_time,'%Y')='2021'
  8. group by uid
  9. having incomplete_cnt >1 and incomplete_cnt<5 and complete_cnt>1
  10. order by incomplete_cnt desc
复制代码
注意:select后面的属性,不管计算了多长,每个之间都要有逗号!!! 
大佬代码:
  1. SELECT uid,
  2. SUM(CASE WHEN submit_time IS NULL THEN 1 ELSE 0 END) "incomplete_cnt",
  3. SUM(CASE WHEN submit_time IS NULL THEN 0 ELSE 1 END) "complete_cnt",
  4. group_concat(distinct concat_ws(':',date(start_time),tag)
  5. order by start_time separator ';') as detail
  6. FROM exam_record er INNER JOIN
  7. examination_info ei
  8. ON er.exam_id = ei.exam_id
  9. WHERE year(start_time) = 2021
  10. GROUP BY uid
  11. HAVING complete_cnt >= 1 AND incomplete_cnt > 1 AND
  12. incomplete_cnt < 5
  13. ORDER BY incomplete_cnt desc
复制代码
其中
(1)用sum和case when函数来求完成和未完成的试卷数
(2)detail的实现是用concat_ws或者concat函数将submit_time和tag连接并且同时distinct:
函数group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator'分隔符'])
concat()函数

concat_ws()函数



免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!更多信息从访问主页:qidao123.com:ToB企服之家,中国第一个企服评测及商务社交产业平台。




欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4