SQL Server - agregação condicional com correlação
Fundo:
ocaso original foi muito simples Calcule o total em execução por usuário, da receita mais alta para a mais baixa:
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);
Inquerir:
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;
Resultado:
╔════╦═══════╦═════════╦════════════╦════════════════════╗
║ 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 ║
╚════╩═══════╩═════════╩════════════╩════════════════════╝
Pode ser calculado de maneira diferente usando funções específicas da janela.
Agora vamos assumir que não podemos usar janelasSUM e reescreva-o:
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;
Eu tenho usadoCROSS APPLY porque eu não gosto de subconsultas correlatas emSELECT lista de colunas ec3 é usado duas vezes.
Tudo funciona como deveria. Mas quando olhamos mais de pertoc2 ec3 são muito parecidos. Então, por que não combiná-los e usar agregação condicional simples:
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;
Infelizmente não é possível.
Várias colunas são especificadas em uma expressão agregada que contém uma referência externa. Se uma expressão que está sendo agregada contiver uma referência externa, essa referência externa deverá ser a única coluna referenciada na expressão.
É claro que eu poderia contorná-lo com outra subconsulta, mas isso se torna um pouco "feio":
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
Postgresql versão. A única diferença éLATERAL ao invés deCROSS 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;
Isso funciona muito bem.
SQLite/MySQL versão (é por isso que eu prefiroLATERAL/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
Eu liAgrega com uma referência externa:
A fonte da restrição está noSQL-92 padrão eSQL Server herdou doSybase codebase. O problema é que o SQL Server precisa descobrir qual consulta calculará o agregado.
Eu não procuro respostas quesó mostre como contorná-lo.
As perguntas são:
Qual parte do padrão não o permite ou interfere?Por que outros RDBMSes não têm problemas com esse tipo de dependência externa?Eles estendemSQL Standard eSQL Server comporta-se como deveria ouSQL Server não o implementa totalmente (corretamente?) ?.Ficaria muito grato por referências a:
ISO standard (92 ou mais recente)Suporte aos padrões do SQL Serverdocumentação oficial de qualquer RDBMS que o explique (SQL Server/Postgresql/Oracle/...)EDITAR:
Eu sei dissoSQL-92 não tem conceito deLATERAL. Mas a versão com subconsultas (como emSQLite/MySQL) também não funciona.
EDIT 2:
Para simplificar um pouco, vamos verificar apenas apenas a subconsulta correlacionada:
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;
A versão acima funciona bem emMySQL/SQLite/Postgresql.
NoSQL Server temos erro. Depois de envolvê-lo com subconsulta para "achatá-lo" para um nível, ele funciona:
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;
O ponto desta questão é como é queSQL standard regular isso.