sql - 在递归 SQL 查询中使用全局列表来避免访问节点
问题描述
我有一个自引用表User:
id | follower
------------|------------
1 (adam) | 2 (bob)
1 (adam) | 3 (charlie)
2 (bob) | 1 (adam)
2 (bob) | 3 (charlie)
请注意,有循环引用。
我想获取用户的所有关注者和关注者的关注者等,以便所有关注者都显示在扁平列表中,并具有各自的深度
对于亚当:
id | follower | depth
---|-------------|-------
1 | 1 (bob) | 0
2 | 3 (charlie) | 0
3 | 1 (adam) | 1 (bob -> adam)
4 | 3 (charlie) | 1 (bob -> charlie)
问题
我想避免第 3 行和第 4 行,这代表两个问题:
adam -> bob -> adam
因为它是圆形的。adam -> bob -> charlie
因为查理之前已经出现过。
我可以通过在分支中保留一path
列已访问的 s 来使用以下查询来解决问题 #1id
WITH RECURSIVE cte AS (
SELECT id, follower, 0 as depth, ARRAY[id] AS path
FROM user
UNION ALL
SELECT id, follower, depth + 1, id || path
FROM user
JOIN cte ON user.id = cte.follower
WHERE NOT path @> Array[user.id]
)
SELECT * from cte
但它不能解决问题#2。
它给出以下结果:
follower | depth | path
------------|-------|-----
2 (bob) | 0 | {2}
3 (charlie) | 0 | {3}
3 (charlie) | 1 | {2, 3}
它仍然存在问题 #2(重复charlie
条目),因为column 仅在特定分支中path
保留 s 列表。id
如何解决问题 #2?
可能的解决方案
我可以通过保持全局缓存(path
等效)在我的代码(Node.JS)中解决它。
const list = {}; /* <-- GLOBAL cache */
function recurse(user, depth = 0) {
for(const { id, followers } of user.followers) {
if (!(id in list)) {
list[id] = {id, depth}
recurse({ followers }, depth + 1);
}
}
}
然而,据我所知,上面的 SQL 查询相当于:
function recursive() {
const list = {}; /* <-- LOCAL cache */
for(const {id} of followers)
if (!(id in list)) ...
如何使用 SQL 中的全局缓存在代码中复制我的解决方案?
或者任何其他方式我可以达到预期的结果?
我正在使用 Node.JS 和 PostgreSQL
解决方案
如果我理解正确,您只想在递归搜索后为每个关注者选择一行:
WITH RECURSIVE cte AS (
SELECT id, follower, 0 as depth, ARRAY[id] AS path
FROM user
UNION ALL
SELECT id, follower, depth + 1, id || path
FROM user
JOIN cte ON user.id = cte.follower
WHERE NOT path @> Array[user.id]
)
SELECT DISTINCT ON (follower) *
FROM cte
ORDER BY follower, depth;
推荐阅读
- angular - Angular 默认应用程序 ng 服务:Chrome 中的隐私错误:NET::ERR_CERT_AUTHORITY_INVALID
- python - For循环仅迭代列表的第一个索引
- excel - 如何在同一行排列重复值?
- angular - 使用 flexlayout angular 使文本框 axquire 全角
- javascript - 在 laravel 中没有定义 JavaScript 函数
- android - 双边框按钮
- regex - 正则表达式匹配字符串和重新定位的组件
- image - 如何将每个 RGB 像素组合成一个超复数?
- ios - React Native - Xcode Simulator 上的 .app 文件要求提供 Bundle URL
- pdf - ghostscript:丢失的pdf原始嵌入字体