español - sql server mysql postgresql

SQL Server-agregación condicional con correlación

Question

Fondo:

El caso original era muy simple. Calcule el total acumulado por usuario desde el ingreso más alto al más bajo:

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);

Consulta:

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

Salida:

╔════╦═══════╦═════════╦════════════╦════════════════════╗
║ 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 ║
╚════╩═══════╩═════════╩════════════╩════════════════════╝

Se podría calcular de forma diferente utilizando funciones específicas de ventana.

Ahora asumamos que no podemos usar SUM ventanas y reescribirlo:

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

He usado CROSS APPLY porque no me gustan las subconsultas correlacionadas en la lista de columnas SELECT y c3 se usa dos veces.

Todo funciona como debería. Pero cuando miramos más de cerca c2 y c3 son muy similares. Entonces, ¿por qué no combinarlos y usar agregación condicional simple?

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;

Lamentablemente no es posible.

Se especifican varias columnas en una expresión agregada que contiene una referencia externa. Si una expresión que se agrega contiene una referencia externa, entonces esa referencia externa debe ser la única columna a la que se hace referencia en la expresión.

Por supuesto, podría evitarlo envolviéndolo con otra subconsulta, pero se vuelve un poco "feo":

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

Versión Postgresql . La única diferencia es LATERAL lugar de 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

Funciona muy bien.

Versión SQLite / MySQL (es por eso que prefiero 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

He leído agregados con una referencia externa :

La fuente de la restricción se encuentra en el estándar SQL-92 , y SQL Server heredó de la base de código de Sybase . El problema es que SQL Server necesita averiguar qué consulta calculará el agregado.

No busco respuestas que solo muestren cómo sortearlas.

Las preguntas son:

1. ¿Qué parte del estándar lo niega o lo interfiere?
2. ¿Por qué otros RDBMS no tienen problemas con este tipo de dependencia externa?
3. ¿Extienden SQL Standard y SQL Server comporta como debería o SQL Server no lo implementa completamente (correctamente)?

Agradecería mucho las referencias a:

• ISO standard (92 o más reciente)
• Soporte de estándares de SQL Server
• Documentación oficial de cualquier RDBMS que lo explique ( SQL Server/Postgresql/Oracle/... ).

EDITAR:

Sé que SQL-92 no tiene concepto de LATERAL . Pero la versión con subconsultas (como en SQLite/MySQL ) tampoco funciona.

LiveDemo

EDIT 2:

Para simplificarlo un poco, verifiquemos solo la subconsulta correlacionada solamente:

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;

La versión anterior funciona bien en MySQL/SQLite/Postgresql .

En SQL Server tenemos error. Después de envolverlo con subconsulta para "aplanarlo" a un nivel, 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;

El punto de esta pregunta es cómo lo regula el SQL standard .

LiveDemo

Answer 1

Hay una solución más fácil:

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;

No estoy seguro de por qué o si esta limitación está en el estándar SQL ''92. Lo tenía bastante bien memorizado hace unos 20 años, pero no recuerdo esa limitación en particular.

Debería tener en cuenta:

• En el momento del estándar SQL 92, las uniones laterales no estaban realmente en el radar. Sybase definitivamente no tenía tal concepto.
• Otras bases de datos tienen problemas con referencias externas. En particular, a menudo limitan el alcance a un nivel de profundidad.
• El estándar SQL en sí tiende a ser altamente político (es decir, impulsado por el proveedor) en lugar de ser impulsado por los requisitos reales de los usuarios de la base de datos. Bueno, con el tiempo, se mueve en la dirección correcta.

Answer 2

No hay tal limitación en el estándar SQL para LATERAL . CROSS APPLY es una extensión específica del proveedor de Microsoft (Oracle la adoptó más adelante por compatibilidad) y sus limitaciones obviamente no se deben al estándar ANSI SQL, ya que la característica MS es anterior al estándar.

LATERAL acuerdo con ANSI SQL es básicamente un modificador para uniones para permitir referencias laterales en el árbol de unión. No hay límite en el número de columnas a las que se puede hacer referencia.

No vería una razón para la extraña restricción, para empezar. Tal vez se deba a que CROSS APPLY fue originalmente diseñado para permitir funciones con valores de tabla, que luego se extendió para permitir sub- SELECT s.

El manual de Postgres explica así LATERAL :

La palabra clave LATERAL puede preceder a un elemento sub- SELECT FROM . Esto permite que el sub- SELECT refiera a las columnas de los elementos FROM que aparecen antes en la lista FROM . (Sin LATERAL , cada sub- SELECT se evalúa de forma independiente y, por lo tanto, no puede hacer referencia cruzada a ningún otro elemento FROM ).

La versión de Postgres de su consulta (sin las funciones de ventana más elegantes) puede ser más simple:

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+ tiene el FILTER agregado más elegante para agregados condicionales.

• NULLIF fue redundante. revenue se definen como NOT NULL , se garantiza que los agregados encuentren 1 o más filas, y el sub- SELECT LATERAL se une en una sum_total , por lo que sum_total no puede ser NULL . Eso fue al revés, pensé en COALESCE . NULLIF tiene sentido, solo sugiero una pequeña simplificación.

• sum_total a numeric una vez.

• Redondear el resultado para que coincida con el resultado deseado.