如何在SQL中高效地执行交叉表查询？

使用CASE和GROUP BY实现动态替代PIVOT

问题：

下表所示数据以行和列的形式组织。目标是将其转换为具有动态列数的表，其中每列代表按指定类别分组的值。

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

期望输出：

bar	val1	val2	val3
A	10	20
B	3	4
C	5
D	6	7	8

原始查询：

以下查询使用CASE表达式和GROUP BY来实现所需结果：

<code class="language-sql">SELECT bar, 
MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar </code>

高效的交叉表替代方案：

为了提高效率和可读性，可以使用tablefunc模块中的crosstab函数来实现动态解决方案。以下是一个示例：

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM tbl_org
   ORDER BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- 更多列？</code>

处理多个值：

对于同一类别下有多个值的场景，可以将crosstab函数扩展为以下形式：

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, val, feh
   FROM tbl_org
   ORDER BY 1, 2')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- 更多列？</code>

内置交叉表函数：

tablefunc模块还为特定列数提供了预定义的crosstab函数：

<code class="language-sql">SELECT * FROM crosstab3('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2');</code>

这些函数使调用更简单，并默认处理文本数据。

动态返回类型：

虽然tablefunc简化了流程，但在处理动态返回类型方面存在局限性。为了解决这个问题，可以考虑其他方法，例如使用PL/pgSQL函数或创建动态SQL语句。

以上是如何在SQL中高效地执行交叉表查询？的详细内容。更多信息请关注PHP中文网其他相关文章！