postgres 視窗函式：使用 CASE 按年齡組分區

我正在使用 postgres 視窗函式來獲取參加比賽的使用者列表以及基於許多列的相應排名，到目前為止一切都很好….現在我需要根據“年齡組”獲得排名許多預定義的類別（例如 0-15 歲、15 歲以上等），其中年齡是根據時間戳列“dob”計算的。

我很確定我可以使用 CASE 語句執行此操作，但我無法正確建構查詢，我已經編寫了下面的查詢，該查詢在單獨的 FROM 子查詢中定義了“年齡組”，因此我可以在 WINDOW 定義中引用它，但這不起作用，因為兩個 FROM 是獨立的

SELECT 
cu.* as compUser, 
cus.time_in_seconds as timeInSeconds,
rank() OVER allTimes as overallRank,
u.gender as gender,
rank() OVER genderTimes as genderRank,
ageGroup as ageGroup,
rank() OVER ageGroupTimes as ageGroupRank,
FROM 
competition_users cu, 
(SELECT CASE WHEN usr.dob>'2000-01-01' AND usr.dob<now() THEN '0-15' ELSE '15+' END FROM users usr WHERE usr.user_id = cu.user_id) ageGroup
LEFT JOIN users u ON cu.user_id = u.user_id
FULL JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = false 
AND cu.competition_id = :compId
WINDOW 
allTimes AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC),
genderTimes AS (PARTITION BY u.gender ORDER BY cus.time_in_seconds ASC),
ageGroupTimes AS (PARTITION BY ageGroup ORDER BY cus.time_in_seconds ASC)

（以上產生：錯誤：對錶“cu”的 FROM 子句條目的引用無效…提示：表“cu”有一個條目，但不能從查詢的這一部分引用。）

有人可以指出我正確的方向嗎？（上面的範例查詢是簡化的，有很多年齡類別）

LATERAL您可以為此使用連接。並且不需要加入users表格兩次：

FROM 
   competition_users AS cu
 LEFT JOIN users AS u
   ON cu.user_id = u.user_id
 LEFT JOIN LATERAL
   ( SELECT CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
                THEN '0-15' 
                ELSE '15+' 
            END AS ageGroup
   ) AS age
   ON TRUE
 LEFT JOIN current_competition_sessions AS ccs 
   ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = FALSE 
 AND cu.competition_id = :compId

然後WINDOW將保持（幾乎）不變：

WINDOW
   ageGroupTimes AS (PARTITION BY age.ageGroup ORDER BY cus.time_in_seconds ASC)

---

或者，可以使用簡單的 CTE 或帶有法線的派生表LEFT JOIN，而無需 (9.3+ available only) LATERAL。使用以下內容，您只需要稍微修改您的上層SELECT和WINDOW（主要刪除表別名）：

FROM
 ( SELECT 
       cu.*, 
       cus.time_in_seconds,
       u.gender,
       CASE WHEN u.dob > '2000-01-01' AND u.dob < now() 
           THEN '0-15' 
           ELSE '15+' 
       END AS ageGroup
   FROM 
       competition_users AS cu
     LEFT JOIN users AS u
       ON cu.user_id = u.user_id
     LEFT JOIN current_competition_sessions AS ccs 
       ON cu.competition_user_id = ccs.competition_user_id 
   WHERE cu.left_competition = FALSE 
     AND cu.competition_id = :compId
 ) AS d

引用自：https://dba.stackexchange.com/questions/121557