sql - create - Agregación de una anotación en GROUP BY en Django

group by django

Question

ACTUALIZAR

Gracias a la respuesta publicada, encontré una forma mucho más sencilla de formular el problema. La pregunta original se puede ver en el historial de revisiones.

El problema

Estoy tratando de traducir una consulta SQL a Django, pero recibo un error que no entiendo.

Aquí está el modelo de Django que tengo:

class Title(models.Model):
  title_id = models.CharField(primary_key=True, max_length=12)
  title = models.CharField(max_length=80)
  publisher = models.CharField(max_length=100)
  price = models.DecimalField(decimal_places=2, blank=True, null=True)

Tengo los siguientes datos:

publisher                    title_id      price  title
---------------------------  ----------  -------  -----------------------------------
New Age Books                PS2106         7     Life Without Fear
New Age Books                PS2091        10.95  Is Anger the Enemy?
New Age Books                BU2075         2.99  You Can Combat    Computer Stress!
New Age Books                TC7777        14.99  Sushi, Anyone?
Binnet & Hardley             MC3021         2.99  The Gourmet Microwave
Binnet & Hardley             MC2222        19.99  Silicon Valley   Gastronomic Treats
Algodata Infosystems         PC1035        22.95  But Is It User Friendly?
Algodata Infosystems         BU1032        19.99  The Busy Executive''s   Database Guide
Algodata Infosystems         PC8888        20     Secrets of Silicon Valley

Esto es lo que quiero hacer: introducir un campo anotado dbl_price que sea el doble del precio, luego agrupar el conjunto de consultas resultante por publisher y, para cada editor, calcular el total de todos los valores de dbl_price para todos los títulos publicados por ese editor.

La consulta SQL que hace esto es la siguiente:

SELECT SUM(dbl_price) AS total_dbl_price, publisher
FROM (
  SELECT price * 2 AS dbl_price, publisher
  FROM title
) AS A 
GROUP BY publisher

La salida deseada sería:

publisher                    tot_dbl_prices
---------------------------  --------------
Algodata Infosystems                 125.88
Binnet & Hardley                      45.96
New Age Books                         71.86 

Consulta de django

La consulta se vería así:

Title.objects
 .annotate(dbl_price=2*F(''price''))
 .values(''publisher'')
 .annotate(tot_dbl_prices=Sum(''dbl_price''))

pero da un error:

KeyError: ''dbl_price''. 

lo que indica que no puede encontrar el campo dbl_price en el queryset.

El motivo del error.

Aquí es por qué ocurre este error: la documentación dice

También debe tener en cuenta que average_rating se ha incluido explícitamente en la lista de valores que se devolverán. Esto es necesario debido al orden de los valores () y la cláusula annotate ().

Si la cláusula de valores () precede a la cláusula annotate (), cualquier anotación se agregará automáticamente al conjunto de resultados. Sin embargo, si la cláusula values ​​() se aplica después de la cláusula annotate (), debe incluir explícitamente la columna agregada.

Por lo tanto, el dbl_price no se pudo encontrar en la agregación, porque fue creado por un annotate anterior, pero no se incluyó en los values() .

Sin embargo, tampoco puedo incluirlo en los values , porque quiero usar values (seguidos de otra annotate ) como dispositivo de agrupación, ya que

Si la cláusula de valores () precede a annotate (), la anotación se computará utilizando la agrupación descrita por la cláusula de valores ().

que es la base de cómo Django implementa SQL GROUP BY . Esto significa que no puedo incluir dbl_price dentro de los values() , porque entonces la agrupación se basará en combinaciones únicas de publisher de ambos campos y dbl_price , mientras que solo debo agrupar por publisher .

Entonces, la siguiente consulta, que solo difiere de la anterior en que dbl_price sobre el campo de price del modelo en lugar del campo anotado dbl_price , en realidad funciona:

Title.objects
 .annotate(dbl_price=2*F(''price''))
 .values(''publisher'')
 .annotate(sum_of_prices=Count(''price''))

porque el campo de price está en el modelo en lugar de ser un campo anotado, por lo que no necesitamos incluirlo en los values para mantenerlo en el conjunto de consultas.

La pregunta

Entonces, aquí lo tenemos: necesito incluir una propiedad anotada en los values para mantenerla en el queryset, pero no puedo hacer eso porque los values también se usan para agrupar (lo que estará mal con un campo adicional). El problema se debe esencialmente a las dos formas muy diferentes en que se usan los values en Django, dependiendo del contexto (si los values van seguidos de annotate o no), que es (1) extracción de valores (lista SELECT SQL sin formato) y (2) Agrupación + agregación sobre los grupos (SQL GROUP BY ) - y en este caso, estas dos formas parecen entrar en conflicto.

Mi pregunta es : ¿hay alguna manera de resolver este problema (sin cosas como volver a caer en sql)?

