如何简化多选查询以提高执行速度?

mysql sql database

55 观看

1回复

602 作者的声誉

此处的SQLFiddle:http ://sqlfiddle.com/#!9/b88d1a/1/0 ---注意,由于大约8000个字符的限制,我无法将足够多的插入内容加载到SQLFiddle中,并且在没有最少数据量的情况下,查询可以跑。因此,SQL插入位于以下pastebin文件中:https ://pastebin.com/MfhJ0Svc- 我无法将320条插入行粘贴到Fiddle或抱歉。

我有一个表,然后有一个名为Review的视图,该视图只包含一个只有唯一用户名的列。该表称为RebasedQuestions,目前包含约40000条记录。

该表用于计算一组人对人所做的评论。

该查询需要生成一个最终表,该表以百分比形式提供主管,所有者,同级和下属的值,然后以6中的值形式给出(复习问题选项的范围为1到6)。每个评论类型的权重为:

Supervisor: 30%
Own: 0%
Peer: 40%
Subordinate: 30%

在此处输入图片说明

这是该视图的DDL:

CREATE VIEW Reviewed AS
  SELECT DISTINCT `t1`.`Reviewed` AS `Reviewed`
  FROM `edsdb`.`RebasedQuestions` `t1`
  ORDER BY `t1`.`Reviewed`;

和主表的DDL:

create table RebasedQuestions
(
    Reviewed varchar(50) null,
    ReviewType varchar(20) null,
    BU int null,
    RebasedValue double null
)
;

这是我的查询,但是我认为它还远没有优化,因此请启发我如何加快它的速度,尤其是当主表每次提交新的评论时,它会以20行的速度增长:

select DISTINCT t1.Reviewed, t2.BU,
  (SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Supervisor') AS Supervisor,
  (((SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Supervisor')/100) * 6) as Sup6,
  (SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Own') AS Own,
  (((SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Own')/100) * 6) as Own6,
  (SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Peer') AS Peer,
  (((SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Peer')/100) * 6) as Peer6,
  (SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Subordinate') AS Subordinate,
  (((SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Subordinate')/100) * 6) as Sub6,
  (ifnull((((SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Supervisor')/100) * 6),6) * 0.3 +
  ifnull((((SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Peer')/100) * 6),6) * 0.4 +
  ifnull((((SELECT ((sum(t2.RebasedValue)/(count(t2.RebasedValue)/20))) from RebasedQuestions t2 WHERE t2.Reviewed = t1.Reviewed and t2.ReviewType = 'Subordinate')/100) * 6),6) * 0.3) as Totaled
from Reviewed t1  JOIN RebasedQuestions t2 on t1.Reviewed = t2.Reviewed

我什至不能将插入物粘贴在这里。

可以在以下pastebin链接上找到它们:https//pastebin.com/MfhJ0Svc

作者: Letholdrus 的来源 发布者: 2017 年 9 月 15 日

回应 1


1

3026 作者的声誉

决定

这是一个建议,您应该能够适应自己的需求。

SELECT 
innerQuery.Reviewed,
innerQuery.BU,
(CASE WHEN (innerQuery.supcount=0) then null else 
innerQuery.suptot/innerQuery.supcount end) as sup,
(CASE WHEN (innerQuery.supcount=0) then null else 
(6*innerQuery.suptot)/(100*innerQuery.supcount) end) as sup6
 ...
(CASE WHEN (innerQuery.supcount=0) then 1.8 else sup6*0.3 end)+ ... as totaled
from  
(SELECT 
Reviewed, 
BU,
(SUM(ReviewType='Supervisor') then RebasedValue else 0 end) as 
suptot
(COUNT(ReviewType='Supervisor') then RebasedValue else 0 end) as 
supcount,
...
FROM RebasedQuestions GROUP BY Reviewed, BU) innerQuery
作者: Taemyr 发布者: 2017 年 9 月 15 日
32x32