Хранимая процедура

← →
Knight © (2008-06-17 09:01) [0]

Помогите осознать логику процедуры, которая должена сделать выборку из двух таблиц.

В результирующий набор данных должны попасть строки из табл1 для которых табл1.поле1=табл2.поле1 AND табл2.поле2=0 ... или табл1.поле1 попадает в интервал значений табл2.поле1 такой, что хотя бы один из них имеет табл2.поле2=0

табл1 --------------------- поле1 поле2 --------------------- 1 | 1 | значение1 2 | 4 | значение2 3 | 6 | значение3 4 | 7 | значение4 5 | 14 | значение5 табл2 --------------------- поле1 поле2 --------------------- 1 | 2 | 23 2 | 5 | 0 3 | 7 | 0 4 | 9 | 2322 5 | 11 | 111 6 | 13 | 232 7 | 15 | 0

← →
Knight © (2008-06-17 09:04) [1]

результат --------------------- поле1 поле2 --------------------- 1 | 4 | значение2 2 | 6 | значение3 3 | 7 | значение4 4 | 14 | значение5

← →
Knight © (2008-06-17 09:06) [2]

Расширю табл1, чтобы выкинуло по-больше.

табл1 --------------------- поле1 поле2 --------------------- 1 | 1 | значение1 2 | 4 | значение2 3 | 6 | значение3 4 | 7 | значение4 5 | 10 | значение5 6 | 12 | значение6 7 | 14 | значение7 результат --------------------- поле1 поле2 --------------------- 1 | 4 | значение2 2 | 6 | значение3 3 | 7 | значение4 4 | 14 | значение7

← →
oldman © (2008-06-17 09:08) [3]

> Помогите осознать логику процедуры

Логика 1
1. Взять запись табл1, оценить ее поле1
2. Найти соответствующую запись в табл2, оценив табл2.поле1
3. проверить у нужной записи в табл2 поле2
4. если оно равно 0, запись из табл1 поместить в набор данных

Логика 2
1. Поставить фильтр табл2 такой, что табл2.поле2=0
2. Работая теперь с табл2.поле1 выбрать нужное из табл1

Логика 3
Если тебе нужен код sql-запроса, или код процедуры, так и скажи...
А то написал простейшее условие с "помогите понять логику"
:)))

← →
Knight © (2008-06-17 09:19) [4]

Спасибо... код я сам напишу главное понять с какого боку подойти :)

Я так понял, что надо сделать выборку из табл2 во временую таблицу в которую войдут все строки из нужного интервала табл1.поле1
(например, для интревала 6-12 из табл1 выбрать 5-13 из табл2)

После пробегаться по выборке из табл1 и проверять ближайшие значения из временной таблицы. Напомните только как в sql найти ближайшее в меньшую сторону, а потом в большую сторону :)

← →
Sergey13 © (2008-06-17 09:21) [5]

> [0] Knight © (17.06.08 09:01)

А насколько велика вторая таблица?

← →
Knight © (2008-06-17 09:22) [6]

Они обе ОЧЕНЬ большие.

← →
Sergey13 © (2008-06-17 09:22) [7]

+ к [5] Sergey13 © (17.06.08 09:21)

и постоянна.

← →
Knight © (2008-06-17 09:24) [8]

В табл1 добавляется примерно 50 тысяч записей в день, во вторую 20 тысяч.

← →
Knight © (2008-06-17 09:24) [9]

т.е. наоборот...

← →
Knight © (2008-06-17 09:27) [10]

Раньше обходился простыми запросами.. процедурами не пользовался, а оказывается удобная штука :)

← →
Knight © (2008-06-17 09:29) [11]

поле1 типа datetime в первую таблицу значения идут примерно раз в 5-6 секунд, во вторую каждые 2.

← →
Sergey13 © (2008-06-17 09:30) [12]

> [8] Knight © (17.06.08 09:24)

Ну, тогда с "доработкой" второй таблицы до "удобного вида" наверное не покатит. 8-)

