Oracle SQL练习之Sum,Count,Decode,Case…When的使用

博主在做报表时整理了一些涉及Oracle语法的SQL代码。代码中使用了COUNT、SUM、DECODE等函数,根据水情站点类型和监测指标统计站点数目、正常站点数目和异常站点数目,还查询了每个站点最新一条实时监控数据,方便大家参考与复习。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

最近在做报表,写了一些SQL,整理一下,其中涉及到Oracle很多语法的使用,方便大家参考与复习!

SELECT      RESULT.SITE_ID,RESULT.SITE_NAME,RESULT.STATION_TYPE,RESULT.STATION_NAME,RESULT.MONITOR_TIME,RESULT.PROJECT_ID,RESULT.PROJECT_NAME,RESULT.MONITOR_RESULT,RESULT.IF_GUARD_LEVEL,
        --根据水情站点类型和监测指标统计站点数目
        COUNT(1) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) TOTAL_COUNT,
        --根据水情站点类型和监测指标统计正常站点数目
        --SUM(CASE WHEN RESULT.IF_GUARD_LEVEL = 'YES' THEN 1 ELSE 0 END) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) NORMAL_COUNT,
        SUM(DECODE(RESULT.IF_GUARD_LEVEL,'YES',1,0)) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) NORMAL_COUNT,
        --根据水情站点类型和监测指标统计异常站点数目
        --SUM(CASE WHEN RESULT.IF_GUARD_LEVEL = 'YES' THEN 0 ELSE 1 END) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) UNNORMAL_COUNT
        SUM(DECODE(RESULT.IF_GUARD_LEVEL,'YES',0,1)) over (partition by RESULT.STATION_TYPE || RESULT.PROJECT_ID) UNNORMAL_COUNT
    FROM
    (
    --查询每个站点最新一条实时监控数据
        SELECT * FROM
         (
                SELECT 
                     R.SITE_ID,
                     B."NAME" AS SITE_NAME,
                     B.STATION_TYPE,
                     DICT."NAME" AS STATION_NAME,
                     TO_CHAR(R.MONITOR_TIME,'yyyy-MM-dd HH:mm') AS MONITOR_TIME,
                     R.PROJECT_ID,
                     D."NAME" AS PROJECT_NAME,
                     R.MONITOR_RESULT,
                     R.IF_GUARD_LEVEL,
                     --查询同一个站点同一个指标的最新一条记录
                     ROW_NUMBER() OVER(PARTITION BY R.SITE_ID,R.PROJECT_ID ORDER BY R.MONITOR_TIME DESC) RN
                FROM T_WATER_REALTIME_RESULT R
                LEFT JOIN V_WATER_SITE_BASE_INFO B ON B."ID" = R.SITE_ID
                LEFT JOIN PLATFORM_DICTIONARY_DATA DICT ON DICT."VALUE" = B.STATION_TYPE AND DICT.CODE = 'WATER_TYPE'
                LEFT JOIN PLATFORM_DICTIONARY_DATA D ON R.PROJECT_ID = D."VALUE" AND D.CODE = 'WATER_PROJECT_TYPE'
                WHERE 1=1
                 AND B.IF_NEW = 'YES'
                ORDER BY R.MONITOR_TIME DESC,R.SITE_ID
            ) T 
        WHERE T.RN = 1
    ) RESULT 
ORDER BY RESULT.STATION_TYPE,RESULT.MONITOR_TIME DESC

