Задача: для каждой строки заказа вернуть $1$ суммарную годовую покупку клиента $суммаповсемзаказамэтогоклиентазатотжегод,чтоиузаказа$ и $2$ ранг клиента в его регионе по этой годовой сумме.

Я покажу два подхода: 1) "JOIN + GROUP BY + коррелированный подзапрос" $чистоагрегирующий+ранжированиебезоконныхфункций$ и 2) "агрегация + оконная функция" $рекомендуемый—чищеиобычноэффективнее$. После — рекомендации по индексам и обсуждение поведения под высокой нагрузкой / требований по согласованности.

Обозначения: Orders$orde{r}_{i}d,custome{r}_{i}d,amount,date$, Customers$custome{r}_{i}d,region$. Под year я понимаю EXTRACT$YEARFROMdate$.

1) JOIN + GROUP BY + коррелированный подзапрос $безоконныхфункций$ Принцип: сначала соберём суммарную годовую покупку per customer/year, затем присоединим её к Orders и посчитаем ранг коррелированным подзапросом $countдругихклиентоввтомжерегионесбольшейсуммой$.

SQL $пример$:
WITH cust_year_sum AS $SELECTcustome{r}_{i}d,EXTRACT(YEARFROMdate)ASyear,SUM(amount)ASannua{l}_{s}umFROMOrdersGROUPBYcustome{r}_{i}d,EXTRACT(YEARFROMdate)$ SELECT
o.,
cys.annual_sum,
(
SELECT 1 + COUNT()
FROM cust_year_sum cys2
JOIN Customers cu2 ON cu2.customer_id = cys2.customer_id
WHERE cys2.year = cys.year
AND cu2.region = cu.region
AND cys2.annual_sum > cys.annual_sum
) AS region_rank
FROM Orders o
JOIN cust_year_sum cys
ON cys.customer_id = o.customer_id
AND cys.year = EXTRACT$YEARFROMo.date$ JOIN Customers cu
ON cu.customer_id = o.customer_id;

Пояснения:

cust_year_sum — одна запись на $customer,year$ с суммой.Коррелированный подзапрос считает сколько других customer в том же region имеют annual_sum больше текущего → ранг = count+1.
Плюсы/минусы:Простой SQL и понятная семантика.Но коррелированный подзапрос может быть дорогим: для каждой уникной записи cust_year_sum $авыприсоединяетееёккаждойстрокезаказа$ выполняется перебор/скан по всем customer в том же году/регионе — O$N^2$ в худшем случае, что плохо на больших данных.Некоторым СУБД оптимизация такого шаблона работает плохо.

2) Рекомендуемая версия — сначала агрегируем, затем используем оконную функцию для ранжирования, затем джоиним обратно к Orders
Порядок: $a$ агрегируем per customer/year, $b$ присоединяем region, $c$ считаем ранг по region+year через DENSE_RANK/RANK, $d$ джоиним к Orders.

SQL $рекомендованный$:
WITH cust_year AS $SELECTo.custome{r}_{i}d,EXTRACT(YEARFROMo.date)ASyear,SUM(o.amount)ASannua{l}_{s}umFROMOrdersoGROUPBYo.custome{r}_{i}d,EXTRACT(YEARFROMo.date)$,
cust_year_with_region AS $SELECTcy.<em>,cu.regionFROMcus{t}_{y}earcyJOINCustomerscuONcu.custome{r}_{i}d=cy.custome{r}_{i}d$,
ranked AS $SELECTcywr.</em>,DENS{E}_{R}ANK()OVER(PARTITIONBYregion,yearORDERBYannua{l}_{s}umDESC)ASregio{n}_{r}ankFROMcus{t}_{y}ea{r}_{w}it{h}_{r}egioncywr$ SELECT
o.*,
r.annual_sum,
r.region_rank
FROM Orders o
JOIN ranked r
ON r.customer_id = o.customer_id
AND r.year = EXTRACT$YEARFROMo.date$;

Пояснения:

На этапе ranked мы имеем ровно одну строку на $customer,year$ и применяем оконную функцию в разрезе $region,year$. Это даёт ранги $DENS{E}_{R}ANKилиRANKвзависимостиотжелаемойсемантики$.Затем JOIN восстанавливает per-order строки, повторяя annual_sum и region_rank для каждого заказа этого клиента в этом году.
Плюсы/минусы:Чище, обычно эффективнее: ранжирование делается один раз над множеством агрегированных строк $кол-во=numberofcustomers\times years$, а не многократно.Оконные функции требуют сортировки/партиционирования; при больших объёмах может потребоваться память/внешний сорт, но это обычно масштабируется лучше, чем квадратичные коррелированные подзапросы.Поддерживается большинством современных СУБД $Postgres,MySQL8+,SQLServerит.д.$.

