累计总和和窗口内的计算

-- 返回每个员工的下一个和上一个薪资SELECTemployee_id,salary,LEAD(salary,1)OVER(ORDERBYsalary DESC)ASnext_salary,LAG(salary,1)OVER(ORDERBYsalary DESC)ASprevious_salaryFROMemployees;

-- 刷新物化视图BEGINDBMS_MVIEW.REFRESH('emp_mv');END;/

-- 返回所有名字以“A”开头的员工SELECTfirst_nameFROMemployeesWHEREREGEXP_LIKE(first_name,'^A.*');

示例:

-- 返回员工薪资的指数和对数SELECTemployee_id,salary,EXP(salary)ASexp_salary,LOG(salary)ASlog_salaryFROMemployees;

解释:

• EXP(salary)：计算 salary的指数。

  示例:

  -- 将多个 XML 元素聚合成一个 XML 文档SELECTXMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT("employee",XMLFOREST(employee_id AS"id",first_name AS"name")))ASCLOB)FROMemployees;

  解释:

  • XMLAGG()聚合 XML 元素为一个 XML 文档。基础查询选择没有上级经理的员工作为递归的起点。CONNECT BY子句和 START WITH子句在处理树形数据结构时尤其有用，支持递归查询和数据的层次展示。

  • 字符串函数：用于字符串的创建、

  总结

  本文详细探讨了 Oracle 数据库中各种高级函数和功能，这些工具和技术在复杂的数据操作和分析中扮演着关键角色。

  示例:

  -- 从 JSON 数据中提取单个值SELECTJSON_VALUE('{"employee": {"id": 1, "name": "John"}}','$.employee.name')ASemployee_nameFROMdual;

  解释:

  • JSON_VALUE()从 JSON 数据中提取指定路径的值。

  3.2 INTERSECT

  功能: 返回两个查询的结果集的交集。这些功能扩展了基本 SQL 查询的能力，包括：

  • 分析函数和 窗口函数：用于执行复杂的数据分析和分区计算，如排名、

    示例:

    -- 使用 MODEL 进行复杂数据处理SELECT*FROM(SELECTdepartment_id,salaryFROMemployees)MODELPARTITIONBY(department_id)DIMENSION BY(ROWNUM ASrnum)MEASURES (salary)RULES(salary[rnum]=salary[rnum-1]*1.1);

    解释:

    • MODEL子句允许在查询中进行复杂的数学和逻辑操作，如将薪资增加 10%。

      9.3.1 创建数据库链接

      示例:

      -- 创建数据库链接CREATEDATABASELINK my_linkCONNECTTOremote_user IDENTIFIED BY'password'USING'remote_db';

      解释:

      • 创建数据库链接 my_link，允许在本地数据库中访问远程数据库 remote_db中的数据。

        示例:

        -- 按薪资降序为每个员工分配一个唯一的行号SELECTemployee_id,salary,ROW_NUMBER()OVER(ORDERBYsalary DESC)ASrow_numFROMemployees;

        解释:

        • ROW_NUMBER() OVER (ORDER BY salary DESC)：按照薪资从高到低排序，并为每行分配一个唯一的序号，无论薪资是否相同。

          示例:

          -- 去除员工名字两端的空格SELECTTRIM(first_name)AStrimmed_nameFROMemployees;

          解释:

          • TRIM(first_name)：去除 first_name字符串两端的空格。通过深入掌握这些工具，用户可以更加高效地进行数据查询和分析，优化数据库操作，提升工作效率。以下是对主要功能的总结：

            1. 分析函数：提供了强大的数据分析能力，如 RANK(), DENSE_RANK(), NTILE()和 ROW_NUMBER()，用于计算排名、

            2. 字符串函数、

               6.1 CONCAT()

               功能: 连接两个字符串。

               示例:

               -- 按薪资降序对员工进行排名SELECTemployee_id,salary,RANK()OVER(ORDERBYsalary DESC)ASsalary_rankFROMemployees;

               解释:

               • RANK() OVER (ORDER BY salary DESC)：按照薪资从高到低排序，并为每个员工分配排名。处理和操作，涵盖 CONCAT(), SUBSTR(), INSTR(), LENGTH(), UPPER(), LOWER(), 和 TRIM()等函数，帮助处理和格式化文本数据。
               • UNION ALL：合并递归的结果。

               9.1.2 XMLELEMENT()

               功能: 创建 XML 元素。

               示例:

               -- 连接员工的名字和姓氏SELECTCONCAT(first_name,' ',last_name)ASfull_nameFROMemployees;

               解释:

               • CONCAT(first_name, ' ', last_name)：将 first_name和 last_name连接成一个完整的名字。数学函数

                 数学函数用于对数值进行数学运算。

               2.3 MIN() 和 MAX()

               功能: 计算分区内的最小值和最大值。

          7.6 ROUND() 和 TRUNC()

          功能: 对日期进行四舍五入和截断操作。

        8.3 MOD()

        功能: 返回两个数值相除的余数。

      7.5 LAST_DAY()

      功能: 返回指定日期所在月份的最后一天的日期。

    七、

    示例:

    -- 返回员工薪资的绝对值SELECTemployee_id,salary,ABS(salary)ASabs_salaryFROMemployees;

    解释:

    • ABS(salary)：计算 salary的绝对值。

    9.2.2 JSON_QUERY()

    功能: 提取 JSON 数据中的嵌套对象或数组。

  • TRUNC(hire_date, 'MONTH')：将 hire_date截断到所在月份的第一天。日期时间和数值数据，支持各种数据格式化和计算需求。

  9.3.2 跨数据库查询

  示例:

  -- 使用数据库链接查询远程数据库SELECT*FROMemployees@my_link;

  解释:

  • 使用 @my_link语法访问远程数据库中的表。

    示例:

    -- 返回员工名字的长度SELECTfirst_name,LENGTH(first_name)ASname_lengthFROMemployees;

    解释:

    • LENGTH(first_name)：计算 first_name字符串的长度。
    • LOWER(first_name)：将名字转换为小写字母。

    • 正则表达式函数：提供了强大的模式匹配和替换功能，包括 REGEXP_LIKE(), REGEXP_INSTR(), REGEXP_SUBSTR(), 和 REGEXP_REPLACE()，用于处理复杂的文本模式匹配和替换操作。

      示例:

      -- 返回员工薪资除以1000的余数SELECTemployee_id,salary,MOD(salary,1000)ASmod_salaryFROMemployees;

      解释:

      • MOD(salary, 1000)：计算 salary除以 1000 的余数。

        示例:

        -- 按薪资降序对员工进行紧密排名SELECTemployee_id,salary,DENSE_RANK()OVER(ORDERBYsalary DESC)ASsalary_rankFROMemployees;

        解释:

        • DENSE_RANK() OVER (ORDER BY salary DESC)：类似于 RANK，但排名之间不会有间隔。

          2.1 SUM()

          功能: 计算分区内所有行的总和。

          示例:

          -- 对员工薪资进行四舍五入和截断操作SELECTemployee_id,salary,ROUND(salary,0)ASrounded_salary,TRUNC(salary,0)AStruncated_salaryFROMemployees;

          解释:

          • ROUND(salary, 0)：将 salary四舍五入到最近的整数。
          • CURRENT_DATE：返回会话的当前日期和时间。

            示例:

            -- 将 XML 数据映射到表中SELECT*FROMXMLTABLE('/employees/employee'PASSING XMLTYPE('<employees><employee><id>1</id><name>John</name></employee></employees>')COLUMNSid NUMBER PATH 'id',name VARCHAR2(50)PATH 'name');

            解释:

            • XMLTABLE()将 XML 数据转换为关系表格式，方便进行 SQL 查询。

            9.5.2 MODEL

            功能: 在 SQL 查询中使用模型进行复杂的数据处理。

          6.5 UPPER() 和 LOWER()

          功能: 将字符串转换为大写或小写。

        • 正则表达式函数：提供强大的文本模式匹配和替换功能，适用于复杂的文本处理。

          8.1 ABS()

          功能: 返回数值的绝对值。

        8.4 POWER() 和 SQRT()

        功能: POWER() 返回数值的指定次幂，SQRT() 返回数值的平方根。

        Oracle 高阶函数与高级功能详解

        Oracle 数据库提供了丰富的高级函数和功能，以应对复杂的数据处理和分析需求。

        7.1 SYSDATE 和 CURRENT_DATE

        功能: 返回当前的日期和时间。

      • 数学函数：用于执行各种数学运算，包括 ABS(), CEIL(), FLOOR(), MOD(), POWER(), SQRT(), EXP(), 和 LOG()，支持对数值进行精确计算和处理。

      5.3 REGEXP_SUBSTR
      功能: 返回正则表达式模式匹配的子串。

    9.3 数据库链接（Database Link）

    功能: 用于跨数据库查询。

  三、

  示例:

  -- 返回员工入职日期增加6个月后的日期SELECThire_date,ADD_MONTHS(hire_date,6)ASnew_dateFROMemployees;

  解释:

  • ADD_MONTHS(hire_date, 6)：将 hire_date日期增加 6 个月。字符串函数

  字符串函数用于对字符串进行各种操作。以及 数学函数：处理和操作字符串、

  示例:

  -- 将员工按薪资降序分成 4 组，每组分配一个编号SELECTemployee_id,salary,NTILE(4)OVER(ORDERBYsalary DESC)ASquartileFROMemployees;

  解释:

  • NTILE(4) OVER (ORDER BY salary DESC)：将结果集按照薪资从高到低分成 4 组，每组编号为 1 到 4。

  8.5 EXP() 和 LOG()

  功能: EXP() 返回数值的指数，LOG() 返回数值的对数。