Тогда в лоб
1. Берем запись и Т1
2. если есть четкое соответствие в Т2 там проверяем поле2
3. если нет соответствия
3.1 берем максимальное, меньше данного, проверяем П2 у него
3.2 берем минимальное, больше данного, проверка П2
4. На основани пункта 3 принимаем решение

← →
Knight © (2008-06-17 09:42) [13]

> [12] Sergey13 © (17.06.08 09:30)

т.е. для @TimeStart, @TimeEnd
1) Создаём временную таблицу результата
2) Делаем выборку из табл2 с мин>=@TimeStart и макс<=@TimeEND
3) Пробегаем по выборке из табл1 сверяя текущее @dt со поле2=0 для мин(поле1)>=@dt и макс(поле1)<=@dt

так?

← →
Knight © (2008-06-17 09:44) [14]

Если нулевое найдено, то запись вставляется в таблицу результата... из которой в конце делает выборка всех строк.

← →
Sergey13 © (2008-06-17 09:47) [15]

> [13] Knight © (17.06.08 09:42)

Я не очень знаю мелкомягкий синтаксис.
Для скольких записей из Т1 нужна выборка?

← →
Knight © (2008-06-17 09:49) [16]

> [15] Sergey13 © (17.06.08 09:47)

Зависит от заданного интревала времени... мож за день, мож за неделю, а мож за год :)

← →
Sergey13 © (2008-06-17 09:55) [17]

> [16] Knight © (17.06.08 09:49)

Тогда возможно нужна не процедура, а функция, которая возвращает да/нет для КАЖДОЙ записи Т1.

← →
Knight © (2008-06-17 11:02) [18]

Мдя.. 11 секунд за день... а за неделю пока не дождался :)))

← →
Knight © (2008-06-17 11:02) [19]

О! (00:02:23)

← →
ЮЮ © (2008-06-17 11:14) [20]

>
> Зависит от заданного интревала времени&#133 мож за день, мож
> за неделю, а мож за год :)

Что-то полей datetime не наблюдаю

> Раньше обходился простыми запросами&#133 процедурами не пользовался,
> а оказывается удобная штука :)

Ешё есть UDF. Для сективных выборок ещё более удобны; можно использовать как таблицы в запросах :)

какая связь то? Доморщенная что ли? Типа 0(1)&#1330(1) ? Или всеже eсть мастер-таблица, где есть все значения
1-2, 4-7, 9-15 поля1 ?

← →
Knight © (2008-06-17 11:30) [21]

В UDF оформил проверку нуля

CREATE FUNCTION IsZero (
@ObjID INT,
@dt datetime
)
RETURNS tinyint AS
BEGIN
if exists(SELECT 1 FROM T2
WHERE ttime BETWEEN (Select MAX(ttime) FROM T2 WHERE ttime<=@dt AND ObjID=@ObjID)
AND (Select MIN(ttime) FROM T2 WHERE ttime>=@dt AND ObjID=@ObjID)
AND v2=0 AND ObjID=@ObjID)

RETURN 1
RETURN 0
END

← →
ЮЮ © (2008-06-17 11:32) [22]

> или табл1.поле1 попадает в интервал значений табл2.поле1
> такой, что хотя бы один из них имеет табл2.поле2=0

т.е. если и есть запись для 15, не не 0
15 | 222
выводим
14 | значение5
?

← →
Knight © (2008-06-17 11:36) [23]

> [22] ЮЮ © (17.06.08 11:32)

таб2 9 | 2322 -> таб1 7 | значение4 таб2 11 | 111 -> таб1 10 | значение5 таб2 13 | 232 -> таб1 12 | значение6 таб2 15 | 0

← →
ЮЮ © (2008-06-17 11:37) [24]

> CREATE FUNCTION IsZero (
> @ObjID INT,
> @dt datetime
> )

Это тоже в точке времени, а не на интервале. Какая тогда разница, за день или за неделю?

← →
Knight © (2008-06-17 11:43) [25]

> [24] ЮЮ © (17.06.08 11:37)

Эта точка времени для проверки из процедуры

CREATE PROCEDURE GetBlaBla( @ObjID INT, @TimeStart datetime, @TimeEnd datetime ) AS BEGIN /* Procedure body */ SELECT ttime, v1 INTO #Temp FROM T1 WHERE ttime BETWEEN @TimeStart AND @TimeEnd AND IsZero(@ObjID,ttime)=1 SELECT * FROM #Temp Drop table #Temp END

← →
Knight © (2008-06-17 11:47) [26]

Всё ли так и можно ли это как-то ускорить?

← →
ЮЮ © (2008-06-17 11:47) [27]

> что хотя бы один из них имеет табл2.поле2=0

а если оба 0

5 | 0
6 | 666
7 | 0

5 | значение5
6 | значение6
7 | значение7

что для 6 выводить значение5 или значение7

Да и в примере у тебя есть

2 | 5 | 0
3 | 7 | 0

3 | 6 | значение3

Почему у тебя она (6-ка) не попала в результат?

← →
Knight © (2008-06-17 11:49) [28]

> [27] ЮЮ © (17.06.08 11:47)

Почему не попала?
результат --------------------- поле1 поле2 --------------------- 1 | 4 | значение2 2 | 6 | значение3 3 | 7 | значение4 4 | 14 | значение5

← →
Knight © (2008-06-17 11:54) [29]

> [27] ЮЮ © (17.06.08 11:47)

Есть табл1.поле1 и табл2.поле1 табл2.поле2

строка 1 значение - табл2.поле1
строка значение - табл1.поле1
строка 2 значение - табл2.поле1

если табл2.поле2=0 в 1 или 2 то табл1.поле2 включается в таблицу результата.

← →
ЮЮ © (2008-06-17 12:05) [30]

а если объект попадет в интервал несколько раз, коль ты ещё и интервал задействуещь? Или поле1 уникально в обеих таблицах?

← →
Knight © (2008-06-17 12:20) [31]

> [30] ЮЮ © (17.06.08 12:05)

Поле1 datetime

← →
ЮЮ © (2008-06-17 12:22) [32]

> Всё ли так и можно ли это как-то ускорить?

А индексы в таблицах есть?
Как долго работает запрос типа
Select
ObjID, MAX(ttime) MaxTime, MIN(ttime) MinTime
FROM T2
WHERE ttime BETWEEN @TimeStart AND @TimeEnd
GROUP BY ObjID
за день, за неделю?

← →
Knight © (2008-06-17 12:27) [33]

IsZero работает 282 ms.

GetBlaBla
за день 0.11 мин.
за 5 дней 2.3 мин

← →
ЮЮ © (2008-06-17 12:38) [34]

> [31] Knight © (17.06.08 12:20)
> > [30] ЮЮ © (17.06.08 12:05)
>
> Поле1 datetime

По примеру и не догадаешься :)

Итак в определенные промежутки времкни еешьу записи пишутся
а) в table2 в формате
0 / не ноль

б) в table1 в формате
какаое-то значение

в) в обе таблицы сразу.

Хочется
«проредить» table1, оставив те записи, пока взведен флаг «в table2 все ещё стоит 0»

Тогда два цикла по двум курсорам. В во внешнем цикле по table1 «скользишь» по table2 до нужной записи в курсоре по table1 и взводишь/сбрасываешь флаг. В зависимости от его значения выводишь или нет записсь из table1 в результирующий НД.

> [29] Knight © (17.06.08 11:54)

> строка 1 значение  табл2.поле1
> строка значение  табл1.поле1
> строка 2 значение  табл2.поле1

Учитывая это можно и в одном цикле по одному курсору, в запросе которого объединить записи из обких таблиц

← →
Knight © (2008-06-17 12:49) [35]

В таблицы пишутся показания с двух датчиков, причём не зависимо друг от друга.

← →
ЮЮ © (2008-06-17 12:59) [36]

> В таблицы пишутся показания с двух датчиков, причём не зависимо
> друг от друга.

А что ты хочешь показать? Почему показания только одного и только в те моменты, когда нет показаний другого?

