Введение

Для решения этой задачи, нужно сделать 2 шага:

Посчитать сумму каждого заказа в каждый месяц
Найти медианное значение в рамках каждого месяца

Расчет суммы заказа

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

Для этого с помощью join мы присоединяем таблицу analysis по полям ord_an и an_id - идентификатор анализа. В этой таблице поле an_price содержит стоимость каждого анализа. Соответственно, сгруппировав по заказам и посчитав сумму всех таких an_price, мы узнаем сумму каждого заказа:

SELECT TO_CHAR(ord_datetime, 'YYYY-MM') AS dt, ord_datetime AS id, SUM(an_price) AS revenue FROM orders o JOIN analysis a ON o.ord_an = a.an_id GROUP BY dt, id

Дополнительно в этом запросе мы выделили из заказов информацию о годе и месяце с помощью функции to_char, чтобы в дальнейшем считать медиану.

Расчет медианы

Дело остается за малым - остается посчитать действительную и интерполированную медиану. Сделать это можно с помощью встроенных сортирующих агрегатных функций PostgreSQL:

PERCENTILE_CONT
PERCENTILE_DISC

Первая функция возвращает заданный перцентиль, интерполируя входные данные. Вторая функция возвращает ближайшее к интерполированному перцентилю значение из самой выборки.

N-перцентиль - это значение в отсортированной выборке данных, которое разбивает эту выборку на 2 части: N% элементов выборки меньше этого значения, а остальные (100%-N%) - больше.

По сути, медиана - пятидесяти процентный перцентиль.

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

Дано: 1; 2
Интерполированная медиана: 1.5

Итог

Таким образом, решение задачи можно записать так:

WITH a AS ( SELECT TO_CHAR(ord_datetime, 'YYYY-MM') AS dt, ord_datetime AS id, SUM(an_price) AS revenue FROM orders o JOIN analysis a ON o.ord_an = a.an_id GROUP BY dt, id ) SELECT dt, PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY revenue)::NUMERIC AS interpolated_median, PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY revenue)::NUMERIC AS real_median FROM a GROUP BY dt ORDER BY dt

Медианные суммы заказов

Оформите Premium