Идея: для каждой страны вычислить пороги выбросов (например, исключить нижние и верхние $1\%$: пороги $p_{0.01}$ и $p_{0.99}$), затем посчитать среднее по заказам, попавшим в этот интервал. Ниже — два варианта: точный (PostgreSQL) и масштабируемый/приблизительный (BigQuery / аналитические СУБД).
1) PostgreSQL (точно, но затратнее):
```
WITH country_bounds AS (
SELECT c.country,
percentile_cont(0.01) WITHIN GROUP (ORDER BY o.amount) AS p_low,
percentile_cont(0.99) WITHIN GROUP (ORDER BY o.amount) AS p_high
FROM Orders o
JOIN Customers c USING (customer_id)
GROUP BY c.country
)
SELECT cb.country,
AVG(o.amount) AS avg_trimmed
FROM Orders o
JOIN Customers c USING (customer_id)
JOIN country_bounds cb USING (country)
WHERE o.amount BETWEEN cb.p_low AND cb.p_high
GROUP BY cb.country;
```
Здесь пороги соответствуют долям $0.01$ и $0.99$.
2) Масштабируемо / приближённо (BigQuery пример, быстрее на больших данных):
```
WITH percentiles AS (
SELECT country,
(SELECT quantiles[OFFSET(1)] FROM UNNEST(APPROX_QUANTILES(amount, 100)) AS quantiles) AS p_low,
(SELECT quantiles[OFFSET(99)] FROM UNNEST(APPROX_QUANTILES(amount, 100)) AS quantiles) AS p_high
FROM Orders o
JOIN Customers c USING(customer_id)
GROUP BY country
)
SELECT p.country,
AVG(o.amount) AS avg_trimmed
FROM Orders o
JOIN Customers c USING(customer_id)
JOIN percentiles p USING(country)
WHERE o.amount BETWEEN p.p_low AND p.p_high
GROUP BY p.country;
```
Здесь используется приближённая функция, дающая пороги для примерно $1\%$ и $99\%$.
Короткие рекомендации по масштабируемости:
- Вычисление порогов делать отдельной агрегацией (один проход по данным), затем JOIN и второй проход для усреднения — это две простые агрегации, хорошо масштабируются.
- На больших объёмах используйте встроенные приближённые агрегаты (tdigest/approx_percentile/APPROX_QUANTILES).
- Индекс/партиционирование по customer_id или country и по amount ускорит группы и фильтры.
- Выберите уровень отсечения $\alpha$ (например, $\alpha =0.01$) в зависимости от задачи.