如何在 PostgreSQL 中使用 CASE 语句、FILTER 或 Crosstab() 高效执行条件计数？

PostgreSQL条件计数：CASE语句与FILTER的效率对比

高效统计表中数据出现的频率至关重要。PostgreSQL常用CASE语句进行条件计数，但当可能值数量增加时，此方法变得繁琐。

使用SUM(CASE WHEN)进行计数：

<code class="language-sql">SELECT
    sum(CASE WHEN question1 = 0 THEN 1 ELSE 0 END) AS ZERO,
    sum(CASE WHEN question1 = 1 THEN 1 ELSE 0 END) AS ONE,
    sum(CASE WHEN question1 = 2 THEN 1 ELSE 0 END) AS TWO,
    category
FROM reviews
GROUP BY category</code>

使用COUNT(CASE WHEN)进行计数：

<code class="language-sql">SELECT
    count(CASE WHEN question1 = 0 THEN 1 END) AS ZERO,
    count(CASE WHEN question1 = 1 THEN 1 END) AS ONE,
    count(CASE WHEN question1 = 2 THEN 1 END) AS TWO,
    category
FROM reviews
GROUP BY category</code>

CASE语句的局限性：

虽然这些方法可以实现条件计数，但存在缺点：

• 代码冗长：编写多个CASE语句容易出错且效率低下。
• 性能问题：使用CASE表达式和ELSE 0可能会影响性能。

PostgreSQL 9.4 版本中使用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>

此方法使用FILTER子句应用不同的过滤器，避免了额外CASE语句的开销。

使用OR NULL简化语法：

为了更简洁，可以使用OR NULL：

<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查询：

当处理大量的选项时，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的FILTER选项和OR NULL语法为条件计数提供了高效便捷的方法，而crosstab()函数则在复杂计数场景中表现出色。

以上是如何在 PostgreSQL 中使用 CASE 语句、FILTER 或 Crosstab() 高效执行条件计数？的详细内容。更多信息请关注PHP中文网其他相关文章！