Переделал... вот так стало
за 1 день 0.3 мин
за 5 дней 1.6 мин.

Мож кто проверит не напартачил ли чего? %)

T1 - ttime, v1
T2 - ttime, v2

CREATE PROCEDURE GetBlaBla( @ObjID INT, @TimeStart datetime, @TimeEnd datetime ) AS BEGIN /* Procedure body */ DECLARE @Max datetime DECLARE @Min datetime SET @Max=(SELECT MIN(ttime) FROM T2 WHERE ttime>=@TimeEnd AND ObjID=@ObjID) SET @Min=(SELECT MAX(ttime) FROM T2 WHERE ttime<=@TimeStart AND ObjID=@ObjID) SELECT a.ttime, a.v1)) FROM T1 a WHERE a.ttime BETWEEN @TimeStart AND @TimeEnd AND a.ObjID=@ObjID AND exists(SELECT 1 FROM T2 WHERE ttime BETWEEN (Select MAX(ttime) FROM T2 WHERE ttime<=a.ttime AND ObjID=@ObjID) AND (Select MIN(ttime) FROM T2 WHERE ttime>=a.ttime AND ObjID=@ObjID) AND v2=0 AND ObjID=@ObjID AND ttime BETWEEN @Min AND @Max) END

Напартачил... идёт просто выборка всех записей за период из Т1

← →
ANB (2008-06-17 22:28) [40]

> Они обе ОЧЕНЬ большие.

Скока миллионов записей ?

← →
ANB (2008-06-17 22:34) [41]

select /*+ full(T1) full(T2) parallel(T1,4) parallel(T2,4) use_hash(T1,T2) */ distinct T1.поле1 ,T1.поле2 from табл1 T1 ,табл2 T2 where табл2.поле1 = табл1.поле1 and табл2.поле2 = 0

Если я правильно перевел условие, то так должно работать.
Если в каждой таблице по миллиону записей, то у меня отработало за 5 мин.

> [37] Knight © (17.06.08 13:03)
>
> Когда показания второго нулевые, то показания первого точнее.

Но когда показания второго ненулевые, их вообще нет в выборке.

И все-таки, что ты хочеь получить теперь, когда стало понятно, что в таблицы пишутся показания с двух датчиков, причём не зависимо друг от друга.

Из твоих попытот следует, что ты хочешь получить только те показания первого, когда показания второго уже нулевые или станут нулевыми при следующем измерении?

З.Ы. Я веду это к тому, что цикл по курсору, ИМХО будет быстрее и «прозрачее», чем EXISTS и агрегирующие подзапросы.

> [41] ANB (17.06.08 22:34)
> табл2.поле1 = табл1.поле1

