为什么count（*）在此子查询（postgresql）中返回多个结果？ [英] Why is count(*) returning multiple results in this subquery (postgresql)?

问题描述

我想将count（*）查询的结果分组到值存储桶中。我正在dellstore2 postgresql示例数据库中对此进行测试。我在下面的查询返回正确的答案，但是对表中的每一行都执行一次（几千个相同的结果）。我可以通过在查询末尾添加 LIMIT 1 来解决此问题，但我想理解为什么要得到重复项以防它指向更广泛的问题我的方法。查询是：

  SELECT 
（SELECT count（*）
 FROM 
 order 
其中
总金额> 0并且总金额<= 100）AS> 0< = 100，
（从
订单中选择计数（*）
 
其中
的总金额> 100并且总金额<= 200）AS> 100 <== 200 
 ... 
来自
订单； 
  

EDIT
Andomar的回答也使我找到了以下方法（改编自简而言之就是SQL（O'Reilly））。这使我可以将存储桶放在一列中，每个存储桶/答案配对都有一行。我以为应该将其包含在该用例中：

 选择情况
当总金额为NULL时'未知'
当总金额<= 100则'不超过100'
当总额<= 200则'不超过200'
 ELSE'超过200'
 END Bucket，
 COUNT（*）结果数 
来自
订单
案例分析
当总量为NULL则'未知'
当总金额<= 100然后'不超过100'
当总金额<= 200 THEN'不超过200'
其他'超过200'
结束
预定至少
 MIN（总金额）； 
  

解决方案

您要从<$ c $中选择每一行c>订单，然后为每一行评估子查询。

请考虑以下方法：

 选择计数（当0  as <0,100] 
 ，计数（如果100< totalamount and totalamount< = 200然后1结束的情况）
来自订单
   p $ p> 
 
 
这将在一次表扫描中计算两个聚合。
 
I want to group the results of a count(*) query into value buckets. I'm testing this on the dellstore2 postgresql sample database. My query below returns the right answers, but does so once for each row in the table (several thousand identical results). I can fix this by adding LIMIT 1 the the end of the query but I'd like to understand why I'm getting the duplicates in case it points to a wider problem with my approach. The query is:
SELECT
    (SELECT count(*)
        FROM
            orders
        WHERE
            totalamount > 0 AND totalamount <= 100) AS ">0 <= 100",
    (SELECT count(*)
        FROM
            orders
        WHERE
            totalamount > 100 AND totalamount <= 200) AS ">100 <= 200"
...
FROM
    orders;
EDIT
Andomar's answer also allowed me to find the following approach (adapted from an example in SQL in a nutshell (O'Reilly)). This lets me have the buckets in one column, with a row for each bucket/answer pairing. I thought I'd include it for anyone with that use-case:
SELECT CASE
        WHEN totalamount IS NULL THEN 'Unknown'
        WHEN totalamount <= 100 THEN 'Not more than 100'
        WHEN totalamount <= 200 THEN 'Not more than 200'
        ELSE 'Over 200'
    END "Bucket",
    COUNT(*) "Number of results"
FROM
    orders
GROUP BY CASE
        WHEN totalamount IS NULL THEN 'Unknown'
        WHEN totalamount <= 100 THEN 'Not more than 100'
        WHEN totalamount <= 200 THEN 'Not more than 200'
        ELSE 'Over 200'
    END
ORDER BY
    MIN(totalamount);

解决方案
You're selecting every row from orders, and then for each row, the subqueries are evaluated.

Consider this approach instead:
select  count(case when 0 < totalamount and totalamount <= 100 then 1 end)
            as "<0,100]"
,       count(case when 100 < totalamount and totalamount <= 200 then 1 end)
            as "<100,200]"
from    Orders
This would calculate both aggregates in a single table scan.

                        
这篇关于为什么count（*）在此子查询（postgresql）中返回多个结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！