sql - 如何使用聚合函数改善慢速 sql 查询
问题描述
我想显示本会计年度客户注册的前十名客户、销售额、利润率。该查询大约需要 65 秒才能运行,并且不被接受 :-( 如您所见,我不擅长 sql,非常乐意帮助改进查询。
SELECT Top 10
AcTr.R3, Actor.Nm,
SUM(CASE WHEN AcTr.AcNo<='3999' THEN AcAm*-1 ELSE 0 END) AS Sales ,
SUM(AcAm*-1) AS TB
FROM AcTr, Actor
WHERE (Actor.CustNo = AcTr.R3) AND
(Actor.CustNo <> '0') AND
(Actor.CreDt >= '20180901') AND
(Actor.CreDt <= '20190430') AND
AcTr.AcYr = '2018' AND
AcTr.AcPr <= '8' AND
AcTr.AcNo>='3000' AND
AcTr.AcNo <= '4999'
GROUP BY AcTr.R3, Actor.Nm
ORDER BY Sales DESC
解决方案
欢迎来到社区。您有一个良好的开端,但将来,如果您可以提供(如评论)CREATE 表声明,以便用户了解实际数据类型,则会更有帮助。并不总是需要,但有帮助。
至于您的查询布局,更常见的是显示 JOIN 语法而不是 WHERE 显示表之间的关系,但这需要时间和实践。
索引有帮助,并且应该基于 WHERE/JOIN 标准和分组字段的组合。此外,如果字段是数字,则不要“引用”它们,只需保留为数字即可。例如,您的 AcYr、AcPr、AcNo。我认为出于会计目的,帐号实际上是一个字符串值与数字。
我建议在您的表上使用以下索引
Table Index
Actr ( AcYr, AcPr, AcNo, R3 )
Actor ( CustNo, CreDt )
Actr 表我首先有过滤条件,最后是 R3,以帮助优化 GROUP BY。按客户编号的 Actor 表,然后是 CreDt(创建日期??),它真的是一个字符串,还是一个日期字段?如果是这样,日期标准将类似于“2018-09-01”和“2019-04-30”
select TOP 10
Actor.Nm,
PreSum.Sales,
PreSm.TB
from
( select
R3,
SUM(CASE WHEN AcTr.AcNo <= '3999'
THEN AcAm * -1 ELSE 0 END) AS Sales,
SUM( AcAm * -1) AS TB
from
Actr
where
AcTr.AcYr = 2018
AND AcTr.AcPr <= 8
AND AcTr.AcNo >= '3000'
AND AcTr.AcNo <= '4999'
GROUP BY
AcTr.R3 ) PreSum
JOIN Actor
on PreSum.R3 = Actor.CustNo
AND Actor.CustNo <> 0
AND Actor.CreDt >= '20180901'
AND Actor.CreDt <= '20190430'
order by
Sales DESC
根据最新的查询/评论,希望按年份进行比较,并在给定时间段内摆脱前 10 名的表现。
select
Actor.Nm,
PreSum.Sales2018,
PreSum.Sales2019,
PreSum.TB2018,
PreSum.TB2019
from
( select
AcTr.R3,
SUM(CASE WHEN AcTr.AcYr = 2018
AND AcTr.AcNo <= '3999'
THEN AcAm * -1 ELSE 0 END) AS Sales2018,
SUM(CASE WHEN AcTr.AcYr = 2019 AND AcTr.AcNo <= '3999'
THEN AcAm * -1 ELSE 0 END) AS Sales2019,
SUM( CASE WHEN AcTr.AcYr = 2018
THEN AcAm * -1 else 0 end ) AS TB2018
SUM( CASE WHEN AcTr.AcYr = 2019
THEN AcAm * -1 else 0 end ) AS TB2019
from
Actr
where
AcTr.AcYr IN ( 2018, 2019 )
AND AcTr.AcPr <= 8
AND AcTr.AcNo >= '3000'
AND AcTr.AcNo <= '4999'
GROUP BY
AcTr.R3 ) PreSum
JOIN Actor
on PreSum.R3 = Actor.CustNo
AND Actor.CustNo <> 0
AND Actor.CreDt >= '20180901'
AND Actor.CreDt <= '20190430'
order by
Sales DESC
推荐阅读
- swift - 如何安全地重命名我们的文件以及对它的所有引用 - Xcode 10?
- python - Python for 循环没有通过第一次迭代传递变量
- flutter - Flutter Ble 广告
- sql - 如何删除窗口函数使用中的重复值?
- haskell - 如何在 Haskell 中修复“无法将预期的 IO a0 类型与 int 匹配”?
- amazon-web-services - Lambda 调用权限与 API Gateway 权限冲突
- python - 如何获得解压大型 bz2 文件所需的时间?
- zsh - 为用户输入命令
- c# - VS2017创建的iOS和Android项目中是否使用了C#?
- c# - 如何在 UILabel 中截断一些文本