如何高效地统计 PostgreSQL 中的条件出现次数？

PostgreSQL条件计数：优化方法

在数据库表中统计数据出现次数时，条件语句可能会显得冗长。幸运的是，PostgreSQL 提供了便捷高效的解决方案。

FILTER聚合选项

对于PostgreSQL 9.4及更高版本，利用FILTER聚合选项可获得最佳效率：

<code class="language-sql">SELECT category
     , count(*) FILTER (WHERE question1 = 0) AS zero
     , count(*) FILTER (WHERE question1 = 1) AS one
     , count(*) FILTER (WHERE question1 = 2) AS two
FROM   reviews
GROUP  BY 1;</code>

简洁的语法

如果简洁性是您的首要任务：

<code class="language-sql">SELECT category
     , count(question1 = 0 OR NULL) AS zero
     , count(question1 = 1 OR NULL) AS one
     , count(question1 = 2 OR NULL) AS two
FROM   reviews
GROUP  BY 1;</code>

交叉表查询

对于处理大型选项列表并追求更高性能，请考虑使用crosstab()函数：

<code class="language-sql">SELECT * FROM crosstab(
     'SELECT category, question1, count(*) AS ct
      FROM   reviews
      GROUP  BY 1, 2
      ORDER  BY 1, 2'
   , 'VALUES (0), (1), (2)'
   ) AS ct (category text, zero int, one int, two int);</code>

以上是如何高效地统计 PostgreSQL 中的条件出现次数？的详细内容。更多信息请关注PHP中文网其他相关文章！