Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Найти пропущенные строки в таблице.  [new]
Alexey_SQL
Member

Откуда: Новосибирск
Сообщений: 50
Доброго дня всем форумчанам и с пятницей.

Стоит задача в таблице на 30-40 мл строк, найти пропущенные ID, которые подаются при формировании файла из внешней системы. т.е. локальные сиквенсы ведутся в таблице, но нужно найти строки, которые не были в файле и не были отданы системой. Хотелось бы реализовать простое решение их поиска, при условии, что ID из внешней системы построены на ключе +1 в каждой записи.

Для примера.

[SerialNo]
2964186452
2964186453
2964186454
2964186456
2964186457
2964186462
2964186465
2964186469
2964186470
2964186472
2964186473
2964186476
2964186477
2964186478
2964186486
2964186487
2964186488

Видим, что не загрузили запись 55, с 57-62, и т.д. Если не сложно укажите код, которым было бы удобно искать эти пропуски и рассчитывать их количество в таблице или за нужный период времени.

Нет ничего хуже, чем зайти в тупик...
1 июл 16, 17:07    [19360698]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
o-o
Guest
сопоставить каждой строке соседнюю и смотреть, где разность >1.
если сервер >= 2012, вообще все даром, на выбор lead/lag
1 июл 16, 17:15    [19360735]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
declare @numbers table(number bigint)

insert @numbers
values(2964186452),
(2964186453),
(2964186454),
(2964186456),
(2964186457),
(2964186462),
(2964186465),
(2964186469),
(2964186470),
(2964186472),
(2964186473),
(2964186476),
(2964186477),
(2964186478),
(2964186486),
(2964186487),
(2964186488)

select * from (
select number
,number - lag(number) over(order by number) as diff
from @numbers) a
where diff > 1
1 июл 16, 17:20    [19360758]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Alexey_SQL
Member

Откуда: Новосибирск
Сообщений: 50
Спасибо, но Сервер SQL 2008. Функцию lag, отказывается принимать.
1 июл 16, 17:29    [19360796]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
declare @numbers table(number bigint)

insert @numbers
values(2964186452),
(2964186453),
(2964186454),
(2964186456),
(2964186457),
(2964186462),
(2964186465),
(2964186469),
(2964186470),
(2964186472),
(2964186473),
(2964186476),
(2964186477),
(2964186478),
(2964186486),
(2964186487),
(2964186488)

select b.number,b.number - a.number 
from
(select number
,row_number() over(order by number) as num
from @numbers) a
join
(select number
,row_number() over(order by number) as num
from @numbers) b on b.num - a.num = 1
where b.number - a.number > 1
1 июл 16, 17:36    [19360835]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Alexey_SQL
Member

Откуда: Новосибирск
Сообщений: 50
Спасибо, все заработало. Единственное, что чуток ошибся в объеме данных, на таблицу это 300-400 мл. строк. Запрос сами понимаете, на таком объеме бежит не реально долго. Есть способ как это можно ускорить. ??
1 июл 16, 18:59    [19361124]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
select a, cast( min(number) as varchar ) + '  -  ' + cast( max(number) as varchar ) 
from
(
select number-row_number() over(order by number) a, * from @numbers
) t
group by a
1 июл 16, 19:29    [19361193]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
aleks2
Guest
Knyazev Alexey
select a, cast( min(number) as varchar ) + '  -  ' + cast( max(number) as varchar ) 
from
(
select number-row_number() over(order by number) a, * from @numbers
) t
group by a

Шо за бред?

declare @numbers table(number bigint PRIMARY KEY)

insert @numbers
values(2964186452),
(2964186453),
(2964186454),
(2964186456),
(2964186457),
(2964186462),
(2964186465),
(2964186469),
(2964186470),
(2964186472),
(2964186473),
(2964186476),
(2964186477),
(2964186478),
(2964186486),
(2964186487),
(2964186488)


select n.number from @numbers as n 
  where not exists( select * from @numbers where number = n.number - 1 )
        and n.number > (select min(number) from @numbers)
union all
select n.number from @numbers as n 
  where not exists( select * from @numbers where number = n.number + 1 )
        and n.number < (select max(number) from @numbers)
order by number
2 июл 16, 07:47    [19362165]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
aleks2
Knyazev Alexey
select a, cast( min(number) as varchar ) + '  -  ' + cast( max(number) as varchar ) 
from
(
select number-row_number() over(order by number) a, * from @numbers
) t
group by a


Шо за бред?


