sql窗口函数之聚合窗口函数(count,max,min,sum)

题1

https://www.nowcoder.com/practice/2b7acdc7d1b9435bac377c1dcb3085d6?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

SELECT 
	uid,
	exam_id,
	ROUND(avg(if(score_cnt>1,(score-min_score)/(max_score-min_score)*100,score)),0) avg_new_score
from (
	SELECT 
		uid,
		exam_id,
		difficulty,
		score,
		min(score) over(PARTITION by exam_id) min_score,
		max(score) over(PARTITION by exam_id) max_score,
		COUNT(score) over(PARTITION by exam_id) score_cnt
	from exam_record
	join examination_info
	using(exam_id)
	where difficulty = 'hard'
)t
where score is not null
group by uid,exam_id
order by exam_id asc,avg_new_score desc;

select uid,exam_id,round(avg(new_score),0) "avg_new_score"
from (
    select 
        er.uid,er.exam_id,
        if(a.min_score = a.max_score , a.min_score ,
             ((er.score - a.min_score) / (a.max_score - a.min_score)) * 100) "new_score"
    from (
        select ei.exam_id, min(score) "min_score", max(score) "max_score"
        from exam_record er inner join 
        examination_info ei
        on er.exam_id = ei.exam_id
        where ei.difficulty = "hard"
        group by ei.exam_id
    ) as a 
    inner join exam_record er
    on a.exam_id = er.exam_id
    and er.submit_time is not null
    ) as b
group by uid,exam_id
order by exam_id,avg_new_score desc

下面方法先用聚合加窗口函数算出归一值，然后再取平均值。

select 
    uid, exam_id, cast(avg(standard) as unsigned) as avg_new_score 
from
    (select uid, exam_id, 
            if(count(score) over (partition by exam_id) = 1, score, 
            100*(score - min(score) over (partition by exam_id))/ (max(score) over (partition by exam_id) - min(score) over (partition by exam_id))) as standard
    from examination_info inner join exam_record using(exam_id)
    where difficulty = "hard" and score is not null) as temp
group by uid, exam_id
order by exam_id, avg_new_score desc

SELECT
    *
FROM
(
    SELECT
        exam_record.uid,
        exam_record.exam_id,
        CASE
            WHEN _max = _min THEN ROUND(AVG(exam_record.score))
            ELSE ROUND(AVG((exam_record.score - _min) * 100 / (_max - _min)))
        END AS score
    FROM
        exam_record
    LEFT JOIN
    (
        SELECT
            exam_id,
            MIN(score) AS _min,
            MAX(score) AS _max
        FROM
            exam_record
        WHERE
            start_time IS NOT NULL
            AND submit_time IS NOT NULL
        GROUP BY
            exam_id
    ) AS norm_info
    ON exam_record.exam_id = norm_info.exam_id
    LEFT JOIN
        examination_info
    ON exam_record.exam_id = examination_info.exam_id
    WHERE
        exam_record.score IS NOT NULL
        AND examination_info.difficulty = "hard"
    GROUP BY
        exam_record.uid,
        exam_record.exam_id
) AS result_without_order
ORDER BY
    exam_id ASC,
    score DESC

题2

https://www.nowcoder.com/practice/5f1cbe74c682485aa73e4c2b30f04a62?tpId=240&tags=&title=&difficulty=0&judgeStatus=0&rp=0

select DISTINCT
    exam_id,
    DATE_FORMAT(start_time, '%Y%m') as start_month,
    count(start_time) over(partition by exam_id, DATE_FORMAT(start_time, '%Y%m')) as month_cnt,
    count(start_time) over(partition by exam_id order by DATE_FORMAT(start_time, '%Y%m')) as cum_exam_cnt
FROM exam_record;

select 
    *,
    sum(month_cnt) over (partition by exam_id order by start_month) as cum_exam_cnt
FROM 
    (select 
         exam_id,
         date_format(start_time,"%Y%m") as start_month,
         count(1) as month_cnt
     from exam_record
     group by exam_id,start_month
    ) as t

SELECT 
    exam_id, DATE_FORMAT(start_time,'%Y%m') start_month, 
    COUNT(DATE_FORMAT(start_time,'%Y%m')) month_cnt, 
    SUM(COUNT(DATE_FORMAT(start_time,'%Y%m'))) over (partition by exam_id order by DATE_FORMAT(start_time,'%Y%m'))
FROM exam_record
GROUP BY exam_id, start_month
ORDER BY exam_id, start_month; # 这一行可有可无