五、

示例:

-- 返回当前的系统日期和当前会话的日期SELECTSYSDATE ASsystem_date,CURRENT_DATEASsession_dateFROMdual;

解释:

• SYSDATE：返回系统的当前日期和时间。

3.3 MINUS

功能: 返回第一个查询的结果集中不在第二个查询结果集中的部分。比较多个查询的结果集。

3.1 UNION

功能: 合并两个查询的结果集，并去除重复行。

示例:

-- 将员工名字转换为大写和小写SELECTfirst_name,UPPER(first_name)ASupper_name,LOWER(first_name)ASlower_nameFROMemployees;

解释:

• UPPER(first_name)：将名字转换为大写字母。
• MAX(salary) OVER (PARTITION BY department_id)：按部门计算最高薪资。

  9.1.1 XMLTABLE()

  功能: 将 XML 数据映射到关系表中。

  示例:

  -- 截取员工名字的前两个字符SELECTSUBSTR(first_name,1,2)ASname_prefixFROMemployees;

  解释:

  • SUBSTR(first_name, 1, 2)：从 first_name字符串的第一个字符开始，截取两个字符。

  八、

  示例:

  -- 按部门计算每个员工的总薪资SELECTdepartment_id,employee_id,salary,SUM(salary)OVER(PARTITIONBYdepartment_id)ASdept_total_salaryFROMemployees;

  解释:

  • SUM(salary) OVER (PARTITION BY department_id)：按部门对员工薪资进行分组，计算每个部门的薪资总和。

  Oracle 提供了丰富的 SQL 函数和功能，远不仅限于上述列举的函数。

