Oracle开窗分析函数,Oracle 惯用的分析开窗函数

SELECT T.EMAIL_USER_NAME,

COUNT(*) OVER(PARTITION BY T.EMAIL_MAIL ORDER BY T.EMAIL_TEL DESC) C

FROM T_BASE_EMAIL T;

SELECT T.EMAIL_USER_NAME,

RANK() OVER(PARTITION BY T.EMAIL_MAIL ORDER BY T.EMAIL_TEL DESC) C

FROM T_BASE_EMAIL T;

SELECT T.EMAIL_USER_NAME,

DENSE_RANK() OVER(PARTITION BY T.EMAIL_MAIL ORDER BY T.EMAIL_TEL DESC) C

FROM T_BASE_EMAIL T;

SELECT T.EMAIL_USER_NAME,

MAX(T.EMAIL_MAIL) OVER(PARTITION BY T.EMAIL_MAIL ORDER BY T.EMAIL_TEL DESC) C

FROM T_BASE_EMAIL T;

--常用的分析函数如下所列:

ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ...)

RANK() OVER(PARTITION BY ... ORDER BY ...)

DENSE_RANK() OVER(PARTITION BY ... ORDER BY ...)

COUNT() OVER(PARTITION BY ... ORDER BY ...)

MAX() OVER(PARTITION BY ... ORDER BY ...)

MIN() OVER(PARTITION BY ... ORDER BY ...)

SUM() OVER(PARTITION BY ... ORDER BY ...)

AVG() OVER(PARTITION BY ... ORDER BY ...)

FIRST_VALUE() OVER(PARTITION BY ... ORDER BY ...)

LAST_VALUE() OVER(PARTITION BY ... ORDER BY ...)

LAG() OVER(PARTITION BY ... ORDER BY ...)

LEAD() OVER(PARTITION BY ... ORDER BY ...)