在窗口函数中使用平均值和首先计算平均值和使用连接之间的性能有差异吗

zf2sa74q  于 2022-10-22  发布在  Mysql
关注(0)|答案(1)|浏览(124)

我正在使用MySQL 8.0 Table NameL:sc(score)
列:student_id、course_id、score
一些虚拟数据:

create table sc(SId varchar(10),CId varchar(10),score decimal(18,1));
insert into sc values('01' , '01' , 80);
insert into sc values('01' , '02' , 90);
insert into sc values('01' , '03' , 99);
insert into sc values('02' , '01' , 70);
insert into sc values('02' , '02' , 60);
insert into sc values('02' , '03' , 80);
insert into sc values('03' , '01' , 80);
insert into sc values('03' , '02' , 80);
insert into sc values('03' , '03' , 80);
insert into sc values('04' , '01' , 50);
insert into sc values('04' , '02' , 30);
insert into sc values('04' , '03' , 20);
insert into sc values('05' , '01' , 76);
insert into sc values('05' , '02' , 87);
insert into sc values('06' , '01' , 31);
insert into sc values('06' , '03' , 34);
insert into sc values('07' , '02' , 89);
insert into sc values('07' , '03' , 98);

问题是:输出所有分数和每个学生的平均分数,并按平均分数的降序排列结果
我想出了两个解决方案:

-- Solution 1
SELECT
  sc.*,
  AVG(score) OVER (PARTITION BY sid) AS avg_score
FROM sc
ORDER BY avg_score DESC
-- Solution 2
select *  from sc 
left join (
    select sid,avg(score) as avscore from sc 
    group by sid
    )r 
on sc.sid = r.sid
order by avscore desc;

但是,如果表真的很大,这两种解决方案的效率性能是否存在差异?

非常感谢。

k2arahey

k2arahey1#

如果将此项添加到表中,则解决方案2可能会发光:

INDEX(sid, score)

我用你的数据和查询做了一个小测试,但结果不确定。(我检查了会话状态“Handler%”值。)
我更喜欢这个2:
选择*FROM(按sid从sc组中选择sid,avg(score)作为avscore)as r按avscore desc加入sc USING(sid)顺序;
建议你尝试一百万行。

相关问题