六、

7.4 NEXT_DAY()

功能: 返回指定日期之后的第一个指定星期几的日期。

8.2 CEIL() 和 FLOOR()

功能: CEIL() 返回大于或等于指定数值的最小整数，FLOOR() 返回小于或等于指定数值的最大整数。

-- 将 JSON 数据映射到关系表中SELECT*FROMJSON_TABLE('{"employees":[{"id":1,"name":"John"},{"id":2,"name":"Jane"}]}','$.employees[*]'COLUMNS(id FORORDINALITY,employee_id NUMBER PATH '$.id',employee_name VARCHAR2(50)PATH '$.name'));

二、查找和排除重复数据，进行集合间的比较和操作。

这些高级函数和功能为处理复杂数据提供了强大的支持，能够满足各种分析和数据操作的需求。复杂聚合等功能，扩展了 SQL 查询的应用范围。

窗口函数：允许在查询中对数据进行分区和排序，从而在计算中考虑特定的窗口或范围。日期和时间函数

日期和时间函数用于对日期和时间进行各种操作。

5.1 REGEXP_LIKE

功能: 判断字符串是否匹配正则表达式模式。

9.5 复杂聚合函数

9.5.1 LISTAGG()

功能: 聚合字符串数据到一个单一的字符串中。分析函数

分析函数用于对结果集中的数据进行排序和排名分析。递归查询

递归查询用于处理层次结构的数据。窗口函数

窗口函数允许在结果集中计算聚合值，不会改变结果集的行数。

5.2 REGEXP_INSTR

功能: 返回正则表达式模式在字符串中首次出现的位置。

6.6 TRIM()

功能: 去除字符串两端的空格或指定字符。

2.2 AVG()

功能: 计算分区内所有行的平均值。

示例:

-- 返回员工薪资的上限和下限SELECTemployee_id,salary,CEIL(salary)ASceil_salary,FLOOR(salary)ASfloor_salaryFROMemployees;

解释:

• CEIL(salary)：将薪资向上取整到最接近的整数。数据库链接、

  示例:

  -- 按部门计算每个员工的平均薪资SELECTdepartment_id,employee_id,salary,AVG(salary)OVER(PARTITIONBYdepartment_id)ASdept_avg_salaryFROMemployees;

  解释:

  • AVG(salary) OVER (PARTITION BY department_id)：按部门对员工薪资进行分组，计算每个部门的薪资平均值。

  6.3 INSTR()

  功能: 返回子串在字符串中出现的位置。

  示例:

  -- 从 JSON 数据中提取嵌套对象SELECTJSON_QUERY('{"employee": {"id": 1, "name": "John"}}','$.employee')ASemployee_objectFROMdual;

  解释:

  • JSON_QUERY()提取 JSON 数据中指定路径的对象或数组。更多高级函数和功能

    9.1 XML 函数

    功能: 用于处理 XML 数据。

    示例:

    -- 递归查询构建员工层次结构WITHEmployeeHierarchy (employee_id,manager_id,level)AS(-- 基础查询，选择根节点（没有上级经理的员工）SELECTemployee_id,manager_id,1ASlevelFROMemployeesWHEREmanager_id ISNULLUNIONALL-- 递归部分，选择每个员工的下属SELECTe.employee_id,e.manager_id,eh.level+1FROMemployees eJOINEmployeeHierarchy eh ONe.manager_id =eh.employee_id)SELECTemployee_id,manager_id,levelFROMEmployeeHierarchy;

    解释:

    • WITH EmployeeHierarchy AS (...)：定义一个递归公共表表达式（CTE）。

    1.2 DENSE_RANK()

    功能: 计算排序后的排名，对于相同值排名相同，但排名之间没有间隔。这些函数包括 SUM() OVER(), AVG() OVER()和 LEAD(), LAG()，用于计算移动总和、