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

Откуда:
Сообщений: 5975
Возникла такая вот задачка: есть выборка, упорядоченная в заданном направлении. Нужно промаркировать (битовым поле) подряд идущие (в направлении упорядочивания) записи с одинаковым значением поределенного поля.
Пример:
select number, convert(smallint, rand(checksum(newid()))*10) flag
into #t_test
from master..spt_values where type='P' and number between 0 and 100

И на основании выборки из #t_test должен получиться такой результат:

numberflagis_same
030
160
491
591
670
1090
1161
1261
1361
1440
1521
1621
1780

Сделал вот так:
select
  t1.number, t1.flag,
  case when f.cnt>1 then 1 else 0 end is_same
from #t_test t1
left join #t_test t2 on t2.number=t1.number+1
left join #t_test t3 on t3.number=t1.number-1
outer apply (
  select count(v.flag) from (
    select flag from (
      select t1.flag f1, t2.flag f2, t3.flag f3
    ) p
    unpivot (
      flag for n in (f1, f2, f3)
    ) unpvt
  ) v where v.flag=t1.flag
) f(cnt)
order by t1.number

- вроде бы все работает. Но как-то это, по моему, тяжеловесно. Можно ли сделать как-то проще?
12 дек 13, 11:41    [15281395]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
(в постинге выше в таблице я для компактности сократил выборку, в реальности в ней последовательность значений поля number непрерывна)
12 дек 13, 11:44    [15281416]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Glory
Member

Откуда:
Сообщений: 104760
Сон Веры Павловны
. Можно ли сделать как-то проще?

select
  t1.number, t1.flag,
  case when t2.flag=t1.flag or t3.flag=t1flag then 1 else 0 end is_same
from #t_test t1
left join #t_test t2 on t2.number=t1.number+1
left join #t_test t3 on t3.number=t1.number-1
12 дек 13, 11:46    [15281435]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Glory
t2.flag=t1.flag or t3.flag=t1flag

Мда.. и в самом деле, как-то я перемудрил.
12 дек 13, 12:22    [15281732]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Добрый Э - Эх
Guest
Сон Веры Павловны
- вроде бы все работает. Но как-то это, по моему, тяжеловесно. Можно ли сделать как-то проще?
Во многом зависит от версии твоего сервера. Так-то в 12-й версии это делается банальным запросом в один уровень посредством оконного LEAD/LAG...
12 дек 13, 12:35    [15281831]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх,

хотя вру... в один уровень не выйдет.
12 дек 13, 12:40    [15281866]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Добрый Э - Эх
Сон Веры Павловны
- вроде бы все работает. Но как-то это, по моему, тяжеловесно. Можно ли сделать как-то проще?
Во многом зависит от версии твоего сервера. Так-то в 12-й версии это делается банальным запросом в один уровень посредством оконного LEAD/LAG...

Я в курсе про lead/lag. У меня 2008.
12 дек 13, 12:41    [15281874]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Добрый Э - Эх
Guest
Сон Веры Павловны,

ну, row_number-то работает. Так-то задача на инвариант группы. А он, в свою очередь, банально строится на разности двух разнооконных row_number-ов...
12 дек 13, 12:43    [15281893]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Добрый Э - Эх
Guest
Ну и как вариант реализации:
--
-- Тестовый набор данных:
with t_test as (
select number, convert(smallint, rand(checksum(newid()))*5) flag
from master..spt_values where type='P' and number between 0 and 100)
--
-- Основной запрос:
select v.number, v.flag,
       case count(1) over(partition by flag, grp_id)
         when 1 then 0
         else 1
       end as is_same
  from (
         select t.*,
                row_number() over(partition by flag order by number)- 
                row_number() over(order by number) as grp_id
           from t_test t
       ) v
 order by number
on-line проверка на sqlfiddle.com
12 дек 13, 12:54    [15281978]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Добрый Э - Эх
Guest
Сон Веры Павловны
(в постинге выше в таблице я для компактности сократил выборку, в реальности в ней последовательность значений поля number непрерывна)
моему варианту, кстати, непрерывность последовательности number-ов по боку. Замечательно отработает и в случае наличия пропусков значений. Главное - чтобы number было монотонно возрастающей, без дубликатов значений.
12 дек 13, 13:28    [15282234]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Добрый Э - Эх,

спасибо, красивый вариант. Есть один нюанс: я решил проверить производительность. Тестовые данные такие:
;with cte as (
  select 1 n
  union all
  select e.n+1 from cte e where e.n<10000000
)
select n number, convert(smallint, rand(checksum(newid()))*10) flag
into dbo.tb_test
from cte option(maxrecursion 0)

alter table tb_test alter column number int not null;
alter table tb_test add constraint pk_tb_test primary key(number);

;with cte as (
  select v.number, v.flag,
         case count(1) over(partition by flag, grp_id)
           when 1 then 0
           else 1
         end as is_same
    from (
           select t.*,
                  row_number() over(partition by flag order by number)- 
                  row_number() over(order by number) as grp_id
             from dbo.tb_test t
         ) v
)
select count(1) from cte where is_same=1

- 1 минута 10 секунд;
;with cte as (
  select
    t1.number, t1.flag,
    case when t1.flag=t2.flag or t1.flag=t3.flag then 1 else 0 end is_same
  from dbo.tb_test t1
  left loop join dbo.tb_test t2 on t2.number=t1.number+1
  left loop join dbo.tb_test t3 on t3.number=t1.number-1
)
select count(1) from cte where is_same=1

- 18 секунд (оптимизатор почему-то стал использовать hash join, с которым время выполнения было ~40 сек., поэтому пришлось прохинтовать запрос).
Разумеется, если использовать вариант с селф-джойном. в котором предварительно придется нумеровать строки (т.е. последовательность number на кластерном индексе не будет непрерывной), то ваш вариант оптимальнее. Но в моем случае выборка нужна исключительно для клиентского приложения, поэтому её вполне можно делать в хранимой процедуре, загнав предварительно пронумерованные данные во временную таблицу. Даже в этом случае получается примерно в 2 раза быстрее:
if object_id('tempdb..#t_test') is not null
  drop table #t_test;
create table #t_test (
  ord int not null,
  number int,
  flag smallint
);
;with cte0 as (
  select
    number, flag, row_number() over (order by number) ord
  from dbo.tb_test
)
insert into #t_test select ord, number, flag from cte0;
alter table #t_test add primary key (ord);

;with cte as (
  select
    t1.number, t1.flag,
    case when t1.flag=t2.flag or t1.flag=t3.flag then 1 else 0 end is_same
  from #t_test t1
  left loop join #t_test t2 on t2.ord=t1.ord+1
  left loop join #t_test t3 on t3.ord=t1.ord-1
)
select count(1) from cte where is_same=1

- 30 секунд.
Тестировалось на локальном сервере, где я гарантированно один, и нагрузка сервера от прочих процессов отсутствует.
13 дек 13, 06:47    [15285811]     Ответить | Цитировать Сообщить модератору
 Re: Маркировка подряд идущих записей с одинаковым значением поля  [new]
Сон Веры Павловны
Member

Откуда:
Сообщений: 5975
Планы запросов: первый - от варианта с селф-джойном, второй - от варианта на разности двух разнооконных row_number-ов.

К сообщению приложен файл. Размер - 97Kb
13 дек 13, 06:52    [15285814]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить