如何简化多选查询以加快执行速度?
SQLFiddle here: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
我甚至不能粘贴在这里插入体内。
他们可以在这个引擎收录链接上找到:https://pastebin.com/MfhJ0Svc
下面是你应该能够适应你的需要的建议。
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
在手机atm上,谢谢你的回答。将尽快测试并回复;) – georgelappies
您还可以提供一些样本和所需的数据 –
请提供您期望的样本输入和输出。即使没有任何调整,如果没有比您写的更有效的查询,我会感到震惊。 –
作为@MKhalidJunaid说,请它会很好,如果使用这个工具 sqlfiddle.com – ARr0w