企业官网建设流程全解析

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 "产品id，7,超低时延", `provider` VARCHAR(20) COMMENT "平面", `clientArea` VARCHAR(50) COMMENT "客户端省份", `domain` VARCHAR(100) COMMENT "域名", `clientOperator` VARCHAR(20) COMMENT "客户端运营商", `ipProtocol` VARCHAR(20) COMMENT "ip协议: 0：ipv4, 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并为一组，3，4并为一组,5，6并为一组进行分组，如何写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;