postgresql - 递归 CTE PostgreSQL 连接多个 ID 与其他字段的附加逻辑

问题描述

在我的 PostgreSQL 数据库中，我有一个 id 列，显示每个唯一的潜在客户。我还有一个 connected_lead_id 列，它显示帐户是否相互关联（即丈夫和妻子、父母和孩子、朋友组、投资者等）。

当我们计算一个时间段内创建的 id 数量时，我们希望看到一个时间段内 connected_ids 的唯一“组”的数量。换句话说，我们不想同时计算夫妻对，我们只想计算一对，因为他们确实是一个领先者。

我们希望能够创建一个仅基于“created_at”日期具有“第一个”ID 的视图，然后在末尾包含“connected_lead_id_1”、“connected_lead_id_2”、“connected_lead_id_3”等附加列。

我们想要添加额外的逻辑，以便我们获取“第一个”id 的源，除非它为空，然后获取“第二个”connected_lead_id 的源，除非它为空，依此类推。最后，我们要从 connected_lead_id 组中获取最早的 on_boarded_date。

id    |    created_at      | connected_lead_id | on_boarded_date | source     |
  2   | 9/24/15 23:00      |        8          |                 |
  4   |  9/25/15 23:00     |        7          |                 |event
  7   |  9/26/15 23:00     |        4          |                 |
  8   |  9/26/15 23:00     |        2          |                 |referral
  11  |  9/26/15 23:00     |       336         |   7/1/17        |online
  142 |  4/27/16 23:00     |       336         |                 |
  336 |  7/4/16 23:00      |        11         |   9/20/18       |referral

最终目标：

id    |    created_at      | on_boarded_date | source     |  
  2   | 9/24/15 23:00      |                 | referral   |
  4   |  9/25/15 23:00     |                 | event      |
  11  |  9/26/15 23:00     |   7/1/17        | online     |

理想情况下，我们还会在末尾添加 i 个额外的列，以显示附加到基本 id 的每个 connected_lead_id。

谢谢您的帮助！

标签： postgresqlcommon-table-expressionrecursive-query

解决方案

演示：db<>小提琴

主要思想 - 草图：

循环遍历有序集合。获取之前在任何( cli )id中从未见过的所有 s 。这些是递归的起点。 问题是您的号码以前从未见过，但由于它的 cli 与它在同一组中。所以最好得到看不见的id的clis。有了这些值，以后在递归部分计算组的 id 就简单多了。由于循环，函数/存储过程是必要的。connected_lead_id14211
递归部分：第一步是获取起始clis的id。使用created_at时间戳计算第一个引用 id。之后，可以在 cli 上进行简单的树递归。

一、功能：

CREATE OR REPLACE FUNCTION filter_groups() RETURNS int[] AS $$
DECLARE
    _seen_values int[];
    _new_values int[];
    _temprow record;
BEGIN
    FOR _temprow IN
        -- 1:
        SELECT array_agg(id ORDER BY created_at) as ids, connected_lead_id FROM groups GROUP BY connected_lead_id ORDER BY MIN(created_at)
    LOOP
        -- 2:
        IF array_length(_seen_values, 1) IS NULL 
            OR (_temprow.ids || _temprow.connected_lead_id) && _seen_values = FALSE THEN

            _new_values := _new_values || _temprow.connected_lead_id;
        END IF;

        _seen_values := _seen_values || _temprow.ids;
        _seen_values := _seen_values || _temprow.connected_lead_id;
    END LOOP;

    RETURN _new_values;
END;
$$ LANGUAGE plpgsql;

对引用同一cli的所有id进行分组
循环遍历 id 数组。如果之前没有看到数组的元素，请将引用的 cli 添加到输出变量 ( _new_values)。在这两种情况下，将 ids 和 cli 添加到存储所有尚未看到的 ids 的变量 ( _seen_values)
发出clis。

到目前为止的结果是{8, 7, 336}（相当于 ids {2,4,11,142}！）

2.递归：

-- 1:
WITH RECURSIVE start_points AS (
    SELECT unnest(filter_groups()) as ids
),
filtered_groups AS (
    -- 3:
    SELECT DISTINCT
       1 as depth, -- 3
       first_value(id) OVER w as id, -- 4
       ARRAY[(MIN(id) OVER w)] as visited, -- 5
       MIN(created_at) OVER w as created_at,
       connected_lead_id,
       MIN(on_boarded_date) OVER w as on_boarded_date -- 6,
       first_value(source) OVER w as source
    FROM groups 
    WHERE connected_lead_id IN (SELECT ids FROM start_points)
    -- 2:
    WINDOW w AS (PARTITION BY connected_lead_id ORDER BY created_at)

    UNION

    SELECT
        fg.depth + 1,
        fg.id,
        array_append(fg.visited, g.id), -- 8
        LEAST(fg.created_at, g.created_at), 
        g.connected_lead_id, 
        LEAST(fg.on_boarded_date, g.on_boarded_date), -- 9
        COALESCE(fg.source, g.source) -- 10
    FROM groups g
    JOIN filtered_groups fg
    -- 7
    ON fg.connected_lead_id = g.id AND NOT (g.id = ANY(visited))

)
SELECT DISTINCT ON (id) -- 11
    id, created_at,on_boarded_date, source 
FROM filtered_groups 
ORDER BY id, depth DESC;

该WITH部分给出了函数的结果。unnest()将 id 数组扩展为每个 id 的每一行。
创建一个窗口：窗口函数按它们的 cli 对所有值进行分组，并按created_at时间戳对窗口进行排序。在您的示例中，所有值都在它们自己的窗口中11，142但它们是分组的。
这是稍后获取最新行的帮助变量。
first_value()给出有序窗框的第一个值. 假设142created_at 时间戳较小，结果将是142. 但它11仍然是。
需要一个变量来保存已访问过的 id。如果没有这些信息，将创建一个无限循环：2-8-2-8-2-8-2-8-...
采用窗口的最小日期（同样的事情：如果142日期小于11这将是结果）。

现在计算递归的起始查询。下面介绍递归部分：

根据先前的递归结果连接表（原始函数结果）。第二个条件是我上面提到的无限循环的停止。
将当前访问的 id 附加到访问的变量中。
如果电流on_boarded_date较早，则采用。
COALESCE给出第一个NOT NULL值。所以第一个NOT NULL source在整个递归过程中都是安全的

在给出所有递归步骤的结果的递归之后，我们只想过滤掉每个起始 id 的最深访问。

DISTINCT ON (id)给出第一次出现 id 的行。为了得到最后一个，整个集合按depth变量降序排列。

postgresql - 递归 CTE PostgreSQL 连接多个 ID 与其他字段的附加逻辑

问题描述

解决方案

推荐阅读