Вы хоть в своём варианте кол-во чтений посмотрите, прежде чем предлагать...а мой бред не мой...Автор: Ицик Бен-Ган
2 июл 16, 12:29    [19362491]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
aleks2
Guest
Knyazev Alexey
aleks2
пропущено...

Шо за бред?


Вы хоть в своём варианте кол-во чтений посмотрите, прежде чем предлагать...а мой бред не мой...Автор: Ицик Бен-Ган


1. Даже патентованный бред - это бред.
2. Да не больше, чем в патентованном бреде.
2 июл 16, 12:41    [19362533]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
aleks2
Knyazev Alexey
пропущено...


Вы хоть в своём варианте кол-во чтений посмотрите, прежде чем предлагать...а мой бред не мой...Автор: Ицик Бен-Ган


1. Даже патентованный бред - это бред.
2. Да не больше, чем в патентованном бреде.


а если кроме балабольства?

тест на 50 тыс записей!

Ваш вариант:
Table 'n'. Scan count 36, logical reads 10667024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 332970 ms, elapsed time = 43666 ms.

Вариант Ицик Бен-Ган:
Table 'n'. Scan count 1, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 27 ms.


Учите матчасть!

ЗЫ: на 0,5 млн результат вашего запроса я за 15 мин. не дождался, вариант Ицика 3 сек.
2 июл 16, 13:20    [19362608]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Knyazev Alexey,

Ваш вариант дает интервалы непрерывности, а ТС'у нужно обратное.
2 июл 16, 14:30    [19362715]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
_human
Member

Откуда:
Сообщений: 569
Alexey_SQL,

select --pvt.*, 
	sum([2]-[1]-1)
from
	(select 
		 ROW_NUMBER() over(order by number) - case when (row_number() over(order by number)+1/2)%2 = 0 then 2 else 1 end as p
		, number
		, case when (row_number() over(order by number)+1/2)%2 = 0 then 2 else 1 end as x
		--, ROW_NUMBER() over(order by number) as rn
	from @numbers) q
pivot (min(number) for x in ([1], [2])) as pvt
2 июл 16, 18:54    [19363225]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Alexey_SQL
Есть способ как это можно ускорить. ??
В варианте от 3unknown 19360835 замените
on b.num - a.num = 1
на
on b.num = a.num + 1
Плюс нужен индекс по number.
2 июл 16, 19:35    [19363301]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
aleks2
Guest
Knyazev Alexey
aleks2
пропущено...


1. Даже патентованный бред - это бред.
2. Да не больше, чем в патентованном бреде.


а если кроме балабольства?

тест на 50 тыс записей!

Ваш вариант:
Table 'n'. Scan count 36, logical reads 10667024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 332970 ms, elapsed time = 43666 ms.

Вариант Ицик Бен-Ган:
Table 'n'. Scan count 1, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 31 ms, elapsed time = 27 ms.


Учите матчасть!

ЗЫ: на 0,5 млн результат вашего запроса я за 15 мин. не дождался, вариант Ицика 3 сек.


Не позорился бы. Если ты не умеешь тестировать - не берись.
Хе-хе "Scan count 36" а чо не 37,5?
3 июл 16, 09:49    [19364100]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
aleks2,

Ну приведи тогда доводы в пользу своего варианта кроме "бред, бред".
3 июл 16, 13:05    [19364311]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Сводный тест.
+ Подготовка
use tempdb;
go

create table dbo.t (id int primary key);

insert into dbo.t with (tablock)
select top (5000000)
 row_number() over (order by (select 1))
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b cross join
 master.dbo.spt_values c;

with s as (select top (10) percent id from dbo.t order by newid()) delete from s;
go
+ Тестирование
use tempdb;
go

declare @prefix nvarchar(50) = newid(), @query nvarchar(max);

declare @queries table (name nvarchar(100), query nvarchar(max));

