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

Откуда:
Сообщений: 658
Имеем таблицу статусов и дат, когда статусы менялись:

create table #t (date date, state bit)
insert into #t values ('20130901',1), ('20130902',1), ('20130903',1), ('20130904',0), ('20130905',0), ('20130906',1)


Это таблица:
date state
2013-09-01 1
2013-09-02 1
2013-09-03 1
2013-09-04 0
2013-09-05 0
2013-09-06 1



Как видно из приведенной таблицы от "20130902" запись есть, а статус фактически не менялся.

Помогите составить запрос, который позволит получить только даты реальной смены статуса:
т.е. мне необходимо получить таблицу
date state
2013-09-01 1
2013-09-04 0
2013-09-06 1
25 окт 13, 16:42    [15033272]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Хитроглазый
Member

Откуда:
Сообщений: 508
тестовое задание делаешь?)
25 окт 13, 17:02    [15033437]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
iap
Member

Откуда: Москва
Сообщений: 47105
Пока aleks2 не подтянулся...
SELECT [date]=MIN([date]), [state]=MIN(CAST([state]AS INT)) FROM
(SELECT N=ROW_NUMBER()OVER(ORDER BY [date])-ROW_NUMBER()OVER(PARTITION BY [state] ORDER BY [date]),* FROM #t)T
GROUP BY N,[state]
ORDER BY [date];
Это, однако, не самое оптимальное решение.
Рекомендую поискать ещё на форуме
25 окт 13, 17:05    [15033457]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Хитроглазый,

Нет не тестовое.

Вопрос возник в связи с тем, что в повседневной жизни подобное не встречается? :)


У меня статус хранится в другой таблице через справочник.
В справочнике возможны значения состояний типа "Открыт", "Приостановлен", "Переоткрыт", "Обследован" и т.п. но все они имеют статус 0/1 - действует/не действует.

Так вот мне необходимо, не смотря на наличие изменений состояний, понимать когда менялись фактические статусы (действует /не действует).
25 окт 13, 17:13    [15033501]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Galyamov Rinat
Member

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

Знать бы по какому поисковому запросу поискать подобное :)
25 окт 13, 17:15    [15033508]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
select date, state
from
(
  select date, state, lag(state, 1, 1-state) over(order by date) as prev_state
  from #t
) t
where state <> prev_state
25 окт 13, 17:16    [15033516]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
iap
Member

Откуда: Москва
Сообщений: 47105
Galyamov Rinat
iap,

Знать бы по какому поисковому запросу поискать подобное :)
По разности ROW_NUMBER() и участию aleks2, например.
Он всё время утверждает, что приводит самый оптимальный запрос для этой задачи.
А я, получается, - вредитель
25 окт 13, 17:17    [15033522]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Galyamov Rinat
Member

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

Выглядит более легковесным, чем вариант предложенный iap`ом.

Буду экспериментировать в этом направлении.
25 окт 13, 17:20    [15033536]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Galyamov Rinat
Member

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

Спасиб, пошел искать.
25 окт 13, 17:20    [15033543]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
iap
Member

Откуда: Москва
Сообщений: 47105
Galyamov Rinat
Гость333,

Выглядит более легковесным, чем вариант предложенный iap`ом.

Буду экспериментировать в этом направлении.
У Вас SQL2012?
Что-то не вижу, где Вы об этом говорили.
25 окт 13, 17:22    [15033552]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Гость333
Member

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

Также ничего не было сказано о том, что версия сервера 2005 или выше ;-)
25 окт 13, 17:26    [15033580]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
iap
Member

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

Также ничего не было сказано о том, что версия сервера 2005 или выше ;-)
Да. Увы.
25 окт 13, 18:03    [15033762]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


Простите. Упустил самую важную деталь.
26 окт 13, 08:45    [15035683]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
iap
Member

Откуда: Москва
Сообщений: 47105
SELECT * FROM #t T WHERE NOT EXISTS
(
 SELECT * FROM #t TT WHERE TT.[state]=T.[state] AND TT.[date]<T.[date] AND NOT EXISTS
 (
  SELECT * FROM #t TTT WHERE TTT.[state]<>TT.[state] AND TTT.[date]<T.[date] AND TTT.[date]>TT.[date]
 )
)
ORDER BY T.[date];
26 окт 13, 09:53    [15035718]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
LR
Member

Откуда: 8P8C
Сообщений: 2423
Galyamov Rinat
Как видно из приведенной таблицы ...

но вовсе не очевидно, могут ли быть в таблице записи на одну дату...
26 окт 13, 18:18    [15036291]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
Galyamov Rinat
Member

Откуда:
Сообщений: 658
Не могут.
В боевой таблице за счет ограничения уникальности.
28 окт 13, 05:19    [15039337]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
select t1.* 
from #t t1
left join  #t t2 on datediff(dd,t2.date,t1.date)=1 
where  t2.date is null or t2.state<>t1.state
order by date
29 окт 13, 07:09    [15044102]     Ответить | Цитировать Сообщить модератору
 Re: Найти даты, в которых происходило смена статусов.  [new]
aleks2
Guest
Galyamov Rinat
Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

Lead/Lag специально для тя придумали.
29 окт 13, 07:39    [15044154]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить