請參考以下查詢
SELECT sum(sales) AS "Sales",
sum(discount) AS "discount",
year
FROM Sales_tbl
WHERE Group by year
現在我還想為 AVG(sales) 顯示一列，該列具有相同的值并基于總銷售額列
輸出
請指教
uj5u.com熱心網友回復：
使用AVG()作為視窗函式：
WITH t AS (
SELECT
SUM(sales) AS sales, SUM(discount) AS discount, year
FROM tbl_sales
GROUP BY year
)
SELECT *,AVG(sales) OVER w_total
FROM t
WINDOW w_total AS (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY year;
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING在這種情況下，框架幾乎是可選的，但在視窗函式中盡可能明確被認為是一種很好的做法。因此，您還可以像這樣撰寫查詢：
WITH t AS (
SELECT
SUM(sales) AS sales, SUM(discount) AS discount, year
FROM tbl_sales
GROUP BY year
)
SELECT *,AVG(sales) OVER ()
FROM t
ORDER BY year;
演示： db<>fiddle
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/346772.html
標籤：sql PostgreSQL postgresql-9.5
下一篇：Oracle自帶聚合函式