Форум пользователей MySQL

NDS · 18.04.2010 19:31:41

Здравствуйте, уважаемые участники форума!
Прошу помощи в составлении запроса...
Есть следующая задача:
При помощи вызова процедуры Call getObjectsByModelAndClass('CNC','USR'); в таблицу ObjectsOfModelAndClass заносятся записи следующего вида:
ModelCode DataSetNum Num ClassCode Name BeginInterval EndInterval
CNC -1 14187 USR Azat 2019-01-07 15:15:00.0 2019-01-07 16:15:00.0
CNC -1 14167 USR BAA 2018-01-10 17:05:00.0 2018-01-10 18:53:00.0
CNC -1 11319 USR BUI 2019-12-07 15:14:00.0 2019-12-07 15:26:00.0
CNC -1 24457 USR EOV 2027-08-09 09:27:00.0 2028-08-09 08:42:00.0
* * * * * * * * * * * * * * *
Затем засчет вызова процедуры Call getElementaryPeriods('2008-08-02 00:27:46', '2008-12-10 17:43:28', 10); создается таблица ElementaryPeriods, которая содержит элементарные периоды заданного размера (в минутах):
BeginElementaryInterval EndElementaryInterval
2008-08-02 00:27:46.0 2008-08-02 00:30:00.0
2008-08-02 00:30:00.0 2008-08-02 00:40:00.0
2008-08-02 00:40:00.0 2008-08-02 00:50:00.0
2008-08-02 00:50:00.0 2008-08-02 01:00:00.0
* * * * * * * * * * *
Необходимо создать хранимую процедуру Call getObjectsByPeriods('2008-08-02 00:27:46', '2008-12-12 17:43:28');, которая будет обходить ElementaryPeriods и из ObjectsOfModelAndClass заносить в таблицу ObjectsOfPeriods объекты, которые попадают в каждый из заданных элементарных периодов.
Собственно, данную хранимку я написал:

DELIMITER $$

DROP PROCEDURE IF EXISTS getObjectsByPeriods $$

CREATE PROCEDURE getObjectsByPeriods(IN beginInt DATETIME, IN endInt DATETIME)

BEGIN

    -- ДЛЯ ОКОНЧАНИЯ ЦИКЛА (ОКОНЧАНИЕ КУРСОРА)

    Declare done integer default 0;

    Declare countNumOfPeriod integer default 0;

    DECLARE beginElemPeriod DATETIME;

    DECLARE endElemPeriod DATETIME;

    -- КУРСОР ДЛЯ ОБХОДА ТАБЛИЦЫ ElementaryPeriods

    Declare ElementaryPeriodsCursor Cursor

    -- ОПРЕДЕЛЕНИЕ HANDLER'А ОКОНЧАНИЯ КУРСОРА

    for Select * from ElementaryPeriods where 1;

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    -- ТАБЛИЦА ОБЪЕКТОВ, ПОПАДАЮЩИХ В ЗАДАННЫЕ ЭЛЕМЕНТАРНЫЕ ПЕРИОДЫ

    CREATE TABLE IF NOT EXISTS ObjectsOfPeriods

    (NumOfPeriod int(10) unsigned DEFAULT NULL,

     BeginElementaryPeriod datetime DEFAULT '0000-00-00 00:00:00',

     EndElementaryPeriod datetime DEFAULT '0000-00-00 00:00:00',

     Num int(10) unsigned DEFAULT NULL,

     Name varchar(255) DEFAULT NULL,

     BeginInterval datetime DEFAULT '0000-00-00 00:00:00',

     EndInterval datetime DEFAULT '0000-00-00 00:00:00'

    )TYPE=MyISAM;

    -- ОЧИЩАЕМ ТАБЛИЦУ

    DELETE FROM ObjectsOfPeriods;

    -- ВРЕМЕННАЯ ТАБЛИЦА ДЛЯ ОБЪЕКТОВ, ПОПАДАЮЩИХ ТОЛЬКО В ЗАДАННЫЙ ИНТЕРВАЛ

    CREATE TEMPORARY TABLE IF NOT EXISTS tmpObjectsOfModelAndClassInInerval

    (Num int(10) unsigned DEFAULT NULL,

     Name varchar(255) DEFAULT NULL,

     BeginInterval datetime DEFAULT '0000-00-00 00:00:00',

     EndInterval datetime DEFAULT '0000-00-00 00:00:00'

    )TYPE=MyISAM;

    -- УБИРАЕМ ЛИШНИЕ ОБЪЕКТЫ ДЛЯ ВЫБОРКИ ОБЪЕКТОВ ПО ЭЛЕМЕНТАРНЫМ ПЕРИОДАМ

    -- (УБИРАЕМ ВСЕ ОБЪЕКТЫ ВНЕ ЗАДАННОГО ИНТЕРВАЛА)

    INSERT INTO tmpObjectsOfModelAndClassInInerval(Num,

                                                   Name,

                                                   BeginInterval,

                                                   EndInterval)

    select Num, Name, BeginInterval, EndInterval

    from ObjectsOfModelAndClass

    where BeginInterval >= beginInt

          and

          EndInterval <= endInt;

    -- КУРСОР

    Open ElementaryPeriodsCursor;

    WHILE done = 0 DO

        FETCH ElementaryPeriodsCursor INTO beginElemPeriod, endElemPeriod;

        INSERT INTO ObjectsOfPeriods(NumOfPeriod,

                                     BeginElementaryPeriod,

                                     EndElementaryPeriod, 

                                     Num,

                                     Name,

                                     BeginInterval,

                                     EndInterval)

        select null,

               beginElemPeriod,

               endElemPeriod,

               Num,

               Name,

               BeginInterval,

               EndInterval

        from tmpObjectsOfModelAndClassInInerval

        where BeginInterval >= beginElemPeriod

              and

              EndInterval <= endElemPeriod;

    END WHILE;

    Close ElementaryPeriodsCursor;

    -- УДАЛЯЕМ ВРЕМЕННУЮ ТАБЛИЦУ

    DROP TEMPORARY TABLE IF EXISTS tmpObjectsOfModelAndClassInInerval;

    -- ИТОГОВАЯ ВЫБОРКА

    select *

    from ObjectsOfPeriods

    order by BeginElementaryPeriod, Name asc;

