SQL Server - условная агрегация с корреляцией
Фон:
Оригинальный случай был очень прост. Рассчитать общее количество пользователей с самого высокого дохода до самого низкого:
CREATE TABLE t(Customer INTEGER NOT NULL PRIMARY KEY
,"User" VARCHAR(5) NOT NULL
,Revenue INTEGER NOT NULL);
INSERT INTO t(Customer,"User",Revenue) VALUES
(001,'James',500),(002,'James',750),(003,'James',450),
(004,'Sarah',100),(005,'Sarah',500),(006,'Sarah',150),
(007,'Sarah',600),(008,'James',150),(009,'James',100);
Query:
SELECT *,
1.0 * Revenue/SUM(Revenue) OVER(PARTITION BY "User") AS percentage,
1.0 * SUM(Revenue) OVER(PARTITION BY "User" ORDER BY Revenue DESC)
/SUM(Revenue) OVER(PARTITION BY "User") AS running_percentage
FROM t;
LiveDemo
Выход:
╔════╦═══════╦═════════╦════════════╦════════════════════╗
║ ID ║ User ║ Revenue ║ percentage ║ running_percentage ║
╠════╬═══════╬═════════╬════════════╬════════════════════╣
║ 2 ║ James ║ 750 ║ 0.38 ║ 0.38 ║
║ 1 ║ James ║ 500 ║ 0.26 ║ 0.64 ║
║ 3 ║ James ║ 450 ║ 0.23 ║ 0.87 ║
║ 8 ║ James ║ 150 ║ 0.08 ║ 0.95 ║
║ 9 ║ James ║ 100 ║ 0.05 ║ 1 ║
║ 7 ║ Sarah ║ 600 ║ 0.44 ║ 0.44 ║
║ 5 ║ Sarah ║ 500 ║ 0.37 ║ 0.81 ║
║ 6 ║ Sarah ║ 150 ║ 0.11 ║ 0.93 ║
║ 4 ║ Sarah ║ 100 ║ 0.07 ║ 1 ║
╚════╩═══════╩═════════╩════════════╩════════════════════╝
Его можно было бы рассчитать по-разному, используя специальные оконные функции.
Теперь предположим, что мы не можем использовать оконный SUM и переписать его:
SELECT c.Customer, c."User", c."Revenue"
,1.0 * Revenue / NULLIF(c3.s,0) AS percentage
,1.0 * c2.s / NULLIF(c3.s,0) AS running_percentage
FROM t c
CROSS APPLY
(SELECT SUM(Revenue) AS s
FROM t c2
WHERE c."User" = c2."User"
AND c2.Revenue >= c.Revenue) AS c2
CROSS APPLY
(SELECT SUM(Revenue) AS s
FROM t c2
WHERE c."User" = c2."User") AS c3
ORDER BY "User", Revenue DESC;
LiveDemo
Я использовал CROSS APPLY, потому что мне не нравятся коррелированные подзапросы в списке SELECT colums, а c3 используется дважды.
Все работает так, как должно. Но когда мы смотрим ближе, c2 и c3 очень похожи. Итак, почему бы не объединить их и использовать простое условное агрегирование:
SELECT c.Customer, c."User", c."Revenue"
,1.0 * Revenue / NULLIF(c2.sum_total,0) AS percentage
,1.0 * c2.sum_running / NULLIF(c2.sum_total,0) AS running_percentage
FROM t c
CROSS APPLY
(SELECT SUM(Revenue) AS sum_total,
SUM(CASE WHEN c2.Revenue >= c.Revenue THEN Revenue ELSE 0 END)
AS sum_running
FROM t c2
WHERE c."User" = c2."User") AS c2
ORDER BY "User", Revenue DESC;
К сожалению, это невозможно.
Несколько столбцов указаны в агрегированном выражении, содержащем внешнюю ссылку. Если агрегированное выражение содержит внешнюю ссылку, то эта внешняя ссылка должна быть единственным столбцом, на который ссылается выражение.
Конечно, я мог обойти его, обертывая другим подзапросом, но он становится немного "уродливым":
SELECT c.Customer, c."User", c."Revenue"
,1.0 * Revenue / NULLIF(c2.sum_total,0) AS percentage
,1.0 * c2.sum_running / NULLIF(c2.sum_total,0) AS running_percentage
FROM t c
CROSS APPLY
( SELECT SUM(Revenue) AS sum_total,
SUM(running_revenue) AS sum_running
FROM (SELECT Revenue,
CASE WHEN c2.Revenue >= c.Revenue THEN Revenue ELSE 0 END
AS running_revenue
FROM t c2
WHERE c."User" = c2."User") AS sub
) AS c2
ORDER BY "User", Revenue DESC
LiveDemo
Postgresql версия. Единственное отличие: LATERAL вместо CROSS APPLY.
SELECT c.Customer, c."User", c.Revenue
,1.0 * Revenue / NULLIF(c2.sum_total,0) AS percentage
,1.0 * c2.running_sum / NULLIF(c2.sum_total,0) AS running_percentage
FROM t c
,LATERAL (SELECT SUM(Revenue) AS sum_total,
SUM(CASE WHEN c2.Revenue >= c.Revenue THEN c2.Revenue ELSE 0 END)
AS running_sum
FROM t c2
WHERE c."User" = c2."User") c2
ORDER BY "User", Revenue DESC;
SqlFiddleDemo
Это работает очень хорошо.
SQLite/MySQL (поэтому я предпочитаю LATERAL/CROSS APPLY):
SELECT c.Customer, c."User", c.Revenue,
1.0 * Revenue / (SELECT SUM(Revenue)
FROM t c2
WHERE c."User" = c2."User") AS percentage,
1.0 * (SELECT SUM(CASE WHEN c2.Revenue >= c.Revenue THEN c2.Revenue ELSE 0 END)
FROM t c2
WHERE c."User" = c2."User") /
(SELECT SUM(c2.Revenue)
FROM t c2
WHERE c."User" = c2."User") AS running_percentage
FROM t c
ORDER BY "User", Revenue DESC;
SQLFiddleDemo-SQLite SQLFiddleDemo-MySQL
Я прочитал Агрегаты с внешней ссылкой:
Источник для ограничения находится в стандарте SQL-92, а SQL Server унаследовал его от кодовой базы Sybase. Проблема в том, что SQL Server должен выяснить, какой запрос будет вычислять агрегат.
Я не ищу ответы, которые только показывают, как обойти его.
Вопросы:
- Какая часть стандартного запрета или вмешивается в это?
- Почему у других RDBMS нет проблем с этой внешней зависимостью?
- Расширяют ли они
SQL Standard и SQL Server ведут себя так, как должно, или SQL Server не реализует его полностью (правильно?)?.
Я был бы очень благодарен за ссылки на:
-
ISO standard (92 или новее)
- Поддержка стандартов SQL Server
- официальная документация из любой СУБД, которая ее объясняет (
SQL Server/Postgresql/Oracle/...).
EDIT:
Я знаю, что SQL-92 не имеет понятия LATERAL. Но версия с подзапросами (например, в SQLite/MySQL) тоже не работает.
LiveDemo
ИЗМЕНИТЬ 2:
Чтобы упростить его, пусть проверяет только коррелированный подзапрос:
SELECT c.Customer, c."User", c.Revenue,
1.0*(SELECT SUM(CASE WHEN c2.Revenue >= c.Revenue THEN c2.Revenue ELSE 0 END)
FROM t c2
WHERE c."User" = c2."User")
/ (SELECT SUM(c2.Revenue)
FROM t c2
WHERE c."User" = c2."User") AS running_percentage
FROM t c
ORDER BY "User", Revenue DESC;
Версия выше отлично работает в MySQL/SQLite/Postgresql.
В SQL Server мы получаем ошибку. После того, как он подделал его подзапросом, чтобы "сгладить" его на один уровень, он работает:
SELECT c.Customer, c."User", c.Revenue,
1.0 * (
SELECT SUM(CASE WHEN r1 >= r2 THEN r1 ELSE 0 END)
FROM (SELECT c2.Revenue AS r1, c.Revenue r2
FROM t c2
WHERE c."User" = c2."User") AS S) /
(SELECT SUM(c2.Revenue)
FROM t c2
WHERE c."User" = c2."User") AS running_percentage
FROM t c
ORDER BY "User", Revenue DESC;
Вопрос этого вопроса в том, как SQL Standard регулирует его.
LiveDemo
Ответы
Ответ 1
Существует более простое решение:
SELECT c.Customer, c."User", c."Revenue",
1.0 * Revenue/ NULLIF(c2.sum_total, 0) AS percentage,
1.0 * c2.sum_running / NULLIF(c2.sum_total, 0) AS running_percentage
FROM t c CROSS APPLY
(SELECT SUM(c2.Revenue) AS sum_total,
SUM(CASE WHEN c2.Revenue >= x.Revenue THEN c2.Revenue ELSE 0 END)
as sum_running
FROM t c2 CROSS JOIN
(SELECT c.REVENUE) x
WHERE c."User" = c2."User"
) c2
ORDER BY "User", Revenue DESC;
Я не уверен, почему, или если это ограничение находится в стандарте SQL '92. У меня это было довольно хорошо запомнено примерно 20 лет назад, но я не помню этого конкретного ограничения.
Я должен отметить:
- Во время стандарта SQL 92 боковые соединения на самом деле не были на радаре. У Sybase определенно не было такой концепции.
- Другие базы данных имеют проблемы с внешними ссылками. В частности, они часто ограничивают область охвата на один уровень глубиной.
- Сам стандарт SQL имеет тенденцию к высокому политическому (то есть, основанному на поставщиках), а не обусловлен фактическими требованиями пользователей к базе данных. Ну, со временем он движется в правильном направлении.
Ответ 2
В стандарте SQL для LATERAL такого ограничения нет. CROSS APPLY является специфическим расширением от Microsoft (Oracle принял его позже для совместимости), и его ограничения, очевидно, не связаны со стандартом ISO/IEC SQL, поскольку функция MS предшествует стандарту.
LATERAL в стандартном SQL - это просто модификатор для объединений, позволяющий использовать боковые ссылки в дереве соединений. Количество столбцов, на которые можно сослаться, не ограничено.
Я не вижу причины для странного ограничения для начала. Может быть, это потому, что CROSS APPLY изначально предназначался для разрешения табличных функций, что позже было расширено, чтобы разрешить sub- SELECT s.
Руководство Postgres manual объясняет LATERAL так:
Ключевое слово LATERAL может предшествовать элементу sub- SELECT FROM. Это позволяет sub- SELECT для ссылки на столбцы элементов FROM, которые появляются перед ним в списке FROM. (Без LATERAL каждый sub- SELECT оценивается независимо друг от друга и поэтому не может давать перекрестные ссылки на любой другой элемент FROM.)
Версия вашего запроса в Postgres (без более элегантных оконных функций) может быть проще:
SELECT c.*
, round(revenue / c2.sum_total, 2) END AS percentage
, round(c2.running_sum / c2.sum_total, 2) END AS running_percentage
FROM t c, LATERAL (
SELECT NULLIF(SUM(revenue), 0)::numeric AS sum_total -- NULLIF, cast once
, SUM(revenue) FILTER (WHERE revenue >= c.revenue) AS running_sum
FROM t
WHERE "User" = c."User"
) c2
ORDER BY c."User", c.revenue DESC;
Postgres 9. 4+ имеет более элегантный агрегат FILTER для условных агрегатов.
NULLIF имеет смысл, я предлагаю лишь незначительное упрощение.
Примените sum_total к numeric один раз.
Округлить результат, чтобы соответствовать желаемому результату.
