使SQL查询更高效

Question

如何让这个SQL查询更有效率？

SELECT 
  (SELECT COUNT(*) FROM table WHERE price < 10) AS priceUnder10,
  (SELECT COUNT(*) FROM table WHERE price BETWEEN 10 AND 20) AS price10to20,
  (SELECT COUNT(*) FROM table WHERE price > 20) AS priceOver20,
  (SELECT COUNT(*) FROM table WHERE colour = 'Red') AS colourRed,
  (SELECT COUNT(*) FROM table WHERE colour = 'Green') AS colourGreen,
  (SELECT COUNT(*) FROM table WHERE colour = 'Blue') AS colourBlue;

我已经在 price 和 colour 列上创建了索引，所以我正在寻找更好的聚合数据的方法。

我尝试使用 GROUP BY、HAVING、自连接和窗口函数，但无法达到相同的结果。

非常感谢任何建议。

- gjb

Answer 1

SELECT 
       COUNT(CASE WHEN price < 10 THEN 1 END) AS priceUnder10,
       COUNT(CASE WHEN price BETWEEN 10 AND 20 THEN 1 END) AS price10to20,
       COUNT(CASE WHEN price> 20 THEN 1 END) AS priceOver20,
       COUNT(CASE WHEN colour = 'Red' THEN 1 END) AS colourRed,
       COUNT(CASE WHEN colour = 'Green' THEN 1 END) AS colourGreen,
       COUNT(CASE WHEN colour = 'Blue' THEN 1 END) AS colourBlue
from YourTable  
WHERE price IS NOT NULL OR  colour IN ('Red','Green','Blue' )

- Martin Smith

---

谢谢，非常好的回答。但是为什么需要 WHERE？ - gjb

@gjb - 可能并非如此，但想象一下您有一个拥有1,000,000行的表格，只有1行具有“红色”，“绿色”或“蓝色”中的非空价格或颜色。其他999,999行对结果没有影响，但仍会被扫描。当然，您可能已经知道您的数据不具有这种分布（price甚至可能不可为空），因此可以将其删除！ - Martin Smith

Answer 2

根据您的数据库处理布尔表达式的方式，这样写：

select sum(price<10),sum(price between 10 and 20)...  from tab; 

或者这个

select sum(case when price<10 then 1 else 0 end),sum(case when price between 10 and 20 then 1 else 0 end)...  from tab; 

可能会有所帮助。

- Nordic Mainframe

Answer 3

SELECT count(*) as products, 

if(price < 10, 'price band 1',
 if (price between 10 and 20, 'price band 2',
  'price band 3'
 )
) as priceband,
  t.colour

from table t
group by t.colour, pricebrand

这将为您提供

products    colour    priceband
53          red       price band 1
65          red       price band 2
12          blue      price band 1
23          blue      price band 2

等等。

- tumbler

---

这将结果嵌套，不完全是我想要的。不过，这还是很有用的知识，谢谢。 - gjb