主页 / dba / 问题 / 121557
Accepted
DaveB
Asked: 2015-11-20 03:04:37 +0800 CST

postgres 窗口函数：使用 CASE 按年龄组分区

  • 772

我正在使用 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”有一个条目，但不能从查询的这一部分引用。）

有人可以指出我正确的方向吗？（上面的示例查询是简化的，有很多年龄类别）

postgresql postgresql-9.4

1 个回答

  1. Best Answer

    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
    • 4

相关问题