выборка по датам из двух таблиц - MySql

[Arina]

Здравствуйте, подскажите, пожалуйста, как правильно оформит запрос. Кажется, что я делаю что-то неверно.

Вот моя задача:

1. В таблице Е хранятся записи пользователей как в ежедневнике:
login, start_date, end_date, event

2. Инициатор login='qwerty' хочет узнать, когда остальные пользователи свободны, т.е. у них в таблице нет записей в течение периода: now() до date_add(now(),interval N day) = [period]

3. Мои идеи были таковы:
 3.1. Найти записи инициатора в течение периода N - значит тогда он занят - сделала временную таблицу:

Код:

create temporary table t1 as
select start_date,end_date
from E
where login='qwerty'
and start_date between [period]
and end_date between [period];

3.2. Теперь надо найти записи пользователей, которые НЕ совпадают с записями инициатора - тогда остальные пользователи заняты.

Код:

select e.start_date,e.end_date
from e,t1
where e.login != t1.login
and not( e.start_date between t1.start_date and t1.end_date)
and not(e.end_date between t1.start_date and t1.end_date)
and e.start_date between [period]
and e.end_date between [period];

3.3. Остается найти время в течение [period], когда НЕ результаты из 3.2, это оставшеесф свободное время по всем пользователям.


Но, кажется, я мыслю неправильно. Подскажите, как лучше сделать запрос!

Спасибочки

[Oldy]

Простите, не очень ясна постановка задачи   , "свободны" это те записи которые не пересекаются по дате с записями "qwerty"?

[Arina]

Ух, как же объяснить...

Значит так, в базе записи

login, start_date, end_date, event

даты - типа datetime, поле event - событие, которое происходит в промежуток start_date и end_date.

Инициатор с логином 'qwerty' хочет найти свободные промежутки всех пользователей в период с now() до now() + N day. Причем, тогда, когда сам свободен.

Свободен - когда нет записи событие начало конец.

Вот

[Dimka]

согласен с Oldy - не понятно, почему свободное время, это не время qwerty.

По поводу совпадения времени: на прошлой неделе как раз решал такую задачу, правда на SQL Server. Обращаю внимание, что занятыми могут быть периоды с частичным наложением. а не полным включением. условия будут такими (пусть SD, FD - интервал тестового периода, а sd, fd - интервал фильтруемой записи):
1. SD >= sd and FD <= fd - запись полностью внутри периода или совпадает с ним
2. SD < sd and FD > fd - запись шире периода
3. SD >= sd and FD > fd and SD <= fd - запись начинается где-то в интервале, но завершается после окончания интервала
4. SD < sd and FD <= fd and FD >= sd - запись начинается до интервала, но завершается где-то в интервале

всё это через OR соедининить

это выборка занятых записей.
При отрицании можно упростить. Фактически, остаётся 2 случая:
1. FD < sd - интервал слева от записи
2. SD > fd - интервал справа от записи

[RXL]

Можно немножко соптимизировать:

Код:

and start_date >= [min_date]
and end_date <= [max_date]

Ведь не может же конец быть раньше начала.
То же самое во втором запросе.
Кроме того, можно объединить в один запрос посредством псевдонимов (AS).

Мой вариант:

Далее использую константы _min_ и _max_ для диапазона времени - замени чем хочешь.

Нас могут интересовать записи, пересекающие этот диапазон:
1) начало и/или конец находится в диапазоне;
2) начинается раньше, заканчивается позже (т.е. полная занятость).

Код:

SELECT login,start_date,end_date
 FROM e
 WHERE
  (start_date BETWEEN _min_ AND _max_ OR end_date BETWEEN _min_ AND _max_)
  OR (start_date<=_min_ AND end_date>=_max_);
 ORDER BY login,start_date

Дальше нужно делать программно.
У тебя в чем время измеряется? В смысле гранулярность: дни, часы, минуты, секунды, 15-ти минутные промежутки и т.д.

[Dimka]

не проверенное решение и для SQL Server - домой пора идти, извиняйте

Код:

select
	e1.start_date,
	e1.end_date 
from
	e e1, 
	e e2 
where
	e2.login = 'qwerty' and
	e1.login != e2.login and
	(e2.end_date < e1.start_date or
	e2.start_date > e1.end_date)

[Arina]

RXL, время как '2004-05-31 00:00:01' - datetime

[RXL]

Arina, считать надо с точностью до секунды? Есть же разумный предел - встречу в 1 секунду нет смысла отмечать.

[Arina]

Хорошо, теперь понятно, что происходит с вхождениями времени.

А если мне надо выбрать такие промежутки, которые НЕ совпадают с промежутками у 'qwerty'?

Т.е. я пытаюсь сначала найти занятые промежутки у 'qwerty', потом найти занятые промежутки остальных, но не тогда, когда 'qwerty' занят.

Наверное, я вообще все неправильно делаю. Ведь мне надо найти СВОБОДНЫЕ промежутки. Я думала сначала найти занятые, а потом выбирать время, когда не эти промежутки.

Ерунда какая получается.

[Arina]

Arina, считать надо с точностью до секунды? Есть же разумный предел - встречу в 1 секунду нет смысла отмечать.

Так удобнее всего было хранить дату и оперировать с ней. Все события пользователи вводят сами и никто не сделает в 1 секунду. А если сделает - пусть. А что плохого?

[Arina]

Вот возник вопрос - можно ли найти промежутки, когда не было записи?
За период, например, с now() до now()+15 дней?

[RXL]

Arina, я под _min_ и _max_ имел в виду твои "с now() до now()+15 дней". Т.е., выбираются записи, относящиется к этому диапазону, для всех пользователей. Делать через SQL и дальше нет смысла - тут есть всякие гадости, которые через SQL замучаешься отлавливать. По этому я и предлогаю дальше делать программно.
На каком языке пишешь? Какой API с MySQL используешь?

[Arina]

А, ты это имел ввиду программно. Я пишу на PHP.

[Arina]

RXL, а как же это можно сделать без запросов к базе? Ведь у меня все от нее отталкивается?

[Oldy]

Господа, просветите пожалуйста (не силен в MySQL),
NOW() - это "теперь"? Т.е. " сию минуту"? А now()+15 - это то, что будет через две недели от текущего момента? Или я не так понимаю?

[Arina]

Господа, просветите пожалуйста (не силен в MySQL),
NOW() - это "теперь"? Т.е. " сию минуту"? А now()+15 - это то, что будет через две недели от текущего момента? Или я не так понимаю?

Oldy, посмотри
http://dev.mysql.com/doc/mysql/ru/Date_and_time_functions.html

тут ужасно хорошо написано про даты.

now() - это сию секунду.

А добавлять можно и секунды, и дни, и месяца

[Arina]

Допустим, получила записи, когда пользователи заняты.

andrew 2004-06-03 08:00:00 2004-06-03 11:00:00 math
andrew 2004-06-03 14:00:00 2004-06-03 16:15:00 english
andrew 2004-06-08 10:00:00 2004-06-08 13:00:00 russian
arina 2004-06-01 09:00:00 2004-06-01 12:00:00 math
arina 2004-06-01 12:15:00 2004-06-01 14:30:00 english
arina 2004-06-02 10:05:00 2004-06-02 12:05:00 estonian
arina 2004-06-02 16:15:00 2004-06-02 18:00:00 shaping
jurik 2004-05-31 23:00:00 2004-05-31 23:30:00 body_bilding

Как же из них выдрать промежутки между?
unix(start_date) - unix(start_date)?

[Anonymous]

Arina, что-то я тебя не пойму...

у тебя есть (min, max) - интервал, применяй условия

1. SD >= sd and FD <= fd - запись полностью внутри периода или совпадает с ним
2. SD < sd and FD > fd - запись шире периода
3. SD >= sd and FD > fd and SD <= fd - запись начинается где-то в интервале, но завершается после окончания интервала
4. SD < sd and FD <= fd and FD >= sd - запись начинается до интервала, но завершается где-то в интервале

всё это через OR соедининить

это выборка занятых записей.
При отрицании можно упростить. Фактически, остаётся 2 случая:
1. FD < sd - интервал слева от записи
2. SD > fd - интервал справа от записи

где SD - min, FD - max. Первые 4 условия заменяются отрицанием последних 2-х.
ты получаешь ВСЕ записи, занятые (или свободные) в данный период. Если тебе нужно знать, свободен ли день вообще, то ты запрашиваешь занятые записи.

Не знаю MySQL, но в SQL Server это будет так

Код:

declare @maxv datetime
declare @minv datetime
set @maxv = '2004-02-01 0:00' -- месячный интервал
set @minv = '2004-01-01 0:00'
declare @result bit -- окончательный результат
declare @cnt int -- вспомогательная переменная
select
  @cnt = count(*) -- число занятых за период
from
  e
where
  not (maxv < e.start_date or minv > e.end_date)
if @cnt > 0
  set @result = 0 -- период занят
else
  set @result = 1 -- период свободен

т.е. факт нужно интерпретировать факт наличия/отсутствия записей

[Dimka]

ой, Гость - это я был

прошу прощения, тогда не дочитал до конца...

полчасика в Access и у меня получился такой вот запрос, выдающий свободные промежутки для приведённого выше примера данных

Код:

SELECT DISTINCT t.*
FROM
  (SELECT e1.end_date AS start_date, e2.start_date AS end_date
  FROM e e1, e e2
  WHERE e1.start_date < e2.start_date AND e2.start_date > e1.end_date) t
WHERE
  NOT EXISTS
    (SELECT *
    FROM e
    WHERE NOT (t.end_date <= e.start_date OR t.start_date >= e.end_date))

результаты такие

Код:

start_date	         end_date

31.05.2004 23:30:00	01.06.2004 9:00:00
01.06.2004 12:00:00	01.06.2004 12:15:00
01.06.2004 14:30:00	02.06.2004 10:05:00
02.06.2004 12:05:00	02.06.2004 16:15:00
02.06.2004 18:00:00	03.06.2004 8:00:00
03.06.2004 11:00:00	03.06.2004 14:00:00
03.06.2004 16:15:00	08.06.2004 10:00:00

[Dimka]

или в оптимизированном (но менее понятном) виде

Код:

SELECT  e1.end_date AS start_date, e2.start_date AS end_date
FROM e e1, e e2
WHERE
  e1.start_date < e2.start_date AND e2.start_date > e1.end_date AND
  NOT EXISTS
    (SELECT *
    FROM e
    WHERE NOT (e2.start_date <= e.start_date OR  e1.end_date >= e.end_date))

[RXL]

Код:

<?php

$con=mysql_connect(&#39;localhost&#39;,&#39;root&#39;);

mysql_select_db(&#39;test&#39;,$con);

$min="2004-05-31 09:00:00";
$max="2004-06-08 18:00:00";

$login="arina";

$query="SELECT login,
  unix_timestamp(if(start_date<&#39;$min&#39;,&#39;$min&#39;,start_date)) AS &#39;beg&#39;,
  unix_timestamp(if(end_date>&#39;$max&#39;,&#39;$max&#39;,end_date)) AS &#39;end&#39;
 FROM e
 WHERE
  (start_date BETWEEN &#39;$min&#39; AND &#39;$max&#39; OR end_date BETWEEN &#39;$min&#39; AND &#39;$max&#39;)
  OR (start_date<=&#39;$min&#39; AND end_date>=&#39;$max&#39;)
 ORDER BY login,start_date";

if(!($res=mysql_query($query,$con))) die("sql error");

$a=array();
while($row=mysql_fetch_array($res,MYSQL_ASSOC)) {
    if(!isset($a[$row[&#39;login&#39;]])) $a[$row[&#39;login&#39;]]=array();
    array_push($a[$row[&#39;login&#39;]],array(&#39;beg&#39;=>$row[&#39;beg&#39;],&#39;end&#39;=>$row[&#39;end&#39;]));
    }

mysql_free_result($res);

mysql_close($con);

$min=strtotime($min);
$max=strtotime($max);

function inverse_range($list) {
    global $min,$max;

    $a=array();
    $l=$min;
    foreach($list as $v) {
        if($v[&#39;beg&#39;]!=$l) {
            array_push($a,array(&#39;beg&#39;=>$l,&#39;end&#39;=>$v[&#39;beg&#39;]));
            }
        $l=$v[&#39;end&#39;];
        }
    if($l!=$max) {
        array_push($a,array(&#39;beg&#39;=>$l,&#39;end&#39;=>$max));
        }
    return $a;
    }

foreach($a as $k=>$v) {
    $a[$k]=inverse_range($v);
    }

function compare_ranges($list1,$list2) {
    $a=array_shift($list1);
    $b=array_shift($list2);
    $r=array();
    while(1) {
        if($a[&#39;beg&#39;]>=$b[&#39;end&#39;]) {
            if(!($b=array_shift($list2))) break;
            else continue;
            }
        if($a[&#39;end&#39;]<=$b[&#39;beg&#39;]) {
            if(!($a=array_shift($list1))) break;
            else continue;
            }
        array_push($r,array(
            &#39;beg&#39;=>($a[&#39;beg&#39;]<$b[&#39;beg&#39;] ? $b[&#39;beg&#39;] : $a[&#39;beg&#39;]),
            &#39;end&#39;=>($a[&#39;end&#39;]>$b[&#39;end&#39;] ? $b[&#39;end&#39;] : $a[&#39;end&#39;])
            ));
        if($a[&#39;end&#39;]>$b[&#39;end&#39;]) {
            if(!($b=array_shift($list2))) break;
            }
        else {
            if(!($a=array_shift($list1))) break;
            }
        }
    return $r;
    }

$r=array();
foreach($a as $k=>$v) {
    if($k!=$login) {
        $r[$k]=compare_ranges($a[$login],$v);
        }
    }

echo "<TABLE border=1>";

foreach($r as $k=>$v) {
    foreach($v as $v2) {
        echo "<TR><TD>$k<TD>".strftime("%Y-%m-%d %T",$v2[&#39;beg&#39;])."<TD>".strftime("%Y-%m-%d %T",$v2[&#39;end&#39;]);
        }
    }

echo "</TABLE>";

?>

Код:

<TABLE border=1>
<TR><TD>andrew<TD>2004-05-31 09:00:00<TD>2004-06-01 09:00:00
<TR><TD>andrew<TD>2004-06-01 12:00:00<TD>2004-06-01 12:15:00
<TR><TD>andrew<TD>2004-06-01 14:30:00<TD>2004-06-02 10:05:00
<TR><TD>andrew<TD>2004-06-02 12:05:00<TD>2004-06-02 16:15:00
<TR><TD>andrew<TD>2004-06-02 18:00:00<TD>2004-06-03 08:00:00
<TR><TD>andrew<TD>2004-06-03 11:00:00<TD>2004-06-03 14:00:00
<TR><TD>andrew<TD>2004-06-03 16:15:00<TD>2004-06-08 10:00:00
<TR><TD>andrew<TD>2004-06-08 13:00:00<TD>2004-06-08 18:00:00
<TR><TD>jurik<TD>2004-05-31 09:00:00<TD>2004-05-31 23:00:00
<TR><TD>jurik<TD>2004-05-31 23:30:00<TD>2004-06-01 09:00:00
<TR><TD>jurik<TD>2004-06-01 12:00:00<TD>2004-06-01 12:15:00
<TR><TD>jurik<TD>2004-06-01 14:30:00<TD>2004-06-02 10:05:00
<TR><TD>jurik<TD>2004-06-02 12:05:00<TD>2004-06-02 16:15:00
<TR><TD>jurik<TD>2004-06-02 18:00:00<TD>2004-06-08 18:00:00
</TABLE>

[Arina]

RXL, dimka, ой-ой. Ну вы даете, ребята!
Класс! Слов нет.

А у меня в это время получилось так:

Код:

$query = "select start_date,end_date from shedules
                where(
                start_date between now() and date_add(now(),interval 15 day)
                or 
                end_date between now() and date_add(now(),interval 15 day))
                or(
                start_date<=now() 
                and 
                end_date>=date_add(now(),interval 15 day))
                order by start_date";
$result = mysql_query($query);

$d = array();
while($row = mysql_fetch_array($result))
 {
 $d[] = array($row[0],$row[1]);
 }

$min = date("Y-m-d H:i:s",mktime(0,0,0,date("m"),date("d"),date("Y")));
$max = date("Y-m-d H:i:s",mktime(0,0,0,date("m"),date("d")+15,date("Y")));

$ar = array();
for($i=0;$i<count($d);$i++)
 {
 if($i == 0)
  if($min < date("Y-m-d H:i:s", strtotime($d[$i][0])))
   {
    echo $min." < ".date("Y-m-d H:i:s", strtotime($d[$i][0]))."<br>";
    $ar[] = array("start_date"=>$min,"end_date"=>date("Y-m-d H:i:s", strtotime($d[$i][0])));
   }

 if(date("Y-m-d H:i:s", strtotime($d[$i][1])) < date("Y-m-d H:i:s", strtotime($d[$i+1][0])))
  {
  echo date("Y-m-d H:i:s", strtotime($d[$i][1]))." < ".date("Y-m-d H:i:s", strtotime($d[$i+1][0]))."<br>";
  $ar[] = array("start_date"=>date("Y-m-d H:i:s", strtotime($d[$i][1])),"end_date"=>date("Y-m-d H:i:s", strtotime($d[$i+1][0])));
  }
 
 if($i == count($d)-1)
  if(date("Y-m-d H:i:s", strtotime($d[$i][1])) < $max)
   {
   echo date("Y-m-d H:i:s", strtotime($d[$i][1]))." < ".$max."<br>";
   $ar[] = array("start_date"=>date("Y-m-d H:i:s", strtotime($d[$i][1])),"end_date"=>$max);
   }
 }

Результаты:

2004-05-31 00:00:00 < 2004-06-01 09:00:00
2004-06-01 12:00:00 < 2004-06-01 12:15:00
2004-06-01 14:30:00 < 2004-06-02 10:05:00
2004-06-02 12:05:00 < 2004-06-02 16:15:00
2004-06-02 18:00:00 < 2004-06-03 08:00:00
2004-06-03 11:00:00 < 2004-06-03 14:00:00
2004-06-03 16:15:00 < 2004-06-04 14:15:00
2004-06-04 16:15:00 < 2004-06-08 10:00:00
2004-06-08 13:00:00 < 2004-06-15 00:00:00

[Arina]

Фуф, будет завтра над чем подумать/*ликующе*/
Ну нет слов таких, чтоб выразить мою благодарность. Завтра разберусь и стану еще чуточку умнее, ура!

До свиданья, на сегодня мне хватит, спать пора.

[RXL]

Arina, тебе осталась самая сложная часть - проверить правильность результатов.
Недостаток моей программы: не показывает тех, кого в выборке не оказалось, да и кого в базе воодще нет.

[Arina]

RXL, а зачем искать тех, кто не попал в выборку?

Допустим, есть список таких промежутков? Можно найти общие по всем пользователям?

В БД удобно в порядке убывания было бы найти, например, в такое время свободно n человек итд. Но надо ли забивать все это в БД?

[RXL]

SELECT DISTINCT login FROM e;
Ну или держать список отдельной таблицей - это лучше и правильнее.

Если можно вычислить наложение для двух пользователей, то почему бы не сделать то же самое для любого числа?
Логически это u1 & u2 для двух, и u1 & ... & uN для N человек.
Использовать так: result=f(u1,f(u2,f(u3,u4)))  f() - это compare_range() из моего примера.

[Arina]

Я правильно понимаю, что-то типо такого:

Код:

$result = array();
foreach($times as $k=>$v){
           if(count($result)==0)
            $result = $v;
           else
            $result = compare_ranges($v,$result);
           }

:?:

 :arrow:

[RXL]

Arina, теперь правильно  

В БД удобно в порядке убывания было бы найти, например, в такое время свободно n человек итд.

Для этого надо несколько поменять compare_ranges(), чтобы выдавать не только свободные промежутки, а все, но со счетчиком занятых людей для каждого промежутка.

[Arina]

Arina, теперь правильно  

В БД удобно в порядке убывания было бы найти, например, в такое время свободно n человек итд.

Для этого надо несколько поменять compare_ranges(), чтобы выдавать не только свободные промежутки, а все, но со счетчиком занятых людей для каждого промежутка.

RXL, а в моей задаче этого не надо - счетчик занятых людей. Поступаю так, если интересно  :

1. Пользователь вводит длительность встречи(конференции,...), ее приоритет, период Р, в течение которого проводить поиск, а также выбирает юзеров из списка - вот только по ним и ищем + в некоторые моменты добавляем самого ининциатора в поиск(напр., те же его свободные промежутки).

2. Те, у кого не было вообще занятых промежутков в течение периода Р - потом просто добавить в вывод результатов.

3. С помощью compare_ranges проводиться выбор свободного времени для всех из списка - что и выдается в результате инициатору как промежутки для выбора.

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

Приоритет встречи + Ранг инициатора
 - Приоритет занятости - Ранг юзера>=1

5. Этот массив разбивается на 2: те, кто должен прийти (напр., если приоритет встречи выше и ранг инициатора выше) и те, кому можно приглашение послать (напр., приоритет встречи выше, а ранг инициатора ниже итд).

6. Этот двойной массив тоже выдается инициатору - он решит, беспокоить ему занятых людей или нет

Вот и все, вроде

[Dimka]

Люди, а зачем это делать на стороне веб-сервера? Оптимизация подразумевает минимизацию сетевого трафика и экономию памяти в том числе. Положим, лог будет пару сотен Мб - вы его будете на веб передавать, чтобы там по массивам бегать? А если одновременно несколько десятков человек будут работать? Каждому будет такой массив выделяться? Имхо, сей путь хоть и привлекает своей "лёгкостью" в реализации, но череват многими неприятностями в реальной работе. (Масштабов данной разработки не знаю, но от вредных привычек лучше избавляться сразу ).

В имеющемся логе присутствует ВСЯ необходимая информация. Средствами SQL можно получить ответ на ЛЮБОЙ вопрос о занятости и незанятости людей, статистику (сколько когда народу занято или свободно). Если в MySQL нет ограничений на подзапросы, то всё в ваших руках (точнее, голове). Просто надо чётко понимать: 1) что надо получить, 2) придумать последовательность операций над пространствами данных, понимая, что получаем на каждом шаге преобразований.

Кратенькая лекция по SQL-образному мышлению.

SELECT выполняется следующим образом

1. Берутся все таблицы из FROM и строится их декартово произведение
2. Если есть конструкции JOIN, то из полученного декартова произведения убираются все строки, не попадающие в условия ON. (связывание)
3. К оставшимся строкам применяются условия WHERE (базовый горизонтальный фильтр) - убираются все несоответствующие.
4. К оставшимся строкам применяется группировка GROUP BY, вычисляются агрегатные функции COUNT, SUM, MIN, MAX и т.п.
5. К оставшимся строкам применяются условия HEAVING (групповой горизонтальный фильтр) - убираются все несоответствующие.
6. Проводится выборка тех полей, которые требуются для результата (вертикальный фильтр).
7. Определяются значения вычиляемых полей, применяются всякие DISTINCT и т.п..
8. Применяется сортировка ORDER BY.

По этой схеме (в обратном порядке) можно провести анализ желаемого результата - придём к исходным данным, которые нужны для получения результата. По ходу анализа выявляются потребности в подзапросах. Так рождается идея построения запроса. Когда получен окончательный запрос, дающий правильный результат - производится его оптимизация (чтобы он решал задачу, но быстрее и был проще). С опытом анализ происходит интуитивно: достаточно одного взгляда на имеющиеся и требуемые данные, чтобы ответить: 1) возможно ли получить ответ, 2) как примерно это делать.