Clever Geek Handbook

Clickhouse - funciones de ventana que no existen ...

Comencé a trabajar con bases de datos en columnas con BigQuery. Cuando tuve que "mudarme" a Clickhouse, me sorprendió desagradablemente la falta de funciones de ventana completas. Por supuesto, existen muchas funciones para trabajar con matrices, funciones de orden superior y otras funciones (una función que ejecutaDifferenceStartingWithFirstValue vale mucho). Inmediatamente me viene a la mente el ganador de 1999 por el título de la palabra más larga Donaudampfschifffahrtsgesellschaftskapitänswitwe. Que en la traducción del alemán significa "la viuda del capitán de la compañía naviera en el Danubio".



La búsqueda de "funciones de ventana en Clickhouse" no arroja resultados significativos. Este artículo es un intento de resumir datos dispersos de Internet, ejemplos con ClickHouseMeetup y mi propia experiencia.



Funciones de ventana: sintaxis



Permítanme recordarles la sintaxis de las funciones de ventana y el tipo de resultado que obtenemos. En los ejemplos, usaremos el dialecto Standart SQL Google BigQuery. Aquí hay un enlace a la documentación sobre funciones de ventana (en la documentación se les llama función analítica; una traducción más precisa suena como funciones analíticas). Y aquí está la lista de funciones en sí.



La sintaxis general se ve así: 



analytic_function_name ( [ argument_list ] ) OVER over_clause
over_clause:
  { named_window | ( [ window_specification ] ) }
window_specification:
  [ named_window ]
  [ PARTITION BY partition_expression [, ...] ]
  [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
  [ window_frame_clause ]
window_frame_clause:
  { rows_range } { frame_start | frame_between }
rows_range:
  { ROWS | RANGE }


Vayamos paso a paso:



  1. La función de ventana se aplica al conjunto de registros definido en la expresión over_clause,
  2. El juego de registros está definido por la cláusula PARTITION BY. Aquí puede enumerar uno o más campos mediante los cuales se determinará el juego de registros. Funciona de forma similar a GROUP BY.

    La clasificación de registros dentro de un conjunto se especifica mediante ORDER BY.
  3. Además, puede restringir un conjunto predefinido de registros como una ventana. La ventana se puede definir estáticamente. Por ejemplo, puede tomar 5 registros como ventana, 2 antes y 2 después del registro actual y el registro actual en sí. Se verá así: FILAS ENTRE 2 PRECEDENTES Y 2 SIGUIENTES.

    Un ejemplo de una construcción para especificar una ventana definida dinámicamente se ve así: RANGE BETWEEN UNBOUNDED PRECEDING Y CURRENT ROW. Esta construcción define una ventana desde el primer registro hasta el registro actual de acuerdo con el orden de clasificación especificado.


Como ejemplo, considere el cálculo de la suma acumulada (ejemplo de la documentación): 



SELECT item, purchases, category, SUM(purchases)
  OVER (
    PARTITION BY category
    ORDER BY purchases
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_purchases
FROM Produce


Resultado: 



+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| orange    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+


Que se puede hacer en Clickhouse



Intentemos repetir este ejemplo en ClickHouse. Por supuesto, ClickHouse tiene las funciones runningAccumulate , arrayCumSum y groupArrayMovingSum . Pero en el primer caso, debe determinar el estado en una subconsulta ( más detalles ), y en el segundo caso, la función devuelve una matriz, que luego debe expandirse.



Construiremos la consulta más general. La solicitud en sí podría verse así: 



SELECT
   items,
   summ as purchases,
   category,
   sumArray(cum_summ) as total_purchases
FROM (SELECT
         category,
         groupArray(item) AS items,
         groupArray(purchases) AS summ,
         arrayMap(x -> arraySlice(summ, 1, x), arrayEnumerate(summ)) AS cum_summ
     FROM (SELECT
               item,
               purchases,
               category
           FROM produce
           ORDER BY category, purchases)
     GROUP BY category)
   ARRAY JOIN items, summ, cum_summ
GROUP BY category, items, summ
ORDER BY category, purchases


Vayamos paso a paso:



  1. Primero, construimos una subconsulta, dentro de la cual se lleva a cabo la clasificación de datos requerida (ORDER BY categoría, compras). La clasificación debe coincidir con los campos de las expresiones PARTITION BY y ORDER BY de la función de ventana.
  2. , , PARTITION BY. item .

    purchases , summ .
  3. — ArrayMap. , func arr.

    arr — [1, 2, …, length(summ)], arrayEnumerate.

    func — arraySlice(summ, 1, x), x — arr, . summ x. , cum_sum , , .



    ArrayMap arrayEnumerate , , . ( 3), ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING. 



    arrayMap(x -> arraySlice(summ, if(x-1 > 0, x-1, 1), if(x-1 > 0, 3, 2)), arrayEnumerate(summ))


    , . 2 ClickHouse:



    • — [edited] — . [/edited]. , , arrayMap arrayFilter. . — ( — ) (alias) arrayMap, arrayFilter .
    • — . , , arrayReverse arraySlice.


  4. El paso final es expandir las matrices en una tabla usando ARRAY JOIN. También necesitamos aplicar la función suma agregada con el modificador -Array (como resultado, la función agregada se ve como sumArray) al resultado devuelto por la función ArrayMap.


Salida



Es posible emular el funcionamiento de las funciones de ventana en ClickHouse. No muy rápido ni muy bonito. Brevemente, la canalización consta de 3 pasos:



  1. Consulta ordenada. Este paso prepara el juego de registros.
  2. Agrupación en matrices y realización de operaciones de matriz. Este paso define la ventana de nuestra función de ventana.
  3. Expansión de nuevo a una tabla usando funciones agregadas.


More articles:


All Articles