这是我在select语句中的sql示例。从oracle转换为postgres,需要一种简单的方法来重新实现与postgres中的分区不同的oracle计数。
, count(distinct on pm.mobseg_state) over (partition by pm.trans_id) as mob_segments_count
, count(distinct on pm.country_reg_region_cd) over (partition by pm.trans_id) as countries_count
Postgres不count(distinct)
直接支持。但是您可以使用子查询来实现它:
select . . .,
sum( (seqnum_tm = 1)::int) as mob_segments_count ,
sum( (seqnum_tr = 1)::int) as countries_count
from (select . . .,
row_number() over (partition by pm.trans_id, pm.country_reg_region_cd order by pm.country_reg_region_cd) as seqnum_tr,
row_number() over (partition by pm.trans_id, pm.mobseg_state order by pm.pm.mobseg_state) as seqnum_tm
. . .
) . . .
这个想法很简单。计算row_number()
的partition by
键和独特的列。然后,只需将值等于“ 1”的次数相加即可。这需要一个子查询,因为您不能嵌套窗口函数。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句