sql - subtotales - ¿Cuál es la diferencia entre los operadores cube, rollup y groupBy?

with rollup mysql

Question

La pregunta está más o menos en el título. No puedo encontrar ninguna documentación detallada sobre las diferencias.

Noto una diferencia porque al intercambiar cubos y grupos por llamadas a funciones, obtengo resultados diferentes. Noté que para el resultado usando ''cubo'', obtuve muchos valores nulos en las expresiones que solía agrupar.

Answer 1

No se pretende que funcionen de la misma manera. groupBy es simplemente un equivalente de la cláusula GROUP BY en SQL estándar. En otras palabras

table.groupBy($"foo", $"bar")

es equivalente a:

SELECT foo, bar, [agg-expressions] FROM table GROUP BY foo, bar

cube es equivalente a la extensión CUBE a GROUP BY . Toma una lista de columnas y aplica expresiones agregadas a todas las combinaciones posibles de las columnas de agrupamiento. Digamos que tienes datos como este:

val df = Seq(("foo", 1L), ("foo", 2L), ("bar", 2L), ("bar", 2L)).toDF("x", "y")

df.show
// +---+---+
// | x| y|
// +---+---+
// |foo| 1|
// |foo| 2|
// |bar| 2|
// |bar| 2|
// +---+---+

y calcula el cube(x, y) con el recuento como una agregación:

df.cube($"x", $"y").count.show
// +----+----+-----+
// | x| y|count|
// +----+----+-----+
// |null| 1| 1| <- count of records where y = 1
// |null| 2| 3| <- count of records where y = 2
// | foo|null| 2| <- count of records where x = foo
// | bar| 2| 2| <- count of records where x = bar AND y = 2
// | foo| 1| 1| <- count of records where x = foo AND y = 1
// | foo| 2| 1| <- count of records where x = foo AND y = 2
// |null|null| 4| <- total count of records
// | bar|null| 2| <- count of records where x = bar
// +----+----+-----+

Una función similar a cube es rollup que computa subtotales jerárquicos de izquierda a derecha:

df.rollup($"x", $"y").count.show
// +----+----+-----+
// | x| y|count|
// +----+----+-----+
// | foo|null| 2| <- count where x is fixed to foo
// | bar| 2| 2| <- count where x is fixed to bar and y is fixed to 2
// | foo| 1| 1| ...
// | foo| 2| 1| ...
// |null|null| 4| <- count where no column is fixed
// | bar|null| 2| <- count where x is fixed to bar
// +----+----+-----+

Solo para comparar, veamos el resultado del grupo simple:

df.groupBy($"x", $"y").count.show
// +---+---+-----+
// | x| y|count|
// +---+---+-----+
// |foo| 1| 1| <- this is identical to x = foo AND y = 1 in CUBE or ROLLUP
// |foo| 2| 1| <- this is identical to x = foo AND y = 2 in CUBE or ROLLUP
// |bar| 2| 2| <- this is identical to x = bar AND y = 2 in CUBE or ROLLUP
// +---+---+-----+

Para resumir:

• Al usar plain GROUP BY cada fila se incluye una sola vez en su resumen correspondiente.

• Con GROUP BY CUBE(..) cada fila se incluye en el resumen de cada combinación de niveles que representa, comodines incluidos. Lógicamente, lo que se muestra arriba es equivalente a algo así (suponiendo que pudiéramos usar marcadores de posición NULL ):

  SELECT NULL, NULL, COUNT(*) FROM table
  UNION ALL
  SELECT x, NULL, COUNT(*) FROM table GROUP BY x
  UNION ALL
  SELECT NULL, y, COUNT(*) FROM table GROUP BY y
  UNION ALL
  SELECT x, y, COUNT(*) FROM table GROUP BY x, y

• Con GROUP BY ROLLUP(...) es similar a CUBE pero funciona jerárquicamente al rellenar columnas de izquierda a derecha.

  SELECT NULL, NULL, COUNT(*) FROM table
  UNION ALL
  SELECT x, NULL, COUNT(*) FROM table GROUP BY x
  UNION ALL
  SELECT x, y, COUNT(*) FROM table GROUP BY x, y

ROLLUP y CUBE provienen de extensiones de data warehousing, por lo que si desea obtener una mejor comprensión de cómo funciona esto, también puede consultar la documentación de su RDMBS favorito. Por ejemplo, PostgreSQL presentó ambos en 9.5 y estos están relativamente bien documentados .