ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Другие варианты запроса, без использования курсора
8 сообщений из 8, страница 1 из 1
Другие варианты запроса, без использования курсора
    #39606487
mr.dfox
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mr.dfox
Гость
Добрый день!

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

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

Содержит данные о состоянии систем (включена/выключена):
idsystem;StatusError;dtCreate1;1;2018-02-22 16:13:42.7401;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.6671;0;2018-02-22 18:30:00.3401;0;2018-02-22 19:00:00.6131;1; 2018-02-22 20:14:43.620

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

Сделал так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
;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



Что то кажется я туплю....
Можете подсказать другие более интересные решения?
...
Рейтинг: 0 / 0
23.02.2018, 11:57
| Ответить | Цитировать | Написать  
Другие варианты запроса, без использования курсора
    #39606503
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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;
...
Рейтинг: 0 / 0
23.02.2018, 12:45
| Ответить | Цитировать | Написать  
Другие варианты запроса, без использования курсора
    #39606505
mr.dfox
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mr.dfox
Гость
Да забыл - MS SQL 2008
...
Рейтинг: 0 / 0
23.02.2018, 12:49
| Ответить | Цитировать | Написать  
Другие варианты запроса, без использования курсора
    #39606511
mr.dfox
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
mr.dfox
Гость
iap
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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;



Красиво, только версия сервера у меня не та (
...
Рейтинг: 0 / 0
23.02.2018, 13:01
| Ответить | Цитировать | Написать  
Другие варианты запроса, без использования курсора
    #39606531
Фотография Дедушка
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дедушка
Участник
mr.dfoxЧто то кажется я туплю....
Можете подсказать другие более интересные решения?
MS SQL 2008а что вас смущает (учитывая версию сервера)?
...
Рейтинг: 0 / 0
23.02.2018, 13:47
| Ответить | Цитировать | Написать  
Другие варианты запроса, без использования курсора
    #39606561
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
mr.dfoxiap
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
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
...
Рейтинг: 0 / 0
23.02.2018, 15:27
| Ответить | Цитировать | Написать  
Другие варианты запроса, без использования курсора
    #39606562
iap
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
iap
Участник
Значение на соседней строке можно получить , сджойнив S саму с собой, хотел я сказать.
...
Рейтинг: 0 / 0
23.02.2018, 15:28
| Ответить | Цитировать | Написать  
Другие варианты запроса, без использования курсора
    #39606668
andrey odegov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey odegov
Участник
Так?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
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;
...
Рейтинг: 0 / 0
24.02.2018, 10:07
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Другие варианты запроса, без использования курсора
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]