《SQL基础教程（第2版）Oracle》——函数、谓词、CASE表达式、窗口函数、GROUPING运算符

1. 函数

● 聚合函数（用来进行数据聚合的函数），属于窗口函数的其中一种。
参见 2. DML语言【专题：聚合函数（进行汇总求值）】

● 算术函数（用来进行数值计算的函数）
● 字符串函数（用来进行字符串操作的函数）
● 日期函数（用来进行日期操作的函数）
● 转换函数（用来转换数据类型和值的函数）

1.2 算术函数（用来进行数值计算的函数）

（1）算术运算符
算术运算符具有“根据输入值返回相应输出结果”的功能，因此它们是出色的算术函数。

+   -   *   /  

（2）ABS函数：计算绝对值，即一个数到原点的距离。

ABS(数值)

参数为NULL时，结果也是NULL.
绝大多数函数对于NULL都返回NULL.

（3）MOD函数：计算除法余数（求余）

MOD(被除数，除数)

小数计算中并没有余数的概念，所以只能对整数类型的列使用MOD函数。
主流的DBMS都支持 MOD函数，只有 SQL Server 不支持该函数。
SQL Server使用特殊的运算符（函数）“%”来计算余数。

（4）ROUND函数：四舍五入

ROUND(对象数值，保留小数的位数)

1.3 字符串函数（用来进行字符串操作的函数）

1.3.1 || 拼接函数

# 字符串1 + 字符串2 + ...
字符串1 || 字符串2 || ...

如果其中包含 NULL，那么得到的结果也是NULL.

|| 函数 在SQL Server 和 MySQL 中无法使用。

SQL Server使用“+”运算符（函数）来连接字符串。

字符串1 + 字符串2 +  ......

MySQL使用CONCAT函数来完成字符串的拼接。

CONCAT(字符串1,  字符串2,  ......)

1.3.2 LENGTH函数：字符串字节个数

返回字符串中包含的字节个数。可在MySQL中使用。

LENGTH(字符串)

MySQL中还存在计算字符串长度（字符个数）的自有函数CHAR_LENGTH.

LENGTH函数无法在SQL Server 中使用。
SQL Server使用LEN函数来计算字符串的字符个数，其中不包含尾随空格。
使用DATALENGTH返回字符串所占用的字节数。

LEN (字符串)   -- 字符个数，字符串长度
DATALENGTH (字符串)  -- 字节个数

在Oracle中：
lengthb(string)计算string所占的字节长度：返回字符串的长度，单位是字节
length(string)计算string所占的字符长度：返回字符串的长度，单位是字符
因此，同样是 LENGTH函数，不同DBMS的执行结果也不尽相同。

对于全部为单字节字符的字符串，占用的字节个数和其字符串长度是一样的。
半角英文字母占用 1 字节，汉字这样的全角字符会占用 2 个或以上的字节（称为多字节字符）。

字节（byte）是计算机中用来表述数据大小的基本单位。
单位 字节/千字节（KB）是字节（byte）的1024倍，
单位 兆字节（MB）是千字节（KB）的1024倍，
单位 千兆字节（GB）是兆字节（MB）的1024 倍。

表示硬盘容量时经常会使用的“100 GB”等， 其中100 GB指的是可以存储：
1024×1024×1024×100= 107,374,182,400个字节（byte），
即能存储这么多个半角英文字母。

1.3.3 LOWER、UPPER函数

LOWER函数：
只针对英文字母，将参数中的字符串全都转换为小写。
不影响原本就是小写的字符。

LOWER(字符串)

UPPER函数：
只针对英文字母，将参数中的字符串全都转换为大写。
不影响原本就是大写的字符。

UPPER(字符串)

1.3.4 REPLACE函数：字符串的替换

将对象字符串的一部分替换为其他的字符串。

（1）对象字符串1 中若包含 替换前的字符串2，则将 对象字符串1 中 替换前的字符串2 那部分，替换为 替换后的字符串3 ；
（2）对象字符串1 中若不包含 替换前的字符串2，则对 对象字符串1 什么也不做。

REPLACE(对象字符串1，替换前的字符串2，替换后的字符串3)

1.3.5 SUBSTRING：字符串的截取

截取出字符串中的一部分字符串。
字符串的起始位置从最左侧开始计算。

SUBSTRING（对象字符串 FROM 截取的起始位置 FOR 截取的字符数）

虽然上述SUBSTRING函数的语法是标准SQL承认的正式语法，但现在只有PostgreSQL和MySQL支持该语法。

# SQL Server将 SUBSTRING函数 的语法内容进行了简化
SUBSTRING(对象字符串，截取的起始位置，截取的字符数)

# Oracle和DB2将语法进一步简化
SUBSTR(对象字符串，截取的起始位置，截取的字符数)

1.4 日期函数（用来进行日期操作的函数）

被标准SQL 承认的可以应用于 绝大多数DBMS的函数。

1.4.1 CURRENT_DATE函数：当前日期

返回SQL执行的日期，也就是该函数执行时的日期。
由于没有参数，因此无需使用括号。

# 语法
CURRENT_DATE

# PostgreSQL、MySQL 
SELECT CURRENT_DATE;

'''  CURRENT_DATE函数无法在SQL Server 中执行
CURRENT_TIMESTAMP()函数返回带时区的当前日期和时间（即时间戳）。
SQL Server使用CURRENT_TIMESTAMP函数来获得当前时间戳,
再使用CAST函数将CURRENT_TIMESTAMP转换为日期类型。  '''
SELECT CAST(CURRENT_TIMESTAMP AS DATE) AS CUR_DATE;

# 在Oracle中使用CURRENT_DATE函数时，需要在FROM子句中指定临时表（DUAL）
SELECT CURRENT_DATE 
  FROM dual;

# 在DB2中使用CURRENT_DATE函数时，需要在CRUUENT和DATE之间添加半角空格，并且还需要指定临时表 SYSIBM.SYSDUMMY1
SELECT CURRENT DATE 
   FROM SYSIBM.SYSDUMMY1;

1.4.2 CURRENT_TIME函数：当前时间

能够取得SQL 执行的时间，也就是该函数执行时的时间。
由于该函数也没有参数，因此同样无需使用括号。

# 语法
CURRENT_TIME

# PostgreSQL、MySQL 
SELECT CURRENT_TIME;

'''  CURRENT_TIME函数无法在SQL Server中执行
CURRENT_TIMESTAMP()函数返回带时区的当前日期和时间（即时间戳）。
SQL Server使用CURRENT_TIMESTAMP函数来获得当前时间戳,
再使用CAST函数将CURRENT_TIMESTAMP转换为时间类型。  '''
SELECT CAST(CURRENT_TIMESTAMP AS TIME) AS CUR_TIME;

# CURRENT_TIMESTAMP函数返回当前日期和时间
# 在Oracle中使用CURRENT_TIMESTAMP函数时，需要在FROM子句中指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP   -- Oracle中没有CURRENT_TIME函数
  FROM dual;

# 在DB2中使用CURRENT_TIME函数时，需要在CRUUENT和TIME之间添加半角空格，并且还需要指定临时表 SYSIBM.SYSDUMMY1
SELECT CURRENT TIME
   FROM SYSIBM.SYSDUMMY1;

1.4.3 CURRENT_TIMESTAMP函数：当前日期和时间

CURRENT_TIMESTAMP函数具有CURRENT_DATE + CURRENT_TIME的功能。
使用该函数可以同时得到当前的日期和时间，当然也可以从结果中截取日期或者时间。

CURRENT_TIMESTAMP

# SQL Server / PostgreSQL / MySQL 
SELECT CURRENT_TIMESTAMP;

# 在Oracle中使用CURRENT_TIMESTAMP函数时，需要在FROM子句中指定临时表（DUAL）
SELECT CURRENT_TIMESTAMP  
  FROM dual;

# 在DB2中使用CURRENT_TIMESTAMP函数时，需要在CRUUENT和TIMESTAMP之间添加半角空格，并且还需要指定临时表 SYSIBM.SYSDUMMY1
SELECT CURRENT TIMESTAMP
   FROM SYSIBM.SYSDUMMY1;

1.4.4 EXTRACT函数：截取日期元素

截取出日期数据中的一部分，例如“年”“月”，或者“小时”“秒”等。
该函数的返回值并不是日期类型而是数值类型。

# 语法
EXTRACT(日期元素 FROM 日期)

