Оптимизация ORM Django Postgresql

У меня есть представление PostgreSQL под названием view_sales_dashboard — оно состоит из нескольких миллионов строк данных о ежедневных продажах.

В представлении Django я хочу представить таблицу, сгруппированную по продуктам, со столбцами как общее значение base_daily_pnl для разных периодов времени: ежедневно, с месяца до даты (MTD), от квартала до даты (QTD), от года до даты (YTD) и Начало на сегодняшний день (ITD)

Чтобы попытаться ограничить количество SQL-запросов, я создаю 5 наборов запросов, чтобы затем сгенерировать таблицу. Чтобы повысить эффективность этого процесса, я исследовал журналы и ожидал увидеть 5 SQL-запросов. Однако в журнале показано 20 запросов (5 типов продуктов * 4 агрегированные группы + запрос ежедневной серии).

См. ниже код Django, модель ORM и журналы.

Может ли кто-нибудь посоветовать 1.) почему запускается так много SQL-запросов 2.) как оптимизировать?

Обратите внимание: queryset_daily_products — это набор запросов данных о ежедневных продажах, сгруппированных по продуктам. Это выглядит так, как показано ниже. Затем я пытаюсь добавить общее количество MTD, QTD, YTD, ITD base_daily_pnl для каждого продукта.

[{'product': 'sweets', 'base_daily_pnl':3000}, 
 {'product': 'car', 'base_daily_pnl':3000}, 
etc .....
]

Результат, который я хочу (который обеспечивает этот цикл, но с множеством ненужных запросов sql):

[{'product': 'sweets', 'base_daily_pnl':3000, 'mtd_pnl': 5000, 'qtd_pnl':6000,'ytd_pnl':8000, 'itd_pnl':10000},
 {'product': 'car', 'base_daily_pnl':4000, 'mtd_pnl': 5100, 'qtd_pnl':6300,'ytd_pnl':8600, 'itd_pnl':12000},
etc .....
]

queryset_sales_all = SalesDashboard.objects.all()

queryset_daily_products = queryset_pnl_all.filter(position_date__range=[latest_pnl_date_str, latest_pnl_date_str]).values('product').annotate(base_daily_pnl=Sum('base_daily_pnl'),base_lmv=Sum('base_lmv'))  
    
    for product in queryset_daily_products:
        matching_mtd = queryset_pnl_all.filter(position_date__range=[start_mth_str,latest_pnl_date_str]).values('product').annotate(mtd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        matching_qtd = queryset_pnl_all.filter(position_date__range=[start_qtd_str, latest_pnl_date_str]).values('product').annotate(qtd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        matching_ytd = queryset_pnl_all.filter(position_date__range=[start_year_str, latest_pnl_date_str]).values('product').annotate(ytd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        matching_itd = queryset_pnl_all.filter(position_date__range=[start_itd_str, latest_pnl_date_str]).values('product').annotate(itd_pnl=Sum('base_daily_pnl')).get(product=daily['product'])
        product['mtd_pnl'] = matching_mtd['mtd_pnl']
        product['qtd_pnl'] = matching_qtd['qtd_pnl']
        product['ytd_pnl'] = matching_ytd['ytd_pnl']
        product['itd_pnl'] = matching_itd['itd_pnl'] 

pnl_product = SummaryPnlProductTable(queryset_daily_product)

Ниже представлена модель ORM:

class SalesDashboard(models.Model):    

    unqiue_id = models.IntegerField(primary_key=True)    
    sales_id = models.CharField(max_length=50)
    base_daily_pnl = models.FloatField(default=0)
    position_date = models.DateField()
    book_id = models.IntegerField()
    book = models.CharField(max_length=100, blank=True, null=True)
    product = models.CharField(max_length=100, blank=True, null=True)
    customer = models.CharField(max_length=100)
    base_lmv = models.FloatField(default=0)

   
    class Meta: 
        managed = False       
        db_table = 'view_sales_dashboard

Ведение журнала:

2.109) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "base_daily_pnl", SUM("view_sales_dashboard"."base_lmv") AS "base_lmv" FROM "view_sales_dashboard" WHERE "view_sales_dashboard"."position_date" BETWEEN '2024-06-30'::date AND '2024-06-30'::date GROUP BY "view_sales_dashboard"."product"; args=(datetime.date(2024, 6, 30), datetime.date(2024, 6, 30)); alias=default
(2.078) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'car'); alias=default
(2.047) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'car'); alias=default
(2.094) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'car'); alias=default
(2.250) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'car') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'car'); alias=default
(2.156) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.093) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.172) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.875) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'coffee') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'coffee'); alias=default
(2.110) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.156) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.203) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.516) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'crisps') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'crisps'); alias=default
(2.281) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.125) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.250) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.594) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sweets') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'sweets'); alias=default
(2.265) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "mtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-06-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 6, 1), datetime.date(2024, 6, 30), 'sundaries'); alias=default
(2.125) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "qtd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-04-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 4, 1), datetime.date(2024, 6, 30), 'sundaries'); alias=default
(2.188) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "ytd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2024-01-01'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2024, 1, 1), datetime.date(2024, 6, 30), 'sundaries'); alias=default
(2.407) SELECT "view_sales_dashboard"."product", SUM("view_sales_dashboard"."base_daily_pnl") AS "itd_pnl" FROM "view_sales_dashboard" WHERE ("view_sales_dashboard"."position_date" BETWEEN '2021-08-02'::date AND '2024-06-30'::date AND "view_sales_dashboard"."product" = 'sundaries') GROUP BY "view_sales_dashboard"."product" LIMIT 21; args=(datetime.date(2021, 8, 2), datetime.date(2024, 6, 30), 'sundaries'); alias=default

