使用WHERE语句计算GROUP_BY的百分比

Let's say I have a table with orders with revenue and status columns. I want to group the orders by revenue group (grouped by increments of 10) and get the percentage of which have their status column set to 1 in their respective revenue group. I thought a window function was the way to go, but the where statement restricts the columns so that I end up with only the columns where status == 1. The end result would look something like: 10 | 76%, 20 | 50% etc.

假设我有一张包含收入和状态列的订单的表格。我想按收入组对订单进行分组（按10的增量分组），并获取其各自收入组中其状态列设置为1的百分比。我认为窗口函数是可行的方法，但where语句限制列，以便我最终只得到status == 1的列。最终结果如下所示： 76％，20 | 50％等

SELECT CASE 
  WHEN revenue between 1 and 10 then 10
  WHEN revenue between 10 and 20 then 20
  WHEN revenue between 20 and 30 then 30
  WHEN revenue between 30 and 40 then 40
  WHEN revenue between 40 and 50 then 50
  else 60 
END as revgroup,
COUNT(*) / CAST(SUM(count(*)) over (partition by CASE 
  WHEN revenue between 1 and 10 then 10
  WHEN revenue between 10 and 20 then 20
  WHEN revenue between 20 and 30 then 30
  WHEN revenue between 30 and 40 then 40
  WHEN revenue between 40 and 50 then 50
else 60 END) as float) as percentage
from "order"
where "order".status = 1
group by revgroup

1 个解决方案

#1

the clause PARTITION BY is excessive in your case, each partition was created using clause GROUP BY

在您的情况下，PARTITION BY子句过多，每个分区都是使用子句GROUP BY创建的

SELECT CASE 
  WHEN revenue between 1 and 10 then 10
  WHEN revenue between 10 and 20 then 20
  WHEN revenue between 20 and 30 then 30
  WHEN revenue between 30 and 40 then 40
  WHEN revenue between 40 and 50 then 50
  else 60 
END as revgroup,
COUNT(*) * 1.0 / SUM(COUNT(*)) OVER () as percentage
from "order"
where "order".status = 1
group by revgroup