# PostgreSQL、MySQL 
SELECT
	CURRENT_TIMESTAMP,
	EXTRACT( YEAR FROM CURRENT_TIMESTAMP ) AS year,
	EXTRACT( MONTH FROM CURRENT_TIMESTAMP ) AS month,
	EXTRACT( DAY FROM CURRENT_TIMESTAMP ) AS day,
	EXTRACT( HOUR FROM CURRENT_TIMESTAMP ) AS hour,
	EXTRACT( MINUTE FROM CURRENT_TIMESTAMP ) AS minute,
	EXTRACT( SECOND FROM CURRENT_TIMESTAMP ) AS second;

'''  EXTRACT函数无法在SQL Server中执行
SQL Server使用DATEPART函数从CURRENT_TIMESTAMP中截取元素。  '''
SELECT
	CURRENT_TIMESTAMP,
	DATEPART ( YEAR, CURRENT_TIMESTAMP ) AS year,
	DATEPART ( MONTH, CURRENT_TIMESTAMP ) AS month,
	DATEPART ( DAY, CURRENT_TIMESTAMP ) AS day,
	DATEPART ( HOUR, CURRENT_TIMESTAMP ) AS hour,
	DATEPART ( MINUTE, CURRENT_TIMESTAMP ) AS minute,
	DATEPART ( SECOND, CURRENT_TIMESTAMP ) AS second;

# 在Oracle中使用EXTRACT函数时，需要在FROM子句中指定临时表（DUAL）
SELECT
	CURRENT_TIMESTAMP,
	EXTRACT( YEAR FROM CURRENT_TIMESTAMP ) AS year,
	EXTRACT( MONTH FROM CURRENT_TIMESTAMP ) AS month,
	EXTRACT( DAY FROM CURRENT_TIMESTAMP ) AS day,
	EXTRACT( HOUR FROM CURRENT_TIMESTAMP ) AS hour,
	EXTRACT( MINUTE FROM CURRENT_TIMESTAMP ) AS minute,
	EXTRACT( SECOND FROM CURRENT_TIMESTAMP ) AS second 
FROM dual;

# 在DB2中使用CURRENT_TIMESTAMP函数时，需要在CRUUENT和TIMESTAMP之间添加半角空格，并且还需要指定临时表 SYSIBM.SYSDUMMY1
SELECT
	CURRENT TIMESTAMP,
	EXTRACT( YEAR FROM CURRENT TIMESTAMP ) AS year,
	EXTRACT( MONTH FROM CURRENT TIMESTAMP ) AS month,
	EXTRACT( DAY FROM CURRENT TIMESTAMP ) AS day,
	EXTRACT( HOUR FROM CURRENT TIMESTAMP ) AS hour,
	EXTRACT( MINUTE FROM CURRENT TIMESTAMP ) AS minute,
	EXTRACT( SECOND FROM CURRENT TIMESTAMP ) AS second 
FROM SYSIBM.SYSDUMMY1;

1.5 转换函数（用来转换数据类型和值的函数）

1.5.1 CAST函数：类型转换

# 语法
CAST（转换前的值 AS 想要转换的数据类型）

提前进行数据类型转换的原因：
（1）可能会插入与表中数据类型不匹配的数据；
（2）在进行运算时由于数据类型不一致发生了错误；
（3）进行自动类型转换会造成处理速度低下。

类型转换其实并不是为了方便用户使用而开发的功能，而是为了方便DBMS内部处理而开发的功能。

# 在Oracle中
-- （1）将字符串类型转换为数值类型
SELECT CAST('0001' AS INTEGER) AS int_col FROM DUAL;
--（2）将数值类型转换为字符串类型（转换为字符串类型时，要表明最大长度。）
SELECT CAST(1 AS VARCHAR2(4)) AS int_col FROM DUAL;


'''
TO_DATE函数即可以使用'2009-12-14'的日期形式，也可以使用'14-12月-09'/'14-12月-2009'的日期形式；
TO_TIMESTAMP函数仅能使用'2009-12-14'的日期形式；
CAST函数仅能使用'14-12月-09'/'14-12月-2009'的日期形式。'''

-- （3）将字符串类型转换为日期类型
-- CAST函数：转换前的字符串中不能包含时间，转换后的DATE类型中包含时间且置为00:00:00
SELECT CAST('14-12月-2009' AS DATE) AS date_col FROM DUAL;   ---->2009-12-14 00:00:00
SELECT CAST('14-12月-09' AS DATE) AS date_col FROM DUAL;  ---->2009-12-14 00:00:00
--当月份为各位数时，前面不用加0
SELECT CAST('1-1月-0001' AS DATE) AS date_col FROM DUAL; ---->0001-01-01 00:00:00
SELECT CAST('14-9月-2009' AS DATE) AS date_col FROM DUAL; ---->2009-09-14 00:00:00

# 等价于：使用TO_DATE函数时，且不包含时间
SELECT TO_DATE('14-12月-2009', 'dd-mon-yy') FROM dual;    ----->2009-12-14 00:00:00
SELECT TO_DATE('14-12月-09', 'dd-mon-yy') FROM dual;    ----->2009-12-14 00:00:00
# 也可在使用TO_DATE函数时，包含时间【注意与下面的秒的区别】
SELECT TO_DATE('2009-12-14 10:30:00', 'YYYY-MM-DD HH24:MI:SS') AS converted_date  FROM dual;    ----->2009-12-14 10:30:00


-- （4）将字符串类型转换为日期+时间（时间戳）类型
''' CAST函数：转换前的字符串中包不包含时间均可,转换后的TIMESTAMP类型一定包含。
若转换前的字符串中包含时间，转换后的TIMESTAMP类型中的时间，即为输入值；
若转换前的字符串中不包含时间，转换后的TIMESTAMP类型中的时间，即为00:00:00.000000'''
SELECT CAST('14-12月-2009 10:30:00' AS TIMESTAMP) AS converted_timestamp  FROM dual;    ----->2009-12-14 10:30:00.000000
SELECT CAST('14-12月-09' AS TIMESTAMP) AS converted_timestamp  FROM dual;    ----->2009-12-14 00:00:00.000000
【注意：
SELECT CAST('14-12月-2009' AS TIMESTAMP) AS converted_timestamp  FROM dual;
-----> 2020-12-14 09:00:00.000000
在原字符串中不包含时间又要转化为TIMESTAMP类型时，结果年份不正确了，所以尽量用上面两种方式。】

# 等价于:使用TO_TIMESTAMP函数
SELECT TO_TIMESTAMP('2009-12-14 10:30:00', 'YYYY-MM-DD HH24:MI:SS') AS Tstamp FROM dual;      ----->2009-12-14 10:30:00.000000000
SELECT TO_TIMESTAMP('2009-12-14', 'YYYY-MM-DD') AS Tstamp FROM dual;  ----->2009-12-14 00:00:00.000000000

1.5.2 COALESCE函数：将NULL转换为其他值

COALESCE函数会返回可变参数中左侧开始第1个不是NULL的值。
参数个数是可变的，因此可以根据需要无限增加。
但是它们的数据类型都必须相同。

COALESCE(数据1，数据2，数据3……)

运算或者函数中含有 NULL时，结果全都会变为 NULL。
能够避免这种结果的函数就是COALESCE。
Oracle中coalesce函数的用法——百度文库

条件函数（CASE、COALESCE、NULLIF、NVL、NVL2、GREATEST、LEAST）—— 阿里云

SELECT
	COALESCE( NULL, 1 )  AS col_1,
	COALESCE( NULL, 'test', NULL )	AS col_2,
	COALESCE( NULL, NULL, '2009-11-01' ) AS col_3 
FROM DUAL;

2. 谓词

谓词也是函数中的一种。
谓词的返回值全都是真值（TRUE/ FALSE/UNKNOWN）。
【通常的其它函数返回值可能是数字、字符串或者日期等。就不能称作“谓词”。】

常见的谓词有：
● 所有的比较运算符（=、<> 、>=、<=、>、<）
● 所有的逻辑运算符（NOT、AND、OR）
● LIKE
● BETWEEN
● IS NULL、IS NOT NULL
● IN、NOT IN
● EXISTS、NOT EXISTS

2.1 LIKE谓词：字符串的部分一致查询

<字段名> LIKE '<查询条件字符串及其规则（模式）>'

