Oracle常用函数

一、行列转换

1.使用MAX(CASE WHEN)、SUM(CASE WHEN)、MAX(DECODE)或者SUM(DECODE)实现行列转换

WITH temp AS (
   SELECT 'John' AS Name,'Math' AS Subject,'90' AS Score FROM dual
	 UNION ALL 
	 SELECT 'John' AS Name,'Science' AS Subject,'80' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'Math' AS Subject,'85' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'Science' AS Subject,'95' AS Score FROM dual
)
SELECT Name,
MAX(CASE WHEN Subject='Math' THEN Score END) AS Math,
MAX(CASE WHEN Subject='Science' THEN Score END) AS Science
FROM temp
GROUP BY Name

WITH temp AS (
   SELECT 'John' AS Name,'Math' AS Subject,'90' AS Score FROM dual
	 UNION ALL 
	 SELECT 'John' AS Name,'Science' AS Subject,'80' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'Math' AS Subject,'85' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'Science' AS Subject,'95' AS Score FROM dual
)
SELECT Name,
SUM(CASE WHEN Subject='Math' THEN Score END) AS Math,
SUM(CASE WHEN Subject='Science' THEN Score END) AS Science
FROM temp
GROUP BY Name

WITH temp AS (
   SELECT 'John' AS Name,'Math' AS Subject,'90' AS Score FROM dual
	 UNION ALL 
	 SELECT 'John' AS Name,'Science' AS Subject,'80' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'Math' AS Subject,'85' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'Science' AS Subject,'95' AS Score FROM dual
)
SELECT NAME，SUM(DECODE(Subject,'Math',Score,NULL)) Math,
SUM(DECODE(Subject,'Science',Score,NULL)) Science FROM temp  GROUP BY Name;

2.使用UNPIVOT实现行列转换，具体语法格式如下：

SELECT *
FROM table_name
UNPIVOT (new_column_name FOR old_column_name IN (column1, column2, column3, ...))

WITH temp AS (
   SELECT '67' AS 语文,'76' AS 数学,'43' AS 英语 FROM dual
)
SELECT *
FROM temp
UNPIVOT ((score) FOR course IN (语文,数学,英语))

WITH temp AS (
   SELECT '67' AS 语文,NULL AS 数学,'43' AS 英语 FROM dual
)
SELECT course,NVL(score,'0') score
FROM temp
UNPIVOT INCLUDE NULLS((score) FOR course IN (语文,数学,英语));

二、字符串相关函数

1. CONCAT()函数可用于将两个字符串连接起来，生成一个新的字符串，格式如下：

   CONCAT(param1,param2)

   其中，param1和param2均为要连接的字符串或者字符型字段

SELECT CONCAT('hello',' world') AS result FROM dual;

如果需要拼接多个字符串或列字段，可使用’||'运算符,具体的语法格式如下：

SELECT column1 || column2 || column3
FROM table_name;

此外，拼接的列字段之间可添加任何文本或字符，例如空格或逗号，如下所示：

SELECT column1 || ' ' || column2 || ', ' || column3 
FROM table_name;

2. SUBSTR()：从字符串中提取一部分子字符串。
3. LENGTH()：返回字符串的长度。
4. INSTR()：查找字符串中一个子字符串的位置。
5. TRIM：删除字符串开头或结尾的空格
6. REPLACE：替换一个字符串中的一个子字符串。
7. UPPER：将字符串转换为大写。
8. LOWER：将字符串转换为小写。
9. INITCAP：将字符串的首字符转为大写，其他字符转换为小写。
10. REGEXP_SUBSTR()： 通过正则表达式提取子字符串

​ REGEXP_SUBSTR(source_string, pattern [, start_position [, nth_match [, match_param]]])
其中，
source_string: 要搜索的字符串。
pattern: 用于匹配源字符串的正则表达式模式。
start_position: 可选。开始搜索的位置，默认为1(第一个字符)。
nth_match: 可选。指定要返回的匹配项索引，默认为1(第一个匹配项)。
match_param: 可选。一个标志，指示如何匹配字符串。例如， ‘i’ 表示不区分大小写匹配等。默认为 ‘c’ 区分大小写匹配

--从URL中提取域名
SELECT REGEXP_SUBSTR('http://www.example.com/mypage.html', '[^/]+[.][^/]+', 1, 1) AS domain FROM dual;
-- 从电子邮件地址中提取用户名
SELECT REGEXP_SUBSTR('user@example.com', '[^@]+') AS result FROM dual;

