问题[recursive](dba)

我在 Postgres 中的递归查询有点挣扎。

鉴于这些表：

create table resource_v3
(
    id            uuid not null primary key,
    name          varchar(255),
    resource_id   uuid,
    resource_type varchar(255),
    tenant_id     uuid
);

和

create table resource_associations
(
    parent_id uuid not null
    child_id  uuid not null
    primary key (parent_id, child_id)
);

鉴于这些表，我构建了以下结构

我要存档的是获取 user1 所在的所有团队或组织。我只需要第一个匹配项，因此给定 user1 递归应该在“团队服务技术人员”和“ORG：Seepex”处停止。它不应该返回“ORG:CS”而不是“ORG:Seepex-UK”

我的查询：

WITH RECURSIVE c AS (
   SELECT v.id from resource_v3 v where v.resource_id = '8a84bf66-6dc4-43c7-ace1-d104fddaa1be'

   UNION ALL

   SELECT sa.parent_id
   FROM resource_associations AS sa JOIN c ON c.id = sa.child_id
       JOIN resource_v3 rv3 on c.id = rv3.id
    WHERE rv3.resource_type != 'TEAM' OR rv3.resource_type != 'ORGANISATION'
)
SELECT r.* FROM c JOIN resource_v3 r ON c.id=r.id;

如我所料返回更多数据：

我在这里做错了什么或者你将如何解决这个问题？似乎我几乎在那里，查询应该在第一次命中时停止向上移动。

我的最终目标是获取给定用户所在的 TEAM 或 ORGANIZATION 的所有子元素。因此，逻辑计划是使用上面的查询获取组织/团队，然后从那里递归获取子元素，这相当容易并且运作良好。

请考虑下表：

我们有约翰的三条记录和杰克的两条记录。在每个日期中，用户要么向金额字段添加一个值（正值），要么从中减去一个值（负值）。

金额总和未发生变化的天数很重要。

例如，从1/1/2022到1/2/2022，dear John 的值是 40。但是在1/2/2022，金额减少了 30。因此，30 天（从1/1到1/2 ), 数量等于 40, 直到1/6 , 总量 ( 40 - 30 ) 保持不变约 120 天。最后，直到今天（今天是8/23），金额的总和将等于40 - 30 + 50 = 60。

问题：

• 如何计算这些间隔以获得天数？
• 如何为所有用户获得这些间隔？

我阅读了有关 Window 或 CTE 的信息，但无济于事。

我为什么问这个问题？

数据库记录的数量已经增加到大约一百万条，对于 21,000 个用户，我们必须获取每个区间的长度并乘以之前记录的总量。

预期输出：

例如，对于 John，我希望得到以下输出：

40 × 30(days) + (40 - 30) × 60 (days) + (40 - 30 + 50) × 53 (days)

我有以下表结构

create table folder
(
    id   integer
        constraint folder_pk
            primary key,
    path TEXT
);

create index folder_path_uindex
    on folder (path);

create table membership
(
    id     integer
        constraint membership_pk
            primary key,
    member integer
        references user,
    groups integer
        references user
);

create index membership_group_member_uindex
    on membership (groups, member);

create table rule
(
    id        integer
        constraint rule_pk
            primary key,
    folder    integer
        references folder,
    principal integer
        references user,
    type      INTEGER,
    level     integer
);

create table user
(
    id   integer
        constraint user_pk
            primary key,
    name integer,
    type text
);

create index principal_name_type_uindex
    on user (name, type);

我需要递归地遍历组以查明用户是否有效地成为组的成员，然后加入规则表以获取与用户相关的所有规则。规则可以直接应用于用户或组。一个组可以是另一个组的成员。

我将如何开始，甚至在 SQLite 中是否可行？

我有点坚持这一点。任何输入表示赞赏

- 编辑 -

我想我找到了解决方案。我会写一个遮阳篷

我正在尝试生成 Strings 列表的所有组合 list = ['A', 'B', 'C', 'D']。

我想生成所有可能性，然后在数据库中搜索

ABCD, ABC, ABD, ACD, BCD, AB, AC, AD, BC, BD, CD, A, B, C, D

我的专栏是这样的：