区别比较运算符=：
使用字符串作为查询条件时，如果使用的是比较运算符=，
即： 查询对象字符串 = 查询条件字符串 ，这种形式，
那么只有在查询条件与查询对象字符串完全/全部一致时比较运算才为真。

模式匹配：
不使用 “=” 来指定查询条件字符串，而以查询对象字符串中是否包含某一条件的规则为基础的查询称为模式匹配。
模式：
其中的模式也就是指 查询条件字符串 中包含的规则。

当进行 查询条件与查询对象 字符串的 部分一致查询时，需要使用LIKE谓词。

正则表达式是另一种更复杂，但比LIKE谓词应用更广泛的“部分一致查询”模式匹配方法。

%是代表 “0个字符以上的任意字符串” 的特殊符号（不强调包含的字符个数也不强调字符串中的内容）。
_（下划线）代表了 “任意1个字符” （强调字符个数，内容不限）。

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
（1）前方一致查询
选取出作为查询条件的字符串（假如是“ddd”、“abc”）与查询对象字符串起始部分相同的记录的查询方法。

# 选取出 strcol列的值为以ddd开头的 记录
SELECT * 
 FROM SampleLike 
WHERE strcol LIKE 'ddd%';

# 选取出 strcol列的值为 “abc + 任意1个字符”的记录
SELECT * 
 FROM SampleLike 
WHERE strcol LIKE 'abc_';   -- 后面只能有1个字符，内容不限

（2）中间一致查询
选取出查询对象字符串中含有作为查询条件的字符串（“ddd、abc”）的记录的查询方法。
无论 该查询条件的字符串 出现在 查询对象字符串 的最后还是中间都没有关系。
因此要在字符串的起始和结束位置都加上% 或 _

查询条件最宽松，也就是能够取得最多记录的是中间一致查询。
因为它同时包含前方一致和后方一致的查询结果。

# 选取出 strcol列的值中包含ddd的 记录
SELECT * 
 FROM SampleLike 
WHERE strcol LIKE '%ddd%';

# 选取出 strcol列的值为 “任意1个字符 + abc + 任意1个字符” 的记录
SELECT * 
 FROM SampleLike 
WHERE strcol LIKE '_abc_';   -- 前、后都只能有1个字符，内容不限

（3）后方一致查询
选取出作为查询条件的字符串（“ddd、abc”）与查询对象字符串的末尾部分相同的记录的查询方法。

# 选取出 strcol列的值为以ddd结尾的 记录
SELECT * 
 FROM SampleLike 
WHERE strcol LIKE '%ddd';

# 选取出 strcol列的值为 “任意1个字符 + abc”的记录
SELECT * 
 FROM SampleLike 
WHERE strcol LIKE '_abc';   -- 前面只能有1个字符，内容不限

2.2 BETWEEN谓词：范围查询

使用3个参数。

# 选取出字段值处在 [下限, 上限] 范围中的记录
(WHERE) <字段名> BETWEEN <下限> AND <上限>

BETWEEN的结果中会包含上、下限这两个临界值。
如果不想让结果中包含临界值，那就必须使用<和>.

# 选取出字段值处在 (下限, 上限) / [下限+1, 上限-1]  范围中的记录
(WHERE) <字段名> > <下限> 
AND <字段名> < <上限>

2.3 IS NULL、IS NOT NULL谓词：判断是否为NULL

选取出字段值为NULL的列的数据，不能使用=，只能使用特定的谓词IS NULL.

(WHERE) <字段名> IS NULL

选取字段为NULL以外的数据时，需要使用IS NOT NULL.

(WHERE) <字段名> IS NOT NULL

2.4 IN、NOT IN谓词

# 指定多个字段值（相当于=和OR的结合）
# 选取出字段值为： 值1 或 值2 或 值3...的记录
(WHERE) <字段名> IN(值1, 值2, 值3, ……)

# 排除多个字段值（相当于<>和AND的结合）
# 选取出字段值不为值1, 且不为值2, 且不为值3...的记录
NOT IN(值1, 值2, 值3, ……)

在使用IN和NOT IN时是无法选取出NULL数据的。

即：即使 IN(值1, 值2, 值3, ……)中包含NULL
NOT IN(值1, 值2, 值3, ……) 中不包含NULL
也都取不出该字段值为NULL的记录。

2.4.1 使用普通SELECT查询、子查询、视图作为IN谓词的参数

普通SELECT查询、子查询和视图，都可以看成是SQL内部生成的表。
“能够将表作为IN的参数”。
“能够将普通SELECT查询作为IN的参数”。
“能够将视图作为IN的参数”。
“能够将子查询作为IN的参数”。（普通子查询、标量子查询、关联子查询）

仍然是按照先内层查询，后外层查询的顺序来执行的。

# 例子：使用普通SELECT查询作为IN的参数
# 选取在000C号店铺销售的商品（product_id）的商品名称（product_name）和销售单价（sale_price）
SELECT product_name, sale_price
  FROM Product
 WHERE product_id IN (SELECT product_id 
                       FROM ShopProduct
                      WHERE shop_id = '000C');
                      
# 以上例子等价于如下：使用关联子查询作为IN的参数
SELECT product_name, sale_price
  FROM Product P
 WHERE product_id IN (SELECT product_id    -- 只能有一个字段，且与IN前面的字段相对应
                       FROM ShopProduct SP
                      WHERE SP.shop_id = '000C'
						AND SP.product_id = P.product_id);

# 还等价于2.5.1中使用关联子查询作为EXISTS谓词的参数
# 且一般将 关联子查询和EXISTS及NOT EXISTS谓词 搭配使用

2.4.2 使用普通SELECT查询、子查询、视图作为NOT IN谓词的参数

仍然是按照先内层查询，后外层查询的顺序来执行的。

# 例子：使用普通SELECT查询作为NOT IN的参数
# 选取在000C号店铺以外销售的商品（product_id）的商品名称（product_name）和销售单价（sale_price）
SELECT product_name, sale_price
  FROM Product
 WHERE product_id NOT IN (SELECT product_id 
                       FROM ShopProduct
                      WHERE shop_id = '000C');

# 以上例子等价于如下：使用关联子查询作为NOT IN的参数
SELECT product_name, sale_price
  FROM Product P
 WHERE product_id NOT IN (SELECT product_id    -- 只能有一个字段，且与IN前面的字段相对应
                       FROM ShopProduct SP
                      WHERE SP.shop_id = '000C'
						AND SP.product_id = P.product_id);

# 还等价于2.5.2中使用关联子查询作为NOT EXISTS谓词的参数
# 且一般将 关联子查询和EXISTS及NOT EXISTS谓词 搭配使用

！！！SQL中最危险的陷阱：（属于中级学习的范畴。）
NOT IN的参数中包含NULL时结果通常会为空，也就是无法选取出任何记录。

因此，在指定值的情况下，NOT IN的参数中不能包含 NULL；
在使用普通SELECT查询、子查询、视图作为NOT IN的参数时，它们的返回值也不能是NULL.

2.5 EXISTS、NOT EXISTS谓词

【一般使用复数 EXISTS 和 NOT EXISTS. 在Oracle中就没有 EXIST 和 NOT EXIST 这两个关键字。 】

2.5.1 使用关联子查询作为EXISTS谓词的参数

EXISTS谓词的作用就是“判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

EXISTS通常都会使用关联子查询作为参数。

EXISTS只关心记录是否存在，因此返回哪些列都没有关系。
所以其参数关联子查询中，一般写成SELECT *
而且要把在EXISTS的子查询参数中书写 SELECT * 当作书写SQL的一种习惯。

# 例子：使用关联子查询作为 EXISTS 的参数
# 选取在000C号店铺销售的商品（product_id）的商品名称（product_name）和销售单价（sale_price）
SELECT product_name, sale_price
  FROM Product P
 WHERE EXISTS (SELECT *   --可以是几个列、全部列*、常数。一般写全部列*
                 FROM ShopProduct SP
                WHERE SP.shop_id = '000C'
                  AND SP.product_id = P.product_id);

# 等价于2.4.1中使用普通SELECT查询、关联子查询作为IN的参数的两种书写方式
# 但一般使用这种，将 关联子查询和EXISTS及NOT EXISTS谓词 搭配使用

2.5.2 使用关联子查询作为NOT EXISTS谓词的参数

NOT EXISTS与EXISTS相反，当 “不存在” 满足关联子查询中指定条件的记录时返回真（TRUE）。