11. REGEXP_LIKE()：只能用于条件表达式，和like类似，但是使用正则表达式进行匹配，语法更为简单。
    REGEXP_LIKE(source_string,regular_expression[,match_parameter])
    其中，
    source_string：需要检查的字符串。
    regular_expression：表示要匹配的正则表达式。
    match_parameter：一个可选参数，指定如何匹配字符串。默认值为“c”，表示区分大小写的匹配。如果没有提供此参数，则将使用默认值。

查找出以A或者B开头的城市
WITH cities AS (
  SELECT 'Shanghai' AS city_name FROM dual
	  UNION ALL 
	 SELECT 'Beijing' AS city_name FROM dual
) 
SELECT city_name FROM cities WHERE REGEXP_LIKE(city_name,'^(A|B)');

12. REGEXP_REPLACE函数用于使用正则表达式替换字符串中的匹配项。
    REGEXP_REPLACE(source_string, pattern, replace_string [, start_position [, occurrence [, match_parameter]]])
    其中，
    source_string是要替换的字符串。
    pattern是正则表达式模式，用于匹配要替换的文本。
    replace_string用于替换匹配项的字符串。
    start_position是源字符串中的起始位置，从该位置开始进行搜索和替换。该参数是可选的，默认值为1。
    occurrence是要替换的匹配项的实例数。如果指定为1，则仅替换第一个匹配项。该参数是可选的，默认值为0，表示替换所有匹配项。
    match_parameter指定匹配行为的附加设置。该参数是可选的，默认值为空。

将字符串中的所有数字替换为“#”：
SELECT REGEXP_REPLACE('abc 123 def 456', '\d', '#') FROM dual;

13. OVER()函数主要用于计算聚合函数的分组汇总结果，常配合窗口函数ROW_NUMBER()、RANK()、DENSE_RANK()、SUM()、AVG()、MIN()、MAX()、FIRST_VALUE()和LAST_VALUE()使用。

怎么实现分组获取第一条数据?
 SELECT Name,Score FROM(WITH temp AS (
   SELECT 'John' AS Name,'90' AS Score FROM dual
	 UNION ALL 
	 SELECT 'John' AS Name,'80' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'85' AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'95' AS Score FROM dual
)
 SELECT t.*,ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Score DESC) AS row_num
 FROM temp t) WHERE row_num=1

怎么得到每组的平均值？
 WITH temp AS (
   SELECT 'John' AS Name,'90' AS Score,'班级1' AS Grade FROM dual
	 UNION ALL 
	 SELECT 'Tom' AS Name,'80' AS Score,'班级1' AS Grade FROM dual
	  UNION ALL 
	 SELECT 'Alex' AS Name,'85' AS Score,'班级2' AS Grade FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'95' AS Score,'班级2' AS Grade FROM dual
)
 SELECT DISTINCT Grade,AVG(Score) OVER (PARTITION BY Grade) AS avg_score
 FROM temp;

  WITH temp AS (
   SELECT 'John' AS Name,'90' AS Score,'班级1' AS Grade FROM dual
	 UNION ALL 
	 SELECT 'Tom' AS Name,'80' AS Score,'班级1' AS Grade FROM dual
	  UNION ALL 
	 SELECT 'Alex' AS Name,'85' AS Score,'班级2' AS Grade FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'95' AS Score,'班级2' AS Grade FROM dual
)
 SELECT DISTINCT Grade,AVG(Score) AS avg_score
 FROM temp GROUP BY Grade;

14. NVL()函数：用于将空值或者NULL转换为特定值，如果表达式的值不为空，则返回原始值。

    WITH temp AS (
       SELECT 'John' AS Name,NULL AS Score FROM dual
    	  UNION ALL 
    	 SELECT 'Mary' AS Name,'95' AS Score FROM dual
    )
    SELECT Name,NVL(Score,'0') AS Result FROM temp;
    

15. NVL2()函数：
    NVL2(expr1, expr2, expr3)
    其中，expr1为需要测试的表达式；如果expr1不为NULL，则NVL2()函数返回expr2的值,否则返回expr3的值。

WITH temp AS (
   SELECT 'John' AS Name,NULL AS Score FROM dual
	  UNION ALL 
	 SELECT 'Mary' AS Name,'95' AS Score FROM dual)
SELECT Name,NVL2(Score,Score,'0') AS Result
FROM temp;

16. LISTAGG函数用于将同一组内的多个值作为列表合并成一个值。

    WITH temp AS(
       SELECT '中国' AS NATION,'上海' AS CITY FROM dual
    	 UNION ALL
    	 SELECT '中国' AS NATION,'北京' AS CITY FROM dual
    	 UNION ALL
    	 SELECT '日本' AS NATION,'东京' AS CITY FROM dual)
    SELECT NATION,LISTAGG(CITY,',') WITHIN GROUP(ORDER BY CITY) AS Cities FROM temp GROUP BY NATION;