澄清一下：顺序无关紧要（ABC = BAC = CAB），它应该返回多个结果（对于 list = ['A', 'B', 'C'] 与上面的 10 列相同，它应该返回 [1 , 2, 3, 4, 5])。

我正在使用 Postgres，我尝试过一些我见过的递归函数，但没有一个能完全满足我的需求。

所以我想限制树深度遍历（作为最简单的递归杀手工具）。将规范化记录转换为树的示例代码：

CREATE TABLE items (
  item_id     serial PRIMARY KEY,
  title text
);
CREATE TABLE joins (
  id          serial PRIMARY KEY,
  item_id     int,
  child_id    int
);
INSERT INTO items (item_id,title) VALUES
  (1,'PARENT'),
  (2,'LEVEL 2'),
  (3,'LEVEL 3.1'),
  (4,'LEVEL 4.1'),
  (5,'LEVEL 4.2'),
  (6,'LEVEL 3.2');
INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,4),
  (3,5),
  (2,6);

WITH RECURSIVE t(item_id, json) AS (
        SELECT item_id, to_jsonb(items)
        FROM items
        WHERE NOT EXISTS (
                SELECT 1
                FROM joins
                WHERE items.item_id = joins.item_id
        )
        UNION ALL
        SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json )
        FROM t
        JOIN joins AS j
                ON t.item_id = j.child_id
        JOIN items AS parent
                ON j.item_id = parent.item_id
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;

由于这个美丽的答案，它起作用了。现在想象我们有

INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,1);

所以我们得到了一个循环1->2->3->1，我想使用限制器来限制它的输出——我希望我的所有树都不大于我给这个树构造函数设置的深度 X。如何在 postgres 13+ 中做这样的事情？

我有桌子currency_pair(c1, c2)；值为(usd,bnb), (cake,bnb), (cake,eth).

我需要找到允许我比较的最短usd路径eth。

这里的结果将是那些相同的值。然后我可以使用第一对建立 usd-bnb 关系，然后我可以使用它来计算 usd-cake 关系，然后我可以使用它来计算 usd-eth。

由于顺序不是确定的，我做的第一步是创建一个物化视图currency_pair_map(c1, c2)，它是 的并集select c1, c2 union select c2, c1。这似乎简化了逻辑。

如果我正确地考虑了这一点，我需要做的是使用WITH RECURSIVE吗？我还应该有某种“depth_limit”参数，以确保在无法建立一对时查询失败。

大声思考这个问题，我们应该始终从以下内容开始：

SELECT *
FROM currency_pair
WHERE
  c1 = 'usd' AND
  c2 = 'eth'

如果有结果，我们应该到此为止。

如果没有，那么我们需要找到所有usd-*对并继续搜索，直到找到以 结尾的一对eth。

使用这个逻辑，到目前为止我有：

WITH RECURSIVE pair_route AS (
  SELECT
    1 depth,
    cp1.id,
    cp1.c1,
    cp1.c2
  FROM currency_pair cp1
  WHERE
    cp1.c1 = 'usd'
  UNION
  SELECT
    pr1.depth + 1,
    cp2.id,
    cp2.c1,
    cp2.c2
  FROM pair_route pr1
  INNER JOIN currency_pair cp2 ON cp2.c1 = pr1.c2
  WHERE
    pr1.depth < 4
)
SELECT *
FROM pair_route;

我认为这是正确的。现在我只需要跟踪路径 (ID) 并确定最短路径。

我正在尝试编写一个查询来识别树结构表中特定父节点的每个最远后代的路径，例如：

    0     1
    |     |
    2     3
    |     |
    4     5
   / \    |
 *6*  8  *7*
      |
     *9*

有很多父母，所有孩子都有一个父母，父母有0-5个孩子（但图表很“长”——大多数父母只有一个孩子）。没有周期。

我正在尝试有效地识别特定节点（而不是任何中间节点）的最远后代的路径。例如在上面：

• get_leaf_paths(1)将返回 1 行：{1, 3, 5, 7}
• get_leaf_paths(2)将返回 2 行：{2, 4, 6}和{2, 4, 8, 9}

样品表：

