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:
- A função de janela é aplicada ao conjunto de registros definido na expressão over_clause,
- 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. - 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:
- 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.
- , , PARTITION BY. item .
purchases , summ . - — 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:
- —
, , arrayMap arrayFilter.. — ( — ) (alias) arrayMap, arrayFilter . - — . , , arrayReverse arraySlice.
- —
- 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:
- Consulta classificada. Esta etapa prepara o conjunto de registros.
- Agrupamento em matrizes e execução de operações de matriz. Esta etapa define a janela de nossa função de janela.
- Expandindo de volta para uma tabela usando funções agregadas.