sql - PostgreSQL - 将窗口函数分配给别名

Question

我正在尝试将 ROW_NUMBER()... 设置为别名，以便我可以在 OFFSET 中引用它。例如OFFSET some_alias - 1。我需要从更大的查询中获取包含 ROW_NUMBER() 的一行。这是我的工作代码(获得正确的 ROW_NUMBER()，但没有被正确的数量抵消):

WITH FirstQuery AS (
    SELECT "RepInitials", COUNT("OrderStatus"), ROW_NUMBER()OVER(ORDER BY COUNT("OrderStatus") DESC)
    FROM "tblBulkSalesQuery"
    WHERE "OrderStatus" = 'CMC'
    GROUP BY "RepInitials"
)
SELECT "RepInitials", COUNT("OrderStatus"), ROW_NUMBER()OVER(ORDER BY COUNT("OrderStatus") DESC)
    FROM "tblBulkSalesQuery"
    WHERE "OrderStatus" = 'CMC'
    GROUP BY "RepInitials"
    LIMIT 1
    OFFSET 1;

Answer 1

select *
from (
    SELECT "RepInitials", 
           COUNT("OrderStatus") as order_status_count, 
           ROW_NUMBER() OVER (ORDER BY COUNT("OrderStatus") DESC) as rn
    FROM "tblBulkSalesQuery"
    WHERE "OrderStatus" = 'CMC'
    GROUP BY "RepInitials"
) as t
where rn = 1

编辑:

t 是嵌套选择(“派生表”)的别名。 PostgreSQL 要求每个派生表都有自己的“名称”，这只能通过分配别名来完成。

这几乎与:

with t as (
  ... here goes the real select ...
)
select *
from t 
where rn = 1;