Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: ←Ctrl  назад   1 [2] 3   вперед  Ctrl→      все
 Re: помогите с подзапросом  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
SirMix
Member

Откуда: Киев
Сообщений: 79
temoxa
SirMix, извени за беспокойство. Вот что я хочу сделать у меня есть ряд Mobitel_ID для каждого из них есть время и скорость в это время! я сделал, так что если скорость 0 то значение 0, а если скорость >0 - то 1! Теперь я хочу сформировать таблицу такого формата: Mobitel_ID, дата начала 0(1), дата окончания 0(1)!


ну в принципе верхний пост с небольшим изменением это то, что тебе надо:

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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
SirMix
Member

Откуда: Киев
Сообщений: 79
temoxa
вот примерная табличка:
для первого блока решение будет такое
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


вот этот скрипт должен быть тем, что нужно:

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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
 		temoxa почитайте эту тему
Как еще можно сделать ?
ИМХО, разбив на непрерывные диапозоны, Вы получите что надо
19 июл 12, 15:27    [12889011]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
temoxa
Member

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

у тебя идет привязка к определенному времени.

а у меня в таблице очень много значений (более 1 млн)! например проверяем значение speed - оно 0 - стартовая дата будет дата этого значения, если следующее значение 1 то конечная дата будет дата значения 1, а если после 0 шел еще один ноль, то пропускаем его и смотрим следующее значение и тд! я понятно изъясняюсь? а то вдруг только путаю... спс за помощь.
19 июл 12, 15:36    [12889088]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
SirMix
Member

Откуда: Киев
Сообщений: 79
temoxa
SirMix,

у тебя идет привязка к определенному времени.

а у меня в таблице очень много значений (более 1 млн)! например проверяем значение speed - оно 0 - стартовая дата будет дата этого значения, если следующее значение 1 то конечная дата будет дата значения 1, а если после 0 шел еще один ноль, то пропускаем его и смотрим следующее значение и тд! я понятно изъясняюсь? а то вдруг только путаю... спс за помощь.


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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
temoxa
Member

Откуда:
Сообщений: 405
 		HandKot,
а как можно разбить на непрерывные диапазоны?
19 июл 12, 16:07    [12889325]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
SirMix
Member

Откуда: Киев
Сообщений: 79
temoxa
HandKot,
а как можно разбить на непрерывные диапазоны?


ну это же тот же подход, что я описал тебе выше...

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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
temoxa
HandKot,
а как можно разбить на непрерывные диапазоны?


ну так пройдите по ссылке, там уже готовый запрос от Добрый Э - Эх
Вам надо только подставить свои данные и всё
19 июл 12, 17:06    [12889729]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
 		мдя, думать не желаем

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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
temoxa
Member

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

спасибо!
19 июл 12, 19:11    [12890193]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
 		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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
 		принцип т от же самый
Вам надо только править 
row_number() over(partition by value order by time)


кстати, что Вы хотели бы получить на Ваших тестовых данных?
20 июл 12, 10:47    [12891975]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
temoxa
Member

Откуда:
Сообщений: 405
 		HandKot,
для каждого id - интервалы дата начала 0(1), дата конца 0(1)!
тоже самое что и в прошлых постах только в разрезе іd..
20 июл 12, 10:52    [12892006]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
 		лучше покажите на тех данных, что выложены на форуме
20 июл 12, 10:55    [12892032]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
temoxa
Member

Откуда:
Сообщений: 405
 		HandKot,
эти данные с фильтром по id - работает все ок!

К сообщению приложен файл. Размер - 32Kb
20 июл 12, 11:46    [12892436]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
temoxa
Member

Откуда:
Сообщений: 405
 		HandKot,
а эти данные без фильтра - данные просто мутота!

К сообщению приложен файл. Размер - 57Kb
20 июл 12, 11:48    [12892449]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
temoxa
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)

Как тогда быть?

Если Вам надо чтобы для данного набора получилось
idminmaxvalue
120100101201001020
220100103201001030
220100104201001051
320100106201001061
320100107201001070
320100108201001081
420100109201001090

то, как я уже гоорил, просто поправьте 
row_number() over(partition by id, value order by time)


ЗЫЖ temoxa , разберитесь в коде и поймите, почему так получается и часть вопросов отпадет
20 июл 12, 11:57    [12892524]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3015
temoxa
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)


а что с этими данными?
23 июл 12, 16:50    [12904036]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
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]     Ответить | Цитировать Сообщить модератору
 Re: помогите с подзапросом  [new]
temoxa
Member

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

и может просто я туплю...
23 июл 12, 17:22    [12904259]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: ←Ctrl  назад   1 [2] 3   вперед  Ctrl→      все
Все форумы / Microsoft SQL Server Ответить