GRUPO Eficiente por una expresión CASE en Amazon Redshift / PostgreSQL

En el procesamiento analítico a menudo existe la necesidad de queuepsar grupos de datos "no importantes" en una sola fila en la tabla resultante. Una forma de hacerlo es agrupar mediante una expresión CASE donde los grupos no importantes se fusionan en una sola fila a través de la expresión CASE que devuelve un único valor, por ejemplo, NULL para los grupos. Esta pregunta se trata de forms eficientes de realizar esta agrupación en Amazon Redshift, que se basa en ParAccel: cerca de PosgreSQL 8.0 en términos de funcionalidad.

Como ejemplo, considere un GROUP BY en el type y url en una tabla donde cada fila es una sola visita de URL. El objective es realizar la agregación de modo que se emita una fila para cada par (tipo, url) donde el recuento de visitas URL excede un cierto umbral y se emita una fila (tipo, NULO) para todos los pares (tipo, URL) donde la visita el recuento está por debajo de ese umbral. El rest de las columnas en la tabla de resultados tendría SUMA / COUNT agregados basados ​​en esta agrupación.

Por ejemplo, los siguientes datos

 +------+----------------------+-----------------------+ | type | url | < 50+ other columns > | +------+----------------------+-----------------------+ | A | http://popular.com | | | A | http://popular.com | | | A | < 9997 more times> | | | A | http://popular.com | | | A | http://small-one.com | | | B | http://tiny.com | | | B | http://tiny-too.com | | 

debe producir la siguiente tabla de resultados con un umbral de 10,000

 +------+------------------------------------+--------------------------+ | type | url | visit_count | < SUM/COUNT aggregates > | +------+------------------------------------+--------------------------+ | A | http://popular.com | 10000 | | | A | | 1 | | | B | | 2 | | 

Resumen:

Amazon Redshift tiene ciertas limitaciones de correlación de subconsulta que uno necesita para ponerse de puntillas. La respuesta de Gordon Linoff a continuación (la respuesta aceptada) muestra cómo realizar un GRUPO POR una expresión CASE utilizando doble agregación y replicando la expresión tanto en la columna de resultados como en la cláusula GROUP BY externa.

 with temp_counts as (SELECT type, url, COUNT(*) as cnt FROM t GROUP BY type, url) select type, (case when cnt >= 10000 then url end) as url, sum(cnt) as cnt from temp_counts group by type, (case when cnt >= 10000 then url end) 

Pruebas adicionales indicaron que la doble agregación se puede "desenrollar" en UNION TODAS las consultas independientes que involucran cada expresión CASE independiente. En este caso particular en un set de datos de muestra con aproximadamente 200M filas, este enfoque consistentemente realizó aproximadamente 30% más rápido. Sin embargo, el resultado es un esquema y datos específicos.

 with temp_counts as (SELECT type, url, COUNT(*) as cnt FROM t GROUP BY type, url) select * from temp_counts WHERE cnt >= 10000 UNION ALL SELECT type, NULL as url, SUM(cnt) as cnt from temp_counts WHERE cnt < 10000 GROUP BY type 

Esto sugiere dos patrones generales para implementar y optimizar la agrupación y el resumen disjuntos arbitrarios en Amazon Redshift. Si el performance es importante para ti, compara ambos.

Lo harías con dos agregaciones:

 select type, (case when cnt > XXX then url end) as url, sum(cnt) as visit_cnt from (select type, url, count(*) as cnt from t group by type, url ) t group by type, (case when cnt > XXX then url end) order by type, sum(cnt) desc; 
  1. Primero, agrupa en type, url .
  2. Luego, agrupa una segunda vez en type, case when visit_count < 10000 then NULL else url .

He utilizado la syntax de SQL Server, espero que también funcione para Postgres.