# 例子：使用关联子查询作为 NOT EXISTS 的参数
# 选取在000C号店铺以外销售的商品（product_id）的商品名称（product_name）和销售单价（sale_price）
SELECT product_name, sale_price
  FROM Product P
 WHERE NOT EXISTS (SELECT *   --可以是几个列、全部列*、常数。一般写全部列*
                 FROM ShopProduct SP
                WHERE SP.shop_id = '000C'
                  AND SP.product_id = P.product_id);

# 等价于2.4.2中使用普通SELECT查询、关联子查询作为NOT IN的参数的两种书写方式
# 但一般使用这种，将 关联子查询和EXISTS及NOT EXISTS谓词 搭配使用

严格来说EXISTS和NOT EXISTS 与 IN和NOT IN并不相同。
EXISTS和NOT EXISTS 拥有 IN和NOT IN 所不具有的便利性，
但没有必要勉强使用 EXISTS和NOT EXISTS 来替代IN和NOT IN .

3. CASE表达式

3.1 简单CASE表达式

类似C语言中的SWITCH函数。

-- 语法：其中的每个<表达式>都可以是字段、常数、计算表达式中的任意一种。
CASE <表达式ʘ>       
	WHEN <表达式> THEN	<表达式> 
	WHEN <表达式> THEN	<表达式> 
	WHEN <表达式> THEN  <表达式>
	...
	ELSE <表达式>   --可省略，会被默认为ELSE NULL
END               --END不能忘记！需要时还可在后面加“AS 列别名”
-- 一般是将整个CASE表达式括起来，设置一个别名，作为一列输出。根据需求定。

--例如：
case sex
  when '1' then '男'
  when '2' then '女’
  else '其他' 
end AS "性别"

其中的每个<表达式>，都可以是字段、常数或计算表达式中的任意一种。
可能不需要进行计算，即为字段或常数；
为计算表达式时，需要进行计算，其返回值可能是数字、字符串或者日期等等。

3.1.1 简单CASE表达式的执行过程

（1）当 “CASE <表达式ʘ> ” 中的 “<表达式ʘ>” 为计算表达式时，对其进行求值。
当其为字段或常数时，直接进行第二步。

（2）如果<表达式ʘ>的值 与 WHEN子句中<表达式>的值 相等（这里默认有一个使用谓词=进行的比较运算），那么就返回其后THEN子句中的表达式，CASE表达式的执行到此为止。

（3）如果<表达式ʘ>的值 与 WHEN子句中<表达式>的值 不相等，那么就比较<表达式ʘ>与下一条WHEN子句的<表达式> 的值。
并重复（2）、（3）.

（4）如果直到最后<表达式ʘ>的值 都与 WHEN子句中<表达式>的值 不相等，那么就返回ELSE中的表达式，CASE表达式执行终止。
ELSE子句也可以省略不写，这时会默认为ELSE NULL。

总之，CASE表达式最终只会返回一个值。
且CASE表达式最后的“END”是不能省略的。

3.2 搜索CASE表达式

与简单CASE表达式相比，优点： 可在WHEN子句中指定不同列。

CASE 
	WHEN <求值表达式> THEN  <表达式> 
	WHEN <求值表达式> THEN  <表达式> 
	WHEN <求值表达式> THEN  <表达式>
	   ...
	ELSE <表达式>     --可省略，会被默认为ELSE NULL
END                  --END不能忘记！需要时还可在后面加“AS 列别名”
-- 一般是将整个CASE表达式括起来，设置一个别名，作为一列输出。根据需求定。


--例如：
case when sex = '1' then '男'
     when sex = '2' then '女'
     else '其他' 
end AS "性别"

3.2.1 搜索CASE表达式的执行过程

WHEN子句中的<求值表达式> ，特别强调了“求值”，更准确的，其实应该写为 “求真值”。
因为这里的<求值表达式>特指：使用谓词编写出来的表达式，返回值只会为真值（TRUE/FALSE/UNKNOWN）。

其它没有加“求值”的<表达式> ，都可以是字段、常数或计算表达式中的任意一种。
可能不需要进行计算，即为字段或常数；
为计算表达式时，需要进行计算，其返回值可能是数字、字符串或者日期等等。

搜索CASE表达式的执行过程：
（1）对第一个WHEN子句中的“< 求值表达式 >”进行求真值。
所谓求真值，就是要调查该表达式的真值是什么。

（2）如果WHEN子句结果的为真（TRUE），那么就返回其后THEN子句中的表达式，CASE表达式的执行到此为止。

（3）如果WHEN子句的结果不为真，那么就跳转到下一条WHEN子句的求真值之中。
并重复（2）、（3）.

（4）如果直到最后的WHEN子句为止，返回结果都不为真，那么就返回ELSE中的表达式，CASE表达式执行终止。
ELSE子句也可以省略不写，这时会默认为ELSE NULL。

总之，CASE表达式最终只会返回一个值。
且CASE表达式最后的“END”是不能省略的。

3.2.2 搜索CASE表达式的书写位置

CASE表达式同其它表达式一样，可以书写在任意位置。

在对SELECT 语句的结果进行编辑时，CASE 表达式能够发挥较大作用。
使用搜索CASE条件表达式和SUM()函数，可以根据不同的条件，计算和汇总数据。
例如：可以利用搜索CASE表达式将下述SELECT语句结果中的行和列进行互换：

-- 根据商品种类计算出销售单价的合计值
SELECT product_type,
	   SUM(sale_price) AS sum_price 
FROM Product 
GROUP BY product_type;

--对按照商品种类得到的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) AS sum_price_clothes,
       SUM(CASE WHEN product_type = '厨房用具' THEN sale_price ELSE 0 END) AS sum_price_kitchen,
       SUM(CASE WHEN product_type = '办公用品' THEN sale_price ELSE 0 END) AS sum_price_office
  FROM Product;

可以实现转化的原理：
（1） 与编程中的SUM函数不同，SQL语言中的SUM函数只能接受，而且只能接受一个列名。
将列名作为参数传递给SUM函数，它将返回该列中所有值的总和。
还可以在SUM函数中使用关于列的条件语句，返回满足条件的所有记录目标列的值的总和。
即设定条件的列 可以和要求值的列不是同一个。
（2）CASE表达式是一种灵活的条件语句，正好可以满足上一句的要求。（艺术）
对CASE表达式中的每个WHEN条件，如果条件满足（即WHEN子句中的“< 求值表达式 >”返回的真值为TRUE），则会将该WHEN条件后面THEN子句中的<表达式>作为CASE表达式的返回值。

而且仍然满足以下原则：
使用聚合函数时的SELECT子句 以及 使用聚合函数时的HAVING子句中，都只能存在以下三种元素：
● 常数
● 聚合函数（COUNT、SUM、AVG、MAX、MIN）及其参数 （其参数也可以是除聚合键之外的其它列名）
● GROUP BY子句中指定的列名（也就是聚合键）(如果没有GROUP BY子句，就没有这个)

关于第二段代码中SELECT子句、SUM函数、CASE表达式、AS列别名的执行顺序与情况的说明：

（1）SELECT：

SELECT语句的执行顺序为：
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

这里执行完FROM 之后，没有WHERE、GROUP BY、 HAVING子句，则直接执行SELECT子句.
【SELECT跟其它子句一样，地位平等（而且顺序还靠后），都是对原表的操作与计算。也并不直接管理输出过程。
把SELECT子句看作在前面FROM、WHERE、GROUP BY、 HAVING子句的操作结果基础上，要进行的计算。若只有字段名和常数，那就代表不用进行计算，只进行选择即可。 】

即会先把表中所有的记录都读取到，以所有记录为单位进行SELECT中计算的操作，并把指针定位到第一个记录。
【如果有 GROUP BY 的话，就会先进行分组，以组为单位执行下面的操作。并把指针定位到第一组的第一个记录。】

（2）CASE表达式： （按从内到外的顺序执行，CASE在第一个SUM函数的内部。）
第一个SUM函数中的CASE表达式中的WHEN条件是“product_type = ‘衣服’”，且当该条件满足时，返回当时记录的sale_price字段。不满足时返回0值.
即：这里进行求和的列，也就是给SUM函数传递的参数列名，是sale_price列。

通过CASE表达式会对一个操作单位（即所有记录）的sale_price列进行这样一个改变：
sale_price列中所有“product_type = '衣服’的记录，保持原值不变，否则就将其值变为0.
【在临时表中操作，肯定不是在原表中操作。】

