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

Question

在我的 PostgreSQL 数据库中，我有一个 id 列显示每个唯一的潜在客户。我还有一个 connected_lead_id 列显示帐户是否相互关联(即夫妻、 parent 和 child 、 friend 组，投资者团体等)。

当我们统计一个时间段内创建的 id 数量时，我们希望看到一个时间段内 connected_ids 的唯一“组”的数量。换句话说，我们不想把夫妻俩都算进去，我们只想算一个，因为他们真的是一个线索。

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

我们想添加额外的逻辑，以便我们获取“第一个”id 的源，除非它为 null，然后获取“第二个”connected_lead_id 的源，除非它为 null，依此类推。最后，我们想从 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。

感谢您的帮助!

Answer 1

好的，目前我能想到的最好方法是首先构建最大的相关 ID 组，然后返回到您的潜在客户表以获取其余数据(有关设置，请参见 SQL Fiddle，完整的查询和结果)。

要获得最大组，您可以使用递归公用表表达式首先增加组，然后通过查询将 CTE 结果过滤到最大组:

with recursive cte(grp) as (
select case when l.connected_lead_id is null then array[l.id] 
            else array[l.id, l.connected_lead_id]
       end      from leads l
union all
select grp || l.id
  from leads l
  join cte
    on l.connected_lead_id = any(grp)
   and not l.id = any(grp)
)
select * from cte c1

上面的 CTE 输出了几个类似的组以及中间组。下面的查询谓词删除了非最大组，并将结果限制为每个可能组的一个排列:

 where not exists (select 1 from cte c2
                   where c1.grp && c2.grp
                     and ((not c1.grp @> c2.grp)
                       or (c2.grp < c1.grp
                      and c1.grp @> c2.grp
                      and c1.grp <@ c2.grp)));

Results :

|        grp |
|------------|
|        2,8 |
|        4,7 |
|         14 |
| 11,336,142 |
|      12,13 |

接下来将上面的最终查询连接回您的 leads 表，并使用窗口函数获取剩余的列值，并使用 distinct 运算符将其修剪为最终结果集:

with recursive cte(grp) as (
...
)
select distinct 
       first_value(l.id) over (partition by grp order by l.created_at) id
     , first_value(l.created_at) over (partition by grp order by l.created_at) create_at
     , first_value(l.on_boarded_date) over (partition by grp order by l.created_at) on_boarded_date
     , first_value(l.source) over (partition by grp 
                                   order by case when l.source is null then 2 else 1 end
                                   , l.created_at) source
     , grp CONNECTED_IDS
  from cte c1
  join leads l
    on l.id = any(grp)
 where not exists (select 1 from cte c2
                   where c1.grp && c2.grp
                     and ((not c1.grp @> c2.grp)
                       or (c2.grp < c1.grp
                      and c1.grp @> c2.grp
                      and c1.grp <@ c2.grp)));

Results :

| id |            create_at | on_boarded_date |   source | connected_ids |
|----|----------------------|-----------------|----------|---------------|
|  2 | 2015-09-24T23:00:00Z |          (null) | referral |           2,8 |
|  4 | 2015-09-25T23:00:00Z |          (null) |    event |           4,7 |
| 11 | 2015-09-26T23:00:00Z |      2017-07-01 |   online |    11,336,142 |
| 12 | 2015-09-26T23:00:00Z |      2017-07-01 |    event |         12,13 |
| 14 | 2015-09-26T23:00:00Z |          (null) |   (null) |            14 |