sql - 数千列的动态枢轴

Question

我正在使用 pgAdmin III/PostgreSQL 9.4 来存储和处理我的数据。我当前数据的示例:

x | y
--+--
0 | 1
1 | 1
2 | 1
5 | 2
5 | 2
2 | 2
4 | 3
6 | 3
2 | 3

我希望如何格式化:

1, 2, 3 -- 列名是唯一的 y 值
0, 5, 4 -- 第一个相应的 x 值
1, 5, 6 -- 第二个相应的 x 值
2, 2, 2 -- 等等

它需要是动态的，因为我有数百万行和数千个 y 的唯一值。

使用动态枢轴方法是否适合这种情况？我没能成功实现这个:

DECLARE @columns VARCHAR(8000)

SELECT @columns = COALESCE(@columns + ',[' + cast(y as varchar) + ']',
'[' + cast(y as varchar)+ ']')
FROM tableName
GROUP BY y

DECLARE @query VARCHAR(8000)

SET @query = '
SELECT x
FROM tableName
PIVOT
(
MAX(x)
FOR [y]
IN (' + @columns + ')
)
AS p'

EXECUTE(@query)

它停在第一行并给出错误:

syntax error at or near "@"

我见过的所有动态枢轴示例都使用了这个，所以我不确定我做错了什么。任何帮助表示赞赏。感谢您的宝贵时间。

**注意:x 值以正确的顺序存储很重要，因为顺序很重要。如有必要，我可以添加另一列来指示顺序。

Answer 1

术语“第一行”假定行的自然顺序，这在数据库表中不存在。所以，是的，您需要像您怀疑的那样添加另一列来指示顺序。为此，我假设有一列 tbl_id。使用 ctid将是不得已的措施。见:

• Deterministic sort order for window functions

您提供的代码看起来像 MS SQL Server 代码； Postgres 的语法无效。

对于 Y 的数百万行和数千个唯一值，尝试返回单个列甚至没有意义。 Postgres 有很大的限制，但还远远不够。 According to the source code或 the manual ，绝对最大列数为 1600。

因此我们甚至没有讨论 SQL 的限制性特征，这要求在执行时知道列和数据类型，而不是在执行期间动态调整。您将需要两个单独的电话，就像我们在这个相关问题下详细讨论过的那样。

• Dynamic alternative to pivot with CASE and GROUP BY

Clodoaldo 在同一问题下的另一个回答返回数组。这实际上可以是完全动态的。这也是我在这里的建议。查询实际上相当简单:

WITH cte AS (
   SELECT *, row_number() OVER (PARTITION BY y ORDER BY tbl_id) AS rn
   FROM   tbl
   ORDER  BY y, tbl_id
   )
SELECT text 'y' AS col, array_agg (y) AS values
FROM   cte
WHERE  rn = 1

UNION ALL
(  -- parentheses required
SELECT text 'x' || rn, array_agg (x)
FROM   cte
GROUP  BY rn
ORDER  BY rn
);

结果:

col | values
----+--------
y   | {1,2,3}
x1  | {0,5,4}
x2  | {1,5,6}
x3  | {2,2,2}

db<> fiddle here
<子>旧sqlfiddle

说明

• CTE 为每组 y 的每一行(每个 x)计算行号 rn。我们将使用它两次，因此使用 CTE。

• 外部查询中的第一个 SELECT 生成 y 值的数组。

• 外部查询中的第二个 SELECT 按顺序生成所有 x 值的数组。数组可以有不同的长度。

为什么 UNION ALL 有括号？见:

• Sum results of a few queries and then find top 5 in SQL