Sybase IQ常用函数大全--分析函数

Sybase IQ常用函数大全–分析函数

查询索引：

1. DENSE_RANK 函数：排列组中的项目。就是按照某一个条件排序，形成行号，如果值相等则行号相等，接下来的值不会跳一个行号，而会继续往下形成。
2. NTILE 函数：将查询结果分配到指定数量的表元中，并为表元中的每一行指派相应的表元号。就是给定一个数 n，将结果集切分成 n 等份，每份给一个相同的行号。
3. RANK 函数：排列组中的项目。就是按照某一个条件排序，形成行号，如果值相等则行号相等，接下来的值会跳一个行号，再继续往下形成。
4. ROW_NUMBER 函数：就是按照某一个条件排序，形成行号，不管值相不相等都会按顺序往下生成行号。

1. DENSE_RANK 函数

排列组中的项目。

语法：

DENSE_RANK () OVER ( ORDER BY <expression> [ ASC | DESC ] )

解析：

• 简而言之：就是按照某一个条件排序，形成行号，如果值相等则行号相等，接下来的值不会跳一个行号，而会继续往下形成。
• 这个与 RANK 函数类似，但是一个是跳过，一个是不跳过下一个行号

参数	说明
expression	排序规范，可以是涉及列引用、集合的任意有效表达式，也可以是调用这些项目的表达式。

• DENSE_RANK 是秩分析函数。行 R 的密集排名是指位于该行前面（包括行 R）并且在 OVER 子句指定的组中或者在整个结果集中保持不重复的行数。DENSE_RANK 和 DENSE_RANK 之间的区别是：当存在并列排名时，RANK 不会保留空位。存在并列排名时 RANK 会留空位。
• DENSE_RANK 需要使用 OVER (ORDER BY) 子句。ORDER BY 子句指定要执行排序的参数以及每个组中行的排列顺序。此 ORDER BY 子句只在 OVER 子句中使用，而不是 SELECT 的 ORDER BY。不允许排序查询中的任何集合函数指定 DISTINCT。
• OVER 子句表示函数对查询结果集进行操作。结果集是在对 FROM、WHERE、GROUP BY 和 HAVING 子句求值完成之后返回的行。OVER 子句定义要包括在秩分析函数计算中的行数据集。
• ASC 或 DESC 参数用于指定升序或降序排序序列。升序是缺省值。
• DENSE_RANK 只能在 SELECT 或 INSERT 语句的选择列表中使用，或者在 SELECT 语句的 ORDER BY 子句中使用。DENSE_RANK 可以在视图中或联合中使用。您不能在子查询中、HAVING 子句中，或者 UPDATE 或 DELETE 语句的选择列表中使用 DENSE_RANK 函数。每个查询仅允许使用一个秩分析函数。

示例：

SELECT s_suppkey, DENSE_RANK()
OVER ( ORDER BY ( SUM(s_acctBal) DESC )
AS rank_dense FROM supplier GROUP BY s_suppkey;

s_suppkey	sum_acctBal	rank_dense
supplier#011	200,000	1
supplier#002	200,000	1
supplier#013	123,000	2
supplier#004	110,000	3
supplier#035	110,000	3
supplier#006	50,000	4
supplier#021	10,000	5

2. NTILE 函数

将查询结果分配到指定数量的表元中，并为表元中的每一行指派相应的表元号。

语法：

NTILE ( <expression1> )
OVER ( ORDER BY <expression2> [ ASC | DESC ] )

解析：

• 简而言之：就是给定一个数 n，将结果集切分成 n 等份，每份给一个相同的行号。

参数	说明
expression1	1 到 32767 之间的一个常量整数，该整数指定表元的数量。
expression2	排序规范，可以是涉及列引用、集合的任意有效表达式，也可以是调用这些项目的表达式。

• NTILE 需要使用 OVER (ORDER BY) 子句。ORDER BY 子句指定要执行排序的参数以及每个组中行的排列顺序。此 ORDER BY 子句只在 OVER 子句中使用，而不是 SELECT 的 ORDER BY。不允许排序查询中的任何集合函数指定 DISTINCT。
• OVER 子句表示函数对查询结果集进行操作。结果集是在对 FROM、WHERE、GROUP BY 和 HAVING 子句求值完成之后返回的行。OVER 子句定义要包括在秩分析函数计算中的行数据集。
  ASC 或 DESC 参数用于指定升序或降序排序序列。升序是缺省值。
• NTILE 只能在 SELECT 或 INSERT 语句的选择列表中使用，或者在 SELECT 语句的 ORDER BY 子句中使用。NTILE 可以在视图中或联合中使用。您不能在子查询、HAVING 子句或者 UPDATE 或 DELETE 语句的选择列表中使用 NTILE 函数。每个查询仅允许使用一个 NTILE 函数。

示例：

以下示例使用 NTILE 函数来确定汽车经销商的销售状态。根据各位经销商售出的汽车数量将经销商分为四组。ntile 为 1 的经销商在汽车销售量排名中位于前 25%。

SELECT dealer_name, sales,
NTILE(4) OVER ( ORDER BY sales DESC )
FROM carSales;

dealer_name	sales	ntile
Boston	1000	1
Worcester	950	1
Providence	950	1
SF	940	1
Lowell	900	2
Seattle	900	2
Natick	870	2
New Haven	850	2
Portland	800	3
Houston	780	3
Hartford	780	3
Dublin	750	3
Austin	650	4
Dallas	640	4
Dover	600	4

3. RANK 函数

排列组中的项目。

语法：

RANK () OVER ( [ PARTITION BY ] ORDER BY < expression> [ ASC | DESC ] )

解析：

• 简而言之：就是按照某一个条件排序，形成行号，如果值相等则行号相等，接下来的值会跳一个行号，再继续往下形成。
• 这个与 DENSE_RANK 函数类似，但是一个是跳过，一个是不跳过下一个行号

参数	说明
expression	排序规范，可以是涉及列引用、集合的任意有效表达式，也可以是调用这些项目的表达式。

• RANK 是秩分析函数。行 R 的秩是指位于 R 前面但不是 R 的对等行的行数。如果有两个或多个行在 OVER 子句指定的组中或者在整个结果集中是不重复的，则连续秩编号中存在一个或多个空位。RANK 和 DENSE_RANK 之间的区别是： **当存在并列排名时，DENSE_RANK 不会保留空位。存在并列排名时 RANK 会留空位。 **
• RANK 需要使用 OVER (ORDER BY) 子句。ORDER BY 子句指定要执行排序的参数以及每个组中行的排列顺序。此 ORDER BY 子句只在 OVER 子句中使用，而不是 SELECT 的 ORDER BY。不允许排序查询中的任何集合函数指定 DISTINCT。
• OVER (ORDER BY) 子句中的 PARTITION BY 窗口分区子句是可选的。
  ASC 或 DESC 参数用于指定升序或降序排序序列。升序是缺省值。
• OVER 子句表示函数对查询结果集进行操作。结果集是在对 FROM、WHERE、GROUP BY 和 HAVING 子句求值完成之后返回的行。OVER 子句定义要包括在秩分析函数计算中的行数据集。
• RANK 只能在 SELECT 或 INSERT 语句的选择列表中使用，或者在 SELECT 语句的 ORDER BY 子句中使用。RANK 可以在视图中或联合中使用。您不能在子查询、HAVING 子句或者 UPDATE 或 DELETE 语句的选择列表中使用 RANK 函数。每个查询仅允许使用一个秩分析函数。

示例：

SELECT Surname, Sex, Salary, RANK() OVER (PARTITION BY Sex
ORDER BY Salary DESC) AS RANK FROM Employees
WHERE State IN ('CA', 'AZ') AND DepartmentID IN (200, 300)
ORDER BY Sex, Salary DESC;

Surname	Sex	Salary	RANK
Savarino	F	72300.000	1
Jordan	F	51432.000	2
Clark	F	45000.000	3
Coleman	M	42300.000	1
Overbey	M	39300.000	2

4. ROW_NUMBER 函数

一个秩函数，它为窗口分区中的每一行返回一个唯一行号，以便在每个窗口分区开始时对行重新进行编号。如果窗口分区不存在，则该函数按从 1 到表基数的顺序对结果集中的行进行编号。

语法：

ROW_NUMBER() OVER ([PARTITION BY <window partition>] ORDER BY <window ordering>)

解析：

• 简而言之：就是按照某一个条件排序，形成行号，不管值相不相等都会按顺序往下生成行号。

参数	说明
window partition	（可选）一个或多个用逗号分隔的值表达式，表示您希望如何分隔一组结果行。
窗口排序（window ordering）	如果指定窗口分区，则定义用于对窗口分区中的行进行排序的表达式；如果未指定窗口分区，则定义用于对结果集中的行进行排序的表达式。

• ROW_NUMBER 函数需要 OVER (ORDER_BY) 窗口规范。OVER (ORDER_BY) 子句中的窗口分区子句是可选的。OVER (ORDER_BY) 子句不能包含窗口构架 ROWS/RANGE 规范。

示例：

以下示例返回 Employees 表中的薪水数据，按部门 ID 对结果集进行分区，并根据雇用开始日期对数据进行排序。ROW_NUMBER 函数为每一行指派一个行号，并对每个窗口分区中的行重新开始编号：

SELECT DepartmentID dID, StartDate, Salary, ROW_NUMBER()OVER(PARTITION BY dID ORDER BY StartDate) FROM Employees ORDER BY 1,2;

dID	StartDate	Salary	Row_number()
100	1986-10-14	42,998.000	1
100	1987-07-23	39,875.500	2
100	1988-03-23	37,400.000	3
100	1989-04-20	42,500.000	4
100	1990-01-15	42,100.000	5
200	1985-02-03	38,500.000	1
200	1987-02-19	39,300.000	2
200	1988-11-22	39,800.000	3
200	1989-06-01	34,892.000	4
200	1990-05-13	33,890.000	5
200	1990-07-11	37,803.000	6