PostgreSQL中仅显示首行COUNT值其余为NULL的实现求助

近期我希望在PostgreSQL中实现仅显示首行的COUNT统计值，其余行对应位置显示为NULL，但多次尝试均未成功。

原SQL代码 #

with firstFunc as(
select count(function_name), function_name,function_id from pivot_da_middleware.get_de_kpis_full() group by function_name,function_id
),
secondFunc as(
select fl.function_name, gdkf.subgroup_name,gdkf.subgroup_id,fl.function_id from firstFunc fl
inner join pivot_da_middleware.get_de_kpis_full() gdkf 
on fl.function_id = gdkf.function_id
--where fl.function_name = 'Operations'
order by fl.function_name
),
thirdFunc as(
    -- second 
    select count(secondFunc.subgroup_name), secondFunc.subgroup_name,secondFunc.subgroup_id,function_id from secondFunc 
    group by secondFunc.subgroup_name,secondFunc.subgroup_id,secondFunc.function_id order by secondFunc.function_id
),
fourthFunc as(
select count(function_id),function_id from thirdFunc tf group by function_id
)

select * from fourthFunc ff right join thirdFunc tf2 on ff.function_id = tf2.function_id

当前结果 #

同一function_id分组下的每一行，count统计列都会重复显示该分组的统计数值。

预期结果 #

同一function_id分组下仅首行显示count统计值，其余行对应位置为NULL。

解决方案 #

利用PostgreSQL的窗口函数row_number()对每个function_id分组内的行进行编号，通过CASE WHEN判断仅保留首行的统计值，其余行设为NULL。修改后的SQL如下：

with firstFunc as(
select count(function_name), function_name,function_id from pivot_da_middleware.get_de_kpis_full() group by function_name,function_id
),
secondFunc as(
select fl.function_name, gdkf.subgroup_name,gdkf.subgroup_id,fl.function_id from firstFunc fl
inner join pivot_da_middleware.get_de_kpis_full() gdkf 
on fl.function_id = gdkf.function_id
order by fl.function_name
),
thirdFunc as(
    select count(subgroup_name) as subgroup_count, subgroup_name, subgroup_id, function_id 
    from secondFunc 
    group by subgroup_name, subgroup_id, function_id 
    order by function_id
),
fourthFunc as(
select count(function_id) as func_total_count, function_id 
from thirdFunc tf 
group by function_id
)
select 
    -- 仅分组内首行显示统计值，其余为NULL
    case when row_number() over(partition by tf2.function_id order by tf2.subgroup_id) = 1 then ff.func_total_count else null end as func_total_count,
    tf2.subgroup_count,
    tf2.subgroup_name,
    tf2.subgroup_id,
    tf2.function_id
from fourthFunc ff 
right join thirdFunc tf2 on ff.function_id = tf2.function_id

关键说明 #

• row_number() over(partition by tf2.function_id order by tf2.subgroup_id)：按function_id分组，每组内按subgroup_id排序并给行编号，首行编号为1。
• CASE WHEN判断：当行编号为1时显示fourthFunc中的统计值，否则返回NULL，实现仅首行显示统计值的需求。

内容的提问来源于stack exchange，提问作者Oscar Chua Wei Wen