ClickHouse参数化视图中如何用变量存储sentiment？

ClickHouse本身不支持原生的参数化视图（即带动态参数的视图），但可以通过以下几种方案实现你需要的效果，替代MySQL中存储过程的作用：

1. 使用会话变量 #

先设置会话级别的变量，再在查询中引用该变量，实现动态过滤和逻辑控制：

-- 设置会话变量
SET @target_sentiment = 'positive';

-- 执行查询
SELECT
    CampaignName,
    CASE WHEN @target_sentiment = sentiment THEN sentiment ELSE NULL END AS sentiment_match,
    COUNT(CASE WHEN HOUR(StartTime) = 23 THEN sentiment ELSE NULL END) AS `23:00`
FROM call_details
WHERE sentiment = @target_sentiment
GROUP BY CampaignName, 
         -- 动态控制是否按sentiment分组
         CASE WHEN @target_sentiment IS NOT NULL THEN sentiment END
ORDER BY CampaignName, sentiment ASC;

注意：会话变量仅在当前连接生效，断开连接后失效。

2. 使用存储过程（ClickHouse 21.3+支持） #

创建带参数的存储过程，封装动态逻辑，支持灵活控制分组条件：

-- 创建存储过程
CREATE OR REPLACE PROCEDURE get_sentiment_report(sentiment_param String, include_sentiment_group Bool)
BEGIN
    DECLARE sql_query String;
    -- 拼接动态SQL，控制是否包含sentiment字段和分组
    SET sql_query = concat(
        'SELECT CampaignName, ',
        CASE WHEN include_sentiment_group THEN 'sentiment, ' ELSE '' END,
        'CASE WHEN ''', sentiment_param, ''' = sentiment THEN sentiment ELSE NULL END AS sentiment_match, ',
        'COUNT(CASE WHEN HOUR(StartTime) = 23 THEN sentiment ELSE NULL END) AS `23:00` ',
        'FROM call_details ',
        'WHERE sentiment = ''', sentiment_param, ''' ',
        'GROUP BY CampaignName ',
        CASE WHEN include_sentiment_group THEN ', sentiment ' ELSE '' END,
        'ORDER BY CampaignName ',
        CASE WHEN include_sentiment_group THEN ', sentiment ' ELSE '' END,
        'ASC'
    );
    -- 执行动态SQL
    EXECUTE IMMEDIATE sql_query;
END;

调用存储过程：

-- 需要按sentiment分组
CALL get_sentiment_report('positive', 1);
-- 不需要按sentiment分组
CALL get_sentiment_report('positive', 0);

3. 使用自定义表函数 #

创建返回表的自定义函数，直接传入参数调用：

-- 创建表函数
CREATE FUNCTION sentiment_view AS (sentiment_param String) ->
    SELECT
        CampaignName,
        CASE WHEN sentiment_param = sentiment THEN sentiment ELSE NULL END AS sentiment_match,
        COUNT(CASE WHEN HOUR(StartTime) = 23 THEN sentiment ELSE NULL END) AS `23:00`
    FROM call_details
    WHERE sentiment = sentiment_param
    GROUP BY CampaignName, sentiment
    ORDER BY CampaignName, sentiment ASC;

调用函数：

SELECT * FROM sentiment_view('positive');

4. 应用层参数化查询 #

如果是在应用程序中调用，直接使用参数绑定机制传递sentiment变量，避免SQL注入：

SELECT
    CampaignName,
    CASE WHEN ? = sentiment THEN sentiment ELSE NULL END AS sentiment_match,
    COUNT(CASE WHEN HOUR(StartTime) = 23 THEN sentiment ELSE NULL END) AS `23:00`
FROM call_details
WHERE sentiment = ?
GROUP BY CampaignName, sentiment
ORDER BY CampaignName, sentiment ASC;

在应用代码中通过ClickHouse客户端的参数绑定功能，将具体的sentiment值传入占位符?。

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