gpt4 book ai didi

python - 将大型 SQL 查询移至 NumPy

转载 作者:行者123 更新时间:2023-11-30 23:50:23 25 4
gpt4 key购买 nike

我的网络应用程序中有一个非常大的 MySQL 查询,如下所示:

query = 
SELECT video_tag.video_id, (sum(user_rating.rating) * video.rating_norm) as score

FROM video_tag
JOIN user_rating ON user_rating.item_id = video_tag.tag_id
JOIN video ON video.id = video_tag.video_id

WHERE item_type = 3 AND user_id = 1 AND rating != 0 AND video.website_id = 2
AND rating_norm > 0 AND video_id NOT IN (1,2,3) GROUP BY video_id
ORDER BY score DESC LIMIT 20"

此查询连接三个表(video、video_tag 和 user_ rating),对结果进行分组,并进行一些基本数学运算来计算每个视频的分数。由于表很大,运行大约需要 2 秒。

我怀疑使用 NumPy 数组进行计算会更快,而不是让 SQL 完成所有这些工作。 “video”和“video_tag”中的数据是恒定的 - 因此我只需将这些表加载到内存中一次,而不必每次都 ping SQL。

但是,虽然我可以将这三个表加载到三个单独的数组中,但复制上述查询(特别是 JOIN 和 GROUP BY 部分)却很费时间。有人有使用 NumPy 数组复制 SQL 查询的经验吗?

谢谢!

最佳答案

让这个练习变得尴尬的是 NumPy 数组的单一数据类型约束。例如,GROUP BY 操作隐式需要(至少)一个连续值字段/列(用于聚合/求和)以及一个用于分区或分组依据的字段/列。

当然,NumPy recarrays 可以为每列(也称为“字段”)使用不同的数据类型来表示 2D 数组(或 SQL 表),但我发现这些复合数组使用起来很麻烦。因此,在下面的代码片段中,我仅使用传统的 ndarray 类来复制 OP 问题中突出显示的两个 SQL 操作。

模仿 NumPy 中的SQL JOIN:

首先,创建两个 NumPy 数组(A 和 B),每个数组代表一个 SQL 表。 A 的主键位于第一列; B 的外键也在第一列中。

import numpy as NP
A = NP.random.randint(10, 100, 40).reshape(8, 5)
a = NP.random.randint(1, 3, 8).reshape(8, -1) # add column of primary keys
A = NP.column_stack((a, A))

B = NP.random.randint(0, 10, 4).reshape(2, 2)
b = NP.array([1, 2])
B = NP.column_stack((b, B))


现在(尝试)使用 NumPy 数组对象复制 JOIN:

# prepare the array that will hold the 'result set':
AB = NP.column_stack((A, NP.zeros((A.shape[0], B.shape[1]-1))))

def join(A, B) :
'''
returns None, side effect is population of 'results set' NumPy array, 'AB';
pass in A, B, two NumPy 2D arrays, representing the two SQL Tables to join
'''
k, v = B[:,0], B[:,1:]
dx = dict(zip(k, v))
for i in range(A.shape[0]) :
AB[i:,-2:] = dx[A[i,0]]


模仿 NumPy 中的SQL GROUP BY:

def group_by(AB, col_id) :
'''
returns 2D NumPy array aggregated on the unique values in column specified by col_id;
pass in a 2D NumPy array and the col_id (integer) which holds the unique values to group by
'''
uv = NP.unique(AB[:,col_id])
temp = []
for v in uv :
ndx = AB[:,0] == v
temp.append(NP.sum(AB[:,1:][ndx,], axis=0))
temp = NP. row_stack(temp)
uv = uv.reshape(-1, 1)
return NP.column_stack((uv, temp))



对于测试用例,它们返回正确的结果:

>>> A
array([[ 1, 92, 50, 67, 51, 75],
[ 2, 64, 35, 38, 69, 11],
[ 1, 83, 62, 73, 24, 55],
[ 2, 54, 71, 38, 15, 73],
[ 2, 39, 28, 49, 47, 28],
[ 1, 68, 52, 28, 46, 69],
[ 2, 82, 98, 24, 97, 98],
[ 1, 98, 37, 32, 53, 29]])

>>> B
array([[1, 5, 4],
[2, 3, 7]])

>>> join(A, B)
array([[ 1., 92., 50., 67., 51., 75., 5., 4.],
[ 2., 64., 35., 38., 69., 11., 3., 7.],
[ 1., 83., 62., 73., 24., 55., 5., 4.],
[ 2., 54., 71., 38., 15., 73., 3., 7.],
[ 2., 39., 28., 49., 47., 28., 3., 7.],
[ 1., 68., 52., 28., 46., 69., 5., 4.],
[ 2., 82., 98., 24., 97., 98., 3., 7.],
[ 1., 98., 37., 32., 53., 29., 5., 4.]])

>>> group_by(AB, 0)
array([[ 1., 341., 201., 200., 174., 228., 20., 16.],
[ 2., 239., 232., 149., 228., 210., 12., 28.]])

关于python - 将大型 SQL 查询移至 NumPy,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7169240/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com