在Python中复制SQL的“联接" [英] Replicating SQL's 'Join' in Python

问题描述

我正在尝试从R切换到Python(主要是有关通用灵活性的问题).使用Numpy，matplotlib和ipython，我能够涵盖所有我的用例，除了合并数据集".我想纯粹在python中模拟SQL的join by子句(内部，外部，完整). R使用合并"功能处理此问题.

I'm in the process of trying to switch from R to Python (mainly issues around general flexibility). With Numpy, matplotlib and ipython, I've am able to cover all my use cases save for merging 'datasets'. I would like to simulate SQL's join by clause (inner, outer, full) purely in python. R handles this with the 'merge' function.

我已经尝试了numpy.lib.recfunctions join_by，但是它与'key'上的重复项存在严重问题:

I've tried the numpy.lib.recfunctions join_by, but it critical issues with duplicates along the 'key':

join_by(key, r1, r2, jointype='inner', r1postfix='1', r2postfix='2',
        defaults=None, usemask=True, asrecarray=False)

在键key上加入数组r1和r2.

键应为字符串或对应的字符串序列 到用于加入数组的字段. 如果在两个输入中找不到key字段，则会引发异常 数组.

The key should be either a string or a sequence of string corresponding to the fields used to join the array. An exception is raised if the key field cannot be found in the two input arrays.

r1和r2都不应该在key上重复: 重复项会使输出非常不可靠.请注意重复 不会被算法寻找.

Neither r1 nor r2 should have any duplicates along key: the presence of duplicates will make the output quite unreliable. Note that duplicates are not looked for by the algorithm.

源: http://presbrey.mit.edu:1234/numpy .lib.recfunctions.html

任何指针或帮助将不胜感激！

Any pointers or help will be most appreciated!

推荐答案

假设您以Python形式表示了一个SQL表的等效表，作为字典列表，所有字典都具有相同的(假设字符串)键(其他表示形式，包括由numpy启用的内容，可以从逻辑上简化为等效形式).现在，内部联接(再次，从逻辑的角度来看)是其笛卡尔积的投影-在一般情况下，采用谓词自变量on(该自变量具有两个自变量，一个为记录" [[dict] ]，然后返回一个真值(如果需要将两个记录连接在一起)，那么一种简单的方法是(使用每个表的前缀来消除歧义，以免两个表可能具有相同的字段")

Suppose you represent the equivalent of a SQL table, in Python, as a list of dicts, all dicts having the same (assume string) keys (other representations, including those enabled by numpy, can be logically boiled down to an equivalent form). Now, an inner join is (again, from a logical point of view) a projection of their cartesian product -- in the general case, taking a predicate argument on (which takes two arguments, one "record" [[dict]] from each table, and returns a true value if the two records need to be joined), a simple approach would be (using per-table prefixes to disambiguate, against the risk that the two tables might otherwise have homonimous "fields"):

def inner_join(tab1, tab2, prefix1, prefix2, on):
  for r1 in tab1:
    for r2 in tab2:
      if on(r1, r2):
        row = dict((prefix1 + k1, v1) for k1, v1 in r1.items())
        row.update((prefix2 + k2, v2) for k2, v2 in r2.items())
        yield row

现在，您当然不会想要这样做，因为性能为O(M * N)-但是，对于您指定的一般性(模拟SQL的join by子句(内部，外部，完整))实际上没有其他选择，因为JOIN的ON子句是不受限制的.

Now, of course you don't want to do it this way, because performance is O(M * N) -- but, for the generality you've specified ("simulate SQL's join by clause (inner, outer, full)") there is really no alternative, because the ON clause of a JOIN is pretty unrestricted.

对于外部联接和完全联接，除了保持标识尚未[[来自一个或两个表]的记录]的信息外，还需要保持信息，否则就产生-对于左联接，您需要添加一个布尔值，在for r2内循环之前重置为yielded = False，如果执行yield则设置为True，在内循环if not yielded:之后生成一个人工联接记录(大概是使用None代表NULL来代替缺少的v2值，因为实际上没有r2可以用于此目的).

For outer and full joins, you need in addition to keep info identifying which records [[from one or both tables]] have not been yielded yet, and otherwise yield -- e.g. for a left join you'd add a bool, reset to yielded = False before the for r2 inner loop, set to True if the yield executes, and after the inner loop, if not yielded:, produce an artificial joined record (presumably using None to stand for NULL in place of the missing v2 values, since there's no r2 to actually use for the purpose).

要获得任何实质性的效率改进，您需要弄清您愿意遵守关于on谓词和表的哪些限制-从您的问题中我们已经知道，您不能忍受对任一表键的约束，但是还有许多其他约束可能会有所帮助，而让我们猜测这种约束在您的情况下实际适用的做法将是徒劳的.

To get any substantial efficiency improvements, you need to clarify what constraints you're willing to abide on regarding the on predicate and the tables -- we already know from your question that you can't live with a unique constraint on either table's keys, but there are many other constraints that could potentially help, and to have us guessing at what such constraints actually apply in your case would be a pretty unproductive endeavor.

这篇关于在Python中复制SQL的“联接"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！