Tenga en cuenta: el ejemplo específico en cuestión se puede resolver moviendo todas las declaraciones de annotate después de los values , lo que se observó mediante varias respuestas. Sin embargo, estoy más interesado en las soluciones (o discusión) que mantendrían la (s) declaración (es) de annotate (s) antes de los values() , por tres razones: 1. También hay ejemplos más complejos, donde la solución sugerida no funcionaría. 2. Puedo imaginar situaciones en las que el queryset anotado se ha pasado a otra función, que en realidad es GROUP BY, de modo que lo único que sabemos es el conjunto de nombres de campos anotados y sus tipos. 3. La situación parece ser bastante sencilla, y me sorprendería si este choque de dos usos distintos de los values() no se haya notado y discutido antes.

Answer 1

Esto se espera de la forma en que group_by funciona en Django. Todos los campos anotados se agregan en la cláusula GROUP BY . Sin embargo, no puedo comentar por qué fue escrito de esta manera.

Puedes hacer que tu consulta funcione así:

Title.objects
  .values(''publisher'')
  .annotate(total_dbl_price=Sum(2*F(''price''))

que produce el siguiente SQL:

SELECT publisher, SUM((2 * price)) AS total_dbl_price
FROM title
GROUP BY publisher

Lo que por casualidad funciona en tu caso.

Entiendo que esta podría no ser la solución completa que buscaba, pero también se pueden incluir algunas anotaciones complejas en esta solución mediante el uso de CombinedExpressions (¡espero!).

Answer 2

Lo que necesitas es esto:

from django.db.models import Sum

Title.objects.values(''publisher'').annotate(tot_dbl_prices=2*Sum(''price''))

Idealmente, invirtí el escenario aquí resumiéndolos primero y luego duplicándolo. Estabas intentando duplicarlo y luego resumirlo. Espero que esto esté bien.

Answer 3

Quizás sea demasiado tarde, pero he encontrado la solución (probada con Django 1.11.1).

El problema es que la llamada a .values(''publisher'') , que se requiere para proporcionar agrupación, elimina todas las anotaciones, que no están incluidas en los campos .values() param.

Y no podemos incluir dbl_price a los campos param, porque agregará otra instrucción GROUP BY .

La solución es hacer que todas las agregaciones, que requieren campos anotados en primer lugar, luego llamen a .values() e incluyan esas agregaciones en los campos param (esto no agregará GROUP BY , porque son agregaciones). Luego deberíamos llamar a .annotate() con CUALQUIER expresión: esto hará que django agregue la instrucción GROUP BY a la consulta SQL usando el único campo de no agregación en la consulta: editor .

Title.objects
    .annotate(dbl_price=2*F(''price''))
    .annotate(sum_of_prices=Sum(''dbl_price''))
    .values(''publisher'', ''sum_of_prices'')
    .annotate(titles_count=Count(''id''))

El único inconveniente con este enfoque: si no necesita ninguna otra agregación excepto la que tiene un campo anotado, deberá incluir algunos de todos modos. Sin la última llamada a .annotate () (¡y debería incluir al menos una expresión!), Django no agregará GROUP BY a la consulta SQL. Un enfoque para lidiar con esto es simplemente crear una copia de su campo:

Title.objects
    .annotate(dbl_price=2*F(''price''))
    .annotate(_sum_of_prices=Sum(''dbl_price'')) # note the underscore!
    .values(''publisher'', ''_sum_of_prices'')
    .annotate(sum_of_prices=F(''_sum_of_prices'')

Además, mencione que debe tener cuidado con los pedidos de QuerySet. Será mejor que llame a .order_by() sin parámetros para borrar el pedido, o con su campo GROUP BY . Si la consulta resultante contendrá ordenamiento por cualquier otro campo, la agrupación será incorrecta. https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#interaction-with-default-ordering-or-order-by

Además, es posible que desee eliminar esa anotación falsa de su salida, así que vuelva a llamar a .values ​​(). Entonces, el código final se ve como:

Title.objects
    .annotate(dbl_price=2*F(''price''))
    .annotate(_sum_of_prices=Sum(''dbl_price''))
    .values(''publisher'', ''_sum_of_prices'')
    .annotate(sum_of_prices=F(''_sum_of_prices'')
    .values(''publisher'', ''sum_of_prices'')
    .order_by(''publisher'')

Answer 4

Su problema proviene de los values() seguidos por annotate() . El orden es importante. Esto se explica en la documentación sobre [orden de anotación y cláusulas de valores] ( https://docs.djangoproject.com/en/1.10/topics/db/aggregation/#order-of-annotate-and-values-clauses )

.values(''pub_id'') limita el campo pub_id con pub_id . Así que no puedes anotar en el income

El método de valores () toma argumentos posicionales opcionales, * campos, que especifican los nombres de los campos a los que se debe limitar SELECT.