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

Откуда:
Сообщений: 360
Добрый день всем!
Нужно определить период актуальности контрольного числа систем.
Дано: Дата актуальности, система, контрольное число.

Определяла следующим образом

DECLARE @t TABLE(name_file_usr VARCHAR (50),wdate DATETIME,texts_r INT)

INSERT INTO @t (
	name_file_usr,
	wdate,
	texts_r
) 
SELECT 'EPB',	'2013-03-12',	10611
UNION SELECT 'EPB','2013-03-11',      10610
UNION SELECT 'EPB','2013-03-07',	10609
UNION SELECT 'EPB','2013-03-06',	10589
UNION SELECT 'EPB','2013-03-05',	10590
UNION SELECT 'EPB','2013-03-04',	10589
UNION SELECT 'EPB','2013-03-01',	10590
UNION SELECT 'EPB','2013-02-28',	10589
UNION SELECT 'EPB','2013-02-27',	10589
UNION SELECT 'EPB','2013-02-26',	10588
UNION SELECT 'EPB','2013-02-25',	10586
UNION SELECT 'EPB','2013-02-22',	10586
UNION SELECT 'EPB','2013-02-21',	10584
UNION SELECT 'EPB','2013-02-20',	10583
UNION SELECT 'EPB','2013-02-19',	10582
UNION SELECT 'EPB','2013-02-18',	10581
UNION SELECT 'EPB','2013-02-15',	10432
UNION SELECT 'EPB','2013-02-14',	10432
UNION SELECT 'EPB','2013-02-13',	10432
UNION SELECT 'EPB','2013-02-12',	10432
UNION SELECT 'EPB','2013-02-11',	10430
UNION SELECT 'EPB','2013-02-08',	10428
UNION SELECT 'EPB','2013-02-07',	10426
UNION SELECT 'EPB','2013-02-06',	10426
UNION SELECT 'EPB','2013-02-05',	10421
UNION SELECT 'EPB','2013-02-04',	10417
UNION SELECT 'EPB','2013-02-01',	10416
UNION SELECT 'ppn','2013-03-07',47
UNION SELECT 'ppn','2013-02-28',45
UNION SELECT 'ppn','2013-02-21',45
UNION SELECT 'ppn','2013-02-14',45
UNION SELECT 'ppn','2013-02-07',45
UNION SELECT 'ppn','2013-01-31',44
UNION SELECT 'ppn','2013-01-24',44
UNION SELECT 'ppn','2013-01-17',44
UNION SELECT 'ppn','2013-01-10',44
UNION SELECT 'ppn','2013-01-08',44
UNION SELECT 'ppn','2012-12-27',44
UNION SELECT 'ppn','2012-12-20',44
UNION SELECT 'ppn','2012-12-13',43
UNION SELECT 'ppn','2012-12-06',43
UNION SELECT 'ppn','2012-11-29',43
UNION SELECT 'ppn','2012-11-22',43
UNION SELECT 'ppn','2012-11-15',42
UNION SELECT 'ppn','2012-11-08',42
UNION SELECT 'ppn','2012-11-01',42
UNION SELECT 'ppn','2012-10-25',42
UNION SELECT 'ppn','2012-10-18',42
UNION SELECT 'ppn','2012-10-11',41
UNION SELECT 'ppn','2012-10-04',41
UNION SELECT 'ppn','2012-09-27',41
UNION SELECT 'ppn','2012-09-20',40

SELECT  name_file_usr,
            MAX(kontr_ch) kontr_ch,
            MIN(d1) d_begin,
            ISNULL(MAX(d2),CAST(GETDATE() AS date )) d_end
    FROM    ( SELECT    name_file_usr,
                        MAX(CASE x
                              WHEN 0 THEN texts_r
                            END) kontr_ch,
                        MIN(wdate) d1,
                        DATEADD(d, -1, MAX(CASE x
                                             WHEN 1 THEN wdate
                                           END)) d2,
                        gr
                        - ROW_NUMBER() OVER ( PARTITION BY name_file_usr,
                                              MAX(CASE x
                                                    WHEN 0 THEN texts_r
                                                  END) ORDER BY gr ) gr
              FROM      ( SELECT    *,
                                    RN
                                    - ROW_NUMBER() OVER ( PARTITION BY name_file_usr,
                                                          x ORDER BY wdate ) gr
                          FROM      ( SELECT    *,
                                                ROW_NUMBER() OVER ( PARTITION BY name_file_usr ORDER BY wdate, x DESC ) RN
                                      FROM      @t t
                                                CROSS JOIN ( SELECT 0
                                                             UNION ALL
                                                             SELECT 1
                                                           ) tt ( x )
                                    ) t
                        ) t
              GROUP BY  name_file_usr,
                        gr
              HAVING    SUM(1 - x) > 0
            ) t
    GROUP BY name_file_usr,
            gr
            ORDER BY name_file_usr,d_begin DESC 


Но по системе 'EPB' период определяется не верно.
cte и outer apply работ долго.
Помогите подправить данный скрипт.
14 мар 13, 15:02    [14047978]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Гость333
Member

Откуда:
Сообщений: 3683
el_sh
Нужно определить период актуальности контрольного числа систем.
Дано: Дата актуальности, система, контрольное число.
...
Но по системе 'EPB' период определяется не верно.

Не помешало бы ещё описание алгоритма, по которому должен определяться этот самый период.
14 мар 13, 15:12    [14048025]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
el_sh
Member

Откуда:
Сообщений: 360
Гость333,

'EPB''2013-03-11' 10610
'EPB''2013-03-07' 10609
'EPB''2013-03-06' 10589
'EPB''2013-03-05' 10590
'EPB''2013-03-04' 10589
'EPB''2013-03-01'10590
'EPB''2013-02-28' 10589
'EPB''2013-02-27' 10589


должно преобразоваться в

'EPB''2013-02-27''2013-02-28'10589
'EPB''2013-03-01''2013-03-03'10590
'EPB''2013-03-04''2013-03-04'10589
'EPB''2013-03-05''2013-03-05'10590
'EPB''2013-03-06''2013-03-06'10589
'EPB''2013-03-07''2013-03-07'10609
'EPB''2013-03-11'NULL 10610
14 мар 13, 18:48    [14049312]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Гость333
Member

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

А по какому признаку определяется, что 8, 9 и 10 марта не должны входить ни в один период?
14 мар 13, 18:54    [14049335]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Maxx
Member [скрыт]

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

Тынц

Да и по ходу ,откуда у вас родилась строка
автор
'EPB' '2013-03-01' '2013-03-03' 10590

Для всех других интревалов у вас другой алгоритм
14 мар 13, 18:54    [14049338]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Задача нумбер 3
14 мар 13, 18:59    [14049351]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Не совсем понял, зачем тут генерация диапазона дат(как в "задаче нумбер 3"):
select name_file_usr, min(wdate) dt1, dt2, texts_r
from (
   select *,
      (select min(wdate)-1
         from @t 
         where name_file_usr=t.name_file_usr 
           and wdate>t.wdate 
           and texts_r!=t.texts_r
       ) dt2
   from @t t
   )g
group by name_file_usr,texts_r,dt2
order by name_file_usr,dt1
15 мар 13, 09:40    [14050896]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Cygapb-007,

Ваш скрипт даёт интервал
name_file_usr dt1 dt2 texts_r
EPB 2013-03-07 00:00:00.000 2013-03-10 00:00:00.000 10609


ТСу нужно, чтобы здесь было '2013-03-07' — '2013-03-07'.
15 мар 13, 10:47    [14051336]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Гость333
Cygapb-007,

Ваш скрипт даёт интервал
name_file_usr dt1 dt2 texts_r
EPB 2013-03-07 00:00:00.000 2013-03-10 00:00:00.000 10609


ТСу нужно, чтобы здесь было '2013-03-07' — '2013-03-07'.
Не уверен, что это так - потому и предложил этот вариант)
15 мар 13, 11:23    [14051614]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
скорее, у ТС ошибка копипаста) ЯТД)
15 мар 13, 11:25    [14051638]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
el_sh
Member

Откуда:
Сообщений: 360
Cygapb-007,

Вы правы, для последнего периода -
EPB 2013-03-07 00:00:00.000 2013-03-10 00:00:00.000 10609
EPB 2013-03-11 00:00:00.000 2013-03-11 00:00:00.000 10609


немного ошиблась

а этот селект
select name_file_usr, min(wdate) dt1, dt2, texts_r
from (
   select *,
      (select min(wdate)-1
         from @t 
         where name_file_usr=t.name_file_usr 
           and wdate>t.wdate 
           and texts_r!=t.texts_r
       ) dt2
   from @t t
   )g
group by name_file_usr,texts_r,dt2
order by name_file_usr,dt1


равносилен outer apply и не устраивает по скорости
15 мар 13, 12:48    [14052241]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
el_sh
равносилен outer apply и не устраивает по скорости
Немножко можно ускорить, но именно что немножко:
select name_file_usr, min(wdate) dt1, dt2, min(texts_r)texts_r
from (
   select *,
      (select min(wdate)-1
         from @t 
         where name_file_usr=t.name_file_usr 
           and wdate>t.wdate 
           and texts_r!=t.texts_r
       ) dt2
   from @t t
   )g
group by name_file_usr,dt2
order by name_file_usr,dt1
15 мар 13, 13:02    [14052339]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
el_sh
Member

Откуда:
Сообщений: 360
Гость333...
Не помешало бы ещё описание алгоритма, по которому должен определяться этот самый период.


Алгоритм прост - нужно уловить дату смены контрольного числа, от нее отнять один день - полученная дата будет датой окончания предыдущего периода.
По системе ppn все отрабатывает правильно, а на epb - нет, потому что контрольное число повторяется несколько раз за период с 01.03.2013 по 06.03.2013. Не могу это обойти, подскажите. Предложенный вариант с группировкой не устраивает по скорости -обрабатываемых данных несколько миллионов - запрос зависает минут на 40.. Дерево тоже не особо помогает..
15 мар 13, 14:54    [14053092]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
посмотрите
DECLARE @t TABLE(name_file_usr VARCHAR (50),wdate DATETIME,texts_r INT)
/* если будет таблица календарь, то будет лучше*/
DECLARE @c TABLE(dt DATETIME)

