doris sql 多个元素分为同一组

doris 表中有如下字段:create table IF NOT EXISTS cdn.edge_client_area_req_fivemin ( time DATETIME NOT NULL COMMENT 时间, metric VARCHAR(50) NOT NULL COMMENT 指标名称, cp VARCHAR(100) COMMENT 公司, productId INT COMMENT 产品id7,超低时延, provider VARCHAR(20) COMMENT 平面, clientArea VARCHAR(50) COMMENT 客户端省份, domain VARCHAR(100) COMMENT 域名, clientOperator VARCHAR(20) COMMENT 客户端运营商, ipProtocol VARCHAR(20) COMMENT ip协议: 0ipv4, 1:ipv6, stateCode INT COMMENT 状态码, orderId VARCHAR(100) COMMENT 订购号, sourceCode VARCHAR(100) COMMENT 来源编码01:边缘节点 03:HCDH, value DOUBLE COMMENT 请求数 )现在想根据productId得值有(1,2,3,4),现在想将1,2并为一组34并为一组,56并为一组进行分组如何写sql方案一CASE WHEN最清晰、最推荐SELECT CASE WHEN productId IN (1, 2) THEN group_1_2 WHEN productId IN (3, 4) THEN group_3_4 WHEN productId IN (5, 6) THEN group_5_6 ELSE other END AS product_group, SUM(value) AS total_requests FROM cdn.edge_client_area_req_fivemin WHERE productId IN (1, 2, 3, 4, 5, 6) GROUP BY CASE WHEN productId IN (1, 2) THEN group_1_2 WHEN productId IN (3, 4) THEN group_3_4 WHEN productId IN (5, 6) THEN group_5_6 ELSE other END;方案二IF嵌套写法更紧凑SELECT IF(productId IN (1, 2), group_1_2, IF(productId IN (3, 4), group_3_4, IF(productId IN (5, 6), group_5_6, other)) ) AS product_group, SUM(value) AS total_requests FROM cdn.edge_client_area_req_fivemin WHERE productId IN (1, 2, 3, 4, 5, 6) GROUP BY product_group;方案三结合时间 / 维度统计真实业务常用SELECT time, CASE WHEN productId IN (1, 2) THEN group_1_2 WHEN productId IN (3, 4) THEN group_3_4 WHEN productId IN (5, 6) THEN group_5_6 END AS product_group, clientArea, SUM(value) AS total_requests FROM cdn.edge_client_area_req_fivemin WHERE productId IN (1, 2, 3, 4, 5, 6) GROUP BY time, product_group, clientArea;方案四使用 CTE逻辑更清晰便于维护WITH grouped_data AS ( SELECT *, CASE WHEN productId IN (1, 2) THEN group_1_2 WHEN productId IN (3, 4) THEN group_3_4 WHEN productId IN (5, 6) THEN group_5_6 END AS product_group FROM cdn.edge_client_area_req_fivemin WHERE productId IN (1, 2, 3, 4, 5, 6) ) SELECT product_group, clientOperator, ipProtocol, SUM(value) AS total_requests FROM grouped_data GROUP BY product_group, clientOperator, ipProtocol;