SQL Query для группировки результатов на основе последовательности

Ответ 1

WITH numbered AS (
  SELECT
    ID, Seq, Amt,
    SeqGroup = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Seq) - Seq
  FROM atable
)
SELECT
  ID,
  Start = MIN(Seq),
  [End] = MAX(Seq),
  TotalAmt = SUM(Amt)
FROM numbered
GROUP BY ID, SeqGroup
ORDER BY ID, Start
;

Ответ 2

Кажется, это работает хорошо. @breakingRows будет содержать все строки, которые разбивают последовательность id и seq (т.е. если id изменяется или если seq не больше, чем предыдущий seq). С этой таблицей вы можете выбрать все строки такой последовательности в @temp. Я должен добавить, однако, что производительность, вероятно, будет не настолько хороша из-за всех подзапросов, но вам нужно будет протестировать, чтобы быть уверенным.

declare @temp table (id int, seq int, amt int)
insert into @temp select 1, 1, 500
insert into @temp select 1, 2, 500
insert into @temp select 1, 3, 500
insert into @temp select 1, 5, 500
insert into @temp select 2, 10, 600
insert into @temp select 2, 11, 600
insert into @temp select 3, 1, 700
insert into @temp select 3, 3, 700

declare @breakingRows table (ctr int identity(1,1), id int, seq int)

insert into @breakingRows(id, seq)
select id, seq
from @temp t1 
where not exists 
    (select 1 from @temp t2 where t1.id = t2.id and t1.seq - 1 = t2.seq)
order by id, seq

select br.id, br.seq as start, 
       isnull ((select top 1 seq from @temp t2 
               where id < (select id from @breakingRows br2 where br.ctr = br2.ctr - 1) or 
                     (id = (select id from @breakingRows br2 where br.ctr = br2.ctr - 1) and
                      seq < (select seq from @breakingRows br2 where br.ctr = br2.ctr - 1))          
               order by id desc, seq desc),
               br.seq)
      as [end],
      (select SUM(amt) from @temp t1 where t1.id = br.id and 
        t1.seq < 
            isnull((select seq from @breakingRows br2 where br.ctr = br2.ctr - 1 and br.id = br2.id), 
                   (select max(seq) + 1 from @temp)) and 
        t1.seq >= br.seq)
from @breakingRows br
order by id, seq

Ответ 3

Ну, возможно, более элегантный способ сделать это (что-то намекает на меня, что есть), но вот подход, который будет работать, если вы используете версию SQL Server, которая принимает общие табличные выражения:

use Tempdb
go

create table [Test]
(
    [id] int not null,
    [Seq] int not null,
    [Amt] int not null
)

insert into [Test] values
(1, 1, 500),
(1, 2, 500),
(1, 3, 500),
(1, 5, 500),
(2, 10, 600),
(2, 11, 600),
(3, 1, 700),
(3, 3, 700)

;with
lower_bound as (
    select *
      from Test
     where not exists (
        select *
          from Test as t1
         where t1.id = Test.id and t1.Seq = Test.Seq - 1
    )
),
upper_bound as (
    select *
      from Test
     where not exists (
        select *
          from Test as t1
         where t1.id = Test.id and t1.Seq = Test.Seq + 1
    )
),
bounds as (
    select id, (select MAX(seq) from lower_bound where lower_bound.id = upper_bound.id and lower_bound.Seq <= upper_bound.Seq) as LBound, Seq as Ubound
      from upper_bound
)
select Test.id, LBound As [Start], UBound As [End], SUM(Amt) As TotalAmt
  from Test
  join bounds
    on Test.id = bounds.id
   and Test.Seq between bounds.LBound and bounds.Ubound
 group by Test.id, LBound, UBound

drop table [Test]

Ответ 4

Так как Andriy уже разместил золотое решение, здесь я беру с помощью инструкции UPDATE, чтобы получить результат из таблицы temp, просто для удовольствия.

declare @tmp table (
    id int, seq int, amt money, start int, this int, total money,
    primary key clustered(id, seq))
;
insert @tmp
select *, start=seq, this=seq, total=convert(money,amt)
from btable
;
declare @id int, @seq int, @start int, @amt money
update @tmp
set 
    @amt = total = case when id = @id and seq = @seq+1 then @amt+total else amt end,
    @start = start = case when id = @id and seq = @seq+1 then @start else seq end,
    @seq = this = seq,
    @id = id = id
from @tmp
option (maxdop 1)
;
select id, start, max(this) [end], max(total) total
from @tmp
group by id, start
order by id, start

Примечания:

• btable: имя вашей таблицы
• id int, seq int, amt money: ожидаемые столбцы в таблице

Ответ 5

Попробуйте выполнить следующий запрос.

select id, min(seq), max(seq), sum(amt) from table group by id

OOps, извините, это неправильный запрос, поскольку вам нужна последовательность