Вариант без промежуточного GROUP BY для annual_sum $черезоконнуюфункциюнаOrders$ Можно также получить annual_sum прямо в строке заказа так:
SUM$amount$ OVER $PARTITIONBYcustome{r}_{i}d,EXTRACT(YEARFROMdate)$ AS annual_sum_per_order
Но ранжировать по region удобнее на агрегированном уровне $одназаписьнаcustomer/year$. Нативное вложение оконных функций $ранжированиепоvalue,которыйсампосчитаноконнойфункцией$ нельзя во многих диалектах, поэтому обычно делают CTE агрегацию → оконная функция → join.

Индексация $рекомендации$

PK/индексы:
Customers.customer_id — PK $обычноесть$.Customers$region$ — индекс, если часто фильтруете или джойните по region на большом наборе.Для ускорения GROUP BY по customer и year:
Индекс на Orders$custome{r}_{i}d,date$ или $custome{r}_{i}d,date,amount$ — помогает быстро выбирать строки для одного customer и диапазона дат/года.Если СУБД поддерживает функциональные/выраженные индексы, можно создать индекс на $custome{r}_{i}d,EXTRACT(YEARFROMdate)$ или на date_trunc${}^{\prime }yea{r}^{\prime },date$ вместе с amount для ускорения группировки по году $Postgresподдерживаетexpressionindex$.Для частых запросов только по текущему году: частичный индекс WHERE date >= '2025-01-01'.Если аналитика — частый сценарий, рассмотрите агрегированную таблицу $materializedview/summarytable$ с индексом по $year,region,custome{r}_{i}d$ и периодическим обновлением $batch/CDC/trigger$.

Последствия для согласованности и поведения под высокой нагрузкой

Снимок данных и уровни изоляции:
В большинстве MVCC СУБД $например,PostgreSQL$ одиночный SELECT выполняется в рамках одного консистентного снимка — вы получите внутренне согласованные суммы и ранги на момент начала запроса. Однако при уровне READ COMMITTED могут быть различия между несколькими SQL-выражениями в рамках одной транзакции $вPostgresREADCOMMITTEDберётновыйснимокдлякаждоговыражения$.Если вам нужна гарантия, что все части отчёта видят один и тот же момент времени данных, запустите запрос в транзакции с REPEATABLE READ $илииспользуйтемеханизмыsnapshot$. Но длинные снимки тормозят VACUUM и могут создавать нагрузку.Атомарность и свежесть:
Для аналитики "на лету" вы, вероятно, будете видеть все уже зафиксированные транзакции. Невозможно одновременно получить абсолютную неделимую картину при постоянно прибывающих записях без либо приостановки записей, либо использования snapshot изоляции.Если требуется строгая консистентность при конкурентных модификациях $например,выхотитевмоментTполучитьточныйрангнесмотрянаодновременныевставки$, можно:запускать отчёт на snapshot-изолированном транзакции $витаетрискдолгихтранзакций$,либо использовать materialized view, обновляемый транзакционно/пакетно,либо иметь потоковую агрегацию $incremental/CDC$ и читать предагрегированные значения.Блокировки и влияние на запись:
Чтение агрегатов обычно не блокирует вставки/обновления при MVCC, но если вы применяете явные блокировки или повышенную изоляцию — записи могут блокироваться или транзакции будут отклоняться $вSERIALIZABLE$.Долгие аналитические запросы в режиме REPEATABLE/ SERIALIZABLE могут замедлить очистку старых версий строк $VACUUM$, приводя к росту хранения/IO.Производительность:
GROUP BY по большой таблице = скан $илииндексныйобход$ + агрегация. Если считывать весь Orders,— высокая нагрузка.Оконные функции добавляют сортировку/партиционирование; память/внешний сорт может потребоваться. Но 한 раз выполнить сортировку по агрегированному набору $customer\times year$ чаще дешевле, чем N коррелированных подзапросов.При экстремальной нагрузке: предпочтительнее поддерживать предварительные агрегаты $summarytable$, которые быстро джойнятся и индексируются.

Резюме / рекомендации

Для читаемости и производительности используйте агрегацию per customer/year + оконная функция $DENS{E}_{R}ANK/RANK$ для ранга, затем джойн к Orders. Это и семантически чисто, и обычно эффективнее.Создайте индексы: Orders$custome{r}_{i}d,date$ / функциональный индекс по году, Customers.customer_id PK и индекс по region.Если данные очень большие и/или запись идёт постоянно:
рассмотрите materialized view / summary table $ежечасная/ежедневнаяагрегацияилиинкрементальныеобновлениячерезCDC$,или используйте транзакционную snapshot-изоляцию, если нужна консистентность на весь запрос $имейтеввидувлияниенаVACUUM/долгиетранзакции$.Избегайте коррелированных подзапросов, которые сравнивают каждый customer с каждым другим на больших наборах — это быстро станет узким местом.

Если хотите, могу:

дать tuned-версии запросов для конкретной СУБД $Postgres/MySQL/SQLServer$,предложить схему materialized view и скрипт для его инкрементального обновления,предложить конкретные индексы с синтаксисом для вашей СУБД.