Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Топик располагается на нескольких страницах: ←Ctrl назад 1 [2] 3 вперед Ctrl→ все |
temoxa Member Откуда: Сообщений: 405 |
вот примерная табличка: для первого блока решение будет такое 97 2012-07-18 21:02:26.000 2012-07-18 21:12:28.000 0 а для 103: 103 2012-07-18 00:56:05.000 2012-07-18 00:58:05.000 0 К сообщению приложен файл. Размер - 64Kb |
19 июл 12, 15:12 [12888874] Ответить | Цитировать Сообщить модератору |
SirMix Member Откуда: Киев Сообщений: 79 |
ну в принципе верхний пост с небольшим изменением это то, что тебе надо: DECLARE @St DATETIME = '20120715 00:00:00', @En DATETIME = '20120717 23:59:59' DECLARE @DD DATETIME DECLARE @T TABLE (MobitelID INT IDENTITY, Da DATE, DT DATETIME, Val INT) DECLARE cur1 CURSOR FOR SELECT @DD OPEN cur1 FETCH NEXT FROM cur1 INTO @DD SET @DD = @St WHILE @@FETCH_STATUS = 0 BEGIN WHILE @DD <= @En BEGIN INSERT INTO @T ( Da, DT ) SELECT CONVERT(DATE, @DD), @DD SET @DD = DATEADD(hour,1,@DD) END FETCH NEXT FROM cur1 INTO @DD END CLOSE cur1 DEALLOCATE cur1 UPDATE @T SET Val = 0 WHERE DT BETWEEN '20120715 00:00:00.000' AND '20120716 07:00:00.000' UPDATE @T SET Val = 2 WHERE DT BETWEEN '20120716 08:00:00.000' AND '20120717 01:00:00.000' UPDATE @T SET Val = 3 WHERE DT BETWEEN '20120717 02:00:00.000' AND '20120717 05:00:00.000' UPDATE @T SET Val = 0 WHERE DT > '20120717 05:00:00.000' SELECT * INTO #tmp1 FROM @T WITH T as (SELECT *, MaxDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE x.DT = DATEADD(HOUR, -1, q.DT)) OR x.DT = (SELECT MAX(DT) FROM #tmp1) THEN 1 ELSE 0 END, MinDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE x.DT = DATEADD(HOUR, 1, q.DT)) OR x.DT = (SELECT MIN(DT) FROM #tmp1) THEN 1 ELSE 0 END FROM #tmp1 x) SELECT * FROM T WHERE (MaxDate = 1 OR MinDate = 1) AND Val = 0 --выполнить, когда времянка уже не нужна IF object_id('tempdb..#tmp1') is not NULL BEGIN DROP TABLE #tmp1 END Val - это есть твоя скорость, которая может принимать значение 0 или же отличное от 0... скрипт выбирает все "старты" и "финиши" где скорость = 0. если нужно выбрать первый 0, то добавь условие что дата самая маленькая. или тебе не это нужно? |
||
19 июл 12, 15:19 [12888943] Ответить | Цитировать Сообщить модератору |
SirMix Member Откуда: Киев Сообщений: 79 |
вот этот скрипт должен быть тем, что нужно: DECLARE @St DATETIME = '20120715 00:00:00', @En DATETIME = '20120717 23:59:59' DECLARE @DD DATETIME DECLARE @T TABLE (MobitelID INT, Da DATE, DT DATETIME, Val INT) DECLARE cur1 CURSOR FOR SELECT @DD OPEN cur1 FETCH NEXT FROM cur1 INTO @DD SET @DD = @St WHILE @@FETCH_STATUS = 0 BEGIN WHILE @DD <= @En BEGIN INSERT INTO @T ( Da, DT ) SELECT CONVERT(DATE, @DD), @DD SET @DD = DATEADD(hour,1,@DD) END FETCH NEXT FROM cur1 INTO @DD END CLOSE cur1 DEALLOCATE cur1 UPDATE @T SET Val = 0, MobitelID = 99 WHERE DT BETWEEN '20120715 00:00:00.000' AND '20120716 07:00:00.000' UPDATE @T SET Val = 2, MobitelID = 100 WHERE DT BETWEEN '20120716 08:00:00.000' AND '20120717 01:00:00.000' UPDATE @T SET Val = 3, MobitelID = 101 WHERE DT BETWEEN '20120717 02:00:00.000' AND '20120717 05:00:00.000' UPDATE @T SET Val = 0, MobitelID = 103 WHERE DT > '20120717 05:00:00.000' SELECT * INTO #tmp1 FROM @T WITH T as (SELECT *, MaxDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE x.DT = DATEADD(HOUR, -1, q.DT)) OR x.DT = (SELECT MAX(DT) FROM #tmp1) THEN 1 ELSE 0 END, MinDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE x.DT = DATEADD(HOUR, 1, q.DT)) OR x.DT = (SELECT MIN(DT) FROM #tmp1) THEN 1 ELSE 0 END FROM #tmp1 x) SELECT MobitelID, DT, Val FROM T WHERE MinDate = 1 AND Val = 0 --выполнить, когда времянка уже не нужна IF object_id('tempdb..#tmp1') is not NULL BEGIN DROP TABLE #tmp1 END |
||
19 июл 12, 15:23 [12888977] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
temoxa почитайте эту тему Как еще можно сделать ? ИМХО, разбив на непрерывные диапозоны, Вы получите что надо |
19 июл 12, 15:27 [12889011] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
SirMix, у тебя идет привязка к определенному времени. а у меня в таблице очень много значений (более 1 млн)! например проверяем значение speed - оно 0 - стартовая дата будет дата этого значения, если следующее значение 1 то конечная дата будет дата значения 1, а если после 0 шел еще один ноль, то пропускаем его и смотрим следующее значение и тд! я понятно изъясняюсь? а то вдруг только путаю... спс за помощь. |
19 июл 12, 15:36 [12889088] Ответить | Цитировать Сообщить модератору |
SirMix Member Откуда: Киев Сообщений: 79 |
WITH T as (SELECT *, MaxDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE q.DT = (SELECT MIN(w.DT) FROM #tmp1 w WHERE w.DT > x.DT)) OR x.DT = (SELECT MAX(DT) FROM #tmp1) THEN 1 ELSE 0 END, MinDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE q.DT = (SELECT MAX(w.DT) FROM #tmp1 w WHERE w.DT < x.DT)) OR x.DT = (SELECT MIN(DT) FROM #tmp1) THEN 1 ELSE 0 END FROM #tmp1 x) SELECT MobitelID, DT, Val FROM T WHERE MinDate = 1 AND Val = 0 |
||
19 июл 12, 15:51 [12889195] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, а как можно разбить на непрерывные диапазоны? |
19 июл 12, 16:07 [12889325] Ответить | Цитировать Сообщить модератору |
SirMix Member Откуда: Киев Сообщений: 79 |
ну это же тот же подход, что я описал тебе выше... WITH T as (SELECT *, MaxDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE q.DT = (SELECT MIN(w.DT) FROM #tmp1 w WHERE w.DT > x.DT)) OR x.DT = (SELECT MAX(DT) FROM #tmp1) THEN 1 ELSE 0 END, MinDate = CASE WHEN x.Val <> (SELECT q.Val FROM #tmp1 q WHERE q.DT = (SELECT MAX(w.DT) FROM #tmp1 w WHERE w.DT < x.DT)) OR x.DT = (SELECT MIN(DT) FROM #tmp1) THEN 1 ELSE 0 END FROM #tmp1 x) --SELECT MobitelID, DT, Val FROM T WHERE MinDate = 1 AND Val = 0 SELECT MobitelID, DT, Val FROM T WHERE (MinDate = 1 OR MaxDate = 1) начало и конец каждого из "непрерывных" диапазонов |
||
19 июл 12, 16:13 [12889368] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
ну так пройдите по ссылке, там уже готовый запрос от Добрый Э - Эх Вам надо только подставить свои данные и всё |
||
19 июл 12, 17:06 [12889729] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot,declare @test table(time datetime,value int) insert into @test( time ,value ) values ('20100101',0) ,('20100102',0) ,('20100103',0) ,('20100104',1) ,('20100105',1) ,('20100106',1) ,('20100107',0) ,('20100108',1) ,('20100109',0) ;with x_test as ( select value, time, case when value > 0 then 1 else 0 end as flag, row_number() over(partition by case when value > 0 then 1 else 0 end order by time) as rn1, row_number() over(order by time) as rn from @test ) select min(t1.time) as min, max(coalesce(t2.time, t1.time)) as max from (select * from x_test where flag = 1) t1 left join (select * from x_test where flag = 0) t2 on t1.rn = t2.rn - 1 group by t1.rn - t1.rn1 Пробовал такой скрипт. работает только на 1! как сделать чтобы работал скрипт и на интервал с 0? |
19 июл 12, 17:47 [12889903] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
мдя, думать не желаемdeclare @test table(time datetime,value int) insert into @test( time ,value ) values ('20100101',0) ,('20100102',0) ,('20100103',0) ,('20100104',1) ,('20100105',1) ,('20100106',1) ,('20100107',0) ,('20100108',1) ,('20100109',0) ;with x_test as ( select value , time , row_number() over(partition by value order by time) + row_number() over(order by time Desc) as grp from @test ) select t1.value , min(t1.time) as min , max(t1.time) as max from x_test t1 group by t1.value, t1.grp order by 2 |
19 июл 12, 18:15 [12890034] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, спасибо! |
19 июл 12, 19:11 [12890193] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
temoxa в предложенном коде есть неявная ошибка (может проявится при определённых наборах данных) так что подумайте как её обойти :) намек declare @test table(time datetime,value int) insert into @test( time ,value ) values ('20100101',0) ,('20100102',0) ,('20100103',0) ,('20100104',1) >>>,('20100104',1) ,('20100105',1) ,('20100106',1) ,('20100107',0) ,('20100108',1) ,('20100109',0) |
20 июл 12, 08:26 [12891234] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, в моих данных такой ошибки не будет.. у меня данные динамические и одинаковых быть не может! но спасибо за предупреждение. у меня теперь просто возник такой вопросик: а если например добавить к этим данным еще какой нить id? например так: declare @test table(id int, time datetime,value int) insert into @test(id, time ,value ) values (1,'20100101',0) ,(1,'20100102',0) ,(2,'20100103',0) ,(2,'20100104',1) ,(2,'20100105',1) ,(3,'20100106',1) ,(3,'20100107',0) ,(3,'20100108',1) ,(4,'20100109',0) Как тогда быть? |
20 июл 12, 10:34 [12891897] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
принцип т от же самый Вам надо только править row_number() over(partition by value order by time) кстати, что Вы хотели бы получить на Ваших тестовых данных? |
20 июл 12, 10:47 [12891975] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, для каждого id - интервалы дата начала 0(1), дата конца 0(1)! тоже самое что и в прошлых постах только в разрезе іd.. |
20 июл 12, 10:52 [12892006] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
лучше покажите на тех данных, что выложены на форуме |
20 июл 12, 10:55 [12892032] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, эти данные с фильтром по id - работает все ок! К сообщению приложен файл. Размер - 32Kb |
20 июл 12, 11:46 [12892436] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, а эти данные без фильтра - данные просто мутота! К сообщению приложен файл. Размер - 57Kb |
20 июл 12, 11:48 [12892449] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
Если Вам надо чтобы для данного набора получилось
то, как я уже гоорил, просто поправьте row_number() over(partition by id, value order by time) ЗЫЖ temoxa , разберитесь в коде и поймите, почему так получается и часть вопросов отпадет |
|||||||||||||||||||||||||||||||||||
20 июл 12, 11:57 [12892524] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, с кодом разобрался! спс но вот не задачка)) а как насчет таких данных: declare @test table(id int, time datetime,value int) insert into @test(id, time ,value ) values (1,'2012-07-18 00:00:56.000',0) ,(1,'2012-07-18 00:01:56.000',0) ,(2,'2012-07-18 00:00:04.000',0) ,(2,'2012-07-18 00:01:04.000',1) ,(2,'2012-07-18 00:02:04.000',1) ,(3,'2012-07-18 00:03:04.000',1) ,(3,'2012-07-18 00:04:04.000',0) ,(3,'2012-07-18 00:05:04.000',1) ,(4,'2012-07-18 00:07:04.000',0) |
20 июл 12, 13:03 [12893019] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot,;with x_test as ( select Mobitel_id, DateOfData, Speed, Moving, row_number() over(partition by Mobitel_id, Moving order by DateOfData) + row_number() over(order by DateOfData Desc) as grp from @Data [b]Where Mobitel_id = 103[/b] ) select t1.Mobitel_id, --t1.Speed, t1.Moving , min(t1.DateOfData) as min , max(t1.DateOfData) as max from x_test t1 group by t1.Mobitel_id, t1.Moving, t1.grp order by 1, 3 Никак не могу раздуплится: когда явно указываю id работает все супер, иначе выдает бред... Помогите пож, с данными функциями сталкиваюсь впервые! спасибо! |
23 июл 12, 16:32 [12903900] Ответить | Цитировать Сообщить модератору |
HandKot Member Откуда: Sergiev Posad Сообщений: 3031 |
а что с этими данными? |
||
23 июл 12, 16:50 [12904036] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, просто оно не хавает! выдает одинаковые данные! да, если просто поочередные даты, то работает, а так дык! values (1,'20100101',0) ,(1,'20100102',0) ,(1,'20100110',1) ,(2,'20100103',0) ,(2,'20100104',1) ,(2,'20100105',1) ,(3,'20100106',1) ,(3,'20100107',0) ,(3,'20100108',1) ,(4,'20100109',0) С этими данными работает! а с детализацией времени не хочет! |
23 июл 12, 16:56 [12904088] Ответить | Цитировать Сообщить модератору |
temoxa Member Откуда: Сообщений: 405 |
HandKot, и может просто я туплю... |
23 июл 12, 17:22 [12904259] Ответить | Цитировать Сообщить модератору |
Топик располагается на нескольких страницах: ←Ctrl назад 1 [2] 3 вперед Ctrl→ все |
Все форумы / Microsoft SQL Server | ![]() |