我有一張如下所示的表格：
| 店鋪 | 年 | 地區 | 浪費 | 平均浪費（年、地區） | 電梯 | Column_I_want_To_Calculate（應用 case when 陳述句） CASE WHEN Lift > Avg(Lift) OVER (PARTITION BYEAR, REGION) THEN 1 ELSE 0 END |
|---|---|---|---|---|---|---|
| 一個 | 2021 | 加州 | 10 | 15 =>(10 20)/2 | 0.67 => 10/15 | 0.67 < (0.67 1.34)/2 = 1.005 那么 0 |
| b | 2021 | 加州 | 20 | 15=> (10 20)/2 | 1.34 => 20/15 | 1.34 > (0.67 1.34)/2 = 1.005 那么 1 |
| C | 2021 | 佛羅里達州 | 8 | 8 => 8/1 | 8/8 | 8 = 8 然后 0 |
| d | 2020 | 洛杉磯 | 25 | 22 => (25 19)/2 | 0.88 => 25/22 | 0.88 > (0.88 0.87)/2 = 0.875 那么 1 |
| e | 2020 | 洛杉磯 | 19 | 22 => (25 19)/2 | 0.87 => 19/22 | 0.87 < (0.88 0.87)/2 = 0.875 那么 0 |
| F | 2019 | 紐約 | 35 | 35 | 35/35 | 35 = 35 然后 0 |
到目前為止，我已經計算了Shop、Year、Region、Waste、Avg Waste (Year, Region)、Lift列。我想計算標記為Column_I_want_To_Calculate的那個。
簡而言之，它計算每個地區和年份的平均提升量，并將 Shops'Lift與同一Region和中所有商店的平均提升量進行比較Year。然后在大于陳述句的情況下分配值 1 或 0。
到目前為止，我已經嘗試過（PostgreSQL），
SELECT shop
,year
,region
,waste
,AVG(waste) over (partition by year, region) as "Avg Waste (Year,Region)"
,waste/avg(waste) over (partition by year, region) AS Lift,
,CASE WHEN waste/avg(waste) over (partition by year, region) >
(SELECT tab2.avg_lift
FROM (
SELECT tab1.year, tab1.region, AVG(tab1.lift) OVER (PARTITION BY tab1.year, tab1.region) avg_lift
FROM (
SELECT year, region, waste/ avg(waste) over (partition by year, region) AS lift
FROM main_table
GROUP BY year,region,waste
ORDER BY lift DESC
) tab1
GROUP BY tab1.year, tab1.region, tab1.lift
) tab2
) THEN 1 ELSE 0 END AS "Column_I_want_To_Calculate"
FROM main_table
GROUP BY shop,
year,
nomos,
waste
;
但是，上面的代碼拋出例外
postgresql 錯誤：用作運算式的子查詢回傳多行
uj5u.com熱心網友回復：
這個根據您的輸入回傳所需的輸出：
SELECT
region
, shop
, waste
, round(AVG(waste) OVER w,2) AS avg_waste
, round(waste / AVG(waste) OVER w,2) AS lift
, CASE
WHEN waste > AVG(waste) OVER w THEN 1
ELSE 0
END AS above_average
FROM i
WINDOW w AS (PARTITION BY year, region)
ORDER BY
1,2,3;
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/498459.html
標籤：PostgreSQL 窗函数 嵌套选择