<think>首先,用户的问题是关于SQL优化,具体是优化多个CASE WHEN语句的写法。用户想知道替代方案和最佳实践。 从系统级指令中,我需要: - 使用$...$格式的行内数学表达式。 - 独立公式使用$$...$$格式并单独成段。 - 确保LaTeX语法正确。 - 使用中文回答。 - 生成相关问题。 - 在回答中引用的段落末尾自然地添加引用标识,如[^1]。 - 回答结构清晰,逐步解决问题,保证真实可靠。 用户提供了几个引用,我需要参考它们: - 引用[1]:讨论CASE表达式,有简单语法和搜索式语法,注意事项如数据类型一致、不要忘记end。 - 引用[2]:类似引用[1],展示简单和搜索式CASE表达式。 - 引用[3]:关于count distinct的优化,建议改写SQL。 - 引用[4]:SQL等价改写优化案例,提到避免对列进行运算,以使用索引。 用户的问题是优化多个CASE WHEN语句的写法。常见的优化方法包括: - 使用派生表或子查询来简化逻辑。 - 应用CASE WHEN的替代方案,如使用JOIN或条件聚合。 - 确保查询高效,避免不必要的计算。 结构化回答: 1. **理解问题**:解释多个CASE WHEN可能导致的性能问题,如代码冗余、可读性差、潜在性能瓶颈。 2. **替代方案**: - 使用派生表或公共表表达式(CTE)来预计算值。 - 使用JOIN操作来映射值。 - 应用条件聚合(如SUM(CASE WHEN ... THEN 1 ELSE 0 END))。 - 利用其他SQL函数如COALESCE、NULLIF等简化逻辑。 3. **最佳实践**: - 确保CASE WHEN分支数据类型一致。 - 避免在WHERE子句中使用CASE WHEN,除非必要。 - 考虑索引使用,避免对列进行函数操作。 4. **示例**:提供代码示例展示优化前后的对比。 5. **生成相关问题**:在回答末尾添加相关问题。 生成相关问题: - 1. 如何测试SQL优化后的性能? - 2. CASE WHEN在哪些场景下不可替代? - 3. SQL优化中还有哪些常见技巧? 确保回答真实可靠:基于SQL优化的一般知识,参考提供的引用。 最终回答用中文。</think>### SQL中多个CASE WHEN语句的优化:替代方案与最佳实践 在SQL查询中,多个CASE WHEN语句常用于条件逻辑处理,但当它们数量过多时,可能导致代码冗余、可读性下降和潜在性能问题(如全表扫描或索引失效)。优化这些语句的核心思路是简化逻辑、减少计算量,并利用SQL的等价改写来提高效率。以下我将逐步解释优化方法、替代方案和最佳实践,确保回答基于SQL优化的一般原则(如避免列运算以使用索引)和参考引用中的建议。 #### 1. **理解问题:为什么需要优化多个CASE WHEN?** - **性能风险**:多个CASE WHEN可能强制数据库进行逐行计算,增加CPU开销。如果CASE WHEN涉及列运算(如`CASE WHEN col > 10 THEN ...`),且未正确使用索引,可能导致全表扫描[^4]。 - **可维护性问题**:代码冗余度高,容易出错(如忘记`END`关键字或数据类型不一致)[^1][^2]。 - **优化目标**:通过减少CASE WHEN数量或改写逻辑,提升查询速度和可读性。 #### 2. **替代方案:简化多个CASE WHEN的写法** 以下是几种常见替代方案,根据引用[^1][^2][^3][^4]的建议,优先选择移植性高且不依赖特定数据库的方法。 - **方案1:使用派生表或公共表表达式(CTE)预计算值** 将多个CASE WHEN逻辑移到子查询中,避免主查询的重复计算。这尤其适用于分组聚合场景。 **示例优化前**: ```sql SELECT employee_id, CASE WHEN department = 'IT' THEN '技术部' WHEN department = 'HR' THEN '人力资源' ELSE '其他' END AS dept_name, CASE WHEN salary > 10000 THEN '高薪' WHEN salary BETWEEN 5000 AND 10000 THEN '中薪' ELSE '低薪' END AS salary_level FROM employees; ``` **示例优化后**(使用CTE): ```sql WITH precomputed AS ( SELECT employee_id, department, salary, CASE WHEN department = 'IT' THEN '技术部' WHEN department = 'HR' THEN '人力资源' ELSE '其他' END AS dept_name, CASE WHEN salary > 10000 THEN '高薪' WHEN salary BETWEEN 5000 AND 10000 THEN '中薪' ELSE '低薪' END AS salary_level FROM employees ) SELECT employee_id, dept_name, salary_level FROM precomputed; ``` **优点**:逻辑集中化,减少主查询复杂度;便于后续过滤或分组。参考引用[^3],这种派生表方法常用于优化`COUNT(DISTINCT)`等聚合操作。 - **方案2:使用JOIN操作映射值(适用于固定映射关系)** 如果CASE WHEN用于映射离散值(如部门代码转名称),可创建映射表并通过JOIN替代,避免CASE WHEN分支。 **示例优化前**: ```sql SELECT employee_id, CASE WHEN department_id = 1 THEN 'IT' WHEN department_id = 2 THEN 'HR' ELSE '其他' END AS dept_name FROM employees; ``` **示例优化后**(使用映射表): ```sql -- 先创建映射表(或使用现有维度表) CREATE TABLE dept_mapping ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) ); INSERT INTO dept_mapping VALUES (1, 'IT'), (2, 'HR'); -- 主查询通过JOIN替代CASE WHEN SELECT e.employee_id, COALESCE(m.dept_name, '其他') AS dept_name FROM employees e LEFT JOIN dept_mapping m ON e.department_id = m.dept_id; ``` **优点**:提升可读性和性能(JOIN可能利用索引);易于维护映射关系。参考引用[^1],这类似于CASE表达式的“搜索式语法”但更结构化。 - **方案3:应用条件聚合(适用于分组统计)** 对于多个CASE WHEN用于聚合(如计数或求和),可合并为单个聚合函数,减少分支数量。 **示例优化前**: ```sql SELECT department, COUNT(CASE WHEN status = 'active' THEN 1 ELSE NULL END) AS active_count, COUNT(CASE WHEN status = 'inactive' THEN 1 ELSE NULL END) AS inactive_count FROM employees GROUP BY department; ``` **示例优化后**(使用条件聚合简化): ```sql SELECT department, SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count, SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count FROM employees GROUP BY department; ``` **优点**:减少NULL处理,提升性能;代码更简洁。参考引用[^3],这种方法常用于优化`COUNT DISTINCT`场景。 - **方案4:利用其他SQL函数简化逻辑** 结合`COALESCE`、`NULLIF`或`FILTER`子句(PostgreSQL支持)减少CASE WHEN数量。 **示例**: ```sql -- 优化前:多个CASE WHEN处理空值 SELECT employee_id, CASE WHEN name IS NULL THEN '未知' ELSE name END, CASE WHEN salary IS NULL THEN 0 ELSE salary END FROM employees; -- 优化后:使用COALESCE SELECT employee_id, COALESCE(name, '未知') AS name, COALESCE(salary, 0) AS salary FROM employees; ``` **优点**:代码更简洁,减少分支逻辑。 #### 3. **最佳实践:确保高效和可靠** - **数据类型一致**:确保所有`THEN`和`ELSE`分支返回相同数据类型,避免隐式转换错误[^1][^2]。 - **避免列运算**:不要在CASE WHEN中对列直接运算(如`CASE WHEN salary * 1.1 > 10000 THEN ...`),否则索引可能失效。改写为: ```sql -- 错误示例(列运算) CASE WHEN trunc(create_time) = '2024-08-05' THEN ... -- 正确优化(使用范围避免函数) CASE WHEN create_time >= '2024-08-05' AND create_time < '2024-08-06' THEN ... ``` 参考引用[^4],这确保索引可用。 - **性能测试**:优化后使用`EXPLAIN`分析执行计划,确认是否减少全表扫描。 - **移植性优先**:CASE表达式是SQL标准,优先于数据库特定函数(如Oracle的`DECODE`或MySQL的`IF`),确保代码可移植[^1]。 - **完整语法**:始终包含`END`,并显式处理`ELSE`分支(即使为`NULL`),避免意外结果。 #### 4. **何时坚持使用CASE WHEN?** 尽管有替代方案,CASE WHEN在复杂条件逻辑(如嵌套条件或多列判断)中仍不可替代。优化重点是减少其使用频率而非完全消除。例如: ```sql -- 复杂场景:CASE WHEN更合适 SELECT CASE WHEN department = 'IT' AND experience > 5 THEN '资深技术' WHEN department = 'HR' AND experience <= 2 THEN '初级HR' ELSE '其他' END AS role FROM employees; ``` ### 性能验证建议 优化后,通过数据库工具(如SQL Server的Execution Plan或MySQL的EXPLAIN)检查查询计划,确认索引使用和扫描行数减少。实际测试中,优化可能提升性能10-50%,尤其在大型数据集上。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值