ClickHouse中如何向UDF或参数化视图传递动态日期参数？

问题背景 #

需要创建可动态传递日期参数fdate的查询（UDF或参数化视图），实现传入单个/多个日期（或日期列）时，返回每个日期对应的库存结果集合并后的结果。现有尝试的UDF定义如下：

CREATE FUNCTION labbis_prd.inventory_query AS (fdate) ->  
  (SELECT a.INVEN,  
          a.R7,  
          stg_kartoteka.InventorinisNr,  
          SUM(a.kiek) AS Kiek  
   FROM  
     (SELECT INVEN,  
             R7,  
             cmpKIEKIS AS kiek  
      FROM labbis_prd.stg_kartoteka  
      UNION ALL  
      SELECT INVEN,  
             stg_turtdet.R7,  
            -stg_turtdet.KIEK_TURT AS kiek  
      FROM labbis_prd.stg_turtdet  
      INNER JOIN labbis_prd.stg_turtdok ON stg_turtdet.DOK_UNIK = stg_turtdok.UNIKAL  
      WHERE stg_turtdok.DOK_DAT > fdate) a  
   INNER JOIN labbis_prd.stg_kartoteka ON a.INVEN = stg_kartoteka.INVEN  
   WHERE stg_kartoteka.InventorinisNr = stg_kartoteka.R2  
   GROUP BY a.INVEN,  
            a.R7,  
            stg_kartoteka.InventorinisNr  
   HAVING SUM(a.kiek) > 0)

核心需求是让fdate支持接收日期值列表或日期列，批量返回对应日期的库存数据。

针对问题的解答 #

1. 能否向UDF传递动态列/值列表作为参数？ #

ClickHouse的标量UDF仅支持接收单个值参数，无法直接传入值列表或列作为参数。若强行传入数组类型，原查询中的WHERE stg_turtdok.DOK_DAT > fdate逻辑无法直接适配，额外处理会大幅增加复杂度且性能不佳，不推荐。

2. 能否定义接受此类动态输入的参数化视图或查询结构？ #

ClickHouse没有原生的参数化视图（类似PostgreSQL的CREATE VIEW ... WITH PARAMETERS），但可通过两种方式模拟：

• 物化视图+分区/过滤：无法动态接收参数，不符合需求。
• 模板查询（字符串拼接）：通过客户端拼接SQL传入日期列表，但存在SQL注入风险，不够优雅。

3. 推荐实现方案 #

最适配的方案是使用LATERAL JOIN + 日期列表，或封装为表函数，具体实现如下：

方案一：LATERAL JOIN批量处理日期列表

直接构造日期列表，通过LATERAL JOIN关联原查询逻辑，返回每个日期对应的结果：

WITH (SELECT ['2023-01-01', '2023-02-01', '2023-03-01']::Array(Date) AS date_list)
SELECT 
    dates.fdate,
    inv_result.*
FROM unnest(date_list) AS dates(fdate)
LATERAL JOIN (
    SELECT a.INVEN,  
            a.R7,  
            stg_kartoteka.InventorinisNr,  
            SUM(a.kiek) AS Kiek  
     FROM  
       (SELECT INVEN,  
               R7,  
               cmpKIEKIS AS kiek  
        FROM labbis_prd.stg_kartoteka  
        UNION ALL  
        SELECT INVEN,  
               stg_turtdet.R7,  
              -stg_turtdet.KIEK_TURT AS kiek  
        FROM labbis_prd.stg_turtdet  
        INNER JOIN labbis_prd.stg_turtdok ON stg_turtdet.DOK_UNIK = stg_turtdok.UNIKAL  
        WHERE stg_turtdok.DOK_DAT > dates.fdate) a  
     INNER JOIN labbis_prd.stg_kartoteka ON a.INVEN = stg_kartoteka.INVEN  
     WHERE stg_kartoteka.InventorinisNr = stg_kartoteka.R2  
     GROUP BY a.INVEN,  
              a.R7,  
              stg_kartoteka.InventorinisNr  
     HAVING SUM(a.kiek) > 0
) AS inv_result

若需从现有表的日期列获取参数，只需将unnest(date_list)替换为目标表的日期列查询即可。

方案二：创建支持数组参数的表函数

通过自定义表函数接收数组类型的日期参数，内部展开后执行查询：

CREATE FUNCTION labbis_prd.inventory_batch_query AS (fdate_array Array(Date)) ->
(
    WITH unnest(fdate_array) AS fdate
    SELECT 
        fdate,
        a.INVEN,  
        a.R7,  
        stg_kartoteka.InventorinisNr,  
        SUM(a.kiek) AS Kiek  
    FROM fdate
    LATERAL JOIN (
        SELECT INVEN,  
               R7,  
               cmpKIEKIS AS kiek  
        FROM labbis_prd.stg_kartoteka  
        UNION ALL  
        SELECT INVEN,  
               stg_turtdet.R7,  
              -stg_turtdet.KIEK_TURT AS kiek  
        FROM labbis_prd.stg_turtdet  
        INNER JOIN labbis_prd.stg_turtdok ON stg_turtdet.DOK_UNIK = stg_turtdok.UNIKAL  
        WHERE stg_turtdok.DOK_DAT > fdate
    ) a  
    INNER JOIN labbis_prd.stg_kartoteka ON a.INVEN = stg_kartoteka.INVEN  
    WHERE stg_kartoteka.InventorinisNr = stg_kartoteka.R2  
    GROUP BY fdate, a.INVEN, a.R7, stg_kartoteka.InventorinisNr  
    HAVING SUM(a.kiek) > 0
)

调用方式：

SELECT * FROM labbis_prd.inventory_batch_query(['2023-01-01', '2023-02-01']::Array(Date))

注意事项 #

• 若日期列表过大，建议对stg_turtdok.DOK_DAT建立索引，或使用分区表优化过滤速度。
• 表函数方式需确保ClickHouse版本为21.8及以上，以支持数组参数的表函数。

内容的提问来源于stack exchange，提问作者Karolis