How to get maximum value of each day in month using django queryset-CodePudding

Let's assume I have Model, Products and I have 100k records. Now I want to pass month 2022-01-01 and get maximum price of each day of the passed month.

Product Model

id     price     date
1.     33.33     2022-01-01
2.     93.33     2022-01-01
3.     64.33     2022-01-02
4.     34.33     2022-01-02
.
.
.
101.   43.33     2022-01-29
102.   74.33     2022-01-30
103.   36.33     2022-01-30

Expecting Result

93.33
64.33
.
.
.
43.33
74.33

Sorry I can't explain in more better way.

Note I just want queryset. Kindly try avoid loop.

CodePudding user response：

You can query the objects and annotate a maximum per day with the following query:

from django.db.models import Max

Product.objects.filter(
    valuta_date__gte="startdate", valuta_date__lt="enddate"
).values("date").order_by("date").annotate(Max("price"))

CodePudding user response：

You can do it like this:

from django.db.models import Max

Product.objects.filter(
    my_date__year="2022", my_date__month="01"
).values('my_date').annotate(
  max_price=Max("price")
).order_by()

But I would suggest executing raw queries when dealing with large dataset as they work faster like this:

max_product_query = """
    SELECT id, MAX(price), my_date FROM your_app_product 
    WHERE strftime("%Y-%m", my_date) = '{0}' GROUP BY my_date;
""".format(year_month)

products = Product.objects.raw(max_product_query)
for product in products:
    # do something with the product

the above query is for sqlite for mysql and postgres use below:

max_product_query = """
    SELECT id, MAX(price), my_date FROM your_app_product 
    WHERE EXTRACT(YEAR_MONTH FROM my_date) = '{0}' GROUP BY my_date';
""".format(year_month)