文章来源于网络收集而来，版权归原创者所有，如有侵权请及时联系！

聚合窗口函数

发布于 2024-08-17 15:49:00 字数 11772 浏览 0 评论 0 收藏 0

对试卷得分做 min-max 归一化

描述：

现有试卷信息表 examination_info （ exam_id 试卷 ID, tag 试卷类别, difficulty 试卷难度, duration 考试时长, release_time 发布时间）：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	C++	hard	80	2020-01-01 10:00:00
3	9003	算法	hard	80	2020-01-01 10:00:00
4	9004	PYTHON	medium	70	2020-01-01 10:00:00

试卷作答记录表 exam_record （ uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

id	uid	exam_id	start_time	submit_time	score
6	1003	9001	2020-01-02 12:01:01	2020-01-02 12:31:01	68
9	1001	9001	2020-01-02 10:01:01	2020-01-02 10:31:01	89
1	1001	9001	2020-01-01 09:01:01	2020-01-01 09:21:59	90
12	1002	9002	2021-05-05 18:01:01	(NULL)	(NULL)
3	1004	9002	2020-01-01 12:01:01	2020-01-01 12:11:01	60
2	1003	9002	2020-01-01 19:01:01	2020-01-01 19:30:01	75
7	1001	9002	2020-01-02 12:01:01	2020-01-02 12:43:01	81
10	1002	9002	2020-01-01 12:11:01	2020-01-01 12:31:01	83
4	1003	9002	2020-01-01 12:01:01	2020-01-01 12:41:01	90
5	1002	9002	2020-01-02 19:01:01	2020-01-02 19:32:00	90
11	1002	9004	2021-09-06 12:01:01	(NULL)	(NULL)
8	1001	9005	2020-01-02 12:11:01	(NULL)	(NULL)

在物理学及统计学数据计算时，有个概念叫 min-max 标准化，也被称为离差标准化，是对原始数据的线性变换，使结果值映射到[0 - 1]之间。

转换函数为：

请你将用户作答高难度试卷的得分在每份试卷作答记录内执行 min-max 归一化后缩放到[0,100]区间，并输出用户 ID、试卷 ID、归一化后分数平均值；最后按照试卷 ID 升序、归一化分数降序输出。（注：得分区间默认为[0,100]，如果某个试卷作答记录中只有一个得分，那么无需使用公式，归一化并缩放后分数仍为原分数）。

由示例数据结果输出如下：

uid	exam_id	avg_new_score
1001	9001	98
1003	9001	0
1002	9002	88
1003	9002	75
1001	9002	70
1004	9002	0

解释：高难度试卷有 9001、9002、9003；

作答了 9001 的记录有 3 条，分数分别为 68、89、90，按给定公式归一化后分数为：0、95、100，而后两个得分都是用户 1001 作答的，因此用户 1001 对试卷 9001 的新得分为(95+100)/2≈98（只保留整数部分），用户 1003 对于试卷 9001 的新得分为 0。最后结果按照试卷 ID 升序、归一化分数降序输出。

思路：

注意点：

将高难度的试卷，按每类试卷的得分，利用 max/min (col) over() 窗口函数求得各组内最大最小值，然后进行归一化公式计算，缩放区间为[0,100]，即 min_max*100
若某类试卷只有一个得分，则无需使用归一化公式，因只有一个分 max_score=min_score,score，公式后结果可能会变成 0。
最后结果按 uid、exam_id 分组求归一化后均值，score 为 NULL 的要过滤掉。

最后就是仔细看上面公式（说实话，这题看起来就很绕）

答案：

SELECT
  uid,
  exam_id,
  round(sum(min_max) / count(score), 0) AS avg_new_score
FROM
  (
    SELECT
      *,
      IF (
        max_score = min_score,
        score,
        (score - min_score) / (max_score - min_score) * 100
      ) AS min_max
    FROM
      (
        SELECT
          uid,
          a.exam_id,
          score,
          max(score) over (PARTITION BY a.exam_id) AS max_score,
          min(score) over (PARTITION BY a.exam_id) AS min_score
        FROM
          exam_record a
          LEFT JOIN examination_info b USING (exam_id)
        WHERE
          difficulty = 'hard'
      ) t
    WHERE
      score IS NOT NULL
  ) t1
GROUP BY
  uid,
  exam_id
ORDER BY
  exam_id ASC,
  avg_new_score DESC;

每份试卷每月作答数和截止当月的作答总数

描述:

现有试卷作答记录表 exam_record（uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-01 09:01:01	2020-01-01 09:21:59	90
2	1002	9001	2020-01-20 10:01:01	2020-01-20 10:10:01	89
3	1002	9001	2020-02-01 12:11:01	2020-02-01 12:31:01	83
4	1003	9001	2020-03-01 19:01:01	2020-03-01 19:30:01	75
5	1004	9001	2020-03-01 12:01:01	2020-03-01 12:11:01	60
6	1003	9001	2020-03-01 12:01:01	2020-03-01 12:41:01	90
7	1002	9001	2020-05-02 19:01:01	2020-05-02 19:32:00	90
8	1001	9002	2020-01-02 19:01:01	2020-01-02 19:59:01	69
9	1004	9002	2020-02-02 12:01:01	2020-02-02 12:20:01	99
10	1003	9002	2020-02-02 12:01:01	2020-02-02 12:31:01	68
11	1001	9002	2020-02-02 12:01:01	2020-02-02 12:43:01	81
12	1001	9002	2020-03-02 12:11:01	(NULL)	(NULL)

