Как выбрать подсчет отдельных (уникальных дальнобойщиков) без группы по функциям и, возможно, без использования наличия (не уверен в последнем)

У меня есть задача, но я не могу ее решить:

Есть дальнобойщики, которым приходится ездить между городами.
У нас есть данные об этих поездках в нашей базе данных в 2 таблицах:

  1. trucker_traffic

     tt_id (key)                
     date                
     starting_point_coordinate                
     destination_coordinate                
     traveller_id                
     event_type ('travel', 'accident')                
     parent_event_id (For 'accident' event type it's tt_id of the original travel. There might be few accidents within one travel.)             
    
  2. trucker_places

     coordinate (key)                
     country                
     city                
    

Мне нужен SQL-запрос, чтобы получить количество всех уникальных водителей грузовиков, которые ездили более одного раза из или в Лондон в июне 2020 года.
В том же запросе вытащите количество тех, кто попал в аварию.

Пример моих попыток

SELECT
    count(distinct(tt.traveller_id)),
        
FROM trucker_traffic tt

JOIN trucker_places tp
    ON tt.starting_point_coordinate = tp.coordinate
     OR tt.destination_coordinate   = tp.coordinate
WHERE 
    tp.city = 'London'
    AND month(tt.date) = 6
    AND year(tt.date) = 2020
    
GROUP BY tt.traveller_id

HAVING count(tt.tt_id) > 1

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

Для второй части задачи (где я выбрал количество поездок с авариями - я думаю, что хорошо использовать такую функцию

SUM(if(count(tt_id = parent_event_id),1,0))

Но я не уверен

Источник
  • 0
    Почему вы говорите в заголовке, что нельзя использовать GROUP BY Это значительно усложняет задачу.
  • 0
    Примечание: вы можете ограничить страну Англией, поскольку в мире существуют другие города с таким же названием ( en.wikipedia.org/wiki/London_(disambiguation) ).
Codelisting
за 1 против
Лучший ответ

Это довольно сложно, поэтому делайте это шаг за шагом.WITH статьи помогают в этом.

Шаги

  1. Найдите поездки из Лондона и в Лондон на июнь 2020 года. Вы можете использоватьIN илиEXISTS чтобы узнать, не было ли в путешествии несчастных случаев.
  2. Сгруппируйте путешествия по Лондону по путешественникам, подсчитайте поездки и поездки в результате несчастных случаев и оставьте только тех путешественников, которые совершили более одного путешествия.
  3. Возьмите этот набор результатов, чтобы подсчитать путешественников и подвести итоги их путешествий.

Запрос

with london_travels as
(
  select 
    traveller_id,
    case when tt_id in
      (select parent_event_id from trucker_traffic where event_type = 'accident')
    then 1 else 0 end as accident
  from trucker_traffic tt
  where event_type = 'travel'
  and month(tt.date) = 6
  and year(tt.date) = 2020
  and exists
  (
    select 
    from trucker_places tp
    where tp.coordinate in (tt.starting_point_coordinate, tt.destination_coordinate)
    and tp.city = 'London'
  )
)
, london_travellers as
(
  select 
    traveller_id,
    count(*) as travels,
    sum(accident) as accident_travels
  from london_travels
  group by traveller_id
  having count(*) > 1;
)
select
  count(*) as total_travellers,
  sum(travels) as total_travels,
  sum(accident_travels) as total_accident_travels
from london_travellers;

Если ваша версия MySQL не поддерживаетWITH предложений, вы, конечно, можете просто вложить запросы. Т.е.

with a as (...), b as (... from a) select * from b;

становится

select * from (... from (...) a) b;

Вы говорите в заголовке запроса, что не хотитеGROUP BY в запросе. Это возможно, но усложняет запрос. Если вы хотите это сделать, я оставляю это вам как задачу. Подсказка: вы можете выбрать путешественников и подсчитать в подзапросах на каждого путешественника.

Codelisting
Популярные категории
На заметку программисту