Clickhouse - funções de janela que não existem ...

Comecei a trabalhar com bancos de dados colunares com o BigQuery. Quando eu tive que "mudar" para Clickhouse, fiquei desagradavelmente surpreso com a falta de funções de janela completas. Existem, é claro, muitas funções para trabalhar com matrizes, funções de ordem superior e outras funções (uma função runningDifferenceStartingWithFirstValue vale muito). O vencedor de 1999 para o título da palavra mais longa Donaudampfschifffahrtsgesellschaftskapitänswitwe vem imediatamente à mente. Que, traduzido do alemão, significa "a viúva do capitão da companhia marítima no Danúbio".



Pesquisar por “funções de janela em Clickhouse” não retorna resultados significativos. Este artigo é uma tentativa de resumir dados dispersos da Internet, exemplos com ClickHouseMeetup e minha própria experiência.



Funções de janela - sintaxe



Deixe-me lembrá-lo da sintaxe das funções de janela e do tipo de resultado que obtemos. Nos exemplos, usaremos o dialeto padrão do SQL do Google BigQuery. Aqui está um link para a documentação sobre funções de janela (elas são chamadas de funções analíticas na documentação - uma tradução mais precisa soa como funções analíticas). E aqui está a lista de funções em si.



A sintaxe geral é semelhante a esta:



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 }


Vamos dar um passo a passo:



  1. A função de janela é aplicada ao conjunto de registros definido na expressão over_clause,
  2. O conjunto de registros é definido pela cláusula PARTITION BY. Aqui você pode listar um ou mais campos pelos quais o conjunto de registros será determinado. Funciona de forma semelhante a GROUP BY.

    A classificação dos registros em um conjunto é especificada usando ORDER BY.
  3. Além disso, você pode restringir um conjunto predefinido de registros como uma janela. A janela pode ser definida estaticamente. Por exemplo, você pode tomar 5 registros como uma janela, 2 antes e 2 depois do registro atual e o próprio registro atual. Será parecido com este: LINHAS ENTRE 2 ANTES E 2 SEGUINTES.

    Um exemplo de construção para especificar uma janela definida dinamicamente se parece com isto - RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT LIN. Esta construção define uma janela do primeiro ao registro atual de acordo com a ordem de classificação especificada.


Como exemplo, considere o cálculo da soma cumulativa (exemplo da documentação):



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              |
+-------------------------------------------------------+


O que pode ser feito em Clickhouse



Vamos tentar repetir este exemplo no ClickHouse. Obviamente, ClickHouse tem as funções runningAccumulate , arrayCumSum e groupArrayMovingSum . Mas, no primeiro caso, você precisa determinar o estado em uma subconsulta ( mais detalhes ) e, no segundo caso, a função retorna um array, que precisa ser expandido.



Vamos construir a consulta mais geral. A própria solicitação pode ter a seguinte aparência:



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


Vamos dar um passo a passo:



  1. Primeiro, construímos uma subconsulta, dentro da qual ocorre a classificação de dados necessária (categoria ORDER BY, compras). A classificação deve corresponder aos campos nas expressões PARTITION BY e ORDER BY da função de janela.
  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. A etapa final é expandir os arrays em uma tabela usando ARRAY JOIN. Também precisamos aplicar a função de agregação sum com o modificador -Array (como resultado, a função de agregação se parece com sumArray) ao resultado retornado pela função ArrayMap.


Resultado



É possível emular o funcionamento das funções da janela no ClickHouse. Não muito rápido e não muito bonito. Resumidamente, o pipeline consiste em 3 etapas:



  1. Consulta classificada. Esta etapa prepara o conjunto de registros.
  2. Agrupamento em matrizes e execução de operações de matriz. Esta etapa define a janela de nossa função de janela.
  3. Expandindo de volta para uma tabela usando funções agregadas.



All Articles