当SUM函数为：
SUM(CASE WHEN product_type = ‘衣服’ THEN 1 ELSE 0 END) AS sum_clothes 时，
则会使用一个新的临时列，存储实体原表Product中各个记录对应的该CASE表达式的返回值（1或0），并在第（3）步中，对该临时列中的各记录值求和。

求出来的即为实体原表Product中，满足product_type = ‘衣服’ 的记录总数。

利用下面的原理，可以写出与其等价的写法：

所有的聚合函数，如果以列名为参数，那么在计算之前就已经把NULL排除在外了。
因此，无论有多少个NULL 都会被无视。

与其等价的写法为：

COUNT( CASE WHEN product_type = '衣服' 
			  THEN product_id      -- 可为任意字段
			  ELSE NULL          -- 必须是NULL
			  END 
			) AS sum_clothes,

（3）SUM函数： 整理好sale_price列中全部的值后，使用SUM函数，对列中所有的记录进行求和。
（4）AS列别名： 将求和得到的值，作为要输出的列sum_price_clothes的第一个记录。

（5）对SELECT中的第二、第三个聚合函数SUM，重复（2）（3）（4）.
至此，执行完SELECT子句中的全部内容。
（6）没有ORDER BY子句，直接输出最终结果sum_price_clothes、sum_price_kitchen、sum_price_office.

---

CASE 表达式是标准 SQL 所承认的功能，因此在任何DBMS 中都可以执行。
但是，有些 DBMS 还提供了一些特有的 CASE 表达式的简化函数，例如 Oracle 中的DECODE、MySQL中的 IF等。

# 通过简单CASE表达式将A～C的字符串加入到商品种类当中
SELECT product_name,
       CASE product_type
            WHEN '衣服'    THEN 'A：' || product_type
            WHEN '办公用品' THEN 'B：' || product_type
            WHEN '厨房用具' THEN 'C：' || product_type
            ELSE NULL
        END AS abc_product_type
  FROM Product;

# 通过搜索CASE表达式将A～C的字符串加入到商品种类当中
SELECT product_name,
       CASE WHEN product_type = '衣服'    THEN 'A：' || product_type
            WHEN product_type = '办公用品' THEN 'B：' || product_type
            WHEN product_type = '厨房用具' THEN 'C：' || product_type
            ELSE NULL
       END AS abc_product_type
  FROM Product;

--使用Oracle中特有的DECODE代替CASE表达式，将A～C的字符串加入到商品种类当中
SELECT  product_name,
        DECODE(product_type, '衣服',     'A：' || product_type,
                             '办公用品', 'B：' || product_type,
                             '厨房用具', 'C：' || product_type,
               NULL) AS abc_product_type
  FROM Product;

【 IF】

1661. 每台机器的进程平均运行时间

在MySQL中，IF既可以作为表达式用，也可在存储过程中作为流程控制语句使用。
SQL的IF语句——nichoo的博客

-- 在MySQL中，IF作为表达式用
select
    machine_id,
    round(2*avg(if(activity_type = 'start',-1,1)*timestamp),3) as processing_time
from
    Activity
group by
    machine_id;

在oracle中，IF仅做为流程控制语句（在存储过程中）使用。
oracle中没有IF表达式，只有CASE表达式，来实现“if-then-else”的逻辑计算功能。
数据库-Oracle条件判断语句——Alive_2020
oracle sql语句中if/else功能的实现的3种写法——顺其自然~

-- 在oracle中
SELECT machine_id, 
	   ROUND(AVG(CASE activity_type WHEN 'start' THEN -timestamp ELSE timestamp end)*2, 3) AS processing_time 
FROM Activity 
group by machine_id;

4. 窗口函数

OLAP是OnLine Analytical Processing 的简称，意思是对数据库数据进行实时分析处理。
例如，市场分析、创建财务报表、创建计划等日常性商务工作。
窗口函数就是为了实现OLAP 而添加的标准SQL功能。

窗口函数也称为OLAP函数。

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。

4.1 窗口函数的语法

-- 窗口函数的语法
<窗口函数> OVER (PARTITION BY <分区依据列清单>
				 ORDER BY <排序用列清单 ASC(默认，升序)/DESC> -- 也可称为排序键
				ROWS/RANGE <确定每个窗口内 框架范围（即汇总范围）的表达式>  )

OVER： 关键字，表示前面的函数是OLAP实时分析处理函数，不是普通的聚合函数。

OVER关键字后面的半角括号中，即为窗口的定义子句。

4.1.1 窗口的定义子句

窗口的定义子句中可以包含三部分：

（1）PARTITION BY <分区依据列清单>
—— 将整个表中的所有记录按分区依据列进行分组，可省略。
通过PARTTION BY分组后，每组所有记录的集合称为窗口。将 “窗口” 理解为包含同一种类数据的范围。

与通过GROUP BY子句分割后的组一样，通过 PARTTION BY 分割后的各个窗口在定义上绝对不会包含共通的部分。就像刀切蛋糕一样，干净利落。

这里“窗口”代表的范围，也即为后面ORDER BY子句的作用范围。

在所有类别的窗口函数中，不指定 PARTITION BY 时的结果：
和使用没有GROUP BY子句的SELECT语句时的效果一样，也就是将整个表作为一个大的窗口来使用。

【分区和分组、分类都一个意思，此处叫分区仅是为了避免与GROUP BY子句常用的“分组”混淆。
这里的 “窗口” 跟组、类也都是一个意思，此处叫 “窗口” 也是为了避免与GROUP BY子句常用的“组”混淆。 】

常见考点：
使用 窗口聚合函数和PARTITION BY 之后，与使用 聚合函数和GROUP BY子句 之后不同，都不会使表中的记录减少。

知识点①： PARTITION BY 子句和GROUP BY子句一样具备分组功能，但是PARTITION BY 子句并不具备 GROUP BY子句具备的汇总功能。
问题②： 聚合函数和聚合窗口函数的区别？
当把聚合函数当作聚合窗口函数使用时，聚合窗口函数仍然具备对每个窗口的每个框架中，参数字段的所有记录进行汇总（输入多行，输出一行）和计算的功能，但不会使原表中的记录变少。
知识点③： 其它专用窗口函数也不会使原表中的记录变少。

（2）ORDER BY <排序键 ASC(默认，升序)/DESC>
——将每个窗口中的记录按排序键及指定的顺序进行排序

可以设有多个排序键，也可以直接省略ORDER BY子句。

PARTITION BY 在横向上对表进行分组；
ORDER BY 决定了每个窗口中记录纵向上排序的规则。

当ORDER BY子句省略时：窗口中的数据因为没有排序依据，呈无序状态。
此时，后面的框架子句无论是使用ROWS还是RANGE，都必须省略。
（省略后代表的框架详细内容，见下面Frame框架子句）

（3）ROWS/RANGE <确定每个窗口内 框架范围（即汇总范围）的表达式>
——— Frame框架子句，一般和窗口聚合函数一起使用。

用来在窗口中指定随着当前记录的变化，窗口聚合函数在当前窗口内更加详细、具体的作用范围（即：在不改变原表中记录数的情况下，进行汇总和计算的范围）。
将这种汇总范围称为框架。

框架设定好了就不会变了，但被作为汇总对象的记录范围会根据设定的框架，随着当前记录的变化而变化。
但无论使用哪种框架子句，对每条当前记录，都不会跨出当前窗口选取汇总记录。

注：当前记录所在窗口，本节中都简称为 “当前窗口” 。

当不省略ORDER BY子句时，可省略Frame框架子句；
或将Frame框架子句与ORDER BY子句一起省略。

Frame框架子句不能脱离ORDER BY子句而独自存在。

当不省略ORDER BY子句时，可省略Frame框架子句：

ORDER BY <一个/多个排序键> 等价于
ORDER BY <一个/多个排序键> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

代表的框架范围为：当前窗口中，第一行记录排序键的值至当前行排序键的值。

当把ORDER BY子句和Frame框架子句都省略时，即 OVER(PARTITION BY <分区键>) 时聚合函数计算出来的结果， 等价于：
OVER ( PARTITION BY <分区键> ORDER BY <排序键> ROWS/RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

每个窗口中的数据因为没有排序依据，呈无序状态。
代表框架是：当前窗口中的所有记录。
ROWS：当前窗口第一行记录到最后一行记录，即当前窗口中的所有记录。
RANGE：当前窗口从第一行记录排序键的值至最后一行记录排序键的值，也即当前窗口中的所有记录。

对此时RANGE框架子句的说明：

除了起点和终点都是PRECEDING或者PRECEDING的两种情况，在其余情况下： 起点必须和终点之间满足某种大小的排序关系，即 起点<终点 。
因此可以认为，在框架的设定上，并没有严格要求 起点<终点。

所以此处同时省略ORDER BY子句和Frame框架子句时，对于使用RANGE关键字的框架情况：在当前窗口中，从第一行记录排序键的值至最后一行记录排序键的值这个范围，也可以当作其就是指当前窗口的所有记录，而不用去管起点和终点的值。
另一种比较官方的解释，见4.1.2.1 用到的关键字及其含义 中对RANGE关键字的介绍。

窗口定义子句中的ORDER BY只是用来决定
窗口函数在每个窗口中按照什么样的顺序进行计算的，对SELECT语句的最终结果的排列顺序并没有影响。

有些DBMS也可以按照窗口函数的ORDER BY子句所指定的顺序对SELECT语句的结果进行排序，但那也仅仅是个例而已。

尽管这两个ORDER BY看上去是相同的，但其实它们的功能却完全不同。

如果要对SELECT语句的最终结果进行排序，那么要在SELECT 语句的最后，单独使用ORDER BY子句进行指定。

4.1.2 Frame框架子句

Oracle窗口函数详解——angu204

【明确 当前记录/行的 以下几个维度的信息（不是全部，用到哪个要清楚哪个）：
① 属于哪个窗口；
② 窗口中的哪个行/记录（行数多少）；
③ 窗口定义子句的ORDER BY中使用了一个还是多个排序键；
④ 具体是哪个/哪几个排序键（每个窗口的排序依据）；
⑤ 当前记录排序键的值为多少。 】

-- / 都是或者的意思，只能选择一个
-- 以下终点和起点的各种类型可自由搭配
{ ROWS / RANGE }
BETWEEN
  {UNBOUNDED PRECEDING  / N PRECEDING / CURRENT ROW } 
AND
  {UNBOUNDED FOLLOWING / N FOLLOWING / CURRENT ROW } 

--当终点是CURRENT ROW时，可省略
{ ROWS / RANGE }
BETWEEN    --省略
{ UNBOUNDED PRECEDING  / N PRECEDING / CURRENT ROW } -- 起点中不能出现FOLLOWING
AND   --省略
{ CURRENT ROW } --当终点是CURRENT ROW时，可省略

等价于

{ ROWS / RANGE }
{UNBOUNDED PRECEDING  / N PRECEDING / CURRENT ROW} -- 默认是起点
''' 在Oracle中，没有BETWEEN..AND关键字，只有一个点且不含FOLLOWING，则默认是起点
终点默认为CURRENT ROW（所以起点中不能出现FOLLOWING）
见 4.1.2.2 不同框架的表达方式中的（2）'''

-- 当起点是CURRENT ROW时，不论N2为多少，终点都不能是PRECEDING 

{ ROWS / RANGE }
BETWEEN
  { N1 PRECEDING } 
AND
  { N2 PRECEDING } --当终点是PRECEDING时，起点也必须是PRECEDING 
-- 且 N1与N2之间没有大小关系

{ ROWS / RANGE }
BETWEEN
  { N3 FOLLOWING }  -- 当起点是FOLLOWING时，终点也必须是FOLLOWING
AND
  { N4 FOLLOWING } 
-- 且N3与N4之间没有大小关系

4.1.2.1 用到的关键字及其含义

（1）ROWS和RANGE
ROWS和RANGE定义了一个窗口的每一行，都是用来指定在当前窗口内的框架范围的。
无论使用哪种框架子句，对每条当前记录，框架范围都不会超出当前窗口。

ROWS：代表物理范围，判断依据是当前记录行。
RANGE：代表逻辑范围，判断依据是在当前记录中ORDER BY 子句使用的排序键的值。
【RANGE子句定义了当前记录中排序键值的逻辑偏移量。】

窗口函数rows between 、range between的使用——卖山楂啦prss

使用RANGE关键字时，相同行会被合并成同一条数据再进行计算。
是否是相同行，是根据ORDER BY排序时的结果决定的。

有ORDER BY时：相同行是说在ORDER BY排序时不唯一的行。【即具有相同数值的行】
             不同行是说ORDER BY排序时具有不同的数值的行。
     
没有ORDER BY：
相比较于有ORDER BY时的情况，因为没有任何依据可以区分出每个窗口中的不同行，
因此整个窗口中的所有行都会被认为是“相同行”。
从而，对当前窗口中每条记录，都会将整个窗口中的所有记录作为框架范围。

（2）BETWEEN … AND ：定义当前窗口中框架的起点和终点。
AND之前的表达式：代表框架的起点；
AND之后的表达式：代表框架的终点。

【除了起点和终点都是PRECEDING或者PRECEDING的两种情况，在其余情况下：
起点必须和终点之间满足某种大小的排序关系。即 起点<终点 】

（3）PRECEDING 和 FOLLOWING
PRECEDING：在…之前。
FOLLOWING：在…之后。

PRECEDING / FOLLOWING 前面可以修饰的内容有：N 和 UNBOUNDED 两种。

① 在Oracle中，N可以是数字，也可以是一个能计算出数字的表达式。但只能是数字。
当使用RANGE关键字，且N是数字或者是能计算出数字的表达式，
要使用 N PRECEDING 和 N FOLLOWING 作为起点或终点，还必须满足：
ORDER BY 后面的排序键只有一个，且为整数型或日期类型。

【使用ROWS关键字 和 N PRECEDING / FOLLOWING搭配时，无论是对排序键的个数，还是类型，都没有限制。】

在MySQL中，N还可以是interval类型的。

在数据库中，interval是一种数据类型，用于表示时间间隔或时间差。
它可以用来计算两个日期之间的时间间隔，或者在某个日期上增加或减少一定的时间。
数据库interval什么意思——worktile

在MySQL中：
若N是数字类型，那么在RANGE中使用 N PRECEDING 和 N FOLLOWING的要求，同Oracle中一样；
若N是时间间隔类型，那么 ORDER BY 后面的排序键只能有一个，且必须为日期类型。
MySQL窗口函数RANGE日期范围——CSDN文库

② UNBOUNDED：不受控制的，无限的

N PRECEDING：既能做起点，也能做终点
与ROWS连用时，代表当前窗口中，当前记录之前N行的记录；
与RANGE连用时，代表当前窗口中，当前记录排序键的值减N的值。

UNBOUNDED PRECEDING：只能做起点
与ROWS连用时，代表起点为当前窗口中的第一行记录；
与RANGE连用时，代表起点为当前窗口中，第一行记录排序键的值。

N FOLLOWING：既能做起点，也能做终点
与ROWS连用时，代表当前窗口中，当前记录之后N行的记录；
与RANGE连用时，代表当前窗口中，当前记录排序键的值加N的值。

UNBOUNDED FOLLOWING：只能做终点
与ROWS连用时，代表终点为当前窗口中的最后一行记录；
与RANGE连用时，代表终点为当前窗口中，最后一行记录排序键的值。

（4）CURRENT ROW
与ROWS连用时，代表当前窗口中，当前记录行；
与RANGE连用时，代表当前窗口中，当前记录排序键的值。

CURRENT ROW 同 N PRECEDING 和 N FOLLOWING一样，
既可以设置成起点，也可以设置成终点。

4.1.2.2 不同框架的表达方式

只列举其中一些，帮助理解，不包含所有情况。

（1）BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：
与ROWS连用时，代表当前窗口中，第一行记录至当前行记录；
与RANGE连用时，代表当前窗口中，第一行记录排序键的值到当前行记录排序键的值这个范围内。

ORDER BY <一个/多个排序键> 等价于
ORDER BY <一个/多个排序键> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

注意：不等价于
ORDER BY <一个/多个排序键> ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

原因：使用RANGE关键字时，相同行（即ORDER BY中的排序键具有相同数值的行）会被合并成同一条数据再进行计算。 而相同情况下，使用ROWS关键字时，不会。

（2）BETWEEN N PRECEDING AND CURRENT ROW ：

与ROWS连用时，代表当前窗口中，当前行之前的N行至当前行。

此时，ROWS窗口子句，即 ROWS BETWEEN N PRECEDING AND CURRENT ROW
可简写成ROWS N PRECEDING.

与RANGE连用时，代表当前窗口中，当前行排序键的值减N的值到当前行排序键的值这个范围内。

此时，RANGE窗口子句，即 RANGE BETWEEN N PRECEDING AND CURRENT ROW
可简写成RANGE N PRECEDING.

（3）BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ：
与ROWS连用时，代表当前窗口中，当前行至最后一行 ；（不可省略任何部分）
与RANGE连用时，代表当前窗口中，当前行排序键的值到最后一行排序键的值这个范围内。 （不可省略任何部分）

（4）BETWEEN CURRENT ROW AND N FOLLOWING：
与ROWS连用时，代表当前窗口中，当前行至当前行之后的N行； （不可省略任何部分）
与RANGE连用时，代表当前窗口中，当前行排序键的值到当前行排序键的值加N的值这个范围内。 （不可省略任何部分）

（5）BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：
与ROWS连用时，代表当前窗口中，第一行到最后一行，即当前窗口中的所有记录；
与RANGE连用时，代表当前窗口中，第一行排序键的值到最后一行排序键的值这个范围内。

当ORDER BY子句省略时，此时后面的框架子句无论是ROWS还是RANGE，都必须省略。
即说明：当把ORDER BY子句和框架子句都省略时，两种框架子句也都是等价的。
即 OVER() 等价于：

OVER( ORDER BY <排序键> ROWS/RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

但此时，每个窗口中的数据因为没有排序依据，呈无序状态。

（6）BETWEEN N PRECEDING AND N FOLLOWING ：
与ROWS连用时，代表当前窗口中，当前行之前的N行至当前行之后的N行；
与RANGE连用时，代表当前窗口中，当前行排序键的值减N的值到当前行排序键的值加N的值这个范围内。

4.2 窗口函数的种类

1.排名窗口函数 和 3.取值窗口函数 为专用窗口函数，即为标准SQL定义的OLAP专用函数。

专用窗口函数无需参数，因此通常括号中都是空的。

窗口函数主要有三种：
1. 排名窗口函数
（1）RANK函数：相等重复，会跳过
返回当前行在分区中的名次，如果存在名词相同的数据，后序的排名将会产生跳跃。
例如：有 3 条记录排在第 1 位时：1 位、1位、1位、4位……

（2）DENSE_RANK函数：相等重复，不会跳过
返回当前行在分区中的名次，即使存在名词相同的数据，后序的排名也是连续值。
例如：有 3 条记录排在第 1 位时：1 位、1位、1位、2位……

（3）ROW_NUMBER函数：相等不重复，不会跳过
为每个窗口中的每个记录分配唯一的连续位次。
例如：有 3 条记录排在第 1 位时：1 位、2位、3位、4位……
当存在值相同的记录时，DBMS会根据适当的顺序对这些记录进行排列。

（4）PERCENT_RANK：
以百分比的形式返回当前行在分区中的名次（即排序后的位置）。
【名次/总的记录数*100%】
默认使用RANK函数返回的位次类型，因此如果存在名次相同的记录，那么将可能返回相同的百分比位次，且其后续返回的百分比排名将会产生跳跃。

（5）CUME_DIST：计算当前行在分区内的累计分部
（6）NTILE：将分区内的数据/记录分为N等份，并返回当前行所在的分片位置。

2. 聚合窗口函数
当聚合函数和窗口定义一起使用，即被当作窗口函数使用时，称为：聚合窗口函数。

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同，但需要在括号内指定作为汇总对象的列。

COUNT，MAX，MIN，SUM，AVG

因此，使用聚合窗口函数的窗口定义子句中，一般都不会省略Frame框架子句。
【即使省略的情况下，也是使用的默认框架。】

以 “自身记录（当前记录）” 作为基准进行统计，就是将聚合函数当作聚合窗口函数使用时的最大特征。

3. 取值窗口函数
（1）LAG：返回窗口内当前行之前的第N行数据；
（2）LEAD：返回窗口内当前行之后的第N行数据；
（3）FIRST_VALUE：返回窗口的第一行数据；
（4）LAST_VALUE：返回窗口的最后一行数据；
（5）NTH_VALUE：：返回窗口的第N行数据。

4.3 窗口函数的书写位置

只能在SELECT、 UPDATE 的SET子句、 ORDER BY子句中使用。
不能在FROM、 WHERE、 GROUP BY、HAVING子句中使用。

原因：
在DBMS内部，窗口函数是对WHERE子句或者GROUP BY子句、HAVING子句处理后的“结果”进行的操作。

在得到用户想要的结果之前，即使使用窗口函数，通过PARTITION BY 对表中的数据进行了分区，并通过ORDER BY对每个窗口中的记录进行了排序处理，结果也是错误的。

如在窗口函数中没有使用PARTITION BY，仅使用了ORDER BY ，并通过窗口函数中的ORDER BY得到了对表中所有记录的排序结果。

但之后，又通过WHERE子句中的条件除去了表中某些记录，或者又使用GROUP BY子句对表中的所有记录进行了分类及汇总处理，那好不容易通过窗口函数得到的排序结果也无法使用了。

正是由于这样的原因，所以在语法上才会有这样的限制。

反之，之所以在ORDER BY子句中能够使用窗口函数，是因为ORDER BY子句会在SELECT子句之后执行，并且保证不会减少结果记录。

4.4 计算移动平均

“计算移动平均”的统计方法，在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

-- 将框架指定为截止到当前记录之前2行（最靠近的3行）
SELECT product_id, product_name, sale_price,
       ROUND(AVG (sale_price) OVER (ORDER BY product_id
                                ROWS 2 PRECEDING),2) AS moving_avg
  FROM Product;

-- 将框架指定为截止到当前记录之后2行（最靠近的3行）
SELECT product_id, product_name, product_type, sale_price,
       ROUND(AVG (sale_price) OVER (ORDER BY product_id ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING),2) AS moving_avg
  FROM Product;

-- 将框架指定为当前记录及其前后1行
SELECT product_id, product_name, sale_price,
       ROUND(AVG (sale_price) OVER (ORDER BY product_id
                                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),2) AS moving_avg
  FROM Product;

5. GROUPING运算符

合计行是不指定聚合键时得到的汇总结果。

-- 如使用如下方式得到合计
SELECT '合计' AS product_type, SUM(sale_price)
  FROM Product
UNION ALL
SELECT product_type, SUM(sale_price)
  FROM Product
GROUP BY product_type;

GROUPING运算符包含以下 3 种：
● ROLLUP
● CUBE
● GROUPING SETS

5.1 ROLLUP——同时得出合计和小计

GROUP BY 有按聚合键分类的功能，也有将各组中多个相同的聚合键汇总为一行的功能。

ROLLUP必须和GROUP BY 一起使用。
ROLLUP是“卷起”的意思，比如卷起百叶窗、窗帘卷等等。
ROLLUP有依次取消/卷起由GROUP BY 中最后一个聚合键所产生的分组的功能，也有将新分组中多个相同的剩余聚合键汇总为一行的功能。

RULLUP 同 GROUP BY 的执行顺序一样，在FROM、WHERE 后面，非常靠前。
具体来说是：FROM → WHERE → GROUP BY → HAVING → SELECT → RULLUP(执行多次) → SELECT(执行多次)
【对表中数据使用的聚合键个数 和 RULLUP的执行次数、表中数据的聚合级以及聚合单位 都成反比。】

GROUP BY (A1,A2,…An)

将表中的数据按原本所有的聚合键A1,A2,…An进行分组，并将每组中多个相同的聚合键进行汇总

→ 有HAVING子句就执行 → SELECT
→ RULLUP（第一次执行）→ SELECT

卷起由最后一个聚合键An产生的分组，并对新分组（称为“超级分组”）中多个相同的剩余聚合键A1,A2,…An-1进行汇总（这些聚合键的GROUPING函数返回值仍为0.），
将去掉的聚合键An显示为NULL（这些聚合键的GROUPING函数返回值为1.）

→ RULLUP（第二次执行）→ SELECT

卷起由最后一个聚合键An-1产生的分组，并对新分组（称为“超级分组”）中多个相同的剩余聚合键A1,A2,…An-2进行汇总（这些聚合键的GROUPING函数返回值仍为0.），
将去掉的聚合键An和An-1显示为NULL（这些聚合键的GROUPING函数返回值为1.）

→ …
→ RULLUP（第n次执行）→ SELECT

卷起由最后一个聚合键A1产生的分组，整个表的数据都在一组（仍为“超级分组”）中，没有了任何聚合键。将去掉的聚合键An, An-1…A1均显示为NULL（这些聚合键的GROUPING函数返回值均为1.）

---

-- 实例 
--Oracle, SQL Server, DB2
SELECT product_type, regist_date, SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

--在MySQL中，要将最后依据改为GROUP BY product_type, regist_date WITH ROLLUP;

【这段代码整个执行结果中每一行都是对分组后各小组中所有记录的聚合键及sale_price字段的汇总结果。】

执行顺序：
①GROUP BY (product_type, regist_date) → SELECT
→ ② RULLUP（卷regist_date） → SELECT
→ ③ RULLUP（卷product_type) → SELECT

其中，①所有未标色的行：也是最先计算出来的结果行

GROUP BY (product_type, regist_date)

原本有product_type 和regist_date两个聚合键字段，由GROUP BY子句先按照所有这两个聚合键对原表中的数据进行分组，然后再对每组中多个相同的这两个聚合键进行汇总。
sum_price是由SUM函数对上面每组内所有的sale_price字段值进行汇总和求和计算小计的结果。

此时使用的聚合键最多，数据被分成的组数就最多，表中的记录最分散。
且每个分组之间都完全相互独立，互不重复。
即：聚合程度最低，聚合级最小，聚合单位最小。

②标红的行：是第一次执行ROLLUP

GROUP BY (product_type)

卷起由最后一个聚合键regist_date产生的分组，得到对原表中数据的新分组，然后再对每个新组中多个相同的剩余聚合键product_type进行汇总的结果。所以regist_date这个字段值下面都为NULL.
sum_price是由SUM函数对新分组中每个组所有的sale_price字段值进行汇总和求和计算小计的结果。

使用的聚合键越少，分成的组数越少，表中的记录就越聚集。
即：聚合程度就越高，聚合级越大，聚合单位越大。

③标绿的行：是第二次执行ROLLUP

GROUP BY ()

卷起由最后一个聚合键product_type产生的分组。此时，没有了任何聚合键，得到的新分组即为原表中的所有数据，因此也无法再对剩余聚合键进行汇总。所以product_type 和regist_date两个字段值下面都为NULL.
sum_price是由SUM函数对原表中所有的sale_price字段值进行汇总和求和计算总计的结果。

随着聚合键减少到0，数据将会恢复到表中记录的最初情况，所有的记录都聚集到一起，处在同一组中，此时表中的记录就最聚集。
即：聚合程度最高，聚合级最大，聚合单位最大。

5.1.1 GROUPING函数——让NULL更加容易分辨

GROUPING函数只能在使用ROLLUP或CUBE的SELECT查询中使用。

只要是 由减少了原本的聚合键字段 所产生的分组，都是超级分组。

该函数在其参数列的值 为超级分组中除该分组正在使用的聚合键之外 的字段 的记录所产生的NULL时返回1，其他情况返回0.

具体而言：
GROUP BY使用原本的所有聚合键的对原表中所有记录进行分组，其产生的分组均为/称为普通分组。
在普通分组中，即使某个字段的值为NULL，GROUPING函数以这些字段为参数，其返回值仍为0.

只要开始执行ROLLUP函数，即开始减少原本的聚合键（卷起由原本聚合键中最后一个产生的分组），从而产生的新分组，都是超级分组。
因此，ROLLUP函数汇总的对象均为超级分组。

对每个超级分组中 除还在使用的剩余聚合键之外 的字段值，ROLLUP都会返回NULL，当GROUPING函数以这些字段为参数时，其返回值就为1.
当该超级分组中的 还在使用的聚合键为NULL时，该聚合键的GROUPING函数返回值仍为0.

从执行结果往执行过程推导，来分辨是来自超级分组记录中的NULL，还是来自原始数据本身的NULL：
【原始数据本身的NULL经过GROUP BY分类及汇总后，都会成为普通分组中的NULL.】
在某一条结果记录中，所有的以聚合键为参数的GROUPING函数返回值：
①全部为0，则说明该条结果记录是来自于普通分组的汇总结果。
如下图中未标色的记录。
②只要有一个为1，则说明该条结果记录是来自于超级分组的汇总结果。
如下图中标红色的记录。
【因为如果当前超级分组还在使用某个值为NULL的聚合键，那么该聚合键的GROUPING函数返回值仍为0. 因此，可以确定那些GROUPING函数返回值为0的聚合键，就一定是当前超级分组还在使用的聚合键。】
③全为1时，说明该条记录就一定是对原表中所有记录的汇总结果。且只会有一条全为1的记录。
如下图中标绿色的记录。

因为ROLLUP是从最后一个聚合键的分组开始卷起的，因此按照SQL语句中GROUP BY子句中原本聚合键的倒序，对GROUPING函数的返回值依次进行判断更方便一些。

-- 使用GROUPING函数来判断NULL
--Oracle, SQL Server, DB2
SELECT GROUPING(product_type) AS product_type, 
         GROUPING(regist_date) AS regist_date, 
         SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

-- 在超级分组记录的键值中插入恰当的字符串
--Oracle, SQL Server, DB2
SELECT CASE WHEN GROUPING(product_type) = 1 
            THEN '商品种类 合计' 
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1 
            THEN '登记日期 合计' 
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
            -- CASE表达式的返回值必须一致
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY ROLLUP(product_type, regist_date);

5.2 CUBE——用数据来搭积木

所谓CUBE，就是将GROUP BY子句中聚合键的“所有可能的组合” 的汇总结果集中到一个结果中。因此，组合的个数就是 2^n 个。

执行顺序：
FROM → WHERE
→ GROUP BY CUBE

将A1,A2,…An，n个聚合键的每一种组合（由多到少，共2^n种）都执行一次GROUP BY 对原表的分类及汇总操作。

→ SELECT

每执行GROUP BY CUBE一次，就执行SELECT一次，以便用SELECT中的聚合函数SUM对每组中的相应字段进行汇总求和)

