Tengo una mesa con varios ID. Cada Id tiene valores ordenados por un índice secuencial.

create table myValues
(
  id  int,
  ind int,
  val int
)

insert into myValues
values
(21, 5, 300),
(21, 4, 310),
(21, 3, 300),
(21, 2, 300),
(21, 1, 345),
(21, 0, 300),
(22, 5, 300),
(22, 4, 300),
(22, 3, 300),
(22, 2, 300),
(22, 1, 395),
(22, 0, 300)

Estoy tratando de encontrar el número de valores consecutivos que sean iguales.

El campo de valor representa algunos datos que deben cambiarse en cada entrada (pero no es necesario que sean únicos en general).

El problema es averiguar cuándo hay más de dos filas consecutivas con el mismo valor (dado el mismo id).

Por lo tanto, estoy buscando una salida como esta:

id  ind   val   count
21  5     300   1
21  4     310   1
21  3     300   2
21  2     300   2
21  1     345   1
21  0     300   1
22  5     300   4
22  4     300   4
22  3     300   4
22  2     300   4
22  1     395   1
22  0     300   1

Soy consciente de que esto es similar al problema de islas y brechas discutido aquí.

Sin embargo, todas esas soluciones dependen de la capacidad de usar una declaración de partición con valores que se supone que aumentan consecutivamente.

Una solución que genere los rangos de "islas" como intermediario también funcionaría, p. Ej.

id  startind   endind
21  3          2
22  5          2

Tenga en cuenta que puede haber muchas islas para cada id.

Estoy seguro de que hay una adaptación simple de la solución de la isla, pero por mi vida no puedo pensar en ella.

0
sonarforte 29 jul. 2016 a las 03:01

2 respuestas

La mejor respuesta

Encuentra el grupo continuo y luego haz una partición count () por eso

select  id, ind, val, count(*) over (partition by id, val, grp)
from
(
    select  *, grp = dense_rank() over (partition by id, val order by ind) - ind
    from    myValues
) d
order by id, ind desc
2
Squirrel 29 jul. 2016 a las 00:41

La otra solución es obviamente más elegante. Tendré que estudiarlo un poco más yo mismo.

with agg(id, min_ind, max_ind, cnt) as (
    select id, min(ind), max(ind), count(*)
    from
        (
        select id, ind, val, sum(brk) over (partition by id order by ind desc) as grp
        from
            (
            select 
                id, ind, val,
                coalesce(sign(lag(ind) over (partition by id, val order by ind desc) - ind - 1), 1) as brk
            from myValues
            ) as d
        ) as d
    group by id, grp
)
select v.id, v.ind, v.val, a.cnt
from myValues v inner join agg a on a.id = v.id and v.ind between min_ind and max_ind
order by v.id, v.ind desc;
0
shawnt00 29 jul. 2016 a las 00:49