请输出每份试卷每月作答数和截止当月的作答总数。由示例数据结果输出如下：

exam_id	start_month	month_cnt	cum_exam_cnt
9001	202001	2	2
9001	202002	1	3
9001	202003	3	6
9001	202005	1	7
9002	202001	1	1
9002	202002	3	4
9002	202003	1	5

解释：试卷 9001 在 202001、202002、202003、202005 共 4 个月有被作答记录，每个月被作答数分别为 2、1、3、1，截止当月累积作答总数为 2、3、6、7。

思路：

这题就两个关键点：统计截止当月的作答总数、输出每份试卷每月作答数和截止当月的作答总数

这个是关键 **sum(count(*)) over(partition by exam_id order by date_format(start_time,'%Y%m'))**

答案：

SELECT exam_id,
       date_format(start_time, '%Y%m') AS start_month,
       count(*) AS month_cnt,
       sum(count(*)) OVER (PARTITION BY exam_id
                           ORDER BY date_format(start_time, '%Y%m')) AS cum_exam_cnt
FROM exam_record
GROUP BY exam_id,
         start_month

每月及截止当月的答题情况（较难）

描述：现有试卷作答记录表 exam_record （ uid 用户 ID, exam_id 试卷 ID, start_time 开始作答时间, submit_time 交卷时间, score 得分）：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2020-01-01 09:01:01	2020-01-01 09:21:59	90
2	1002	9001	2020-01-20 10:01:01	2020-01-20 10:10:01	89
3	1002	9001	2020-02-01 12:11:01	2020-02-01 12:31:01	83
4	1003	9001	2020-03-01 19:01:01	2020-03-01 19:30:01	75
5	1004	9001	2020-03-01 12:01:01	2020-03-01 12:11:01	60
6	1003	9001	2020-03-01 12:01:01	2020-03-01 12:41:01	90
7	1002	9001	2020-05-02 19:01:01	2020-05-02 19:32:00	90
8	1001	9002	2020-01-02 19:01:01	2020-01-02 19:59:01	69
9	1004	9002	2020-02-02 12:01:01	2020-02-02 12:20:01	99
10	1003	9002	2020-02-02 12:01:01	2020-02-02 12:31:01	68
11	1001	9002	2020-01-02 19:01:01	2020-02-02 12:43:01	81
12	1001	9002	2020-03-02 12:11:01	(NULL)	(NULL)

请输出自从有用户作答记录以来，每月的试卷作答记录中月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。结果按月份升序输出。

由示例数据结果输出如下：

start_month	mau	month_add_uv	max_month_add_uv	cum_sum_uv
202001	2	2	2	2
202002	4	2	2	4
202003	3	0	2	4
202005	1	0	2	4

month	1001	1002	1003	1004
202001	1	1
202002	1	1	1	1
202003	1		1	1
202005		1

由上述矩阵可以看出，2020 年 1 月有 2 个用户活跃（mau=2），当月新增用户数为 2；

2020 年 2 月有 4 个用户活跃，当月新增用户数为 2，最大单月新增用户数为 2，当前累积用户数为 4。

思路：

难点：

1.如何求每月新增用户

2.截至当月的答题情况

大致流程：

（1）统计每个人的首次登陆月份 min()

（2）统计每月的月活和新增用户数：先得到每个人的首次登陆月份，再对首次登陆月份分组求和是该月份的新增人数

（3）统计截止当月的单月最大新增用户数、截止当月的累积用户数，最终按照按月份升序输出

答案：

-- 截止当月的单月最大新增用户数、截止当月的累积用户数，按月份升序输出
SELECT
    start_month,
    mau,
    month_add_uv,
    max( month_add_uv ) over ( ORDER BY start_month ),
    sum( month_add_uv ) over ( ORDER BY start_month )
FROM
    (
    -- 统计每月的月活和新增用户数
    SELECT
        date_format( a.start_time, '%Y%m' ) AS start_month,
        count( DISTINCT a.uid ) AS mau,
        count( DISTINCT b.uid ) AS month_add_uv
    FROM
        exam_record a
        LEFT JOIN (
         -- 统计每个人的首次登陆月份
        SELECT uid, min( date_format( start_time, '%Y%m' )) AS first_month FROM exam_record GROUP BY uid ) b ON date_format( a.start_time, '%Y%m' ) = b.first_month
    GROUP BY
        start_month
    ) main
ORDER BY
    start_month

较难或者困难的题目可以根据自身实际情况和面试需要来决定是否要跳过。

分享到QQ

分享到微博