MySQL带多AND/OR条件的CASE语句分组iOS版本异常排查

问题根因 #

• 原CASE判断逻辑遗漏了14.4.x版本的匹配规则，导致这类版本直接落入ELSE分支输出原始版本号
• 没有添加14.5以上版本的归类规则，14.6、14.7等版本无法匹配到对应分组
• CTE的列定义和内部SELECT的别名不统一，容易引发逻辑混淆

修正后的完整SQL #

WITH os_split AS
(
 SELECT 
        CASE
            WHEN "os_version" ILIKE '11%' THEN 'iOS 11'
            WHEN "os_version" ILIKE '12%' THEN 'iOS 12'
            WHEN "os_version" ILIKE '13%' THEN 'iOS 13'
            -- 匹配14、14.0~14.4所有子版本
            WHEN "os_version" = '14' 
                OR "os_version" ILIKE '14.0%' 
                OR "os_version" ILIKE '14.1%'
                OR "os_version" ILIKE '14.2%'
                OR "os_version" ILIKE '14.3%'
                OR "os_version" ILIKE '14.4%' THEN 'iOS 14'
            WHEN "os_version" ILIKE '14.5%' THEN 'iOS 14.5'
            -- 匹配14.6及以上的14版本
            WHEN "os_version" ILIKE '14.%' THEN 'iOS 14.6+'
            -- 更高版本比如15+如果需要归类可在这里补充规则
            ELSE "os_version"
            END AS device_name,
        COUNT("os_version") as number_of_devices
 FROM temp_db.data_lake.adjust_csv_imports
 WHERE "platform" = 'mobile_app'
 AND "event_name" = 'onboarding_completed'
 AND "os_name" = 'ios'
 GROUP BY 1
 HAVING COUNT("os_version") > 0
)
SELECT 
        device_name, 
        number_of_devices,
        ROUND(number_of_devices::NUMERIC / (SELECT SUM(number_of_devices) FROM os_split), 2) AS Pct_To_Total
FROM os_split
-- 如果需要过滤掉15+等高版本可在这里加条件：WHERE device_name LIKE 'iOS%'
ORDER BY number_of_devices DESC, device_name DESC;

关键修改说明 #

• 补充了14.4版本的匹配规则，所有14.4.x版本都会统一归入iOS 14分组
• 新增14.x版本兜底判断，14.5以上的14版本都会归入iOS 14.6+分组
• 简化了占比计算逻辑，去掉了不必要的自连接排名逻辑，避免性能开销和逻辑错误
• 统一了列别名，和后续查询的字段引用保持一致
• 增加了数值类型转换，避免整数除法导致占比计算为0的问题
• 你提到的加括号包裹OR条件报错的问题，本方案中的OR逻辑无需额外加括号即可正常执行，若仍有报错可检查是否存在引号、符号拼写类的语法错误

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