CREATE TABLE graph (
    id bigint PRIMARY KEY,
    parent_id bigint,
    FOREIGN KEY (parent_id) REFERENCES graph(id)
);
INSERT INTO graph (id, parent_id)
    VALUES (0, NULL),
           (1, NULL),
           (2, 0),
           (3, 1),
           (4, 2),
           (5, 3),
           (6, 4),
           (7, 5),
           (8, 4),
           (9, 8);

我希望结果看起来像：

SELECT get_leaf_paths.* FROM get_leaf_paths(0);
path
-----
{0, 2, 4, 6}
{0, 2, 4, 8, 9}
(2 rows)

在我最初尝试使用递归查询的函数时，我很难只选择最远的叶子，特别是因为某些分支比其他分支短（例如6，9以上分支处于不同的深度）。路径可能很深（数千或数百万个元素），所以我还想避免为每个中间节点生成路径的过多内存使用。

任何想法将不胜感激。谢谢！

我在 MS SQL SERVER 2014 中有一个这样的表：

ID|Race|Lap  
1 |21  |11  
2 |21  |NULL
3 |21  |NULL  
4 |21  |NULL  
5 |29  |65  
6 |29  |NULL  
7 |29  |NULL  
8 |29  |NULL 

我正在尝试通过根据第一个值向其添加 1 来填充 Lap 列。分区基于 Race 列。这样的结果将是最终结果：

ID|Race|Lap  
1 |21  |11  
2 |21  |12
3 |21  |13  
4 |21  |14  
5 |29  |65  
6 |29  |66  
7 |29  |67  
8 |29  |68  

可能还有其他方法可以做到这一点，但我宁愿坚持使用递归 CTE。有没有办法做到这一点？

想象一下，我有一个像这样的表“部门”：

name TEXT
parent_department TEXT (nullable)

我也有一个像这样的“预算”表：

department_name TEXT
budget INTEGER

我需要在父部门列之后从“叶到根”“遍历”这个部门表。最终结果是我需要总结每个部门的预算，但问题是我将在任何给定时间使用的“预算”表非常稀疏（10 到 100 行），而部门是非常密集（100m 行）。

通常，我会通过创建一个“递归”CTE 来解决这个问题，该 CTE 将部门关系“扁平化”为一个只有“根部门”、“叶部门”的临时表。然后我会将叶子部门的那个表加入预算，并按根分组。问题是“部门”表现在太大了（我承认，与部门的类比在这里有点分解，没有公司拥有超过 1 亿个部门）。

具体来说，如果我有这样的部门（孩子->父母）：

C -> B
B -> A
A -> NULL
E -> D
D -> NULL
F -> G
G -> NULL

和这样的预算：

A 1
C 3
E 5

我想得到输出：

A 4
D 5

理想情况下，例如，F 和 G 永远不会被触及，因为像 F 和 G 这样的数亿行不应该出现。但在我当前的查询中，他们确实如此。我该如何解决这个问题，以便我只“遍历”我实际需要的部门？

我有一个包含这样名称的列的表：

id, employee
1, Mr. John Cole Thornton
2, Mr. Paul George Mckenzie
3, Mr. George Mick McDoughal
4, Ms. Emily Suzan Flemming
5, Mr. Alan Bourdillion Traherne

我有第二张表，上面有名字列表，像这样

id, first_name
1, Emily
2, John
3, George
4, Suzan
5, Paul
6, Alan
7, Mary
8, Mick
9, Bourdillion
10, Jim
11, Cole

我想删除第一个表中的名字，以获得：

id, employee
1, Mr. Thornton
2, Mr. Mckenzie
3, Mr. McDoughal
4, Ms. Flemming
5, Mr. Traherne

无论有多少名字，我都想删除它们而不调用多次我的 first_names 表，我想知道如果不使用函数中的循环，这是否可能。

我试过这样的查询：

WITH RECURSIVE name AS (   SELECT REPLACE(t1.employee, t2.first_name, '') sec_name 
                        FROM  t1, t2 
                        WHERE position(t2.first_name in t1.employee) > 0 ) 
SELECT sec_name FROM name ;

但是我得到的输出与输入中的名字一样多，例如：

Id, sec_name
1, John Thornton
1, Cole Thornton
2, Paul Mckenzie
2, George Mckenzie
...

我的 Postgres 版本是 9.6 。

任何帮助都感激不尽 ！