insert into @queries
values
 (N'Classic', N'/*Classic - ' + @prefix + N'*/declare @a int, @b int; select
 @a = a.id, @b = b.id
from
 dbo.t a cross apply
 (select top (1) id from dbo.t where id > a.id order by id) b
where
 b.id > a.id + 1
option
 (maxdop 1);'),
(N'By Knyazev Alexey (modified)', N'/*By Knyazev Alexey (modified) - ' + @prefix + N'*/declare @a int, @b int; with a as
(
 select
  max(id) as id
 from
  (select id - row_number() over (order by id) as g, id from dbo.t) t
 group by
  g
)
select
 @a = a.id, @b = b.id
from
 a cross apply
 (select top (1) id from dbo.t where id > a.id order by id) b
option
 (maxdop 1, order group);'),
(N'By 3unknown (modified)', N'/*By 3unknown (modified) - ' + @prefix + N'*/declare @a int, @b int; select
 @a = a.id, @b = b.id
from
(select id, row_number() over(order by id) as num from dbo.t) a join
(select id, row_number() over(order by id) as num from dbo.t) b on b.num = a.num + 1
where
 b.id - a.id > 1
option
 (maxdop 1);'),
(N'By aleks2 (modified)', N'/*By aleks2 (modified) - ' + @prefix + N'*/declare @a int, @b int; select
 @a = a.id, @b = b.id
from
 (
  select n.id, row_number() over (order by n.id) as rn from dbo.t as n 
    where not exists( select * from dbo.t where id = n.id - 1)
          and n.id > (select min(id) from dbo.t)
 ) a join
 (
  select n.id, row_number() over (order by n.id) as rn from dbo.t as n 
    where not exists( select * from dbo.t where id = n.id + 1)
          and n.id < (select max(id) from dbo.t)
 ) b on b.rn = a.rn
option
 (maxdop 1);'),
(N'By _human (modified)', N'/*By _human (modified) - ' + @prefix + N'*/declare @a int, @b int; select
 @a = pvt.[1], @b = pvt.[2] 
from
	(select 
		 row_number() over(order by id) - case when (row_number() over(order by id)+1/2)%2 = 0 then 2 else 1 end as p
		, id
		, case when (row_number() over(order by id)+1/2)%2 = 0 then 2 else 1 end as x
		--, row_number() over(order by id) as rn
	from dbo.t) q
pivot (min(id) for x in ([1], [2])) as pvt
option
 (maxdop 1);');

declare q cursor local fast_forward for
 select query from @queries;

open q;
while 1 = 1
 begin
  fetch next from q into @query;
  if @@fetch_status <> 0
   break;

  exec(@query);
 end;

select
 q.name as [Description],
 cast(qs.last_elapsed_time / 1000000. as money) as [Elapsed time, sec],
 cast(qs.last_worker_time / 1000000. as money) as [CPU, sec],
 qs.last_logical_reads as [logical reads],
 qs.last_physical_reads as [physical reads],
 qp.query_plan
from
 sys.dm_exec_query_stats qs cross apply
 sys.dm_exec_query_plan(qs.plan_handle) qp cross apply
 sys.dm_exec_sql_text(qs.plan_handle) t join
 @queries q on t.text like N'/*' + name + N' - ' + @prefix + N'*/%'
order by
 qs.last_elapsed_time;
+ Результат
DescriptionElapsed time, secCPU, seclogical readsphysical reads
By 3unknown (modified)3,8143,8124335040
By aleks2 (modified)3,85063,2286677511917
By Knyazev Alexey (modified)4,22283,7852151718610564
Classic8,24688,2414149397690
By _human (modified)64,588813,12092977174861

ЗЫ: Те, кого не устроило как был модифицирован их оригинальный запрос, вправе изменить тест и выложить свой вариант.
3 июл 16, 13:47    [19364381]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
aleks2
Guest
invm
Сводный тест.

И не лень тебе?

Pavel1211
aleks2,

Ну приведи тогда доводы в пользу своего варианта кроме "бред, бред".

Зачем?
Запомни, чайнег, группировка - страшное зло.
3 июл 16, 14:04    [19364401]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Alexey_SQL
Member

Откуда: Новосибирск
Сообщений: 50
Спасибо за тестирование.

Воспользовался вариантом от 3unknown, судя по всему как самый быстрый. В целом на 10-12 мл. строк, записей за сутки. Поиск потерянных занимает в районе 5 минут, с учетом того, что нет индекса по полю [SerialNo] as number, если его создавать, то места в БД будет занимать нереально много. Храним данные за 12 месяцев, по 1:2 месяца в схеме = 400 мл. строк. *2*12=9,6 миллиарда записей. Создавать на это поле индекс не желательно в нашем случае, а 5-ти минутный результат поиска потерянных записей за сутки вполне устраивает. Но если есть конечно, рациональные предложения по ускорению запроса, буду признателен за вашу помощь. Так же всем отдельное спасибо, кто старался помочь в решении это задачи. :)


select b.number, (b.number - a.number) as Count_tranzact_loss
from

(

select number,row_number() over(order by number) as num

from (select [SerialNo] as number FROM [rec_20160701_20160715].[dbo].[rec] where convert(date,ChargingTime)>=convert(date,GETDATE()-1) ) c
)
as a

join

(
select number,row_number() over(order by number) as num

from (select [SerialNo] as number FROM [rec_20160701_20160715].[dbo].[rec] where convert(date,ChargingTime)>=convert(date,GETDATE()-1) ) d
)
as b


on b.num = a.num + 1

where b.number - a.number > 1
4 июл 16, 07:39    [19365549]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
invm
ЗЫ: Те, кого не устроило как был модифицирован их оригинальный запрос, вправе изменить тест и выложить свой вариант.


use tempdb
go
drop table n

create table n (number bigint primary key)
go

with n1
as ( select * from (values(0),(1),(2),(3))t(n))
, n2 as (select 1 as n from n1 a cross join n1 b )
, n3 as (select 1 as n from n2 a cross join n2 b )
, n4 as (select 1 as n from n3 a cross join n3 b )

insert into n
select distinct row_number() over (order by (select null) ) + abs(cast( cast( newid() as varbinary(8) ) as bigint )%1000) from n4
go


set statistics io on
set statistics time on

--Ицик Бен-Ган
/*
Table 'n'. Scan count 1, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 27 ms.
*/

select a, cast( min(number) as varchar ) + '  -  ' + cast( max(number) as varchar ) 
from
(
select number-row_number() over(order by number) a, * from n
) t
group by a
option (maxdop 1)

--aleks2 
/*
Table 'n'. Scan count 6, logical reads 10633224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 320672 ms,  elapsed time = 322473 ms.
*/
select n.number from n as n 
  where not exists( select * from n where number = n.number - 1 )
        and n.number > (select min(number) from n)
union all
select n.number from n as n 
  where not exists( select * from n where number = n.number + 1 )
        and n.number < (select max(number) from n)
order by number
option (maxdop 1)

--3unknown
/*
Table 'n'. Scan count 2, logical reads 258, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 51 ms.
*/
select a.number, b.number
from
(select number, row_number() over(order by number) as num from n) a join
(select number, row_number() over(order by number) as num from n) b on b.num = a.num + 1
where
 b.number - a.number > 1
option
 (maxdop 1);
4 июл 16, 10:18    [19365860]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
aleks2
Хе-хе "Scan count 36" а чо не 37,5?

у вас 6(!!!) обращений к таблице... я дал шанс распараллелить ваш запрос к сиквелу, чтоб хоть на этом время сэкономить
4 июл 16, 10:20    [19365870]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
invm
Member

Откуда: Москва
Сообщений: 9838
Knyazev Alexey,

Повторюсь - код Ицика выдает интервалы непрерывности, ТС'у же нужны границы разрывов в последовательности.
4 июл 16, 11:11    [19366066]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
invm
Повторюсь - код Ицика выдает интервалы непрерывности, ТС'у же нужны границы разрывов в последовательности.


Да понял я это ещё после первого вашего коммента, цель всех тестов производительности показать, что ранжирование имеет право на жизнь, а то их aleks2 боится, как чумы и варианты с 6 обращениями к одной таблице считает приоритетными, и дикое кол-во чтений его совсем не смущает...про время выполнения я вообще молчу.
4 июл 16, 11:33    [19366152]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
aleks2
Guest
Alexey_SQL
Спасибо за тестирование.

Воспользовался вариантом от 3unknown, судя по всему как самый быстрый. В целом на 10-12 мл. строк, записей за сутки. Поиск потерянных занимает в районе 5 минут, с учетом того, что нет индекса по полю [SerialNo] as number, если его создавать, то места в БД будет занимать нереально много.


Я с вас косею, Шура.
У вас что, и кластерного индекса по SerialNo нету?
4 июл 16, 11:40    [19366171]     Ответить | Цитировать Сообщить модератору
 Re: Найти пропущенные строки в таблице.  [new]
aleks2
Guest
Knyazev Alexey
aleks2
Хе-хе "Scan count 36" а чо не 37,5?

у вас 6(!!!) обращений к таблице... я дал шанс распараллелить ваш запрос к сиквелу, чтоб хоть на этом время сэкономить

С арифметикой у тя хорошо.
А вот с пониманием - неважно.

Только полный идиот сочтет "and n.number > (select min(number) from @numbers)" за "обращение к таблице".
Для правильного индекса - это, по факту, константа.
4 июл 16, 11:44    [19366186]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить