SQL заявка, свързваща няколко таблици (MySQL)

Имам нужда от "малко" помощ за SQL заявка (MySQL).

Имам следните таблици:

COURIERS таблица:

    +------------+
    | COURIER_ID |
    +------------+

DELIVERIES таблица:

    +-------------+------------+------------+
    | DELIVERY_ID | COURIER_ID | START_DATE |
    +-------------+------------+------------+

ORDERS таблица:

    +----------+-------------+-------------+
    | ORDER_ID | DELIVERY_ID | FINISH_DATE |
    +----------+-------------+-------------+

COORDINATES таблица:

    +-------------+-----+-----+------+
    | DELIVERY_ID | LAT | LNG | DATE |
    +-------------+-----+-----+------+

В реалната база данни имам повече колони във всяка таблица, но за този пример горните колони са достатъчни.

От какво се нуждая?

  • SQL заявка, която връща всички куриери [COURIER_ID], последната им доставка [DELIVERY_ID] (на базата на последното START_DATE), последната координата на доставката [LAT и LNG] (на базата на последното DATE) и оставащия брой поръчки (общо поръчки на последна доставка, които нямат FINISH_DATE).

  • Куриерът не може да има доставки, в този случай искам DELIVERY_ID = NULL, LAT = NULL и LNG = NULL в резултата.

  • Доставката може да няма координати, в този случай искам LAT = NULL и LNG = NULL в резултата.

Какво можах да направя?

SELECT c.`COURIER_ID`,
       d.`DELIVERY_ID`,
       r.`LAT`,
       r.`LNG`,
  (SELECT COUNT(DISTINCT `ORDER_ID`)
   FROM `ORDERS`
   WHERE `DELIVERY_ID` = d.`DELIVERY_ID`
     AND `FINISH_DATE` IS NULL) AS REMAINING_ORDERS

FROM `COURIERS` AS c
LEFT JOIN `DELIVERIES` AS d USING (`COURIER_ID`)
LEFT JOIN `COORDINATES` AS r ON r.`DELIVERY_ID` = d.`DELIVERY_ID`

WHERE (CASE WHEN
         (SELECT MAX(`START_DATE`)
          FROM `DELIVERIES`
          WHERE `COURIER_ID` = c.`COURIER_ID`) IS NULL THEN d.`START_DATE` IS NULL ELSE d.`START_DATE` =
         (SELECT MAX(`START_DATE`)
          FROM `DELIVERIES`
          WHERE `COURIER_ID` = c.`COURIER_ID`) END)
  AND (CASE WHEN
         (SELECT MAX(`DATE`)
          FROM `COORDINATES`
          WHERE `DELIVERY_ID` = d.`DELIVERY_ID`) IS NULL THEN r.`DATE` IS NULL ELSE r.`DATE` =
         (SELECT MAX(`DATE`)
          FROM `COORDINATES`
          WHERE `DELIVERY_ID` = d.`DELIVERY_ID`) END)
GROUP BY c.`COURIER_ID`
ORDER BY d.`START_DATE` DESC

Проблемът е, че тази заявка е много бавна (от 5 до 20 секунди), когато имам над 5k COORDINATES и понякога не връща всички куриери.

Благодаря ви много за всяко решение.


mysql sql group-by select left-join
person Lucas NN    schedule 05.09.2014    source източник
comment
Вие наистина не сте задали въпрос. Изглежда, че търсите помощ за оптимизиране на заявката, която вече сте написали. Трябва да изясните въпроса си. Ако търсите помощ за оптимизиране на заявката, трябва да посочите какви са желаните резултати.   -  person axiopisty    schedule 05.09.2014
comment
Вярвам, че това е въпрос, защото тази заявка не работи, както ми трябва, независимо от производителността. Производителността е само един от проблемите. Благодаря.   -  person Lucas NN    schedule 05.09.2014
comment
Трябва ли да върнете всички резултати в една заявка към базата данни? Или би било добре да изпратите няколко заявки към базата данни, стига крайният резултат да е правилният набор от данни?   -  person axiopisty    schedule 05.09.2014
comment
Също така би било полезно, ако включите примерни данни във вашите таблици заедно с очакваните резултати.   -  person axiopisty    schedule 05.09.2014
comment
Използването на max(date) може да получи няколко реда вместо последния.   -  person Jaugar Chang    schedule 05.09.2014
comment
@axiopisty За мен най-добрият начин е да използвам само една заявка, но ако можете да мислите за добро решение, използвайки повече от една заявка, мога да променя кода си.   -  person Lucas NN    schedule 05.09.2014
comment
@axiopisty Ще се опитам да получа примерни данни. Благодаря.   -  person Lucas NN    schedule 05.09.2014
comment
@JaugarChang О, добро око, не го разбрах.   -  person Lucas NN    schedule 05.09.2014
comment
Можете първо да генерирате две подзаявки от delivery's last coordinates и courier's last deliveries. Тогава ги съединете заедно. Би трябвало да е по-ефективно. Има решение за получаване на първия или последния ред в конкретна група с помощта на MySql, което можете да направите справка   -  person Jaugar Chang    schedule 05.09.2014
comment
@axiopisty Защо изтри отговора си? Бих искал да опитам и двата отговора и да избера най-добрия. =)   -  person Lucas NN    schedule 05.09.2014
comment
Изтрих го, защото беше неправилно. Забравих да включа броя на оставащите поръчки. Но сега коригирах отговора и го публикувах отново. Все още може да е грешно поради моето тълкуване на вашия въпрос. Не е ясно дали въпросната крайна_дата е тази в таблицата с поръчки или таблицата с доставки.   -  person axiopisty    schedule 05.09.2014
comment
@axiopisticy Благодаря. FINISH_DATE в таблицата DELIVERIES е безполезен в този случай. Ще редактирам въпроса.   -  person Lucas NN    schedule 05.09.2014

Отговори (2)


arrow_upward
1
arrow_downward

Не успях да тествам тази заявка, тъй като в момента нямам настроена база данни mysql, още по-малко с тази схема и примерни данни. Но мисля, че това ще ви свърши работа:

select
  c.courier_id
  , d.delivery_id
  , co.lat
  , co.lng
  , oc.cnt as remaining_orders
from
  couriers c
  left join (
    select
      d.delivery_id
      , d.courier_id
    from
      deliveries d
      inner join (
        select
          d.delivery_id
          , max(d.start_date) as start_date
        from
          deliveries d
        group by
          d.delivery_id
      ) dmax on dmax.delivery_id = d.delivery_id and dmax.start_date = d.start_date
  ) d on d.courier_id = c.courier_id
  left join (
    select
      c.delivery_id
      , c.lat
      , c.lng
    from
      coordinates c
      inner join (
        select
          c.delivery_id
          , max(c.date) as date
        from
          coordinates c
        group by
          c.delivery_id
      ) cmax on cmax.delivery_id = c.delivery_id and cmax.date = c.date
  ) co on co.delivery_id = d.delivery_id
  left join (
    select
      o.delivery_id
      , count(o.order_id) as cnt
    from
      orders o
    where
      o.finish_date is null
    group by
      o.delivery_id
  ) oc on oc.delivery_id = d.delivery_id
person axiopisty    schedule 05.09.2014
comment
Благодаря, ще опитам отговора ви и ще се върна, за да кажа дали работи. - person Lucas NN; 05.09.2014

arrow_upward
2
arrow_downward

Опитайте тази:

SELECT C.COURIER_ID, D.DELIVERY_ID, D.START_DATE, D.FINISH_DATE, 
       B.LAT, B.LNG, B.DATE, C.NoOfOrders
FROM COURIERS C 
LEFT JOIN ( SELECT * 
            FROM (SELECT * 
                  FROM DELIVERIES D 
                  ORDER BY D.COURIER_ID, D.START_DATE DESC
                ) A 
            GROUP BY COURIER_ID
          ) AS A ON C.COURIER_ID = A.COURIER_ID 
LEFT JOIN ( SELECT * 
            FROM (SELECT * 
                  FROM COORDINATES CO 
                  ORDER BY CO.DELIVERY_ID, CO.DATE DESC
                ) B
            GROUP BY CO.DELIVERY_ID
          ) AS B ON A.DELIVERY_ID = B.DELIVERY_ID 
LEFT JOIN ( SELECT O.DELIVERY_ID, COUNT(1) NoOfOrders
            FROM ORDERS O WHERE FINISH_DATE IS NULL
            GROUP BY O.DELIVERY_ID
          ) AS C ON A.DELIVERY_ID = C.DELIVERY_ID;
person Saharsh Shah    schedule 05.09.2014
comment
Благодаря, ще опитам отговора ви и ще се върна, за да кажа дали работи. - person Lucas NN; 05.09.2014