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

Откуда: Где где .. в караганде
Сообщений: 538
 		Добрый день!

Есть таблица примерно такого вида:

CREATE TABLE [dbo].[SystemCHk_log](
[id] [int] IDENTITY(1,1) NOT NULL,
[idSystem] [int] NULL,
[StatusError] [bit] NULL, -- статус включен выключен
[dtCreate] [datetime] NULL
)

Содержит данные о состоянии систем (включена/выключена):
idsystem;StatusError;dtCreate
1;1;2018-02-22 16:13:42.740
1;0;2018-02-22 16:13:59.847
1;1;2018-02-22 16:14:43.620
1;0;2018-02-22 16:15:02.080
1;0;2018-02-22 16:30:00.667
1;0;2018-02-22 18:30:00.340
1;0;2018-02-22 19:00:00.613
1;1;2018-02-22 20:14:43.620


Необходимо получить выборку в которой будет отражено начало изменения статуса и его завершение:
idSystem;StatusError;dtStart;dtEnd
1; 1; 2018-02-22 16:13:42.740; 2018-02-22 16:13:59.847
1; 0; 2018-02-22 16:13:59.847; 2018-02-22 16:14:43.620
1; 1; 2018-02-22 16:14:43.620; 2018-02-22 16:15:02.080
1; 0; 2018-02-22 16:15:02.080; 2018-02-22 20:14:43.620
1; 1; 2018-02-22 20:14:43.620;


Сделал так:

;WITH tq AS 
(
SELECT 
l.[idSystem],l.[StatusError],l.[dtCreate] as dtstart
,isnull(
   (select top 1 ll.dtCreate from [SystemCHk_log] ll 
        where ll.idSystem=l.idSystem 
           and ll.dtCreate>l.dtCreate 
           and  l.[StatusError]<>ll.[StatusError] 
    order by ll.dtCreate)
,getdate())  as dtend
 FROM [analysis].[dbo].[SystemCHk_log] l
 )
 
SELECT dSystem, StatusError, min(dtstart) as dtstart, dtend
FROM tq 
GROUP BY idSystem, StatusError, dtend 
ORDER BY idSystem, dtend


Что то кажется я туплю....
Можете подсказать другие более интересные решения?
23 фев 18, 11:57    [21213507]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты запроса, без использования курсора  [new]
iap
Member

Откуда: Москва
Сообщений: 47063
 
