postgregroup在SQL中的连续值计数?

Question

我有一个(postgres)表，像这样

order     data     
1         12,1
2         12,1
3         12,1
4         13,4
5         13,4
6         12,1
7         12,1 
8         16
9         16

我想建立一个SQL语句，允许我显示连续值，带计数。

order     data     count     
1         12,1     3
4         13,4     2
6         12,1     2
8         16       2

在(postgres) SQL中有这样的方法吗?

Answer 1

您可以看到我的查询结构和结果:dbfiddle

with recursive
cte_r as (
select "order",
data,
row_number() over (order by "order") as rn
from test
order by "order", data),
cte as (
select "order",
data,
rn,
rn as grp
from cte_r
where rn = 1
union all
select cr."order",
cr.data,
cr.rn,
case
when c.data isnull or c.data = cr.data then c.grp
else c.grp + 1
end
from cte c,
cte_r cr
where c.rn = cr.rn - 1
)
select distinct first_value("order") over (w) as "order",
data,
count(*) over (w)
from cte
window w as (partition by grp)
order by 1;