Разрывы и ошибки в системе с 3 столбцами с использованием SQL Server

Question

Я столкнулся со странным поведением с пробелами и островным решением. С 3 столбцами (3-й столбец не является целым числом), результат на самом деле является случайным. Давайте предположим, что мы следующий запрос:

Declare @Table1 TABLE
(
    ID varchar(50), 
    yr float, 
    CO1 varchar(50)
);

INSERT INTO @Table1 (ID, yr, CO1)
VALUES ('I2','2011','ABE'), ('I2','2012','ABE'), ('I2','2013','ABE'),
       ('I2','2014','ABE'), ('I2','2014','ABE'), ('I2','2005','ABD'),
       ('I2','2006','ABD'), ('I2','2007','ABD'), ('I2','2008','ABD'),
       ('I2','2007','ABA CD'), ('I2','2011','ABA CD'), ('I2','2013','ABA CD');

SELECT 
    ID, CO1, StartSeqNo = MIN(yr), EndSeqNo = MAX(yr)
FROM 
    (SELECT 
         ID, yr, CO1,
         rn = yr - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY yr)
     FROM 
         @Table1) a
GROUP BY 
    ID, CO1, rn ;

Результат, к которому я стремлюсь:

ID  CO1    StartSeqNo   EndSeqNo
----------------------------
I2  ABA CD    2007       2007
I2  ABA CD    2011       2011
I2  ABA CD    2013       2013
I2  ABD       2005       2008
I2  ABE       2011       2014

Я просмотрел stackru и другие места, чтобы определить, что я что-то упустил. Я уже пытался с четким и dens_rank, ни один не дает должного результата

Вот конкретные и заимствованные запросы, которые я уже пробовал:

--- distinct 

SELECT distinct ID,CO1, StartSeqNo=MIN(yr), EndSeqNo=MAX(yr)
FROM (
    SELECT distinct ID, yr, CO1
        ,rn=yr-ROW_NUMBER() OVER (PARTITION BY ID ORDER BY yr)
    FROM @Table1) a
GROUP BY ID, CO1, rn ;

--- with dense_rank
SELECT ID,CO1, StartSeqNo=MIN(yr), EndSeqNo=MAX(yr)
FROM (
    SELECT ID, yr, CO1
        ,rn=yr-dense_rank() OVER (PARTITION BY ID ORDER BY yr)
    FROM @Table1) a
GROUP BY ID, CO1, rn ;

Я не понимаю, почему гапсы и островные запросы не будут работать с нецелым столбцом. Я считаю, что есть проблема с группировкой где-то. Пожалуйста, помогите мне с этим.

Сим

Answer 1

Без пробелов годы будут представлять собой последовательную нумерацию в каждой группе ID/CO1, которую можно сравнить с нумерацией без пробелов, которая, конечно, также должна быть последовательной для каждого ID/CO1, упорядоченного по году. Таким образом, если вы не ORDER BY CO1 (до года), вы также должны использовать CO1 для PARTITION BY в функции нумерации строк. Кроме того, ваши данные содержат повторяющиеся строки, поэтому, чтобы равные годы в группе ID/CO1 совпадали, используйте функцию RANK вместо ROW_NUMBER:

WITH a (ID, CO1, yr, nmbr) AS (
  SELECT ID, CO1, yr
    , yr - RANK() OVER (PARTITION BY ID, CO1 ORDER BY yr)
  FROM @Table1
)
SELECT ID, CO1, StartSeqNo = MIN(yr), EndSeqNo = MAX(yr)
FROM a
GROUP BY ID, CO1, nmbr;

Наконец, позвольте мне предложить использовать int вместо float для чисел года.

Answer 2

Тебе нужно DENSE_RANK потому что вы получили несколько строк с одинаковой комбинацией ID/ год, и вам нужно добавить CO1 в PARTITION BY:

SELECT 
    ID, CO1, StartSeqNo = MIN(yr), EndSeqNo = MAX(yr)
FROM 
    (SELECT 
         ID, yr, CO1,
         rn = yr - dense_rank() OVER (PARTITION BY ID, CO1 ORDER BY yr)
     FROM 
         @Table1) a
GROUP BY 
    ID, CO1, rn ;

Answer 3

Вы, кажется, хотите:

select id, co1, min(yr), max(yr)
from (select *, (case when max(grp) over(partition by co1) > 1 then grp else 1 end) as grp1
      from (select *, yr - lag(yr, 1, yr) over (partition by id, co1 order by yr) as grp
            from table
           ) t
       ) t
group by id, co1, grp1;