WITH T AS(SELECT * FROM
(VALUES
  (1,1,CAST('2018-02-22T16:13:42.740'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:13:59.847'AS DATETIME))
 ,(1,1,CAST('2018-02-22T16:14:43.620'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:15:02.080'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:30:00.667'AS DATETIME))
 ,(1,0,CAST('2018-02-22T18:30:00.340'AS DATETIME))
 ,(1,0,CAST('2018-02-22T19:00:00.613'AS DATETIME))
 ,(1,1,CAST('2018-02-22T20:14:43.620'AS DATETIME))
 )t(idsystem,StatusError,dtCreate)
)
,G AS(SELECT N=ROW_NUMBER()OVER(PARTITION BY idsystem ORDER BY dtCreate)-ROW_NUMBER()OVER(PARTITION BY idsystem,StatusError ORDER BY dtCreate),* FROM T)
,S AS(SELECT idsystem,StatusError,dtStart=MIN(dtCreate)FROM G GROUP BY idsystem,N,StatusError)
SELECT idsystem,StatusError,dtStart,dtEnd=LEAD(dtStart)OVER(PARTITION BY idsystem ORDER BY dtStart)
FROM S
ORDER BY idsystem,dtStart;
23 фев 18, 12:45    [21213593]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты запроса, без использования курсора  [new]
mr.dfox
Member

Откуда: Где где .. в караганде
Сообщений: 538
 		Да забыл - MS SQL 2008
23 фев 18, 12:49    [21213600]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты запроса, без использования курсора  [new]
mr.dfox
Member

Откуда: Где где .. в караганде
Сообщений: 538
iap
WITH T AS(SELECT * FROM
(VALUES
  (1,1,CAST('2018-02-22T16:13:42.740'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:13:59.847'AS DATETIME))
 ,(1,1,CAST('2018-02-22T16:14:43.620'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:15:02.080'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:30:00.667'AS DATETIME))
 ,(1,0,CAST('2018-02-22T18:30:00.340'AS DATETIME))
 ,(1,0,CAST('2018-02-22T19:00:00.613'AS DATETIME))
 ,(1,1,CAST('2018-02-22T20:14:43.620'AS DATETIME))
 )t(idsystem,StatusError,dtCreate)
)
,G AS(SELECT N=ROW_NUMBER()OVER(PARTITION BY idsystem ORDER BY dtCreate)-ROW_NUMBER()OVER(PARTITION BY idsystem,StatusError ORDER BY dtCreate),* FROM T)
,S AS(SELECT idsystem,StatusError,dtStart=MIN(dtCreate)FROM G GROUP BY idsystem,N,StatusError)
SELECT idsystem,StatusError,dtStart,dtEnd=LEAD(dtStart)OVER(PARTITION BY idsystem ORDER BY dtStart)
FROM S
ORDER BY idsystem,dtStart;


Красиво, только версия сервера у меня не та (
23 фев 18, 13:01    [21213633]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты запроса, без использования курсора  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5114
mr.dfox
Что то кажется я туплю....
Можете подсказать другие более интересные решения?
MS SQL 2008
а что вас смущает (учитывая версию сервера)?
23 фев 18, 13:47    [21213731]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты запроса, без использования курсора  [new]
iap
Member

Откуда: Москва
Сообщений: 47063
mr.dfox
iap
WITH T AS(SELECT * FROM
(VALUES
  (1,1,CAST('2018-02-22T16:13:42.740'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:13:59.847'AS DATETIME))
 ,(1,1,CAST('2018-02-22T16:14:43.620'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:15:02.080'AS DATETIME))
 ,(1,0,CAST('2018-02-22T16:30:00.667'AS DATETIME))
 ,(1,0,CAST('2018-02-22T18:30:00.340'AS DATETIME))
 ,(1,0,CAST('2018-02-22T19:00:00.613'AS DATETIME))
 ,(1,1,CAST('2018-02-22T20:14:43.620'AS DATETIME))
 )t(idsystem,StatusError,dtCreate)
)
,G AS(SELECT N=ROW_NUMBER()OVER(PARTITION BY idsystem ORDER BY dtCreate)-ROW_NUMBER()OVER(PARTITION BY idsystem,StatusError ORDER BY dtCreate),* FROM T)
,S AS(SELECT idsystem,StatusError,dtStart=MIN(dtCreate)FROM G GROUP BY idsystem,N,StatusError)
SELECT idsystem,StatusError,dtStart,dtEnd=LEAD(dtStart)OVER(PARTITION BY idsystem ORDER BY dtStart)
FROM S
ORDER BY idsystem,dtStart;



Красиво, только версия сервера у меня не та (
Значение на соседней строке можно, сджойнив S саму с собой. Чтобы LEAD() не использовать.
Даже будет лучше, наверно, не LEFT JOIN, а OUTER APPLY (SELLECT TOP(1) * FROM ... WHERE ... ORDER BY ...) SS
23 фев 18, 15:27    [21213898]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты запроса, без использования курсора  [new]
iap
Member

Откуда: Москва
Сообщений: 47063
 		Значение на соседней строке можно получить, сджойнив S саму с собой, хотел я сказать.
23 фев 18, 15:28    [21213901]     Ответить | Цитировать Сообщить модератору
 Re: Другие варианты запроса, без использования курсора  [new]
andrey odegov
Member

Откуда:
Сообщений: 473
 		Так?
WITH
  T AS(
    SELECT *
    FROM(VALUES
      (1,1,CAST('2018-02-22T16:13:42.740'AS DATETIME)),
      (1,0,CAST('2018-02-22T16:13:59.847'AS DATETIME)),
      (1,1,CAST('2018-02-22T16:14:43.620'AS DATETIME)),
      (1,0,CAST('2018-02-22T16:15:02.080'AS DATETIME)),
      (1,0,CAST('2018-02-22T16:30:00.667'AS DATETIME)),
      (1,0,CAST('2018-02-22T18:30:00.340'AS DATETIME)),
      (1,0,CAST('2018-02-22T19:00:00.613'AS DATETIME)),
      (1,1,CAST('2018-02-22T20:14:43.620'AS DATETIME))
    )t(idsystem,StatusError,dtCreate)
  )
select * from(
  select l.idsystem,l.statuserror,
    l.dtcreate,min(r.dtcreate) fin
    ,row_number()
       over(partition by l.idsystem,min(r.dtcreate)
            order by l.dtcreate) n
  from t l left join t r
    on l.idsystem=r.idsystem and
       l.dtcreate<r.dtcreate and
       l.statuserror!=r.statuserror
  group by l.idsystem,l.statuserror,l.dtcreate
)t where n=1 order by dtcreate;
24 фев 18, 10:07    [21215057]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить