主页 / dba / 问题 / 305820
Accepted
Jurgen Cuschieri
Asked: 2022-01-09 07:20:05 +0800 CST

PostgreSQL 中的递归 CTE 以生成不同频率的数据

  • 772

我正在尝试编写一个在 Postgresql 14 中使用递归 CTE 的数据生成查询。

考虑包含函数 getfreq 的模式“sc”。getfreq 将一个 int 作为参数（表示另一个表的外键），并返回一个 int 返回，它表示频率。

现在考虑这个查询：

WITH RECURSIVE rec AS 
(
SELECT 1 as fk FROM generate_series(1, sc.getfreq(1), 1)
UNION ALL
SELECT r.fk + 1 FROM rec AS r WHERE r.fk + 1 <= 10
)
select row_number() OVER () as pk, fk from rec

getfreq 期望从 1 到 10 的 int （因此 r.fk <= 10 退出条件）。它返回一个频率 N。我希望递归 CTE 的每次迭代都创建 N 行。每次迭代的结果将由 UNION ALL 子句组合在一起。最后，我想要一个结果，其中行数等于 getfreq 在 10 次迭代中返回的频率总和。

在上面的示例中，sc.getfreq(1) 将始终返回 5，因此我得到了 50 行的结果集；第一个 5 fk = 1，第二个 5 fk = 2，依此类推。但是，实际上应该使用迭代值调用 sc.getfreq()，因此第二次迭代应该是 sc.getfreq(2) 等等。自然，sc.getfreq(2) 将返回不同的频率而不是 5，因此最终结果不应有 50 行。

我曾尝试在 getfreq 中使用“fk”，如下所示： sc.getfreq(fk); 因为“fk”被 CTE 的递归部分递增（因此在第二次迭代中为 2，在第三次迭代中为 3，依此类推），但列“fk”在 FROM 的上下文中不存在，大概是因为“SELECT”部分还没有运行。

递归 CTE 适合解决这个问题吗？我可以通过一些调整来实现我想要的吗？

示例输出，其中 getfreq(1) 返回 5，getfreq(2) 返回 2，getfreq(3) 返回 1。

PK FK
1 1
2 1
3 1
4 1
5 1
6 2
7 2
8 3

......等等（这是3次迭代的不完整示例）。

postgresql cte

1 个回答

  1. Best Answer

    我认为您不需要递归解决方案。

    select 
    row_number() over (order by r.fk, s.p) as pk, 
    r.fk
from 
    generate_series(1, 10) as r (fk),
    generate_series(1, sc.getfreq(r.fk)) as s (p)
 ;
    • 5

相关问题