带peewee的多个聚合函数

Question

我几乎不知道SQL，但我一直在使用peewee，到目前为止，它已经工作得很好。我碰到了一个不知如何解决的问题。

我有两个表A和B。在其他字段中，A有多个带外键的B。B有3个字段(在我的情况下是5，但3是最小复制):

A:
id param_1 ...
0  ...
1  ...
2  ...

和

B:
id A_key x  y  z
0  0     3. 5. 1
1  0     4. 4. 2
2  1     2. 3. 3
3  1     5. 1. 4    

对于每个A，我想获得一方面对应于x的最小值的z，另一方面对应于y的最小值。对于x或y这样做很容易:(Q1)

A.select(fn.MIN(B.x), B.z.alias('z_x')).join(B).group_by(A)
> A_id x  z_x
> 0    3. 1
> 1    2. 3

我可以对b做同样的事情。我的问题是:你会怎么做才能同时拥有两者?(Q2)

???
> A_id x  z_x  y  z_y 
> 0    3. 1    4. 2
> 1    2. 3    1. 4

当然，我试着做

A.select(fn.MIN(B.x), B.z.alias('z_x'), fn.MIN(B.y), B.z.alias('z_y')).join(B).group_by(A)

但是z_x列被z_y代替了

对于SQL专家来说，(Q1)对应于

SELECT MIN("t1"."x"), "t1"."z" FROM "A" AS "t2" INNER JOIN "B" AS "t1" ON ("t1"."A_id" = "t2"."id") GROUP BY "t2"."id"

我还可以翻译一个SQL查询(Q2)在矮小的如果有人看到纯SQL的解决方案!

很高兴得到任何帮助，如果需要，我可以提供更多细节!

Answer 1

这最终比我最初想象的要困难，因为Sqlite有点使查询工作的简化版本…然而，它们并不完全正确。无论如何，至少就我对问题的理解而言，以下内容似乎工作正常(对于每个A，获得最小X和最小Y的Z):

class A(Base):
key = TextField()
class B(Base):
a = ForeignKeyField(A)
x = IntegerField()
y = IntegerField()
z = IntegerField()
db.create_tables([A, B])
a1 = A.create(key='a1')
a2 = A.create(key='a2')
B.create(a=a1, x=1, y=100, z=10)
B.create(a=a1, x=10, y=10, z=5)
B.create(a=a1, x=100, y=1, z=2)
B.create(a=a2, x=2, y=200, z=20)
B.create(a=a2, x=20, y=20, z=15)
B.create(a=a2, x=200, y=4, z=10)
# The idea is we will create first a query that gets us
# the minimum X for each A (subq).
BX = B.alias()
subq = BX.select(BX.a, fn.MIN(BX.x).alias('minx')).group_by(BX.a)
# Then we want to get the Z's for all rows whose X is
# equal to the minimum X (for each A).
z_for_x = (BX
.select(BX.a, BX.z, subq.c.minx)
.join(subq, on=((BX.a == subq.c.a_id) & (BX.x == subq.c.minx)))
.group_by(BX.a, BX.z))
# Do the same for the Y's.
# First find the minimum Y for each A.
BY = B.alias()
subq = BY.select(BY.a, fn.MIN(BY.y).alias('miny')).group_by(BY.a)
# Then get all Z's for each A whose Y is equal to the
# minimum Y we found.
z_for_y = (BY
.select(BY.a, BY.z, subq.c.miny)
.join(subq, on=((BY.a == subq.c.a_id) & (BY.y == subq.c.miny)))
.group_by(BY.a, BY.z))
# We'll use common table expressions to access these
# two distinct subqueries.
xcte = z_for_x.cte('zforx', columns=['ba', 'bz', 'minx'])
ycte = z_for_y.cte('zfory', columns=['ba', 'bz', 'miny'])
# Then get all A's and their associated minimum X and it's
# corresponding Z, and minimum Y and it's corresponding Z:
q = (A.select(A, xcte.c.minx, xcte.c.bz, ycte.c.miny, ycte.c.bz)
.join_from(A, xcte, JOIN.INNER, on=(A.id == xcte.c.ba))
.join_from(A, ycte, JOIN.INNER, on=(A.id == ycte.c.ba))
.with_cte(xcte, ycte))
# Execute our query:
for row in q.tuples():
print(row)

#OUTPUT:
# 1, 'a1', 1, 10, 1, 2
# 2, 'a2', 2, 20, 4, 10

输出说明:

对于a1，最小X=1 (z=10)和最小Y=1 (z=2)

对于a2，最小X=2 (z=20)和最小Y=4 (z=10)