Monday, 25 April 2022

Django ORM: Get maximum value of a field with corresponding other fields values

I have this Table (Counters):

cell_id tftralacc tfnscan thtralacc thnscan date_time
13997 10 360 94 360 2022-02-22 00:00:00+01
13997 0 360 0 360 2022-02-22 01:00:00+01
13997 0 360 0 360 2022-02-22 02:00:00+01
13997 0 360 0 360 2022-02-22 03:00:00+01
13997 36 360 83 360 2022-02-22 04:00:00+01
13997 0 360 2 360 2022-02-22 05:00:00+01
13997 1 360 15 360 2022-02-22 06:00:00+01
13997 11 360 159 360 2022-02-22 07:00:00+01
13997 21 360 409 360 2022-02-22 08:00:00+01
13997 25 360 1282 360 2022-02-22 09:00:00+01
13997 20 360 1201 360 2022-02-22 10:00:00+01
13997 30 360 1381 360 2022-02-22 11:00:00+01
13997 42 360 924 360 2022-02-22 12:00:00+01
14000 1 360 36 360 2022-02-22 00:00:00+01
14000 0 360 0 360 2022-02-22 01:00:00+01
14000 1 360 0 360 2022-02-22 02:00:00+01
14000 0 360 2 360 2022-02-22 03:00:00+01
14000 0 360 0 360 2022-02-22 04:00:00+01
14000 0 360 12 360 2022-02-22 05:00:00+01
14000 3 360 4 360 2022-02-22 06:00:00+01
14000 24 360 123 360 2022-02-22 07:00:00+01
14000 31 360 374 360 2022-02-22 08:00:00+01
14000 18 360 620 360 2022-02-22 09:00:00+01
14000 38 360 1616 360 2022-02-22 10:00:00+01
14000 36 360 1410 360 2022-02-22 11:00:00+01
14000 24 360 957 360 2022-02-22 12:00:00+01

I want to get the specific date_time value of the maximum traffic (which is calculated based on the the fields tftralacc, tfnscan, thtralacc and thnscan) for every cell_id.

I've managed to get this maximum value for every cell_id by using the annotate() and group_by() functions of the Django's QuerySet API:

result = Counters.objects.filter(
    date_time__gte = date_start,
    date_time__lte = date_end
).annotate(
    # calculate the traffic for each row.
    traffic = Case(
        When(Q(tfnscan=0) or Q(thnscan=0), then=0),
        default = Round((F('tftralacc')*1.0/F('tfnscan')) + 
                        (F('thtralacc')*1.0/F('thnscan')), 2),
        output_field=FloatField()
    )
).order_by('cell_id').values(
    # Group by cell_id.
    'cell_id'
).order_by().annotate(
    # calculate the max traffic for the grouped Cells.
    max_traffic = Max('traffic')
)

The calculated traffic for every date_time is demonstrated here: enter image description here

My code successfully returns the maximum traffic for every cell_id:

cell_id max_traffic
13997 3.92
14000 4.59

But my goal is to get the Corresponding date_time value for every max value. like this:

cell_id max_traffic date_time
13997 3.92 2022-02-22 11:00:00+01
14000 4.59 2022-02-22 10:00:00+01

or

cell_id date_time
13997 2022-02-22 11:00:00+01
14000 2022-02-22 10:00:00+01

Because that max value is just a mean to get the date_time and not the goal.

Note: There is this question that describes my problem, but its answer refers to a work-around solution, which is not possible with my problem. SO Question



from Django ORM: Get maximum value of a field with corresponding other fields values

No comments:

Post a Comment