英文:
Find out the percentage for each case
问题
Here's a shorter query to calculate the successful delivery percentage for each runner without using a Common Table Expression (CTE) or subqueries:
SELECT
runner_id,
(COUNT(*) FILTER (WHERE cancellation = '')) * 100.0 / COUNT(*) AS percentages_successful_deliveries
FROM runner_orders
GROUP BY runner_id
ORDER BY runner_id;
This query uses the FILTER clause to count the successful deliveries where cancellation is an empty string, and then calculates the percentage for each runner. It should provide the same results as your previous query but in a more concise form.
英文:
I have this table:
CREATE TABLE runner_orders (
"order_id" integer,
"runner_id" integer,
"distance" decimal (5,2),
"duration" decimal (5,2),
"cancellation" varchar(23)
);
INSERT INTO runner_orders
("order_id", "runner_id", "distance", "duration", "cancellation")
VALUES
(1, 1, 20, 32, ''),
('2', '1', 20, 27, ''),
('3', '1', 13.4, 20, ''),
('4', '2', 23.4, 40, ''),
('5', '3', 10, 15, ''),
('6', '3', NULL, NULL, 'Restaurant Cancellation'),
('7', '2', 25, 25, ''),
('8', '2', 23.4, 15, ''),
('9', '2', NULL, NULL, 'Customer Cancellation'),
('10', '1', 10, 10, '');
And they ask me:
> What is the successful delivery percentage for each runner?
I did this, that finally gave me the correct results:
WITH cte_1
AS (
SELECT runner_id, (COUNT(runner_id))*100 AS percentages
FROM runner_orders
WHERE cancellation = ''
group by runner_id
)
SELECT cte_1.runner_id, (percentages/(COUNT(ru.cancellation))) as percentages_successful_deliveries
FROM cte_1
FULL JOIN runner_orders AS ru
ON cte_1.runner_id = ru.runner_id
GROUP BY cte_1.runner_id, cte_1.percentages
ORDER BY runner_id
But I wonder, How can I get the same results in a shorter query? I tried subqueries, and It didn't work for me. This is one of the many subqueries I tried:
SELECT runner_id, ((COUNT(runner_id) over (PARTITION BY runner_id, cancellation))*100)/(count(runner_id))
FROM runner_orders
group by runner_id, cancellation
So how I can make this work in a shorter and more simple way than a CTE.
答案1
得分: 3
使用以下条件聚合：
select runner_id,
count(case when cancellation = '' then 1 end) *100.0 / count(*) as 百分比
from runner_orders
group by runner_id
order by runner_id
count(case when cancellation = '' then 1 end) 意味着，对于每个 runner_id，仅计算 cancellation = '' 的行数。在计数中的 case 表达式将对 cancellation <> '' 的行返回 null，对其他行返回 1（您可以使用其他值），请注意计数函数不会计算 null 值，这将仅返回 cancellation = '' 的行数。
英文:
Use conditional aggregation as the following:
select runner_id,
count(case when cancellation = '' then 1 end) *100.0 / count(*) as percantage
from runner_orders
group by runner_id
order by runner_id
count(case when cancellation = '' then 1 end) means, for each runner_id count only the rows where cancellation = ''. The case expression inside the count will return null for rows where cancellation <> '' and 1 (you may use anything other than 1) for other rows, noting that the count function is not counting null values, this will return only the count of rows where cancellation = ''.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论