Как использовать вычисление агрегатной функции в качестве критерия фильтрации

#mysql #sql #count #subquery #inner-join

#mysql #sql #количество #подзапрос #внутреннее соединение

Вопрос:

У меня есть таблицы по заказам на бронирование

 Bookings (booking_id, booking_time, driver_id, customer_id)
Drivers (driver_id, name)

Мне нужно идентифицировать всех клиентов, у которых по крайней мере половина их бронирований была выполнена одним и тем же водителем в течение последних 30 дней.

Объяснение

У клиента x было 12 бронирований, из которых 7 были завершены driver_01.
У клиента y было 10 бронирований, из которых 4 были завершены driver_02.
У клиента z было 3 бронирования, из которых 3 были завершены driver_03

На выходе будут возвращены driver_01 и driver_03 с booking_id

Я пытался использовать самосоединение и подсчет агрегатов, но я все еще не понимаю логики

Ответ №1:

Вы можете сделать это с помощью оконных функций:

 select b.*, d.name as driver_name
from driver d
inner join (
    select b.*, 
        count(*) over(partition by driver_id, customer_id) / count(*) over(partition by customer_id) as driver_ratio
    from booking b
) b on b.driver_id = d.driver_id
where driver_ratio >= 0.6

1. @user14767369 . , , Я думаю, что это лучшее решение, как с точки зрения простоты, так и производительности.

2. да, я так думаю, я никогда раньше не использовал partition и count over, все еще ищу ссылку на эту функцию. Спасибо!

Ответ №2:

Вы правильно определили, что объединение полезно для получения общего количества бронирований на одного клиента, поэтому

 DROP TABLE IF EXISTS BOOKINGS,drivers;

create table Bookings (booking_id int, driver_id int, customer_id varchar(3));

create table Drivers (driver_id int, name varchar(3));

insert into bookings values
(1,1,1),(2,1,1),(3,2,1),(4,2,1),(5,3,1),
(6,1,2),(7,2,1);

insert into drivers values
(1,'aaa'),(2,'bbb');

select b.driver_id,d.name,b.customer_id,count(*) bcount,scount, count(*) / scount * 100 percent
from bookings b
join (select customer_id,count(*) scount from bookings group by customer_id) s
        on s.customer_id = b.customer_id
join  drivers d on d.driver_id = b.driver_id
group by driver_id,d.name,customer_id having count(*) / scount * 100 >= 50;

 ----------- ------ ------------- -------- -------- ---------- 
| driver_id | name | customer_id | bcount | scount | percent  |
 ----------- ------ ------------- -------- -------- ---------- 
|         1 | aaa  | 2           |      1 |      1 | 100.0000 |
|         2 | bbb  | 1           |      3 |      6 |  50.0000 |
 ----------- ------ ------------- -------- -------- ---------- 
2 rows in set (0.002 sec)

Легче протестировать 50%, чем 60% — не забудьте внести изменения в соответствии с вашими требованиями.

Ответ №3:

Я проверил @P.Код Сэлмона, поскольку у меня тоже был аналогичный подход, и я нахожу @P.Лосось — это правильный ответ, однако, если вы передадите дату, скажем, например, вы хотите вернуться только за последние 30 дней, как вы указали в своем вопросе, это может не сработать

смотрите ниже

 SELECT b.booking_date,b.driver_id, d.name, b.customer_id, COUNT(*) b_count, c_count, COUNT(*) / c_count * 100 percent
FROM bookings b 
JOIN (SELECT customer_id, COUNT(*) c_count from bookings GROUP BY customer_id) c ON c.customer_id = b.customer_id 
JOIN drivers d ON d.driver_id = b.driver_id 
WHERE b.booking_date BETWEEN NOW() - INTERVAL 30 DAY AND NOW()
GROUP BY booking_date, driver_id, d.name, customer_id 
HAVING COUNT(*) / c_count * 100 >= 50;