---

题目链接

https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=240

牛客-SQL进阶挑战 03 聚合分组查询

聚合函数

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

SELECT
  t1.tag,
  t1.difficulty,
  ROUND(
    (SUM(score) - MAX(score) - MIN(score)) / (COUNT(score) -2),
    1
  ) clip_avg_score
FROM
  examination_info t1
  JOIN exam_record t2 ON t1.exam_id = t2.exam_id
WHERE
  t1.tag = 'SQL'
  AND t1.difficulty = 'hard';
1
2
3
4
5
6
7
8
9
10
11
12
13

SQL 124 统计作答次数

SELECT
  COUNT(id) total_pv,
  COUNT(submit_time) complete_pv,
  COUNT(DISTINCT IF(submit_time IS NOT NULL, exam_id, NULL)) complete_exam_cnt
FROM
  exam_record;
1
2
3
4
5
6

SQL 125 得分不小于平均分的最低分

SELECT MIN(score) min_score_over_avg
FROM exam_record
WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag = 'SQL')
AND score >=
(
    SELECT AVG(score) avg_score
    FROM exam_record
    WHERE exam_id IN (
            SELECT exam_id FROM examination_info WHERE tag = 'SQL'
    )
);
1
2
3
4
5
6
7
8
9
10
11

分组查询

SQL 126 平均活跃天数和月活人数

SELECT
  DATE_FORMAT(submit_time, '%Y%m') `month`,
  ROUND((
      COUNT(DISTINCT uid, DATE(submit_time)) / COUNT(DISTINCT uid)
    ),2) avg_active_days,
  COUNT(DISTINCT uid) mau
FROM
  exam_record
WHERE
  submit_time IS NOT NULL
  AND DATE_FORMAT(submit_time, '%Y') = 2021
GROUP BY
  DATE_FORMAT(submit_time, '%Y%m')
ORDER BY
  `month` ASC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SQL 127 月总刷题数和日均刷题数

SELECT
  DATE_FORMAT(submit_time, '%Y%m') submit_month,
  COUNT(*) month_q_cnt,
  ROUND(COUNT(*) / AVG(DAY(LAST_DAY(submit_time))), 3) avg_day_q_cnt
FROM
  practice_record
WHERE
  YEAR(submit_time) = '2021'
  AND submit_time IS NOT NULL
GROUP BY
  DATE_FORMAT(submit_time, '%Y%m')
  
UNION ALL

SELECT
  '2021汇总' submit_month,
  COUNT(*) month_q_cnt,
  ROUND(COUNT(*) / 31, 3) avg_day_q_cnt
FROM
  practice_record
WHERE
  YEAR(submit_time) = '2021'
  AND submit_time IS NOT NULL
ORDER BY
  submit_month;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SQL 128 未完成试卷数大于1的有效用户

方案一

SELECT t1.uid,t1.incomplete_cnt,t1.complete_cnt,t2.detail
FROM (
	SELECT 
		uid, 
		SUM(IF(submit_time IS NULL,1,0)) incomplete_cnt, 
		COUNT(submit_time) complete_cnt
	FROM 
		exam_record
	WHERE 
		YEAR(start_time)=2021
	GROUP BY 
		uid
	HAVING 
		COUNT(submit_time) >= 1 
		AND SUM(IF(submit_time IS NULL,1,0)) > 1 
		AND SUM(IF(submit_time IS NULL,1,0)) < 5
) t1,(
	SELECT 
		uid,
		GROUP_CONCAT(
			DISTINCT CONCAT(DATE(start_time),':',tag) 
			ORDER BY start_time 
			SEPARATOR ';') detail
	FROM 
		exam_record t1 
	JOIN examination_info t2 
		ON t1.exam_id = t2.exam_id
	WHERE 
		YEAR(start_time) = 2021
	GROUP BY 
		uid
) t2
WHERE 
	t1.uid = t2.uid
ORDER BY 
	t1.incomplete_cnt DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

方案二

SELECT
  uid,
  SUM(IF(submit_time IS NULL, 1, 0)) incomplete_cnt,
  COUNT(submit_time) complete_cnt,
  GROUP_CONCAT(
    DISTINCT CONCAT(DATE(start_time), ':', tag)
    ORDER BY start_time
    SEPARATOR ';') detail
FROM
  exam_record t1
  JOIN examination_info t2 ON t1.exam_id = t2.exam_id
WHERE
  YEAR(start_time) = 2021
GROUP BY
  uid
HAVING
  COUNT(submit_time) >= 1
  AND SUM(IF(submit_time IS NULL, 1, 0)) > 1
  AND SUM(IF(submit_time IS NULL, 1, 0)) < 5
ORDER BY
  incomplete_cnt DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21