GROUP BY 发挥的仍然是将原表中的记录按聚合键分组，以及将各组中多个相同的聚合键值汇总为一行的功能。
CUBE影响的是每次执行BROUP BY使用的聚合键组合，以及执行次数。

-- 使用CUBE取得全部组合的结果
--Oracle, SQL Server, DB2
SELECT CASE WHEN GROUPING(product_type) = 1 
            THEN '商品种类 合计'
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1 
            THEN '登记日期 合计'
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
       SUM(sale_price) AS sum_price
  FROM Product
 GROUP BY CUBE(product_type, regist_date);

5.3 GROUPING SETS——取得期望的积木

Oracle中grouping sets 操作符的使用，可以帮你生成多种分组统计结果——米多sir

Oracle GROUPING SETS——shangboerds

Oracle group by 扩展函数详解（grouping sets、rollup、cube）—— 鱼丸丶粗面

GROUPING SETS运算符可以用于从ROLLUP或者CUBE的结果中取出每个聚合键Ai单独对原表进行分类汇总的结果。

只取GROUP BY(Ai)，i = 1, 2, …n

可以排除：
① 小计或总计记录（所有的合计记录）；
② 使用多个（≥2个）聚合键对原表进行分类汇总的记录。

GROUP BY GROUPING SETS (A,B,C)  

等价与  

GROUP BY A  
  UNION ALL  
GROUP BY B  
  UNION ALL  
GROUP BY C

-- 使用GROUPING SETS取得部分组合的结果
--Oracle, SQL Server, DB2
SELECT CASE WHEN GROUPING(product_type) = 1 
            THEN '商品种类 合计'
            ELSE product_type END AS product_type,
       CASE WHEN GROUPING(regist_date) = 1 
            THEN '登记日期 合计'
            ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
       SUM(sale_price) AS sum_tanka
  FROM Product
 GROUP BY GROUPING SETS (product_type, regist_date);

-- 等价于：
-- 按商品种类分组
SELECT
  product_type,
  '登记日期 合计' AS regist_date,
  SUM(sale_price) AS sum_tanka
FROM
  Product
GROUP BY
  product_type

UNION ALL

-- 按登记日期分组
SELECT
  '商品种类 合计' AS product_type,
  CAST(regist_date AS VARCHAR(16)) AS regist_date,
  SUM(sale_price) AS sum_tanka
FROM
  Product
GROUP BY
  regist_date;