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

Откуда:
Сообщений: 132
Добрый день,
есть вот такие данные (view), sql server 2014:

 Declare @YourTable table ([Event] varchar(100),[Start] DateTime,[End] DateTime, [Tag] varchar(25))
  Insert Into @YourTable values
  ('10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:44:05.000','9/9/16 10:48:08.000','Big'),
  ('10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:44:10.000','9/9/16 10:49:40.000','Big'),
  ('10PIC700422.PV 10-PSV-700073A 10-PSV-700073B','9/9/16 10:50:03.000','9/9/16 10:51:04.000','Small'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 12:51:07.000','4/4/16 13:58:09.000','Big'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:04:04.000','4/4/16 14:29:00.000','Small'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 14:51:02.000','4/4/16 14:58:00.000','Big'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 15:04:06.000','4/4/16 15:29:08.000','Small'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4-4-16 15:45:08.000','4-4-16 15:55:09.000','Big'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4-4-16 16:22:08.000','4-4-16 16:40:09.000','Big'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 16:50:04.000','4/4/16 16:55:00.000','Small'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 17:05:02.000','4/4/16 17:20:00.000','Big'),
  ('11PIC41010.PV 11-PSV-401002A 11-PSV-401002B','4/4/16 17:22:06.000','4/4/16 17:29:08.000','Small'),
  ('11PIC41010.PV 11-PSV-401002W 11-PSV-401002D','4/4/16 16:04:01.000','4/4/16 16:45:00.000','Big');


Я изпользую следующий запрос для получения необходимого результата, сгруппировать по [Event] и упорядочить по [Start] и
изменять в группе 'Big' на 'Small' где 'Small' идет после 'Big':

 Select [Event]
  ,[Start]
  ,[End] 
  ,[Tag]
  ,[Tag_new] = case when Tag='Big' and 'Small' = Lead(Tag,1,Tag) over (Partition By Event Order By Start) then 'Small' else tag end
  From  @YourTable

                                       Event    Start                   End                     Tag  Tag_new
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B    2016-09-09 10:44:05.000 2016-09-09 10:48:08.000 Big   Big
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B    2016-09-09 10:44:10.000 2016-09-09 10:49:40.000 Big   Small
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B    2016-09-09 10:50:03.000 2016-09-09 10:51:04.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 12:51:07.000 2016-04-04 13:58:09.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 14:04:04.000 2016-04-04 14:29:00.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 14:51:02.000 2016-04-04 14:58:00.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 15:04:06.000 2016-04-04 15:29:08.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 15:45:08.000 2016-04-04 15:55:09.000 Big   Big
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 16:22:08.000 2016-04-04 16:40:09.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 16:50:04.000 2016-04-04 16:55:00.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 17:05:02.000 2016-04-04 17:20:00.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 17:22:06.000 2016-04-04 17:29:08.000 Small Small
11PIC41010.PV 11-PSV-401002W 11-PSV-401002D     2016-04-04 16:04:01.000 2016-04-04 16:45:00.000 Big   Big


Я хотел бы добавить еще одно уловие, при появлений следующей последовательности(в группе) в колоке Tag нужно добавить
строку после Small и перед Big с таким же Event Start Tag_new как у Big:

Small
Big
Small



хотел бы получить результат ниже:

                       Event                    Start                   End                     Tag  Tag_new
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B    2016-09-09 10:44:05.000 2016-09-09 10:48:08.000 Big   Big
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B    2016-09-09 10:44:10.000 2016-09-09 10:49:40.000 Big   Small
10PIC700422.PV 10-PSV-700073A 10-PSV-700073B    2016-09-09 10:50:03.000 2016-09-09 10:51:04.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 12:51:07.000 2016-04-04 13:58:09.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 14:04:04.000 2016-04-04 14:29:00.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 14:51:02.000                               Bad
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 14:51:02.000 2016-04-04 14:58:00.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 15:04:06.000 2016-04-04 15:29:08.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 15:45:08.000 2016-04-04 15:55:09.000 Big   Big
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 16:22:08.000 2016-04-04 16:40:09.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 16:50:04.000 2016-04-04 16:55:00.000 Small Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 17:05:02.000                               Bad
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 17:05:02.000 2016-04-04 17:20:00.000 Big   Small
11PIC41010.PV 11-PSV-401002A 11-PSV-401002B     2016-04-04 17:22:06.000 2016-04-04 17:29:08.000 Small Small
11PIC41010.PV 11-PSV-401002W 11-PSV-401002D     2016-04-04 16:04:01.000 2016-04-04 16:45:00.000 Big   Big


Заранее благодарю за помощь.
18 окт 16, 12:02    [19793938]     Ответить | Цитировать Сообщить модератору
 Re: Добавление строк при дополнительном условий  [new]
sakot
Member

Откуда:
Сообщений: 132
извиняюсь, там Tag_new будет "Bad" для новой строки.
18 окт 16, 12:05    [19793952]     Ответить | Цитировать Сообщить модератору
 Re: Добавление строк при дополнительном условий  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
sakot,

Lead offset = 2 если правильно понимаю
18 окт 16, 12:09    [19793977]     Ответить | Цитировать Сообщить модератору
 Re: Добавление строк при дополнительном условий  [new]
sakot
Member

Откуда:
Сообщений: 132
похоже это может быть решением проблемы

WITH YourQueryEnhanced AS
(
    Select [Event]
    ,[Start]
    ,[End] 
    ,[Tag]
    ,[Tag_new] = case when Tag='Big' and 'Small' = Lead(Tag,1,Tag) over (Partition By [Event] Order By Start) then 'Small' else tag end
    ,ROW_NUMBER() OVER(Partition By [Event] Order By Start) AS SortInx --You should put a secure ORDER BY here!!!
    ,0 AS InnerSortInx
    From  @YourTable
), BadMarked AS
(
 SELECT *
        ,CASE WHEN LAG([Tag],1) OVER(Order By SortInx)='Small' 
                AND LAG([Tag],2) OVER(Order By SortInx)='Big' 
                AND LAG([Tag],3) OVER(Order By SortInx)='Big' THEN 1 ELSE 0 END AS BadSequence 
    FROM YourQueryEnhanced 
    ) 
	select * from
	(
	SELECT *
    FROM BadMarked
	UNION ALL 
	    SELECT [Event],DATEADD(s, -1, [Start]) [Start],[End],'Bad','Bad',SortInx-1,1,2 
    FROM BadMarked 
    WHERE BadMarked.BadSequence=1
	) a order by [Event]
    ,[Start]
18 окт 16, 14:58    [19795003]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить