Вычислить проценты количества в SQL относительно категории
Я работаю в MySQL. Изначально у меня была такая таблица:
Моя задача состоит в том, чтобы рассчитать процент бронирований, которые были отменены для людей, которые находились в длинном списке ожидания, и для людей, которые ждали недолго. После некоторых манипуляций я получил следующий код:
SELECT
CASE WHEN days_in_waiting_list > (SELECT AVG(days_in_waiting_list) FROM Bookings) THEN 'Long wait'
ELSE 'Short wait'
END AS waiting,
is_canceled, COUNT(*), count(*) * 100.0 / sum(count(*)) over() AS perc_cancelled
FROM Bookings
GROUP BY waiting, is_canceled;
Результирующая таблица:
Но я хочу, чтобы проценты рассчитывались для категории, а не для всей таблицы. Чтобы сумма процентов в Коротком ожидании была равна 100, то же самое и в Долгом ожидании. Я хочу, чтобы это было так:
Есть ли способ сделать это? Я знаю, что можно использовать over (раздел, ожидая), но это дает мне ошибку
Код ошибки: 1054. Неизвестный столбец «ожидает» в «разделе окна»
Ответы 4
вы хотите, чтобы проценты рассчитывались для каждой категории, поэтому для perc_cancelled необходима группировка по категории ожидания
count() * 100.0 / sum(count()) over(partition by wait) КАК perc_cancelled
да, у меня была такая же идея. Но это дает мне ошибку «Неизвестный столбец «ожидает» в «разделе окна»
Я только что попробовал MariaDB 10.5.8, и он отлично работает...
WITH cte AS (
SELECT
CASE WHEN days_in_waiting_list > (SELECT AVG(days_in_waiting_list) FROM Bookings)
THEN 'Long wait'
ELSE 'Short wait'
END AS waiting,
is_canceled,
COUNT(*) as subTotal,
sum(count(*) over (partition by waiting ORDER BY is_canceled ASC) as totalSum,
FROM Bookings
GROUP BY waiting, is_canceled;
)
SELECT waiting,
is_canceled,
subTotal* (100.0)/totalSum as percentage
FROM cte
group by waiting,is_canceled
Большое спасибо за ваш вклад! Но это показывает мне, что есть синтаксическая ошибка, и я не могу понять, где.
Код ошибки: 1064. У вас ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, на предмет правильного синтаксиса для использования рядом с «как общая сумма, промежуточная сумма * (100,0) / общая сумма в процентах ОТ ГРУППА заказов по wai» в строке 7.
можешь попробовать сейчас?
Я бы использовал оконные функции для вычисления среднего значения, а затем агрегирования:
select waiting, is_cancelled,
count(*) / sum(count(*)) over(partition by waiting) as ratio
from (
select b.*,
case when days_in_waiting_list > avg(days_in_waiting_list) over()
then 'Long Wait'
else 'Short wait'
end as waiting
from bookings b
) b
group by waiting, is_cancelled
order by waiting, is_cancelled
Я не знаю, почему у меня не работают другие решения, может быть, у меня старая версия MySQL, но это работает!! Большое спасибо :)
Создание таблицы только с соответствующими данными и 40 случайными строками:
CREATE TABLE test (
id INT AUTO_INCREMENT PRIMARY KEY,
is_canceled INT DEFAULT FLOOR(RAND() * 2),
days_in_waiting_list INT DEFAULT FLOOR(RAND() * 100)
);
INSERT INTO test () VALUES (), (), (), (), (), (), (), (), (), ();
INSERT INTO test () VALUES (), (), (), (), (), (), (), (), (), ();
INSERT INTO test () VALUES (), (), (), (), (), (), (), (), (), ();
INSERT INTO test () VALUES (), (), (), (), (), (), (), (), (), ();
Вы были очень близки, просто добавьте PARTITION BY waiting:
SELECT
CASE WHEN days_in_waiting_list > (
SELECT AVG(days_in_waiting_list)
FROM test
) THEN 'Long' ELSE 'Short' END AS waiting,
is_canceled,
COUNT(*),
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY waiting)
FROM test
GROUP BY waiting, is_canceled;
@Strawberry Извините, я не знал, как это сделать, но я изучу эту тему, спасибо!