sqlite - 使用sqlite递归计算出现次数

Question

考虑以下示例表


x_id
name_id1
name_id2


x1
约翰
坦率

×2
坦率
约翰

x3
jack
约翰

x4
约翰
jack

x5
鲍勃
坦率

x6
乔治
鲍勃

x7
鲍勃
芬兰人

x8
标记
詹姆士

x9
詹姆士
芬兰人


目标是提取以下结果


姓名
频率


约翰
4

鲍勃
3

詹姆士
2


从概念上讲，这是以下过程的结果:

计算所有名称的频率并选择出现频率最高的那个，那就是 John，它出现了 4 次(行 x1 到 x4)。

现在删除所有包含 John 的行，留下行 x5至 x9 .再次确定出现频率最高的名称。这给了你 Bob，它出现了 3 次(行 x5 到 x7)。

现在也删除行 x5至 x7 ，这给我们留下了行 x8至 x9 .再次确定出现频率最高的名称。这给了我们发生 2 次的 James。

现在也删除行 x8至 x9 ，这让我们一无所有，所以我们完成了。

此数据存储在 SQLite 中的连接表中，如下所示(在实际情况下，每个 x_id 可以有两个以上的名称)


ID
x_id
名称_id


1
x1
约翰

2
x1
坦率

3
×2
约翰

4
×2
坦率

5
x3
约翰

6
x3
jack

7
x4
约翰

8
x4
jack

9
x5
鲍勃

10
x5
坦率

11
x6
鲍勃

12
x6
乔治

13
x7
鲍勃

14
x7
芬兰人

13
x8
詹姆士

14
x8
标记

13
x9
詹姆士

14
x9
芬兰人


我们需要什么样的程序来检索描述的结果？ 考虑到上面的连接表是可变长度的(只是为了确保我们没有提出固定数量的 join s 作为有效答案)。
我确实考虑过使用 WITH RECURSIVE然而，这将不允许我们

执行聚合函数COUNT在递归选择中，当我们想要计算出现次数时，这似乎是必需的。

删除所有以前的 x_id s，只有当前在队列中的那些。

Answer 1

我从未使用过 SQLite，但在许多 DBMS 中都可以使用递归查询，不幸的是，我认为它们的功能不足以完成您想要的操作。问题是您需要逐渐忽略越来越多的行，如果它们包含结果表中已经存在的名称。我不是 100% 确定，但我相信用递归 CTE 是不可能做到这一点的。

考虑到 SQLite 不提供允许循环的扩展语言(在这里，您可以找到一些复制循环的方法，例如递归 CTE
但我不确定您的情况是否可行)，可能的合理选择之一是在应用程序端部分处理此问题。

首先，我会创建一个这样的临时表:

CREATE TEMP TABLE visitedName (
  x_id VARCHAR(10),
  is_visited BOOLEAN
);

x_id与 x_id 相同您表中的列(我称之为 mytable )，is_visited是一个 bool 值，指定是否 x_id必须被忽略。

那你填visitedName具有 x_id 的所有不同值:

INSERT into visitedName
SELECT DISTINCT x_id, FALSE
FROM mytable

现在您需要找到最流行的名称，忽略 is_visited 所在的行。是真的:

SELECT mt.name_id, COUNT(mt.name_id) name_count
FROM mytable mt JOIN visitedName vn ON mt.x_id = vn.x_id
WHERE NOT vn.is_visited
GROUP BY mt.name_id
ORDER BY name_count DESC
LIMIT 1

在应用程序端，您应该检索返回的唯一行并提取 name_id ，作为参数传递给下一个查询。如果它没有返回任何行，那么你就完成了，你可以 DROP TABLE visitedName .

最后你更新了visitedName表，标记为已访问所有 x_id s 包含至少一次出现的 name_id之前得到。

UPDATE visitedName
SET is_visited = TRUE
WHERE x_id IN (
  SELECT x_id
  FROM mytable
  WHERE name_id = ? -- name_id got before
)

从第 3 点重新开始。

如果您的连接延迟太高或者您期望结果表中的行数太多，那么客户端-应用程序和 DBMS 之间的持续通信是一个相当大的开销。在这种情况下，您需要更高级的东西。

对于那些有兴趣复制 OP 给出的表的人，这是我使用的脚本:

CREATE TABLE mytable(
  id integer,
  x_id varchar(10),
  name_id varchar(50),
  PRIMARY KEY (id)
);

INSERT INTO mytable (id, x_id, name_id) VALUES
  (1, 'x1', 'John'),
  (2, 'x1', 'Frank'),
  (3, 'x2', 'John'),
  (4, 'x2', 'Frank'),
  (5, 'x3', 'John'),
  (6, 'x3', 'Jack'),
  (7, 'x4', 'John'),
  (8, 'x4', 'Jack'),
  (9, 'x5', 'Bob'),
  (10, 'x5', 'Frank'),
  (11, 'x6', 'Bob'),
  (12, 'x6', 'George'),
  (13, 'x7', 'Bob'),
  (14, 'x7', 'Finn'),
  (15, 'x8', 'James'),
  (16, 'x8', 'Mark'),
  (17, 'x9', 'James'),
  (18, 'x9', 'Finn');