Как рассчитать медиану в SQL без функций percentile или median, используя оконные функции и группировки?

Что проверяют

Умеете ли вы решать задачу окнами и помните ли про чётное и нечётное количество, дубли, NULL и группы. Следом просят посчитать медиану по каждой категории и спрашивают, чем ваш запрос отличается от встроенной функции.

Как отвечать

Каркас: пронумеровать значения внутри группы, посчитать их количество и взять одно или два средних:

• нумерация: ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount) и COUNT(amount) OVER (PARTITION BY category) в одном CTE. Именно ROW_NUMBER, а не RANK: на повторах RANK даёт пропуски, и нужного номера может не оказаться;
• выбор средних: оставить строки, где номер равен FLOOR((cnt + 1) / 2.0) или CEILING((cnt + 1) / 2.0). При 5 значениях обе границы равны 3, при 6 это 3 и 4;
• итог: AVG(amount) с GROUP BY category. При нечётном числе усредняется одна строка, при чётном две;
• NULL: отфильтровать WHERE amount IS NOT NULL до нумерации. ROW_NUMBER нумерует и пустые значения, COUNT(*) их считает, и медиана сдвигается.

Второй известный приём: две нумерации, по возрастанию и по убыванию, и строки, где номера отличаются не больше чем на единицу. Он работает только с уникальным тай-брейком в ORDER BY, например по id, иначе на дублях номера не сойдутся.

Сверить результат в PostgreSQL можно через percentile_cont(0.5) WITHIN GROUP (ORDER BY amount). В ClickHouse median приближённая, точная это quantileExact, об этом стоит сказать, если вы с ним работаете.

Где спотыкаются

Пишут (cnt + 1) / 2 и (cnt + 2) / 2, рассчитывая на целочисленное деление. В PostgreSQL и SQL Server это работает, а в MySQL / возвращает дробь, и при чётном количестве запрос возьмёт одну строку вместо двух.

Забывают, что в SQL Server AVG от целого столбца возвращает целое: медиана 2 и 3 получится 2. Нужно AVG(amount * 1.0) или явное приведение типа.

Не учитывают NULL и дубли, потому что проверили запрос на аккуратном наборе из пяти разных чисел.