END $$

DELIMITER ;

NumOfPeriod BeginElementaryPeriod EndElementaryPeriod Num Name BeginInterval EndInterval
NULL 2008-11-07 09:20:00.0 2008-11-07 09:30:00.0 10745 TYV 2008-11-07 09:26:00.0 2008-11-07 09:27:00.0
NULL 2008-12-08 09:40:00.0 2008-12-08 09:50:00.0 61835 KGU 2008-12-08 09:41:00.0 2008-12-08 09:50:00.0
NULL 2008-12-08 09:40:00.0 2008-12-08 09:50:00.0 61847 KIA 2008-12-08 09:40:00.0 2008-12-08 09:41:00.0
NULL 2008-12-09 09:10:00.0 2008-12-09 09:20:00.0 69452 BAA 2008-12-09 09:12:00.0 2008-12-09 09:18:00.0
* * * * * * * * * * * * * * * * * * * * *
Но, я не знаю, как внутри курсора определить количество записей, которые попадают в заданный элементарный период, который задается внутри курсора?...
Мне нужно, что бы в поле NumOfPeriod заносился порядковый номер элементарного периода. При этом, как вы понимаете, в какой-либо один и тот же период может попадать множество объектов. Т.е. поле NumOfPeriod может иметь вид
NumOfPeriod
1
2
2
2
3
4
4
* * *
Неужели для этого внутри курсора нужно открывать еще один, в котором уже заводить переменную-счетчик? Или как-то так...
Данное поле мне нужно, уже для последующего составления двухмерного ассоциативного массива в PHP (что бы не проводить проверку на один и тот же период средствами PHP).
Очень прошу помощи! - как реализовать решение данной задачи?
И еще один вопрос:
Если задать продолжительный интервал (например, в 5 лет) и небольшой размер элементарных периодов (в 10 минут), то данная процедура выполняет выборку продолжительное время: минуты 1,5 - 2. Возможно ли оптимизировать запрос? Не использовать курсор или т.п.
Я в данных делах новичок, так что прошу не судить строго...))
Заранее, спасибо.

vasya · 18.04.2010 20:11:23

Внутри курсора открывать ещё один курсор не нужно. Используете две переменных, одна счетчик, вторая предыдущее значение периода.
Если предыдущее значение периода совпадает с текущим, то счетчик не меняется, если нет, то счечик увеличивается на 1 и предыдущее значение периода обновляется на текущее.

NDS · 18.04.2010 20:59:36

Уважаемый, vasya!
Я так понимаю, нужно добавить что-то вроде этого:

    Declare countNumOfPeriod integer default 0;

    DECLARE previousBeginElemPeriod DATETIME DEFAULT '0000-00-00 00:00:00';

. . .

    WHILE done = 0 DO

        FETCH ElementaryPeriodsCursor INTO beginElemPeriod, endElemPeriod;

        IF previousBeginElemPeriod <> beginElemPeriod THEN

            SET previousBeginElemPeriod := beginElemPeriod;

            SET countNumOfPeriod := countNumOfPeriod + 1;

        END IF;

. . .

        where BeginInterval >= beginElemPeriod

              and

              EndInterval <= endElemPeriod;

-- ЗАНЕСТИ ПОСЛЕДНЕЕ ЗНАЧЕНИЕ BeginInterval ИЗ ТАБЛИЦЫ ObjectsOfPeriods В ПЕРЕМЕННУЮ previousBeginElemPeriod

Т.е. нужно брать предыдущее значение именно из таблицы ObjectsOfPeriods - таблицы, в которую уже занесены записи удовлетворяющие условию фильтра:

        where BeginInterval >= beginElemPeriod

              and

              EndInterval <= endElemPeriod;

Подскажите, пожалуйста, как из таблицы ObjectsOfPeriods взять последнее значение поля BeginElementaryPeriod и занести его в переменную previousBeginElemPeriod?...

Отредактированно NDS (18.04.2010 21:04:19)

vasya · 18.04.2010 21:12:05

Declare countNumOfPeriod integer default 0;
DECLARE previousBeginElemPeriod DATETIME DEFAULT '0000-00-00 00:00:00';
. . .
WHILE done = 0 DO
FETCH ElementaryPeriodsCursor INTO beginElemPeriod, endElemPeriod;
INSERT INTO ObjectsOfPeriods(NumOfPeriod,
....
select countNumOfPeriod,
....
where BeginInterval >= beginElemPeriod
and
EndInterval <= endElemPeriod
and
if (previousBeginElemPeriod=beginElemPeriod,1,countNumOfPeriod:=countNumOfPeriod+1);

Конструкция IF .. END IF; в начале курсора не нужна. Эта проверка проводится в части where самого запроса.

UPD: Первоначально неправильно написал условие, см исправленный вариант.

vasya · 18.04.2010 21:19:44

Что-то я не совсем не то сначала написал. См исправленный вариант в прошлом посте.

vasya · 18.04.2010 21:24:42

Что-то совсем клиника наступила. Такой вариант вроде похож на правду.

Declare countNumOfPeriod integer default 0;
DECLARE previousBeginElemPeriod DATETIME DEFAULT '0000-00-00 00:00:00';
. . .
WHILE done = 0 DO
FETCH ElementaryPeriodsCursor INTO beginElemPeriod, endElemPeriod;
INSERT INTO ObjectsOfPeriods(NumOfPeriod,
....
select countNumOfPeriod,
....
where BeginInterval >= beginElemPeriod
and
EndInterval <= endElemPeriod
and
if (previousBeginElemPeriod=beginElemPeriod,1,(countNumOfPeriod:=countNumOfPeriod+1) and (previousBeginElemPeriod:=beginElemPeriod));

NDS · 18.04.2010 21:26:20

Я извиняюсь))
опишите, пожалуйста, более подробно данные строки:

              and

if (previousBeginElemPeriod=beginElemPeriod,countNumOfPeriod:=countNumOfPeriod+1,(countNumOfPeriod:=0) OR (previousBeginElemPeriod:=beginElemPeriod));

т.е. в условие фильтра нужно добавить еще и условие увеличения счетчика?... и при этом увеличивать само значение счетчика...
как это сделать?) - при выполнении запроса, как Вы написали, происходит ошибка...

И поводу времени выполнения запроса:
в теории, это нормально, если данная процедура будет выполнятся минуты две? - при продолжительном интервале (например, в 5 лет) и небольшом размере элементарных периодов (в 10 минут)... (т.е. таблица ElementaryPeriods имеет более, чем 280000 записей...)
При выполнении исправленного варианта

              and

if (previousBeginElemPeriod=beginElemPeriod,1,(countNumOfPeriod:=countNumOfPeriod+1) and (previousBeginElemPeriod:=beginElemPeriod));

так же - ошибка... (

Отредактированно NDS (18.04.2010 21:29:11)

vasya · 18.04.2010 21:41:13

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

set @countNumOfPeriod = 0;
set @previousBeginElemPeriod = '0000-00-00 00:00:00';
. . .
WHILE done = 0 DO
FETCH ElementaryPeriodsCursor INTO beginElemPeriod, endElemPeriod;
INSERT INTO ObjectsOfPeriods(NumOfPeriod,
....
select countNumOfPeriod,
....
where BeginInterval >= beginElemPeriod
and
EndInterval <= endElemPeriod
and
if (@previousBeginElemPeriod=beginElemPeriod,1,(@countNumOfPeriod:=@countNumOfPeriod+1) and (@previousBeginElemPeriod:=beginElemPeriod));

vasya · 18.04.2010 21:49:01

if (@previousBeginElemPeriod=beginElemPeriod,1,(@countNumOfPeriod:=@countNumOfPeriod+1) and (@previousBeginElemPeriod:=beginElemPeriod));

Если предыдущее значение периода совпадает с текущим (условие @previousBeginElemPeriod=beginElemPeriod), то будет возвращена 1.
Если же нет, то выполняется увеличение счетчика (@countNumOfPeriod:=@countNumOfPeriod+1) и переопределение предыдущего значения периода (@previousBeginElemPeriod:=beginElemPeriod). Каждая из этих операций возвращает true. true and true будет тоже true.

Таким образом наше условие if() в фильтре не забракует никаких строк (так как всегда возвращает true), а предназначено только для увеличения счетчика.

NDS · 18.04.2010 21:57:58

Уважаемый, vasya!
Благодарю за помощь и подробное объяснение!!! - с учетом последних исправлений все заработало...

Форум пользователей MySQL

#1 18.04.2010 19:31:41

Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#2 18.04.2010 20:11:23

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#3 18.04.2010 20:59:36

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#4 18.04.2010 21:12:05

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#5 18.04.2010 21:19:44

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#6 18.04.2010 21:24:42

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#7 18.04.2010 21:26:20

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#8 18.04.2010 21:41:13

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#9 18.04.2010 21:49:01

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

#10 18.04.2010 21:57:58

Re: Определение кол-ва записей, попадающих в заданный период, внутри курсора.

Board footer