时间戳

首页 > 解决方案 > 如何修复子查询运行时间过长？

sql - 如何修复子查询运行时间过长？

问题描述

从FDIC 的网站，我下载了 CSV 文件。

我使用银行名称“NAMEFULL”作为参数，然后将银行所在的所有其他银行的存款“DEPSUMBR”相加“MSABR”。

我通过构建子查询来处理此代码，但查询时间太长 - 我会让 Access 运行一个小时，但查询每次都会冻结。

SELECT max(NAMEFULL), sum(DEPSUMBR) AS Deposit FROM ALL_2018 WHERE MSABR IN (SELECT DISTINCT MSABR FROM ALL_2018 WHERE NAMEFULL = [Enter Bank Name] AND MSABR <> '0') ;

输出应显示共享 MSA 作为参数银行的其他银行的名称以及每个 MSA 中的存款总和。

下面是csv文件的截图

在此处输入图像描述

尝试显示哪些地区的市场份额排名前 5 位的 MSA：

RANK (*) Over (partition by tots.msabr, tots.namefull, order by percentmsashare desc) as Rank
(
SELECT  tots.msabr, tots.namefull, 100 * (tots.summsabra / tots.summsa) as percentmsashare  FROM
(
 SELECT msabra.msabr, msabra.namefull, msabra.summsabra, msa.summsa FROM
 (
  SELECT msabr, namefull, sum(depsumbr) as summsabra
  FROM all_2018 
  GROUP BY msabr, namefull
 ) msabra
 INNER JOIN
 (
  SELECT msabr, sum(depsumbr) as summsa
  FROM all_2018 
  GROUP BY msabr
 ) msa
 ON msabra.msabr = msa.msabr
) tots
INNER JOIN
(
  SELECT distinct msabr
  FROM ALL_2018 
  WHERE namefull = 'Regions bank' and msabr > '0'
) bnks 
ON tots.msabr = bnks.msabr
)

标签： sqlms-accesssubquery

解决方案

抱歉，这只是一个混乱的、无法解释的代码答案。如果我们使用内部联接而不是 IN 过滤到“仅在与所需银行相关的 msabr 列表中具有 msabr 的银行”，即使没有索引，它也可以在旧 ibm t60p 上的 access 2010 中运行几秒钟

SELECT * FROM
(
  SELECT msabr, namefull, sum(depsumbr) as sumdep 
  FROM all_2018 
  GROUP BY msabr, namefull
) sums
INNER JOIN
(
  SELECT distinct msabr
  FROM ALL_2018 
  WHERE namefull = 'Regions bank' and msabr > 0
) bnks 
ON sums.msabr = bnks.msabr

我不知道您是想要输出中的每个 msabr/分支配对的总和还是每个分支的总和。如果它只是在外部再次分支任一组（替换为select *）select nameful, sum(sumdep) ... group by namefull或尝试这种形式：

SELECT d.namefull, sum(d.depsumbr) as sumdep
FROM all_2018 d
INNER JOIN
(
  SELECT distinct msabr
  FROM ALL_2018 
  WHERE namefull = 'Regions bank' and msabr > 0
) bnks 
ON d.msabr = bnks.msabr
GROUP BY d.namefull

如果您想通过 msa 求和，请更改第一种形式的 sums 子查询中的分组，使其不提及分支。修改 msa 的第二种形式而不是分支将其从外部选择/组中交换出来

我不知道第二个查询是如何执行的，因为我是在手机上写的；它未经测试

这是一个查询，可以回答您在评论中的两个问题：

SELECT tots.msabr, tots.namefull, 100 * (tots.summsabra / tots.summsa) as percentmsashare  FROM
(
 SELECT msabra.msabr, msabra.namefull, msabra.summsabra, msa.summsa FROM
 (
  SELECT msabr, namefull, sum(depsumbr) as summsabra
  FROM all_2018 
  GROUP BY msabr, namefull
 ) msabra

 INNER JOIN
 (
  SELECT msabr, sum(depsumbr) as summsa
  FROM all_2018 
  GROUP BY msabr
 ) msa
 ON msabra.msabr = msa.msabr
) tots
INNER JOIN
(
  SELECT distinct msabr
  FROM ALL_2018 
  WHERE namefull = 'Regions bank' and msabr > 0
) bnks 
ON tots.msabr = bnks.msabr

ORDER BY tots.msabr ASC, tots.summsabra / tots.summsa DESC

它以 MSA 中存款总额的市场份额递减的方式显示 MSA 中的所有银行。为此，它将数据分组在两个不同的级别：per-bank-per-msa 和 per-msa，然后百分比由 (perbankmsa / permsa) 给出

看起来像：

在此处输入图像描述

我可以想出一些方法来去除前 5 名之后的任何内容，但是这个查询最好在更强大的数据库中完成。现在，我将使用前端忽略此列表中每个 msa 的前 5 行之后的任何行 - 处理问题 2

问题 1，Regions 银行的市场份额会更简单：删除INNER JOIN(...)bnks ON ...并在 ORDER BY 上方添加一个 WHERE 子句，也就是说WHERE tots.namefull = 'Regions bank'- 整个过程访问因此是：计算所有 msa 的总和，计算所有银行的总和-msa，连接在一起，仅限于那些提到“地区银行”的行。因为每个 msa 的每家银行的市场份额是在过滤到仅“区域银行”之前计算的，所以我们仅在该 msa 中获得了“区域银行”的真实百分比。如果我们在计算总和之前过滤到“区域银行”，那么“区域银行”将拥有每个 MSA 的 100%，因为我们在分组/求和之前过滤掉了所有竞争行

SELECT tots.msabr, tots.namefull, 100 * (tots.summsabra / tots.summsa) as percentmsashare  FROM
(
 SELECT msabra.msabr, msabra.namefull, msabra.summsabra, msa.summsa FROM
 (
  SELECT msabr, namefull, sum(depsumbr) as summsabra
  FROM all_2018 
  GROUP BY msabr, namefull
 ) msabra

 INNER JOIN
 (
  SELECT msabr, sum(depsumbr) as summsa
  FROM all_2018 
  GROUP BY msabr
 ) msa
 ON msabra.msabr = msa.msabr
) tots
WHERE tots.namefull = 'Regions bank'

从技术上讲，我们不需要外部选择；where 子句和求和可以被推入 tots 和 tots 被取消。它只是以这种方式结束，作为对需要连接 3 个表的早期查询的修改（并且访问一次只能连接两个 - 再次，选择是多余的，我本可以将连接括起来 -select * from (a join b) join c与select * from (select * from a join b) join c- 我倾向于做后者，因为我的大部分工作都在不接受前者的数据库中）

好的，所以我在评论中谈到了笛卡尔积，以给出排名。这很讨厌，但它是这样工作的。假设我们有以下田径成绩：

Name, Event, Seconds
Usain, 100m, 9.0
Jonno, 100m, 10.1
Timmy, 100m, 11.3
Roger, 400m, 41.3
Salva, 400m, 42.1
Erdoh, 400m, 44.0

如果我们运行这样的查询：

SELECT * FROM
  scores s1
  INNER JOIN scores s2 ON s1.event = s2.event and s1.time <= s2.time

然后<=将导致行成倍增加：

s1Name, s1Event, s1Seconds, s2Name, s2Event, s2Seconds
Usain, 100m, 9.0, Usain, 100m, 9.0
Jonno, 100m, 10.1, Usain, 100m, 9.0
Jonno, 100m, 10.1, Jonno, 100m, 10.1
Timmy, 100m, 11.3, Usain, 100m, 9.0
Timmy, 100m, 11.3, Jonno, 100m, 10.1
Timmy, 100m, 11.3, Timmy, 100m, 11.3

只有 1 个 usain 行；只有一个人等于或快于usain，那就是usain。有两个 jonno 行；usain 更快，所以他的时间<少于。乔诺等于乔诺。Usain 和 Jonno 都比 Timmy 快，所以我们最终得到一个 Timmy 行与 usain 匹配，timmy 与 jonno 匹配，timmy 与自己匹配

因此，您会注意到 Usain 有 1 排，他在比赛中排名第一。Jonn 有 2 排，排在第 2 位。Timmy 有 3 排，排在第三位。因此，如果我们仅对 s1 数据进行分组并对其进行计数，我们将得到一个排名：

SELECT s1.Name, s1.Event, s1.Time, count(*) as ranking 
FROM
  scores s1
  INNER JOIN scores s2 ON s1.event = s2.event and s1.time <= s2.time
GROUP BY
  s1.Name, s1.Event, s1.Time

s1Name, s1Event, s1Seconds, ranking
Usain, 100m,  9.0, 1
Jonno, 100m, 10.1, 2
Timmy, 100m, 11.3, 3

现在，您正在计算市场份额等总体百分比的主要查询已经很大而且很难看；我不会通过将它全部粘贴两次使它变得更大和更丑陋，以便它可以连接到自身。也许制作一个视图（CREATE VIEW AS [big ugly select statement]）然后编写另一个查询将视图连接到自身

请记住：您可能需要在过滤到“仅区域银行”之前执行此排名技巧，因为您想要对所有银行进行排名，然后仅选择“区域银行”为名称且<=5 为排名的银行

推荐阅读