INSERT INTO @c(dt)
SELECT DateAdd(dd, ROW_NUMBER() OVER (ORDER BY number), '2012-09-20') FROM master.dbo.spt_values 
/*конец таблица календарь*/

INSERT INTO @t (
	name_file_usr,
	wdate,
	texts_r
) 
SELECT 'EPB',	'2013-03-12',	10611
UNION SELECT 'EPB','2013-03-11',      10610
UNION SELECT 'EPB','2013-03-07',	10609
UNION SELECT 'EPB','2013-03-06',	10589
UNION SELECT 'EPB','2013-03-05',	10590
UNION SELECT 'EPB','2013-03-04',	10589
UNION SELECT 'EPB','2013-03-01',	10590
UNION SELECT 'EPB','2013-02-28',	10589
UNION SELECT 'EPB','2013-02-27',	10589
UNION SELECT 'EPB','2013-02-26',	10588
UNION SELECT 'EPB','2013-02-25',	10586
UNION SELECT 'EPB','2013-02-22',	10586
UNION SELECT 'EPB','2013-02-21',	10584
UNION SELECT 'EPB','2013-02-20',	10583
UNION SELECT 'EPB','2013-02-19',	10582
UNION SELECT 'EPB','2013-02-18',	10581
UNION SELECT 'EPB','2013-02-15',	10432
UNION SELECT 'EPB','2013-02-14',	10432
UNION SELECT 'EPB','2013-02-13',	10432
UNION SELECT 'EPB','2013-02-12',	10432
UNION SELECT 'EPB','2013-02-11',	10430
UNION SELECT 'EPB','2013-02-08',	10428
UNION SELECT 'EPB','2013-02-07',	10426
UNION SELECT 'EPB','2013-02-06',	10426
UNION SELECT 'EPB','2013-02-05',	10421
UNION SELECT 'EPB','2013-02-04',	10417
UNION SELECT 'EPB','2013-02-01',	10416
UNION SELECT 'ppn','2013-03-07',47
UNION SELECT 'ppn','2013-02-28',45
UNION SELECT 'ppn','2013-02-21',45
UNION SELECT 'ppn','2013-02-14',45
UNION SELECT 'ppn','2013-02-07',45
UNION SELECT 'ppn','2013-01-31',44
UNION SELECT 'ppn','2013-01-24',44
UNION SELECT 'ppn','2013-01-17',44
UNION SELECT 'ppn','2013-01-10',44
UNION SELECT 'ppn','2013-01-08',44
UNION SELECT 'ppn','2012-12-27',44
UNION SELECT 'ppn','2012-12-20',44
UNION SELECT 'ppn','2012-12-13',43
UNION SELECT 'ppn','2012-12-06',43
UNION SELECT 'ppn','2012-11-29',43
UNION SELECT 'ppn','2012-11-22',43
UNION SELECT 'ppn','2012-11-15',42
UNION SELECT 'ppn','2012-11-08',42
UNION SELECT 'ppn','2012-11-01',42
UNION SELECT 'ppn','2012-10-25',42
UNION SELECT 'ppn','2012-10-18',42
UNION SELECT 'ppn','2012-10-11',41
UNION SELECT 'ppn','2012-10-04',41
UNION SELECT 'ppn','2012-09-27',41
UNION SELECT 'ppn','2012-09-20',40



;
With
	c1(dt) As (
		Select
			c.dt
		From
			@c c
		Where
			(Select Min(t_.wdate) From @t t_)  <= c.dt  
			And c.dt <= (Select Max(t_.wdate) From @t t_) 
		Except
		Select
			t_.wdate
		From
			@t t_
	),
	c2 As (
		Select 
			Min(x.dt) - 1 mind
			, Max(x.dt) maxd
		From (
			Select
				c1.dt
				, c1.dt + ROW_NUMBER() Over (Order By c1.dt Desc) grp
			From
				c1 c1) x 
		Group By
			x.grp
	)
Select
	x.name_file_usr
	, x.texts_r
	, Min(x.wdate) d_begin
	, Case When x.grp = 0 Then Null Else Max(Coalesce(c2.maxd, x.wdate)) End d_end
From (
	Select 
		t.name_file_usr
		, t.texts_r
		, t.wdate
		, DENSE_RANK() Over (Partition By t.name_file_usr Order By t.wdate Desc) - DENSE_RANK() Over (Partition By t.name_file_usr, t.texts_r Order By t.wdate Desc) grp
	From 
		@t t) x
Left Join c2 c2 On c2.mind = x.wdate		
Group By
	x.name_file_usr
	, x.texts_r
	, x.grp
Order By
	x.name_file_usr
	, 3


если будет таблица календарь, то будет лучше
15 мар 13, 15:05    [14053181]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
все же пока на сгенерированных данных самый быстрый вариант этот:
if OBJECT_ID('tempdb..#temp') is not null drop table #temp
select top (1) with ties *
into #temp
   from #table 
   order by name_file_usr, row_number() over (partition by name_file_usr,texts_r order by wdate)

