同时查询Sqlite多个参数并处理缺失值

Question

在SQL查询中，有没有可能做到像这样？也许可以提供一个列表作为输入参数？我想要的日期是连续的，但数据库中并不是所有的日期都有。如果某个日期不存在，结果应该是“无”。

dates = [dt.datetime(2008,1,1), dt.datetime(2008,1,2), dt.datetime(2008,1,3), dt.datetime(2008,1,4), dt.datetime(2008,1,5)]
id = "361-442"
result = []
for date in dates:
    curs.execute('''SELECT price, date FROM prices where date = ? AND id = ?''', (date, id))
    query = curs.fetchall()
    if  query == []:
        result.append([None, arg])
    else:
        result.append(query)

Answer 1

在sqlite中完成所有工作，你可以使用左连接（LEFT JOIN）来用None填补缺失的价格：

sql='''
SELECT p.price, t.date
FROM ( {t} ) t
LEFT JOIN price p
ON p.date = t.date
WHERE p.id = ?
'''.format(t=' UNION ALL '.join('SELECT {d!r} date'.format(d=d) for d in date))

cursor.execute(sql,[id])
result=cursor.fetchall()

不过，这种方法需要在Python中生成一个（可能很大的）字符串，以便创建一个包含所有所需日期的临时表。这不仅慢（包括sqlite创建临时表所需的时间），而且还很脆弱：如果len(date)超过大约500，sqlite就会报错。

OperationalError: too many terms in compound SELECT

如果你已经在其他表中有了所有所需的日期，可能就能绕过这个问题。这样，你可以用类似下面的方式替换掉上面那个复杂的“UNION ALL” SQL：

SELECT p.price, t.date
FROM ( SELECT date from dates ) t
LEFT JOIN price p
ON p.date = t.date

虽然这样有所改进，但我的时间测试（见下文）显示，在Python中做一部分工作仍然更快：

---

在Python中做一部分工作：

如果你知道日期是连续的，因此可以用一个范围来表示，那么：

curs.execute('''
    SELECT date, price
    FROM prices
    WHERE date <= ?
        AND date >= ?
        AND id = ?''', (max(date), min(date), id))

否则，如果日期是任意的，那么：

sql = '''
    SELECT date, price
    FROM prices
    WHERE date IN ({s})
        AND id = ?'''.format(s={','.join(['?']*len(dates))})
curs.execute(sql,dates + [id])

---

为了形成result列表，并在缺失价格的地方插入None，你可以用(date, price)对来构建一个dict，然后使用dict.get()方法在date键缺失时提供默认值None：

result = dict(curs.fetchall())
result = [(result.get(d,None), d) for d in date]

注意，为了将dict构建为从日期到价格的映射，我在SQL查询中交换了date和price的顺序。

---

时间测试：

我比较了这三个函数：

def using_sqlite_union():
    sql = '''
        SELECT p.price, t.date
        FROM ( {t} ) t
        LEFT JOIN price p
        ON p.date = t.date
    '''.format(t = ' UNION ALL '.join('SELECT {d!r} date'.format(d = str(d))
                                      for d in dates))
    cursor.execute(sql)
    return cursor.fetchall()

def using_sqlite_dates():
    sql = '''
        SELECT p.price, t.date
        FROM ( SELECT date from dates ) t
        LEFT JOIN price p
        ON p.date = t.date
    '''
    cursor.execute(sql)
    return cursor.fetchall()

def using_python_dict():
    cursor.execute('''
        SELECT date, price
        FROM price
        WHERE date <= ?
            AND date >= ?
            ''', (max(dates), min(dates)))

    result = dict(cursor.fetchall())
    result = [(result.get(d,None), d) for d in dates]
    return result

N = 500
m = 10
omit = random.sample(range(N), m)
dates = [ datetime.date(2000, 1, 1)+datetime.timedelta(days = i) for i in range(N) ]
rows = [ (d, random.random()) for i, d in enumerate(dates) if i not in omit ]

rows定义了插入到price表中的数据。

---

时间测试结果：

像这样运行时间测试：

python -mtimeit -s'import timeit_sqlite_union as t' 't.using_python_dict()'

得到了这些基准测试结果：

·────────────────────·────────────────────·
│  using_python_dict │ 1.47 msec per loop │
│ using_sqlite_dates │ 3.39 msec per loop │
│ using_sqlite_union │ 5.69 msec per loop │
·────────────────────·────────────────────·

using_python_dict的速度大约是using_sqlite_dates的2.3倍。即使我们将总日期数增加到10000，速度比仍然保持不变：

·────────────────────·────────────────────·
│  using_python_dict │ 32.5 msec per loop │
│ using_sqlite_dates │ 81.5 msec per loop │
·────────────────────·────────────────────·

结论：把所有工作都放到sqlite中并不一定更快。