Clever Geek Handbook

Clickhouse-不存在的窗口功能...

我开始使用BigQuery处理列式数据库。当我不得不“移动”到Clickhouse时，我对缺少完整的窗口功能感到惊讶。当然，有许多用于处理数组的函数，高阶函数和其他函数（其中一个函数runningDifferenceStartingWithFirstValue非常有价值）。想到1999年最长单词Donaudampfschifffahrtsgesellschaftskapitänswitwe的冠军便马上浮现在脑海。从德语翻译为“多瑙河船公司船长的遗ow”。



搜索“ Clickhouse中的窗口功能”不会返回有意义的结果。本文试图总结来自Internet的分散数据，ClickHouseMeetup的示例以及我的经验。



窗口函数-语法



让我提醒您有关窗口函数的语法以及得到的结果的类型。在示例中，我们将使用Standart SQL Google BigQuery方言。这是有关窗口函数的文档的链接（在文档中它们被称为分析函数-诸如分析函数之类的更准确的翻译声音）。而这里是函数本身的列表。



通用语法如下所示： 



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 }


让我们逐步进行：



  1. window函数应用于over_clause表达式中定义的记录集，
  2. 记录集由PARTITION BY子句定义。在这里您可以列出一个或多个字段，通过这些字段可以确定记录集。与GROUP BY类似。

    使用ORDER BY指定集合中记录的排序。
  3. 您还可以将一组预定义的记录限制为一个窗口。该窗口可以静态定义。例如，您可以将5条记录作为一个窗口，在当前记录和当前记录本身之前2条，之后2条。它看起来像这样：在2个开始和2个跟随之间行。

    用于指定动态定义的窗口的构造示例如下所示-无边界优先级和当前行之间的范围。此构造根据指定的排序顺序定义了从第一条记录到当前记录的窗口。


例如，考虑累计总和的计算（来自文档的示例）： 



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


结果： 



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


在Clickhouse中可以做什么



让我们尝试在ClickHouse中重复此示例。当然，ClickHouse具有runningAccumulate，arrayCumSum和groupArrayMovingSum函数。但是在第一种情况下，您需要确定子查询中的状态（更多详细信息），在第二种情况下，该函数返回一个数组，然后需要对其进行扩展。



我们将构建最通用的查询。该请求本身可能看起来像这样： 



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


让我们逐步进行：



  1. 首先，我们构造一个子查询，在其中进行所需的数据排序（ORDER BY类别，购买）。排序必须与窗口函数的PARTITION BY和ORDER BY表达式中的字段匹配。
  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. 最后一步是我们需要使用ARRAY JOIN将数组扩展为表。我们还需要将-sum聚合函数和-Array修饰符一起应用到ArrayMap函数返回的结果中（因此，该聚合函数看起来像sumArray）。


输出量



可以在ClickHouse中模拟窗口功能的操作。不太快也不太漂亮。简而言之，管道包括3个步骤：



  1. 排序查询。此步骤准备记录集。
  2. 分组为数组并执行数组操作。此步骤定义了我们的窗口函数的窗口。
  3. 使用聚合函数扩展回表。


More articles:


All Articles