select name_file_usr, min(wdate) dt1, dt2, min(texts_r)texts_r
from (
   select *,
      (select min(wdate)-1
         from #temp
         where name_file_usr=t.name_file_usr 
           and wdate>t.wdate 
           and texts_r!=t.texts_r
       ) dt2
   from #temp t
   )g
group by name_file_usr,dt2
order by name_file_usr,dt1
весь вопрос в адекватности сгенерированных данных :)
+ генерация тестовых данных
-- if OBJECT_ID('tempdb..#table') is not null drop table #table
if OBJECT_ID('tempdb..#table') is null begin 
   create table #table (name_file_usr VARCHAR (50),wdate DATETIME,texts_r INT)
   ;with 
   files as(select * from (values
      ('QWE'),('WER'),('ERT'),('RTY'),('TYU'),('YUI'),('UIO'),('IOP'),('OP['),('P[]'),('ASD'),('SDF'),('DFG'),('FGH'),('GHJ'),('HJK'),('JKL'),('KLP'),('LPO'),
      ('ZXC'),('XCV'),('CVB'),('VBN'),('BNM'),('NMK'),('POI'),('OIU'),('IUY'),('UYT'),('YTR'),('TRE'),('REW'),('EWQ'),('WQA'),('QAS'),('LKJ'),('KJH'),('HGF'),
      ('JHG'),('GFD'),('FDS'),('SAQ'),('AQW'),('MNB'),('NBV'),('BVC'),('VCX'),('CXZ'),('XZA'),('ZAS'),('ЙЦУ'),('ЦУК'),('УКЕ'),('КЕН'),('ЕНГ'),('НГШ'),('ГШЩ'),
      ('ШЩЗ'),('ЩЗХ'),('ЗХЪ'),('ХЪЭ'),('ЪЭЖ'),('ФЫВ'),('ЫВА'),('ВАП'),('АПР'),('ПРО'),('РОЛ'),('ОЛД'),('ЛДЖ'),('ДЖЭ'),('ЖЭЪ'),('ЭЪХ'),('ЯЧС'),('ЧСМ'),('СМИ'),
      ('МИТ'),('ИТЬ'),('ТЬБ'),('ЬБЮ'),('БЮЖ'),('ЮЖЭ'),('QWER'),('WERT'),('ERTY'),('RTYU'),('TYUI'),('YUIO'),('UIOP'),('IOPL'),('OPLK'),('PLKJ'),('LKJH'),('KJHG'),
      ('JHGF'),('HGFD'),('GFDS'),('FDSA'),('DSAZ'),('SAZX'),('AZXC'),('ZXCV'),('XCVB'),('CVBN'),('VBNM'),('BNML'),('NLKJ')
      )t(name_file_usr)),
   dates as (
      select name_file_usr, GETDATE() wdate, rnd%100000 texts_r
         from files
         cross apply (select ABS(CHECKSUM(NEWID()))rnd ) r
      union all
      select d.name_file_usr, DATEADD(D,rnd%8,d.wdate), case when rnd%13<10 then rnd%100000 else d.texts_r end
         from dates d
         cross apply (select ABS(CHECKSUM(NEWID()))rnd ) r
         where d.wdate<GETDATE()+1000
      )
   INSERT INTO #table(name_file_usr,wdate,texts_r) 
      select * from dates
      order by name_file_usr, wdate
   option(maxrecursion 0)
   
   select * from #table order by name_file_usr, wdate
end
15 мар 13, 15:26    [14053355]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
опс... меня терзают смутные сомненья в правильности скрипта... скорее всего там ошибка, но проверять сейчас нет времени - нагрузили работой...
15 мар 13, 15:33    [14053411]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
el_sh
Member

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

спасибо, попробую - отпишусь
15 мар 13, 17:30    [14054295]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
добавьте РК(name_file_usr, wdate) - и будет вам щастье
16 мар 13, 13:56    [14056956]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3058
el_sh
HandKot,

спасибо, попробую - отпишусь


не посмотрел, что у Вас для разных name_file_usr времена пересекаются
поэтому в моем скрипте будет ошибка
тогда пока берите этот кусок
Select
	x.name_file_usr
	, x.texts_r
	, Min(x.wdate) d_begin
	, Max(x.wdate) End d_end
From (
	Select 
		t.name_file_usr
		, t.texts_r
		, t.wdate
		, DENSE_RANK() Over (Partition By t.name_file_usr Order By t.wdate Desc) - DENSE_RANK() Over (Partition By t.name_file_usr, t.texts_r Order By t.wdate Desc) grp
	From 
		@t t) x

для каждой полученной группы получите номер и приджойните саму же по номеру, чтобы получить нужную границу
18 мар 13, 08:16    [14060877]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
el_sh
Member

Откуда:
Сообщений: 360
Cygapb-007
добавьте РК(name_file_usr, wdate) - и будет вам щастье


