SQL - перебирать когда-либо строку таблицы в MySQL?

Итак, у меня есть 2 таблицы, communication и movement.

communication имеет столбцы fromID, timestamp с идентификатором вызывающего абонента и временем совершения звонка. Затем у меня есть другая таблица movement, в которой есть ID,timestamp,x,y, в которой есть идентификатор человека, его местоположение (x,y) и время, когда они находятся в этом месте.

Я хочу написать запрос, который выглядит примерно так:

For every single row of communication(R)
    SELECT * FROM movement m
    WHERE m.ID = R.fromID && m.timestamp <= R.timestamp
    ORDER BY timestamp

По сути, это поиск ближайшего movement timestamp для заданного communication timestamp. После этого, в конце концов, я хочу найти местоположение (x,y) звонка на основе данных movement.

Как бы я это сделал? Я знаю, что существует подход, основанный на наборах, но я не хочу делать это таким образом. Я посмотрел на cursors, но у меня такое ощущение, что производительность на нем ужасна.

Так есть ли способ сделать это с помощью цикла? По сути, я хочу перебрать каждую строку communication и получить результат.

Я пробовал что-то вроде этого:

DELMITER $$ 
CREATE PROCEDURE findClosestTimestamp() 
BEGIN 
DECLARE commRowCount DEFAULT 0; 
DECLARE i DEFAULT 0; 
DECLARE ctimestamp DEFAULT 0; 
SELECT COUNT(*) FROM communication INTO commRowCount; 

SET i = 0; 
WHILE i < commRowCount DO 
SELECT timestamp INTO ctimestamp FROM communication c 
SELECT * FROM movement m 
WHERE m.vID = c.fromID && m.timestamp <= R.timestamp
END$$ 
DELIMITER ;

Но я знаю, что это совершенно неправильно.

Единственный способ сделать это курсоры? Я просто не могу найти пример этого нигде в Интернете, и я совершенно не знаком с процедурами в SQL.

Любое руководство будет принято с благодарностью, спасибо!!


mysql sql loops stored-procedures cursors
person ocean800    schedule 11.06.2015    source источник
comment
Я знаю, что существует подход, основанный на наборах, но я не хочу делать это таким образом. Почему бы и нет? В чем хороша РСУБД?   -  person Karl Kieninger    schedule 11.06.2015
comment
Ну, на самом деле у меня уже есть версия на основе набора... но, к сожалению, я не должен делать это в формате, основанном на наборе.   -  person ocean800    schedule 11.06.2015
comment
@ocean800 Вам нужен только один ряд (ближайший) от движения?   -  person Barranka    schedule 11.06.2015
comment
Причина, по которой производительность курсоров ужасна, заключается в том, что они делают именно это. Если вы хотите перебирать строки, используйте курсор - это то, что делает курсор (и именно поэтому он работает плохо). Есть ли веская причина, по которой вы не пытаетесь сделать это на основе набора?   -  person Dan Field    schedule 11.06.2015
comment
@Barranka Мне нужна каждая movement строка, в которой timestamp ближе всего к строке communication.   -  person ocean800    schedule 11.06.2015
comment
@DanField Понятно .. значит, единственный способ сделать это - через cursors? К сожалению, да, я не могу использовать подход, основанный на наборах.   -  person ocean800    schedule 11.06.2015
comment
Вы можете реализовать свой собственный курсор (что вы пытаетесь сделать здесь), но вы также можете использовать его рабочую реализацию, если у вас нет очень конкретной оптимизации, которую вы думаете, что можете осуществить.   -  person Dan Field    schedule 11.06.2015
comment
@DanField Понятно .. спасибо за помощь. Тогда я посмотрю больше на курсоры.   -  person ocean800    schedule 11.06.2015
comment
@ocean800 Думаю, я недостаточно ясно выразился... вам нужно только одно движение для каждой записи связи? Если вам нужны все движения для каждого сообщения, почему бы вам просто не написать запрос с внутренним соединением и соответствующими критериями упорядочения?   -  person Barranka    schedule 11.06.2015
comment
@ocean800, я бы по-прежнему рекомендовал подход, основанный на наборах - курсоры обычно следует использовать только в крайнем случае. Мне сложно представить, когда вам действительно нужно их использовать.   -  person Dan Field    schedule 11.06.2015
comment
@Barranka Да, мне нужно только одно движение для каждой записи общения. Во всяком случае, проблема в том, что мне нужно написать процедуру для этого, на самом деле у меня уже есть работающий подход на основе набора.   -  person ocean800    schedule 11.06.2015
comment
Не пинать дохлую лошадь, но и избегать логики, основанной на множествах, без причины — вот что: неразумно. Если вам это навязывают, то вам нужно бороться с этим зубами и когтями: объяснить это короткими словами и показать им миллион ссылок (легко найденных), объясняющих, почему циклы в СУБД глупы. Не сдавайся.   -  person johnjps111    schedule 11.06.2015

Ответы (1)


arrow_upward
9
arrow_downward

Давайте посмотрим, смогу ли я указать вам правильное направление с помощью курсоров:

delimiter $$
create procedure findClosestTimeStamp()
begin
    -- Variables to hold values from the communications table
    declare cFromId int;
    declare cTimeStamp datetime;
    -- Variables related to cursor:
    --    1. 'done' will be used to check if all the rows in the cursor 
    --       have been read
    --    2. 'curComm' will be the cursor: it will fetch each row
    --    3. The 'continue' handler will update the 'done' variable
    declare done int default false;
    declare curComm cursor for
        select fromId, timestamp from communication; -- This is the query used by the cursor.
    declare continue handler for not found -- This handler will be executed if no row is found in the cursor (for example, if all rows have been read).
        set done = true;

    -- Open the cursor: This will put the cursor on the first row of its
    -- rowset.
    open curComm;
    -- Begin the loop (that 'loop_comm' is a label for the loop)
    loop_comm: loop
        -- When you fetch a row from the cursor, the data from the current
        -- row is read into the variables, and the cursor advances to the
        -- next row. If there's no next row, the 'continue handler for not found'
        -- will set the 'done' variable to 'TRUE'
        fetch curComm into cFromId, cTimeStamp;
        -- Exit the loop if you're done
        if done then
            leave loop_comm;
        end if;
        -- Execute your desired query.
        -- As an example, I'm putting a SELECT statement, but it may be
        -- anything.
        select *
        from movement as m
        where m.vID = cFromId and m.timeStamp <= cTimeStamp
        order by timestampdiff(SECOND, cTimeStamp, m.timeStamp)
        limit 1;
    end loop;
    -- Don't forget to close the cursor when you finish
    close curComm;
end $$
delimiter ;

Использованная литература:

person Barranka    schedule 11.06.2015
comment
Большое спасибо!! Я даже не могу сказать, насколько это помогает :) - person ocean800; 11.06.2015
comment
@ocean800 рад помочь. Я отредактировал свой ответ, включив в него ссылки на соответствующие разделы справочного руководства. Я предлагаю вам прочитать их. Ваше здоровье - person Barranka; 11.06.2015