Нет там равенства полей :(

← →
ANB (2008-06-18 10:21) [45]

В результирующий набор данных должны попасть строки из табл1 для которых табл1.поле1=табл2.поле1 AND табл2.поле2=0 ... или табл1.поле1 попадает в интервал значений табл2.поле1 такой, что хотя бы один из них имеет табл2.поле2=0

???

> [45] ANB (18.06.08 10:21)
> или табл1.поле1 попадает в интервал значений табл2.поле1
> такой, что хотя бы один из них имеет табл2.поле2=0

или табл1.поле1 попадает в интервал значений табл2.поле1 такой, что хотя бы один из них имеет табл2.поле2=0

← →
ANB (2008-06-18 10:32) [47]

> или табл1.поле1 попадает в интервал значений табл2.поле1
> такой, что хотя бы один из них имеет табл2.поле2=0

Переведи. Как определить интервал (я так понял это between ?), если подходящих значений табл2.поле1 15 ?

Если же вопрос стоит так : выбрать все записи из табл1, у которых табл1.поле1 входит в набор табл2.поле1 при табл2.поле2=0
то запрос выглядит так :

select T1.поле1 ,T1.поле2 from табл1 T1 where табл1.поле1 in ( selecty табл2.поле1 from табл2 T2 where табл2.поле2 = 0 )

Но такой запрос при больших таблицах будет довольно медленным. Мой делает тоже самое, но быстрее.

> [47] ANB (18.06.08 10:32)

поле1 в табл1 и табл2 практически не имеет общих значений... интервалы занесения показаний датчиков разные. Надо отловить только те показания первого датчика во время снятия которых значения второго были нулевые.

>
> [0] Knight © (17.06.08 09:01)
> Помогите осознать логику процедуры, которая должена сделать
> выборку из двух таблиц.

Через 3 с половиной часа телепетор уловил мысль. Автор продолжает «шифроваться»

> [34] ЮЮ © (17.06.08 12:38)
> Хочется
> «проредить» table1, оставив те записи, пока взведен флаг
> «в table2 все ещё стоит 0»

Телепатор перегревается и требует подтверждений своих догадок

> [42] ЮЮ © (18.06.08 06:13)
> ты хочешь получить только
> те показания первого, когда показания второго уже нулевые
> или станут нулевыми при следующем измерении?

Через сутки автор сдается

> [48] Knight © (18.06.08 10:40)
> Надо отловить
> только те показания первого датчика во время снятия которых
> значения второго были нулевые.

И это свои, не один год здесь обитающие. А мы ещё от новичков чего-то требуем.

Теперь по subj-у. Где-то так:

CREATE FUNCTION GetBlaBla( @ObjID INT, @TimeStart datetime, @TimeEnd datetime ) RETURNS @Result TABLE(ttime datetime, v1 int) AS BEGIN DECLARE c1 CURSOR LOCAL FOR SELECT ttime, v1 FROM T1 WHERE ttime BETWEEN @TimeStart AND @TimeEnd OPEN c1 DECLARE c2 CURSOR LOCAL FOR SELECT ttime, v2 FROM T2 WHERE ttime BETWEEN @TimeStart AND @TimeEnd OPEN c2 DECLARE @t2Before datetime, @v2Before int DECLARE @t2After datetime, @v2After int DECLARE @t1 datetime, @v1 int FETCH NEXT FROM c2 INTO @t2After, @v2After IF @@FETCH_STATUS = 1 BEGIN  нет показаний в t2 INSERT INTO @Resul SELECT ttime, v1 FROM T1 WHERE ttime BETWEEN @TimeStart AND @TimeEnd RETURN END SET @v2Before = 0 FETCH NEXT FROM c1 INTO @t1, @v1 WHILE @@FETCH_STATUS <> 1 BEGIN IF @@FETCH_STATUS = 0 BEGIN  продвинемся по T2 до тех пор, пока не достигнем того, что  текущая запись добавлена не позже, а предудущая раньще времени записи в T1 WHILE @t2After < @t1 BEGIN SET @t2Before = @t2After SET @v2Before = @v2After FETCH NEXT FROM c2 INTO @t2After, @v2After IF @@FETCH_STATUS = 1 BEGIN  считаем, что до конца периода стоит 0 SET @t2After = @TimeEnd + 0.01 SET @v2After = 0 END END IF (@v2After = 0) OR (@v2Before = 0) INSERT INTO @Resul SELECT @t1, @v1 FETCH NEXT FROM c1 INTO @t1, @v1 END END RETURN END

← →
ANB (2008-06-19 18:49) [50]

> Надо отловить только те показания первого датчика во время
> снятия которых значения второго были нулевые.

Вот это условие мона расшифровать поподробнее ? И я совершенно не пронаблюдал интервалов. Есть только точки.

Я бы понял задачу так :

Нужно вывести все значения таблицы2 (показаний второго датчика). Если же какое то значение поля "Показание датчика" таблицы2 равно 0, то заменить его соответствующим значением из таблицы1, причем найти в ней строку, ближайщую по времени (Т1.поле1) к времени измерения второго датчика (Т2.поле1).

Так ?

> [51] Knight © (19.06.08 22:11)
> Спасибо, завтра погляжу&#133 чёт сёдня приболел.

Не забудь в SELECT-ах курсоров &#133 AND(ObjID = @ObjID) ORDER BY ttime

Хранимая процедура Найти похожие ветки