Упорядочить строки в последовательности и заполнить пробелы для пропущенных строк
У меня проблема с отсутствующими строками в таблице, которая вызывает у меня головную боль.
В качестве базы данных у меня есть следующая таблица:
declare @table table
(
id1 int,
id2 int,
ch char(1) not null,
val int
)
insert into @table values (1112, 121, 'A', 12)
insert into @table values (1351, 121, 'A', 13)
insert into @table values (1411, 121, 'B', 81)
insert into @table values (1312, 7, 'C', 107)
insert into @table values (1401, 2, 'A', 107)
insert into @table values (1454, 2, 'D', 107)
insert into @table values (1257, 6, 'A', 1)
insert into @table values (1269, 6, 'B', 12)
insert into @table values (1335, 6, 'C', 12)
insert into @table values (1341, 6, 'D', 5)
insert into @table values (1380, 6, 'A', 3)
Выходные данные должны быть упорядочены по id2 и следовать фиксированной последовательности ch, которая должна повторяться до начала следующего id2.
Последовательность:
'A'
'B'
'C'
'D'
Если последовательность или шаблон прерывается, он должен заполнить недостающие строки нулем, чтобы я получил эту таблицу результатов:
id1 id2 ch val
----------------------------
1112 121 'A' 12
NULL 121 'B' NULL
NULL 121 'C' NULL
NULL 121 'D' NULL
1351 121 'A' 13
1411 121 'B' 81
NULL 121 'C' NULL
NULL 121 'D' NULL
NULL 7 'A' NULL
NULL 7 'B' NULL
1312 7 'C' 107
NULL 7 'D' NULL
1401 2 'A' 107
NULL 2 'B' NULL
NULL 2 'C' NULL
1454 2 'D' 107
и так далее...
То, что я ищу, это способ сделать это без итераций.
Я надеюсь, что кто-то может помочь!
Заранее спасибо!
3 ответа
Решение может быть таким:
declare @table table ( id1 int, id2 int, ch char(1) not null, val int )
insert into @table values (1112, 121, 'A', 12)
,(1351, 121, 'A', 13),(1411, 121, 'B', 81),(1312, 7, 'C', 107),(1401, 2, 'A', 107)
,(1454, 2, 'D', 107),(1257, 6, 'A', 1),(1269, 6, 'B', 12),(1335, 6, 'C', 12)
,(1341, 6, 'D', 5),(1380, 6, 'A', 3)
;with foo as
(select
*
,row_number() over (partition by id2 order by id1) rwn
,ascii(isnull(lag(ch,1) over (partition by id2 order by id1),'A'))-ascii('A') prev
,count(*) over (partition by id2,ch) nr
,ascii(ch)-ascii('A') cur
from @table
)
,bar as
(
select
*,case when cur<=prev and rwn>1 then 4 else 0 end + cur-prev step
from foo
)
,foobar as
(
select *,sum(step) over (partition by id2 order by id1 rows unbounded preceding) rownum
from bar
)
,iterations as
(
select id2,max(nr) nr from foo
group by id2
)
,blanks as
(
select
id2,ch chnr,char(ch+ascii('A') )ch,ROW_NUMBER() over (partition by id2 order by c.nr,ch)-1 rownum,c.nr
from iterations a
inner join (values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) c(nr)
on c.nr<=a.nr
cross join (values (0),(1),(2),(3)) b(ch)
)
select
b.id1,a.id2,a.ch,b.val
from blanks a
left join foobar b
on a.id2=b.id2 and a.rownum=b.rownum
order by a.id2,a.rownum
Сначала я делаю запрос "foo", который просматривает номер строки и получает предыдущее значение для ch для каждого id2.
Затем "bar" определяет количество пропущенных значений между строками. Например, если предыдущий был A, а ток - AC, то есть 2. Если предыдущий был A, а ток - A, то 4!
Затем "foobar" добавляет шаги, таким образом нумеруя исходные строки, где они должны быть в конечном выводе.
"iterations" считает количество раз, которое должны появиться строки "ABCD".
"BLANKS" - это все последние строки, то есть для каждого id2, он выводит все строки "ABCD", которые должны быть в конечном выводе, и нумерует их в rownum.
Наконец, я оставил соединение "foobar" с "BLANKS" на id2 и rownum. Таким образом мы получаем правильное количество строк, и выводятся места, где есть значения в оригинале.
Меня немного смущает ваш вывод, попробуйте это:
Обновить
DECLARE @table TABLE
(
row INT IDENTITY(1, 1) ,
id1 INT ,
id2 INT ,
ch CHAR(1) NOT NULL ,
val INT
);
DECLARE @Sequence TABLE ( ch3 CHAR(1) NOT NULL );
INSERT INTO @Sequence
VALUES ( 'A' );
INSERT INTO @Sequence
VALUES ( 'B' );
INSERT INTO @Sequence
VALUES ( 'C' );
INSERT INTO @Sequence
VALUES ( 'D' );
INSERT INTO @table
VALUES ( 1112, 121, 'A', 12 );
INSERT INTO @table
VALUES ( 1351, 121, 'A', 13 );
INSERT INTO @table
VALUES ( 1411, 121, 'B', 81 );
INSERT INTO @table
VALUES ( 1312, 7, 'C', 107 );
INSERT INTO @table
VALUES ( 1401, 2, 'A', 107 );
INSERT INTO @table
VALUES ( 1454, 2, 'D', 107 );
INSERT INTO @table
VALUES ( 1257, 6, 'A', 1 );
INSERT INTO @table
VALUES ( 1269, 6, 'B', 12 );
INSERT INTO @table
VALUES ( 1335, 6, 'C', 12 );
INSERT INTO @table
VALUES ( 1341, 6, 'D', 5 );
INSERT INTO @table
VALUES ( 1380, 6, 'A', 3 );
SELECT r.id1 ,
fin.id2 ,
ch3 ,
r.val
FROM ( SELECT *
FROM ( SELECT CASE WHEN r.chd - l.chd = 1 THEN 0
ELSE 1
END [gap in sq] ,
l.*
FROM ( SELECT id2 ,
ASCII(ch) chd ,
ch ,
val ,
id1 ,
row
FROM @table
) AS l
LEFT JOIN ( SELECT id2 ,
ASCII(ch) chd ,
row
FROM @table
) AS r ON l.row = r.row - 1
) AS temp ,
@Sequence s
WHERE temp.[gap in sq] = 1
OR ( temp.[gap in sq] = 0
AND s.ch3 = temp.ch
)
) AS fin
LEFT JOIN @table r ON r.id2 = fin.id2
AND r.id1 = fin.id1
AND r.ch = fin.ch3
Если вам удастся добавить дополнительный столбец в вашу таблицу, который определяет, какие [id2] являются частью той же последовательности, вы можете попробовать это:
declare @table table
(
id1 int,
id2 int,
ch char(1) not null,
val int,
category int -- extra column
)
insert into @table values (1112, 121, 'A', 12, 1)
insert into @table values (1351, 121, 'A', 13, 2)
insert into @table values (1411, 121, 'B', 81, 2)
insert into @table values (1312, 7, 'C', 107, 3)
insert into @table values (1401, 2, 'A', 107, 4)
insert into @table values (1454, 2, 'D', 107, 4)
insert into @table values (1257, 6, 'A', 1, 5)
insert into @table values (1269, 6, 'B', 12, 5)
insert into @table values (1335, 6, 'C', 12, 5)
insert into @table values (1341, 6, 'D', 5, 5)
insert into @table values (1380, 6, 'A', 3, 5)
DECLARE @sequence table (seq varchar(1))
INSERT INTO @sequence values ('A'), ('B'), ('C'), ('D')
SELECT b.id1, a.id2, a.seq, b.val, a.category
INTO #T1
FROM (
SELECT *
FROM @table
CROSS JOIN @sequence
) A
LEFT JOIN (
SELECT * FROM @table
) B
ON 1=1
AND a.id1 = b.id1
AND a.id2 = b.id2
AND a.seq = b.ch
AND a.val = b.val
;WITH rem_duplicates AS (
SELECT *, dup = ROW_NUMBER() OVER (PARTITION by id2, seq, category ORDER BY id1 DESC)
FROM #T1
) DELETE FROM rem_duplicates WHERE dup > 1
SELECT * FROM #T1 ORDER BY id2 DESC, category ASC, seq ASC
DROP TABLE #T1