Django Select from a Subquery-CodePudding

I want to query with window function and then do some group by aggregation on the subquery. But I couldn't make it with ORM method. It will return aggregate function calls cannot contain window function calls

Is there any way to make a query like SQL below without using .raw()

SELECT a.col_id, AVG(a.max_count) FROM (
  SELECT col_id,
  MAX(count) OVER (PARTITION BY part_id ORDER BY part_id) AS max_count
  FROM table_one
) a
GROUP BY a.col_id;

Example

table_one

| id | col_id | part_id | count |
| -- | ------ | ------- | ----- |
| 1  | c1     | p1      | 3     |
| 2  | c2     | p1      | 2     |
| 3  | c3     | p2      | 1     |
| 4  | c2     | p2      | 4     |

First I want to get the max base on the part_id

| id | col_id | part_id | count | max_count |
| -- | ------ | ------- | ----- | --------- |
| 1  | c1     | p1      | 3     | 3         |
| 2  | c2     | p1      | 2     | 3         |
| 3  | c3     | p2      | 1     | 4         |
| 4  | c2     | p2      | 4     | 4         |

And finally get the avarage of max_count group by col_id

| col_id | avg(max_count) |
| ------ | -------------- |
| c1     | 3              |
| c2     | 3.5            |
| c3     | 4              |

The models I have now

def Part(models.Model):
    part_id = models.UUIDField(primary_key=True, editable=False, default=uuid.uuid4)
    name = models.CharFields()

def Col(models.Model):
    part_id = models.UUIDField(primary_key=True, editable=False, default=uuid.uuid4)
    name = models.CharFields()

def TableOne(models.Model):
    id = models.UUIDField(primary_key=True, editable=False, default=uuid.uuid4)
    col_id = models.ForeignKey(
        Col,
        on_delete=models.CASCADE,
        related_name='table_one_col'
    )
    part_id = models.ForeignKey(
        Part,
        on_delete=models.CASCADE,
        related_name='table_one_part'
    )
    count = models.IntegerField()

I want to do group by after the partition by. This is the query I did which will bring error.

query = TableOne.objects.annotate(
    max_count=Window(
        expression=Max('count'),
        order_by=F('part_id').asc(),
        partition_by=F('part_id')
    )
).values(
    'col_id'
).annotate(
    avg=Avg('max_count')
)

CodePudding user response：

You can use subqueries in Django, you don't need to use window functions. First the subquery is a Part queryset that is annotated with the max count from TableOne

from django.db.models import Avg, Max, Subquery, OuterRef

parts = Part.objects.filter(
    id=OuterRef('part_id')
).annotate(
    max=Max('table_one_part__count')
)

Then annotate a TableOne queryset with the max count from the subquery, perform values on the column we want to group by (col_id ) and then annotate again with the average to generate your desired output

TableOne.objects.annotate(
    max_count=Subquery(parts.values('max')[:1])
).values(
    'col_id'
).annotate(
    Avg('max_count')
)