Блог

Я едва знаю 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    
 

Я хочу получить z то, что соответствует минимуму x , с одной стороны, y с другой стороны, для каждого A .
Сделать это для 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
 

и я могу сделать то же самое для Б. Мой вопрос: что бы вы сделали, чтобы иметь и то, и другое? (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) в peewee, если кто-то видит решение в чистом SQL!

Рад любой помощи, и я могу предоставить более подробную информацию, если это необходимо!

Это оказалось сложнее, чем я думал изначально, потому что Sqlite в некотором роде заставляет работать упрощенные версии запроса… Однако они не совсем верны. Во всяком случае, кажется, что следующее работает правильно, по крайней мере, насколько я понимаю вопрос (получите Z для min X и min Y, для каждого A):

 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) amp; (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) amp; (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)