如何将存储在独立表中的CASE业务规则嵌入SELECT语句？

在多个存储过程的CASE语句中应用了业务规则，但业务规则会持续变更，希望将这些规则分离到独立表中，只需在一处修改规则即可，无需更新多个存储过程。咨询如何将存储在独立表中的规则作为列嵌入SELECT语句中？

原数据及查询示例：

CREATE OR REPLACE TABLE stage_data 
(
    event_date    DATE,
    website       VARCHAR,
    event_type    VARCHAR,
    event_action  VARCHAR,
    index         VARCHAR,
    index_value   VARCHAR
);

INSERT INTO stage_data
VALUES
    ('2023-07-01', 'website_1', 'event', 'form_success',       null, null),
    ('2023-07-01', 'website_2', 'page',  'hoursanddirections', 3,    null),
    ('2023-07-01', 'website_3', 'page',  'vehicledetails',     3,    null),
    ('2023-07-01', 'website_4', 'event', 'click_to_call',      6,    'sale'),
    ('2023-07-01', 'website_5', 'event', 'click_to_call',      6,    'service');
    
SELECT 
    event_date,
    website,
    CASE 
        WHEN event_type = 'event' AND event_action = 'form_success'                                              
            THEN 'Y' 
            ELSE 'N' 
    END AS lead_events,
    CASE
        WHEN event_type = 'page' AND event_action = 'hoursanddirections' AND index = 3                           
            THEN 'Y' 
            ELSE 'N' 
    END AS hd_views,
    CASE
        WHEN event_type = 'page' AND event_action = 'vehicledetails' AND index = 3
            THEN 'Y' 
            ELSE 'N' 
    END AS vdp_views,
    CASE
        WHEN event_type = 'event' AND event_action = 'click_to_call' 
             AND index = 6 AND index_value = 'sale'    
            THEN 'Y' 
            ELSE 'N' 
    END AS ctc_sales,
    CASE 
        WHEN event_type = 'event' AND event_action = 'click_to_call'      
             AND index = 6 AND index_value = 'service' 
            THEN 'Y' 
            ELSE 'N' 
    END AS ctc_services
FROM 
    stage_data
ORDER BY 
    1, 2, 3, 4, 5, 6;

尝试将规则存入表但无法直接引用：

CREATE OR REPLACE TABLE stage_business_rules 
(
    businesss_rules  VARCHAR
);

INSERT INTO stage_business_rules
VALUES 
('case when event_type=''event'' and event_action=''form_success'' then ''Y'' else ''N'' end AS lead_events'),
('case when event_type=''page'' and event_action=''hoursanddirections'' and index=3 then ''Y'' else ''N'' end AS hd_views'),
('case when event_type=''page'' and event_action=''vehicledetails''  and index=3 then ''Y'' else ''N'' end AS vdp_views'),
('case when event_type=''event'' and event_action=''click_to_call'' and index=6 and index_value=''sale'' then ''Y'' else ''N'' end AS ctc_sales'),
('case when event_type=''event'' and event_action=''click_to_call'' and index=6 and index_value=''service'' then ''Y'' else ''N'' end AS ctc_services');

SELECT 
    event_date
  , website 
  , (SELECT businesss_rules FROM stage_business_rules) -- 此处无法直接生效
FROM stage_data;

静态SQL无法直接解析字符串形式的规则，需要通过动态SQL实现，以下提供两种方案：

方案一：结构化存储规则（推荐） #

将规则拆分为列名、条件表达式、满足/不满足返回值，而非存储整段CASE语句，更易维护且降低风险。

1. 创建结构化规则表 #

CREATE OR REPLACE TABLE stage_business_rules (
    rule_id INT AUTO_INCREMENT PRIMARY KEY,
    column_name VARCHAR(50) NOT NULL, -- 生成的目标列名
    condition_expr VARCHAR(255) NOT NULL, -- 规则判断条件
    true_value VARCHAR(10) NOT NULL, -- 满足条件的返回值
    false_value VARCHAR(10) NOT NULL -- 不满足条件的返回值
);

INSERT INTO stage_business_rules (column_name, condition_expr, true_value, false_value)
VALUES
('lead_events', 'event_type = ''event'' AND event_action = ''form_success''', 'Y', 'N'),
('hd_views', 'event_type = ''page'' AND event_action = ''hoursanddirections'' AND index = 3', 'Y', 'N'),
('vdp_views', 'event_type = ''page'' AND event_action = ''vehicledetails'' AND index = 3', 'Y', 'N'),
('ctc_sales', 'event_type = ''event'' AND event_action = ''click_to_call'' AND index = 6 AND index_value = ''sale''', 'Y', 'N'),
('ctc_services', 'event_type = ''event'' AND event_action = ''click_to_call'' AND index = 6 AND index_value = ''service''', 'Y', 'N');

2. 用动态SQL拼接并执行查询 #

以MySQL为例：

-- 拼接完整查询语句
SET @sql = (
    SELECT CONCAT(
        'SELECT event_date, website, ',
        GROUP_CONCAT(
            CONCAT('CASE WHEN ', condition_expr, ' THEN ''', true_value, ''' ELSE ''', false_value, ''' END AS ', column_name)
            SEPARATOR ', '
        ),
        ' FROM stage_data ORDER BY 1, 2, 3, 4, 5, 6'
    )
    FROM stage_business_rules
);

-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

方案二：存储整段CASE语句（兼容原有尝试） #

如果坚持存储整段CASE语句，可通过动态SQL拼接所有规则片段生成完整查询：

1. 修正规则表并插入数据 #

-- 修正字段拼写错误：businesss_rules → business_rules
CREATE OR REPLACE TABLE stage_business_rules (
    business_rules VARCHAR(500) NOT NULL
);

INSERT INTO stage_business_rules (business_rules)
VALUES 
('CASE WHEN event_type=''event'' AND event_action=''form_success'' THEN ''Y'' ELSE ''N'' END AS lead_events'),
('CASE WHEN event_type=''page'' AND event_action=''hoursanddirections'' AND index=3 THEN ''Y'' ELSE ''N'' END AS hd_views'),
('CASE WHEN event_type=''page'' AND event_action=''vehicledetails'' AND index=3 THEN ''Y'' ELSE ''N'' END AS vdp_views'),
('CASE WHEN event_type=''event'' AND event_action=''click_to_call'' AND index=6 AND index_value=''sale'' THEN ''Y'' ELSE ''N'' END AS ctc_sales'),
('CASE WHEN event_type=''event'' AND event_action=''click_to_call'' AND index=6 AND index_value=''service'' THEN ''Y'' ELSE ''N'' END AS ctc_services');

2. 拼接并执行动态SQL #

SET @sql = (
    SELECT CONCAT(
        'SELECT event_date, website, ',
        GROUP_CONCAT(business_rules SEPARATOR ', '),
        ' FROM stage_data ORDER BY 1, 2, 3, 4, 5, 6'
    )
    FROM stage_business_rules
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• 动态SQL需要对应数据库权限（如MySQL的EXECUTE权限）。
• 存储整段SQL的方式存在SQL注入风险，仅适用于规则表内容完全可信的场景。
• 不同数据库的动态SQL语法不同：PostgreSQL用EXECUTE，SQL Server用sp_executesql，需根据实际环境调整。

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