我有一個相當復雜的嵌套查詢，其結構如下：
select descr, sum(percentage) as perc from
( COMPLEX QUERY HERE)
as A group by descr order by perc desc
這個查詢的結果是這樣的：
descr perc
------------------------------------
bottom (1 * 18 cm2) 2.77
totalarea (1 * 70 cm2) 2.49
innerwalls (1 * 22 cm2) 2.37
bottom (2 * 12 cm2) 1.55
innerwalls (2 * 17 cm2) 1.51
risperror 0.29
totalnumberdefects (1 * 30 cm2) 0.00
clipserror 0.00
outerwalls (1 * 18 cm2) 0.00
outerwalls (2 * 16 cm2) 0.00
現在我需要添加一列，其中包含 perc 的 Sum()，因此在這種情況下，我希望結果如下：
descr perc sum
------------------------------------------------
bottom (1 * 18 cm2) 2.77 10.98
totalarea (1 * 70 cm2) 2.49 10.98
innerwalls (1 * 22 cm2) 2.37 10.98
bottom (2 * 12 cm2) 1.55 10.98
innerwalls (2 * 17 cm2) 1.51 10.98
risperror 0.29 10.98
totalnumberdefects (1 * 30 cm2) 0.00 10.98
clipserror 0.00 10.98
outerwalls (1 * 18 cm2) 0.00 10.98
outerwalls (2 * 16 cm2) 0.00 10.98
鑒于查詢非常復雜且緩慢，我想通過從上述查詢的結果開始添加上述列，而不必再次為結果呼叫相同的查詢。
如何才能做到這一點？
uj5u.com熱心網友回復：
您可以對結果使用 SUM 視窗函式：
WITH myquery AS (
select descr, sum(percentage) as perc from
( COMPLEX QUERY HERE)
as A group by descr order by perc desc
)
SELECT *, SUM(perc) OVER () AS "sum"
FROM myquery
uj5u.com熱心網友回復：
使用sum()與作為視窗函式：
SELECT *,SUM(perc) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM (your_complex_sql_query) j
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING在這種情況下，框架是可選的——因為框架沒有順序或磁區——但讓事情變得明確被認為是一種很好的做法。所以你也可以只使用OVER ()
SELECT *,SUM(perc) OVER ()
FROM (your_complex_sql_query) j
演示： db<>fiddle
uj5u.com熱心網友回復：
不使用分組依據：
我曾經window function sum()通過 descr 獲得總 perc 和總 perc。
SELECT DISTINCT descr,
SUM(perc) OVER (PARTITION BY descr) AS perc,
SUM(perc) OVER () AS "sum"
FROM (COMPLEX QUERY HERE) AS sq;
或使用CTE：
WITH cte AS (COMPLEX QUERY HERE)
SELECT DISTINCT descr,
SUM(perc) OVER (PARTITION BY descr) AS perc,
SUM(perc) OVER () AS "sum"
FROM cte;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/341480.html
標籤：sql PostgreSQL 和