如何在SQL中计算百分比/总数？

Question

我有一组典型的CUSTOMER/ORDERS表，并且我想展示一个特定客户所负责的总销售额百分比。我可以像这样获取系统中订单的总数量：

SELECT COUNT(order_id) FROM orders

我可以这样获取客户下的订单总数：

SELECT COUNT(order_id) FROM orders WHERE cust_id = 541

我该如何将这些内容合并为一个查询，并返回特定客户销售额的百分比？谢谢！

- user144051

Answer 1

MySQL:

SELECT ROUND(
  100.0 * (
      SUM(IF(cust_id = 541, 1, 0)) / COUNT(order_id)
  ), 1) AS percent_total
FROM orders;

编辑

我想如果我注意到了postgres标签，那可能会有所帮助。我原以为这是一个MySQL的问题。

PostgreSQL：

SELECT ROUND(
  100.0 * (
      SUM(CASE WHEN cust_id = 541 THEN 1 ELSE 0 END)::numeric / COUNT(order_id)
  ), 1) AS percent_total
FROM orders;

附言：我的PostgreSQL有点生疏，如果MySQL查询在PostgreSQL上也可行，我想知道 :)

编辑2

我无法强调足够要谨慎使用下面的count(*)建议。通常情况下，你应该避免在PostgreSQL中使用它。

- hobodave

---

为什么要避免在PostgreSQL中使用count(*)？ - David S

可能他的意思是Postgres每次都会重新计算数量，这可能是一个昂贵的操作。 - Mr Shoubs

至少在PostgreSql中，我相信在除法前需要将总数进行强制类型转换，使用(...)::DECIMAL(5,4)之类的方法，否则除法将会是整数除法，结果将为零。 - NoChance

NoChance 是正确的。以上的解决方案对我没有用（产生了 0.0），但这个可以：SELECT ROUND(100.0 * (SUM(CASE WHEN cust_id = 541 THEN 1 ELSE 0 END)::DECIMAL / COUNT(order_id)), 1) AS percent_total FROM orders; - Michael Rush

假设orders中每行的order_id都不为空（这似乎很可能），那么COUNT(*)和COUNT(order_id)是相同的。COUNT(*)表示“计算输入行数”，而COUNT(foo)表示“计算foo非空值的输入行数”。 - jbg

我使用avg代替sum+count，以使代码稍微更短（例如avg(case when cust_id = 541 then 1 else 0 end)*100）。这样做会降低效率吗？ - Franco

Answer 2

一个解决方案是使用嵌套查询 -

SELECT count(*) / (SELECT count(*) FROM orders)
FROM orders
WHERE cust_id = 541

- Rob Elliott

---

不，这是一件非常糟糕的事情。在PostgreSQL中，count(*)并不像MySQL那样是O(1)的。这个查询需要进行两次完整的表扫描。这是不必要的，并且随着表的大小增加，性能将变得越来越差劲。 - hobodave

我也想补充一点：把某件事情做得“更容易打字”绝对不是一个好的理由。除非你正在参加某种比赛，争取在尽可能短的时间内完成尽可能多的代码。 - hobodave

hobodave: 不，它不需要 2 个完整的表扫描。它将需要 1 个完整的表扫描和 1 个索引扫描（假设 cust_id 上有索引）。 - user80168

一个更简单的查询语句是：选择 cust_id，( count(*)/(select count(1) from orders ) * 100 ) from orders group by cust_id; - Roopesh Majeti

COUNT(*)和COUNT(1)是一样的。而且，hobodave，你的解决方案是等效的，因为COUNT(order_id)的意思是“计算order_id非空的行数”。由于order_id可能永远不会为空，所以它与COUNT(*)完全相同。不，user80168，这不需要进行表扫描和索引扫描。如果你已经在做序列扫描，就没有必要再扫描索引了。 - jbg

Answer 3

select abc.item_name, sum(amount) as total
  from (select a.item_id, d.applicablefrom, a.item_name, a.final_item_status, d.rate, c.item_name as sub_item_name,
               b.sub_item_qty as itemqty, (b.sub_item_qty * d.rate) as amount 
          from tblitem_master a,
               tblitem_master c,
               tblitem_bom_master b,
               (select rate, applicablefrom, itemid
                  from tblperiodrates
                 where applicablefrom = (select max(applicablefrom) 
                                           from tblperiodrates
                                          where applicablefrom<='2005-5-18')) as d
         where a.item_id = b.item_id
           And b.sub_item_id = c.item_id
           and b.sub_item_id = d.itemid
           and a.final_item_status='f') as abc
 group by abc.item_name

- vikram

Answer 4

select max([order].customerid) customer_id, count(orderid) customer_orders, (select count(orderid) from [order]) as total_orders,
100.0 * (count(orderid))/(select count(orderid) from [order])
from [order] inner join customer
on [order].customerid = customer.customerid
group by [order].customerid

为了说明流程，我包含了比最终结果集中需要看到的更多的列。 将count(order_id)保存在一个临时变量中会更有效率。 我不习惯使用Postgres，希望这个代码能够以最小的修改工作。

- keni

---

当我的每日投票计数重置时，它会变成-1。对于这么简单的事情，没有必要进行JOIN和GROUP BY。 - hobodave

-1，这是无效的SQL（[和]是SQL中非法字符）。 - user330315

@a_horse_with_no_name：在SQL Server中不是非法的，当我回答时我没有PostgreSQL实例，但在SQL Server上测试通过。对于2个表，是的，你需要一个连接。 - keni

@keni：那么显然微软违反了SQL标准。在标准SQL和几乎所有其他DBMS中，用于保留字的正确字符是双引号，例如："order"。 - user330315

@a_horse_with_no_name：如果您分享记录标准的链接，我会点赞这个。 - keni

@keni：最近推出了《SQL 99完全手册》这本书，其中涉及到的章节是这一篇：http://kb.askmonty.org/en/sql-language-elements#delimited-identifier。 - user330315