Почему вы ожидаете, что будет выполняться меньше запросов, все .get(..) делают запрос, и вы делаете это в цикле, еще больше усложняя процесс запроса.

— 23.07.2024 09:48

@willeM_VanOnsem понял: как вы думаете, как лучше всего с этим справиться?

— 23.07.2024 10:08

Так что же такое daily? Я перечисляю словари с product?

— 23.07.2024 10:17

@willeM_VanOnsem daily следует переименовать в продукт — отредактировано выше. Извините за путаницу - неверное имя переменной.

— 23.07.2024 10:24

а что это, список словарей? Можете ли вы добавить для этого несколько примеров данных. Это может помочь решить проблему.

— 23.07.2024 10:32

@willeM_VanOnsem да, правильный список словарей. Я обновил выше то, что я зацикливаю, и желаемый конечный результат.

— 23.07.2024 10:48

django postgresql django-views django-orm django-tables2

23.07.2024 08:29

Стоит ли изучать PHP в 2023-2024 годах?

Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...

Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией

В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.

Приемы CSS-макетирования - floats и Flexbox

Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...

Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest

В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...

Концепция локализации и ее применение в приложениях React ⚡️

Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...

Пользовательский скаляр GraphQL

Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вы можете легко «пакетировать» запросы так, чтобы для всех продуктов нам требовалось только четыре.

Сначала нам нужно сделать словари более доступными, мы можем сделать это с помощью:

lut = {item['products']: item for item in queryset_daily_products}


def update_lut(qs, start, target, lut):
    for item in (
        qs.filter(
            position_date__range=[start, latest_pnl_date_str], product__in=lut
        )
        .values('product')
        .annotate(result=Sum('base_daily_pnl'))
    ):
        lut[item['product']][target] = item['result']


update_lut(queryset_pnl_all, start_mth_str, 'mtd_pnl', lut)
update_lut(queryset_pnl_all, start_qtd_str, 'qtd_pnl', lut)
update_lut(queryset_pnl_all, start_year_str, 'ytd_pnl', lut)
update_lut(queryset_pnl_all, start_itd_str, 'itd_pnl', lut)

Фактически мы можем переписать это, чтобы сделать это с помощью одного запроса, используя параметр filter=… [Django-doc], но, скорее всего, в этом нет необходимости.

Спасибо. Не могли бы вы показать немного подробностей о том, как использовать filter=parameter, чтобы сделать его одним запросом?

— 23.07.2024 17:39

@Энди: .annotate(result1=Sum('base_daily_pnl', filter=Q(position_date__range=[frm1, to1]), result2 =Sum('base_daily_pnl', filter=Q(position_date__range=[frm2, to2]))

— 23.07.2024 18:13

Большое спасибо за вашу помощь - очень ценю

— 23.07.2024 19:07

23.07.2024 13:35