python - 为数百万个术语建立多对多关系

Question

我在一个有大约 400 万条记录的 python sqlite3 数据库中有以下信息。

Term         No of articles      Article Ids
Obama           300            [411,523,534, …. 846]
Gandhi         3900            [23,32,33…..4578]
Mandela        3900            [21,14,56,145 …4536]
George Bush     450            [230,310 … 700]
Tony Blair      350            [225,320 … 800]
Justin Bieber   25             [401 , 420, 690 …. 904]
Lionel Messi    150            [23, 78, …… 570]

“文章 ID”是一个包含 ID 列表的 blob(由 API 返回)

我的任务是从 ID 列表中为每个术语找到公共(public) ID，并将它们保存在“relationships.db”中

如何建立关系，找到哪些文章同时谈论甘地和曼德拉(相交的文章 ID)？

relationships.db 应该是这样的；

Term 1              Term 2          No of Common Article Ids    Common Article IDS
Obama               Gandhi                17                    [34,123,25 ...]
Obama               Mandela               43                    [145,111,234,456 ....]
Obama               George Bush           46
Obama               Tony Blair             2
Obama               Justin Bieber         36
Obama               Lionel Messi           3
Gandhi              Mandela               40
Gandhi              George Bush           41
Gandhi              Tony Blair            32
Gandhi              Justin Bieber         31
Gandhi              Lionel Messi          20
Mandela             George Bush           20
Mandela             Tony Blair            11
Mandela             Justin Bieber         19
Mandela             Lionel Messi          39
George Bush         Tony Blair            46
George Bush         Justin Bieber         49
George Bush         Lionel Messi           2
Tony Blair          Justin Bieber         50
Tony Blair          Lionel Messi           3
Justin Bieber       Lionel Messi           6

使用“for 循环”循环遍历每个术语以获得交集是很痛苦的。有没有一种有效的方法来做到这一点？在“内存”和“速度”之间会有取舍吗？

Answer 1

评论暗示了这一点，但不可能(或至少很难)在信息仍在 BLOB 中时对其进行处理。如果重组数据库，您可以取得更大的进步:

Term      Article_id
Ghandi    33
Obama     411
Obama     523
Ghandi    23
Obama     846
...
Mandela   23

这种“平面”表示有一些优点。首先，它使添加新文章变得容易，您不必提取 blob。其次，通过适当的索引，您可以轻松恢复原始计数:

SELECT COUNT(*) FROM news WHERE Term="Ghandi"`

如果需要，这些可以存储在单独的表中。要提取联合文章，您可以搜索如下内容:

SELECT A.Article_id, A.Term, B.Term FROM news AS A 
JOIN news AS B ON A.Article_id = B.Article_id 
AND A.Term != B.Term

请注意，这会重复计算，但很容易纠正。这是一个完整的最小工作示例，其中修改了表格以显示更多匹配项:

import sqlite3

conn = sqlite3.connect(":memory:")

raw_items = '''
Ghandi    33
Obama     411
Obama     521
Ghandi    23
Obama     21
Ghandi    411
Mandela   21'''

script = '''
CREATE TABLE news ( 
Term STRING,
Article_id INTEGER
);'''

conn.executescript(script)
items = [line.split() for line in raw_items.strip().split('\n')]
conn.executemany("INSERT INTO news VALUES (?,?)", items)

cmd = '''SELECT COUNT(*) FROM news WHERE Term="Obama"'''
print "Obama size: ", conn.execute(cmd).fetchone()

cmd = '''
SELECT A.Article_id, A.Term, B.Term FROM news AS A 
JOIN news AS B ON A.Article_id = B.Article_id 
AND A.Term != B.Term '''

for result in conn.execute(cmd).fetchall():
    print result

这给出:

Obama size:  (3,)
(411, u'Obama', u'Ghandi')
(21, u'Obama', u'Mandela')
(411, u'Ghandi', u'Obama')
(21, u'Mandela', u'Obama')