Не, счастья не будет.. PK реальной таблицы - identity, есть уникальный индекс на указанные поля с включенным столбцом контрольного числа
18 мар 13, 18:07    [14063971]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
el_sh
Cygapb-007
добавьте РК(name_file_usr, wdate) - и будет вам щастье


Не, счастья не будет.. PK реальной таблицы - identity, есть уникальный индекс на указанные поля с включенным столбцом контрольного числа
Вот планы выполнения:
+ set statistics profiler on
Без индексов:
;WITH VTable AS (SELECT * FROM (VALUES 
	(21967,1,'select name_file_usr, min(wdate) dt1, dt2, min(texts_r)texts_r
from (
   select *,
      (select min(wdate)-1
         from #table 
         where name_file_usr=t.name_file_usr 
           and wdate>t.wdate 
           and texts_r!=t.texts_r
       ) dt2
   from #table t
   )g
group by name_file_usr,dt2
order by name_file_usr,dt1',1,1,0,NULL,NULL,NULL,NULL,1173.299,NULL,NULL,NULL,14.24984,NULL,NULL,'SELECT',0,NULL),
	(21967,1,'  |--Parallelism(Gather Streams, ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC))',1,2,1,'Parallelism','Gather Streams','ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC)',NULL,1173.299,0,0.0396349,86,14.24984,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011], [Expr1012]',NULL,'PLAN_ROW',1,1),
	(21967,4,'       |--Sort(ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC))',1,3,2,'Sort','Sort','ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC)',NULL,1173.299,0.005630631,0.009381473,86,14.21021,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011], [Expr1012]',NULL,'PLAN_ROW',1,1),
	(0,0,'            |--Compute Scalar(DEFINE:([Expr1012]=tertiary_weights([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr])))',1,4,3,'Compute Scalar','Compute Scalar','DEFINE:([Expr1012]=tertiary_weights([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr]))','[Expr1012]=tertiary_weights([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr])',1173.299,0,5.866495E-05,86,14.1952,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011], [Expr1012]',NULL,'PLAN_ROW',1,1),
	(21967,4,'                 |--Stream Aggregate(GROUP BY:([Expr1009], [t].[name_file_usr]) DEFINE:([Expr1010]=MIN([partialagg1014]), [Expr1011]=MIN([partialagg1015])))',1,5,4,'Stream Aggregate','Aggregate','GROUP BY:([Expr1009], [t].[name_file_usr])','[Expr1010]=MIN([partialagg1014]), [Expr1011]=MIN([partialagg1015])',1173.299,0,0.0009973041,34,14.19514,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011]',NULL,'PLAN_ROW',1,1),
	(22004,4,'                      |--Sort(ORDER BY:([Expr1009] ASC, [t].[name_file_usr] ASC))',1,6,5,'Sort','Sort','ORDER BY:([Expr1009] ASC, [t].[name_file_usr] ASC)',NULL,2346.598,0.005630631,0.02054322,34,14.19414,'[t].[name_file_usr], [Expr1009], [partialagg1014], [partialagg1015]',NULL,'PLAN_ROW',1,1),
	(22004,4,'                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1009], [t].[name_file_usr]))',1,7,6,'Parallelism','Repartition Streams','PARTITION COLUMNS:([Expr1009], [t].[name_file_usr])',NULL,2346.598,0,0.03673568,34,14.16797,'[t].[name_file_usr], [Expr1009], [partialagg1014], [partialagg1015]',NULL,'PLAN_ROW',1,1),
	(22004,4,'                                |--Hash Match(Partial Aggregate, HASH:([t].[name_file_usr], [Expr1009]), RESIDUAL:([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] = [tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [Expr1009] = [Expr1009]) DEFINE:([partialagg1014]=MIN([tempdb].[dbo].[#table].[wdate] as [t].[wdate]), [partialagg1015]=MIN([tempdb].[dbo].[#table].[texts_r] as [t].[texts_r])))',1,8,7,'Hash Match','Partial Aggregate','HASH:([t].[name_file_usr], [Expr1009]), RESIDUAL:([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] = [tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [Expr1009] = [Expr1009]), DEFINE:([partialagg1014]=MIN([tempdb].[dbo].[#table].[wdate] as [t].[wdate]), [partialagg1015]=MIN([tempdb].[dbo].[#table].[texts_r] as [t].[texts_r]))','[partialagg1014]=MIN([tempdb].[dbo].[#table].[wdate] as [t].[wdate]), [partialagg1015]=MIN([tempdb].[dbo].[#table].[texts_r] as [t].[texts_r])',2346.598,0,0.1795284,34,14.13123,'[t].[name_file_usr], [Expr1009], [partialagg1014], [partialagg1015]',NULL,'PLAN_ROW',1,1),
	(0,0,'                                     |--Compute Scalar(DEFINE:([Expr1009]=[Expr1007]-''1900-01-02 00:00:00.000''))',1,9,8,'Compute Scalar','Compute Scalar','DEFINE:([Expr1009]=[Expr1007]-''1900-01-02 00:00:00.000'')','[Expr1009]=[Expr1007]-''1900-01-02 00:00:00.000''',30934,0,0.0015467,34,13.9517,'[t].[name_file_usr], [t].[wdate], [t].[texts_r], [Expr1009]',NULL,'PLAN_ROW',1,1),
	(30934,4,'                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[name_file_usr], [t].[wdate], [t].[texts_r]))',1,10,9,'Nested Loops','Inner Join','OUTER REFERENCES:([t].[name_file_usr], [t].[wdate], [t].[texts_r])',NULL,30934,0,0.06465206,34,13.95016,'[t].[name_file_usr], [t].[wdate], [t].[texts_r], [Expr1007]',NULL,'PLAN_ROW',1,1),
	(30934,4,'                                               |--Table Scan(OBJECT:([tempdb].[dbo].[#table] AS [t]))',1,11,10,'Table Scan','Table Scan','OBJECT:([tempdb].[dbo].[#table] AS [t])','[t].[name_file_usr], [t].[wdate], [t].[texts_r]',30934,0.08987017,0.01705295,26,0.1069231,'[t].[name_file_usr], [t].[wdate], [t].[texts_r]',NULL,'PLAN_ROW',1,1),
	(30934,30934,'                                               |--Stream Aggregate(DEFINE:([Expr1007]=MIN([tempdb].[dbo].[#table].[wdate])))',1,12,10,'Stream Aggregate','Aggregate',NULL,'[Expr1007]=MIN([tempdb].[dbo].[#table].[wdate])',1,0,4.42388E-06,15,13.77858,'[Expr1007]',NULL,'PLAN_ROW',1,30934),
	(4449455,30934,'                                                    |--Filter(WHERE:([tempdb].[dbo].[#table].[texts_r]<>[tempdb].[dbo].[#table].[texts_r] as [t].[texts_r]))',1,13,12,'Filter','Filter','WHERE:([tempdb].[dbo].[#table].[texts_r]<>[tempdb].[dbo].[#table].[texts_r] as [t].[texts_r])',NULL,6.539801,0,4.16308E-05,15,13.64173,'[tempdb].[dbo].[#table].[wdate]',NULL,'PLAN_ROW',1,30934),
	(4457958,30934,'                                                         |--Index Spool(SEEK:([tempdb].[dbo].[#table].[name_file_usr]=[tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [tempdb].[dbo].[#table].[wdate] > [tempdb].[dbo].[#table].[wdate] as [t].[wdate]))',1,14,13,'Index Spool','Eager Spool','SEEK:([tempdb].[dbo].[#table].[name_file_usr]=[tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [tempdb].[dbo].[#table].[wdate] > [tempdb].[dbo].[#table].[wdate] as [t].[wdate])',NULL,86.73084,0.8915034,0.0312864,19,12.35393,'[tempdb].[dbo].[#table].[wdate], [tempdb].[dbo].[#table].[texts_r]',NULL,'PLAN_ROW',1,30934),
	(30934,1,'                                                              |--Table Scan(OBJECT:([tempdb].[dbo].[#table]))',1,15,14,'Table Scan','Table Scan','OBJECT:([tempdb].[dbo].[#table])','[tempdb].[dbo].[#table].[name_file_usr], [tempdb].[dbo].[#table].[wdate], [tempdb].[dbo].[#table].[texts_r]',30934,0.08979166,0.0341844,26,0.1239761,'[tempdb].[dbo].[#table].[name_file_usr], [tempdb].[dbo].[#table].[wdate], [tempdb].[dbo].[#table].[texts_r]',NULL,'PLAN_ROW',1,1)
	) AS vtable([Rows],[Executes],[StmtText],[StmtId],[NodeId],[Parent],[PhysicalOp],[LogicalOp],[Argument],[DefinedValues],[EstimateRows],[EstimateIO],[EstimateCPU],[AvgRowSize],[TotalSubtreeCost],[OutputList],[Warnings],[Type],[Parallel],[EstimateExecutions]))
select * from VTable
То же, с кластерным индексом
;WITH VTable AS (SELECT * FROM (VALUES 
	(21967,1,'select name_file_usr, min(wdate) dt1, dt2, min(texts_r)texts_r
from (
   select *,
      (select min(wdate)-1
         from #table 
         where name_file_usr=t.name_file_usr 
           and wdate>t.wdate 
           and texts_r!=t.texts_r
       ) dt2
   from #table t
   )g
group by name_file_usr,dt2
order by name_file_usr,dt1',1,1,0,NULL,NULL,NULL,NULL,1173.299,NULL,NULL,NULL,6.236303,NULL,NULL,'SELECT',0,NULL),
	(21967,1,'  |--Parallelism(Gather Streams, ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC))',1,2,1,'Parallelism','Gather Streams','ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC)',NULL,1173.299,0,0.0396349,86,6.236303,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011], [Expr1012]',NULL,'PLAN_ROW',1,1),
	(21967,4,'       |--Sort(ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC))',1,3,2,'Sort','Sort','ORDER BY:([t].[name_file_usr] ASC, [Expr1010] ASC, [Expr1012] ASC)',NULL,1173.299,0.005630631,0.009381473,86,6.196669,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011], [Expr1012]',NULL,'PLAN_ROW',1,1),
	(0,0,'            |--Compute Scalar(DEFINE:([Expr1012]=tertiary_weights([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr])))',1,4,3,'Compute Scalar','Compute Scalar','DEFINE:([Expr1012]=tertiary_weights([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr]))','[Expr1012]=tertiary_weights([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr])',1173.299,0,5.866495E-05,86,6.181656,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011], [Expr1012]',NULL,'PLAN_ROW',1,1),
	(21967,4,'                 |--Stream Aggregate(GROUP BY:([Expr1009], [t].[name_file_usr]) DEFINE:([Expr1010]=MIN([partialagg1014]), [Expr1011]=MIN([partialagg1015])))',1,5,4,'Stream Aggregate','Aggregate','GROUP BY:([Expr1009], [t].[name_file_usr])','[Expr1010]=MIN([partialagg1014]), [Expr1011]=MIN([partialagg1015])',1173.299,0,0.0009973041,34,6.181598,'[t].[name_file_usr], [Expr1009], [Expr1010], [Expr1011]',NULL,'PLAN_ROW',1,1),
	(22004,4,'                      |--Sort(ORDER BY:([Expr1009] ASC, [t].[name_file_usr] ASC))',1,6,5,'Sort','Sort','ORDER BY:([Expr1009] ASC, [t].[name_file_usr] ASC)',NULL,2346.598,0.005630631,0.02054322,34,6.180601,'[t].[name_file_usr], [Expr1009], [partialagg1014], [partialagg1015]',NULL,'PLAN_ROW',1,1),
	(22004,4,'                           |--Parallelism(Repartition Streams, Hash Partitioning, PARTITION COLUMNS:([Expr1009], [t].[name_file_usr]))',1,7,6,'Parallelism','Repartition Streams','PARTITION COLUMNS:([Expr1009], [t].[name_file_usr])',NULL,2346.598,0,0.03673568,34,6.154427,'[t].[name_file_usr], [Expr1009], [partialagg1014], [partialagg1015]',NULL,'PLAN_ROW',1,1),
	(22004,4,'                                |--Hash Match(Partial Aggregate, HASH:([t].[name_file_usr], [Expr1009]), RESIDUAL:([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] = [tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [Expr1009] = [Expr1009]) DEFINE:([partialagg1014]=MIN([tempdb].[dbo].[#table].[wdate] as [t].[wdate]), [partialagg1015]=MIN([tempdb].[dbo].[#table].[texts_r] as [t].[texts_r])))',1,8,7,'Hash Match','Partial Aggregate','HASH:([t].[name_file_usr], [Expr1009]), RESIDUAL:([tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] = [tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [Expr1009] = [Expr1009]), DEFINE:([partialagg1014]=MIN([tempdb].[dbo].[#table].[wdate] as [t].[wdate]), [partialagg1015]=MIN([tempdb].[dbo].[#table].[texts_r] as [t].[texts_r]))','[partialagg1014]=MIN([tempdb].[dbo].[#table].[wdate] as [t].[wdate]), [partialagg1015]=MIN([tempdb].[dbo].[#table].[texts_r] as [t].[texts_r])',2346.598,0,0.1795284,34,6.117691,'[t].[name_file_usr], [Expr1009], [partialagg1014], [partialagg1015]',NULL,'PLAN_ROW',1,1),
	(0,0,'                                     |--Compute Scalar(DEFINE:([Expr1009]=[Expr1007]-''1900-01-02 00:00:00.000''))',1,9,8,'Compute Scalar','Compute Scalar','DEFINE:([Expr1009]=[Expr1007]-''1900-01-02 00:00:00.000'')','[Expr1009]=[Expr1007]-''1900-01-02 00:00:00.000''',30934,0,0.0015467,34,5.938163,'[t].[name_file_usr], [t].[wdate], [t].[texts_r], [Expr1009]',NULL,'PLAN_ROW',1,1),
	(30934,4,'                                          |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[name_file_usr], [t].[wdate], [t].[texts_r], [Expr1016]) WITH UNORDERED PREFETCH)',1,10,9,'Nested Loops','Inner Join','OUTER REFERENCES:([t].[name_file_usr], [t].[wdate], [t].[texts_r], [Expr1016]) WITH UNORDERED PREFETCH',NULL,30934,0,0.06465206,34,5.936616,'[t].[name_file_usr], [t].[wdate], [t].[texts_r], [Expr1007]',NULL,'PLAN_ROW',1,1),
	(30934,4,'                                               |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#table] AS [t]))',1,12,10,'Clustered Index Scan','Clustered Index Scan','OBJECT:([tempdb].[dbo].[#table] AS [t])','[t].[name_file_usr], [t].[wdate], [t].[texts_r]',30934,0.08979166,0.0170922,26,0.1068839,'[t].[name_file_usr], [t].[wdate], [t].[texts_r]',NULL,'PLAN_ROW',1,1),
	(30934,30934,'                                               |--Stream Aggregate(DEFINE:([Expr1007]=MIN([tempdb].[dbo].[#table].[wdate])))',1,13,10,'Stream Aggregate','Aggregate',NULL,'[Expr1007]=MIN([tempdb].[dbo].[#table].[wdate])',1,0,1.1E-06,15,5.76508,'[Expr1007]',NULL,'PLAN_ROW',1,30934),
	(30797,30934,'                                                    |--Top(TOP EXPRESSION:((1)))',1,14,13,'Top','Top','TOP EXPRESSION:((1))',NULL,1,0,1E-07,15,5.731052,'[tempdb].[dbo].[#table].[wdate]',NULL,'PLAN_ROW',1,30934),
	(30797,30934,'                                                         |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#table]), SEEK:([tempdb].[dbo].[#table].[name_file_usr]=[tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [tempdb].[dbo].[#table].[wdate] > [tempdb].[dbo].[#table].[wdate] as [t].[wdate]),  WHERE:([tempdb].[dbo].[#table].[texts_r]<[tempdb].[dbo].[#table].[texts_r] as [t].[texts_r] OR [tempdb].[dbo].[#table].[texts_r]>[tempdb].[dbo].[#table].[texts_r] as [t].[texts_r]) ORDERED FORWARD)',1,15,14,'Clustered Index Seek','Clustered Index Seek','OBJECT:([tempdb].[dbo].[#table]), SEEK:([tempdb].[dbo].[#table].[name_file_usr]=[tempdb].[dbo].[#table].[name_file_usr] as [t].[name_file_usr] AND [tempdb].[dbo].[#table].[wdate] > [tempdb].[dbo].[#table].[wdate] as [t].[wdate]),  WHERE:([tempdb].[dbo].[#table].[texts_r]<[tempdb].[dbo].[#table].[texts_r] as [t].[texts_r] OR [tempdb].[dbo].[#table].[texts_r]>[tempdb].[dbo].[#table].[texts_r] as [t].[texts_r]) ORDERED FORWARD','[Uniq1005], [tempdb].[dbo].[#table].[wdate], [tempdb].[dbo].[#table].[texts_r]',1,0.003125,0.0002524039,23,5.366942,'[Uniq1005], [tempdb].[dbo].[#table].[wdate], [tempdb].[dbo].[#table].[texts_r]',NULL,'PLAN_ROW',1,30934)
	) AS vtable([Rows],[Executes],[StmtText],[StmtId],[NodeId],[Parent],[PhysicalOp],[LogicalOp],[Argument],[DefinedValues],[EstimateRows],[EstimateIO],[EstimateCPU],[AvgRowSize],[TotalSubtreeCost],[OutputList],[Warnings],[Type],[Parallel],[EstimateExecutions]))
select * from VTable
4.4М строк (TotalSubtreeCost=14.2498400) сократились до 31К (до одного считывания, TotalSubtreeCost=6.2363030) после
CREATE CLUSTERED INDEX #table_idx ON #table(name_file_usr ASC,wdate ASC)


К сообщению приложен файл. Размер - 15Kb
18 мар 13, 22:20    [14064646]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
ээ... забыл аттач убрать:)
18 мар 13, 22:21    [14064650]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Тогда уж и второй скрин, с кластерным индексом выложу...

К сообщению приложен файл. Размер - 15Kb
18 мар 13, 22:24    [14064660]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
el_sh
Member

Откуда:
Сообщений: 360
Cygapb-007,

кластерный индекс уже есть на PK identity
Есть, как и говорила UNIQUE NONCLUSTERED INDEX (name_file_usr ASC,wdate ASC) INCLUDE ( texts_r)
Но в плане выполнения по нему идет Scan
19 мар 13, 12:09    [14066441]     Ответить | Цитировать Сообщить модератору
 Re: Не верно определяется период  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
Экзотика:
with a as
( 
 select
  *,
  row_number() over (partition by name_file_usr order by wdate) as n, 
  row_number() over (partition by name_file_usr order by wdate desc) as n_rev, 
  row_number() over (partition by name_file_usr order by wdate) - 
  row_number() over (partition by name_file_usr, texts_r order by wdate) as grp
 from
  @t
),
b as
(
 select
  *,
  row_number() over (partition by name_file_usr, texts_r, grp order by wdate) as n_grp,
  case when n % 2 = 1 then max(wdate) over (partition by name_file_usr, n / 2) else max(wdate) over (partition by name_file_usr, (n + 1) / 2) end as d1,
  case when n_rev = 1 then null else dateadd(day, -1, case when n % 2 = 1 then max(wdate) over (partition by name_file_usr, (n + 1) / 2) else max(wdate) over (partition by name_file_usr, n / 2) end) end as d2
 from
  a
),
c as
(
 select
  name_file_usr, texts_r, n_grp,
  min(d1) over (partition by name_file_usr, texts_r, grp) as d_start,
  max(d2) over (partition by name_file_usr, texts_r, grp) as d_end 
 from
  b
)
select
 name_file_usr, texts_r, d_start, d_end
from
 c
where
 n_grp = 1
order by
 name_file_usr, d_start;
20 мар 13, 12:27    [14071563]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить