我的查询如下。 我可以按窗口函数而不是逐组使用分区吗?我必须更改partnername和合作伙伴编号。同样,基于伙伴编号划分的第一组也在改变。我的诱人包含大约2400万张记录。我正在研究此查询的性能。目前,我的存储程序需要大约1个小时才能执行。
INSERT INTO #FinalResultTable
(
[F1],
[F2],
[F3],
[F4],
[Partner #],
[Partner Name],
[F5],
[F6],
[F7],
[F8],
[Partner Amount (rounded)]
,[Entity Name]
,[Investment Number]
)
SELECT
[F1],
[F2],
[F3],
[F4],
-2 as [Partner #],
'Work Paper Total'
AS [PartnerName], -- VARCHAR
[F5],
[F6],
[F7],
[F8],
MAX([WorkPaperTotal])
, [Entity Name]
,[Investment Number]
FROM #FinalResultTable
WHERE [Partner #] > 0
GROUP BY
[F1],
[F2],
[F3],
[F4],
[F5],
[F6],
[F7],
[F8],
[Entity Name],
[Investment Number]
union all
SELECT
[F1],
[F2],
[F3],
[F4],
-3 as [Partner #],
'Partner Total'
AS [PartnerName], -- VARCHAR
[F5],
[F6],
[F9],
[F10],
MAX([WorkPaperTotal]) -SUM([Partner Amount (rounded)])
, [Entity Name]
,[Investment Number]
FROM #FinalResultTable
WHERE [Partner #] > 0
GROUP BY
[F1],
[F2],
[F3],
[F4],
[F5],
[F6],
[F9],
[F10],
[Entity Name],
[Investment Number]
请提出您的建议以提高此查询的性能。
我正在简化请求。您有这样的数据:
F1 |F7 |F9 |Workpertotal |合作伙伴--- ---- ---- --------------------- ---------------------------------------------------------------------------------------------------------------------1 |1 |1 |1000 |101 |1 |2 |2000 |201 |2 |1 |3000 |301 |2 |2 |4000 |402 |1 |1 |5000 |50
您想要
的结果select f1, f7 as f, max(workpapertotal) as result, -2 as partner
from mytable
group by f1, f7
union all
select f1, f9 as f, max(workpapertotal) - sum(partneramount) as result, -3 as partner
from mytable
group by f1, f9;
第一部分给您:
F1 |f |结果|伙伴--- --- --------- -------------1 |1 |2000 |-21 |2 |4000 |-22 |1 |5000 |-2
第二部分给了您:
F1 |f |结果|伙伴--- --- --------- -------------1 |1 |2960 |-31 |2 |3940 |-32 |1 |4950 |-3
最终结果
F1 |f |结果|伙伴--- --- --------- -------------1 |1 |2000 |-21 |1 |2960 |-31 |2 |4000 |-21 |2 |3940 |-32 |1 |5000 |-22 |1 |4950 |-3
(其中f表示f7 f7 for for for for for partner -2和f9的伙伴-3)。从桌子上的五行中,尽管聚集了六行,您仍会获得六行。因此,您已经采取了两个查询工会的痛苦已经是唯一可行的方法。我想您的WERY子句(WHERE [Partner #] > 0
)不会排除太多行,因此必须读取和分类表的大部分数据,然后将其分组才能进行汇总。这甚至必须发生两次。这需要时间。您对此无能为力。购买硬件是我的第一个想法。
您可以尝试以下索引以提供预分类数据。这是对查询可能会或可能不接受的DBMS的要约。
CREATE INDEX idx1
ON #FinalResultTable(f1,f2,f3,f4,f5,f6,f7,f8,[Entity Name],[Investment Number])
WHERE [Partner #] > 0;
CREATE INDEX idx2
ON #FinalResultTable(f1,f2,f3,f4,f5,f6,f9,f10,[Entity Name],[Investment Number])
WHERE [Partner #] > 0;