postgresql 分组编号及分组在组内编号

1、分组编号：
使用dense_rank()：

select dense_rank() over (order by left(name, 1)) as outer_id,
row_number() over (partition by left(name, 1) order by name desc) as inner_id,
src_id, name
from cte;

2、分组在组内编号

ROW_NUMBER () OVER (partition by 字段 ORDER BY 字段 DESC) AS 字段别名

3、不通过group by 直接计算重复数量
count(*) OVER (PARTITION BY ss.“号码”) AS “计数”,