teradata ANSI extension-QUALIFY

(–本文是个人学习和使用过程中的总结，如有错误欢迎指正 )

假设我们有下面表，STATISTICAL_DATE、PROVINCE_CODE、 TOTAL_SCORE三列，我们希望取出来每个PROVINCE_CODE下C列最小值 对应的数据

2015/9/30   |  11000000    |   80.7325
2015/10/31  |  11000000    |   80.667
2015/12/31  |  11000000    |   53.617
2015/11/30  |  11000000    |   81.0965
2015/10/31  |  12000000    |   267.243
2015/12/31  |  12000000    |   71.418
2015/9/30   |  12000000    |   370.147
2015/11/30  |  12000000    |   199.7055
2015/9/30   |  13000000    |   564.0975
2015/10/31  |  13000000    |   149.4765
2015/11/30  |  13000000    |   107.6975
2015/12/31  |  13000000    |   41.2635
2015/10/31  |  14000000    |   2,494.11
2015/9/30   |  14000000    |   2,185.12

我们可以使用JOIN实现

SELECT T1.STATISTICAL_DATE, T1.PROVINCE_CODE, T1.TOTAL_SCORE
  FROM XXX T1
 INNER JOIN (SELECT PROVINCE_CODE, MIN(TOTAL_SCORE) AS SCORE_MIN
               FROM XXX
              GROUP BY 1) T2
    ON T1.PROVINCE_CODE = T2.PROVINCE_CODE
   AND T1.TOTAL_SCORE = T2.TOTAL_SCORE

我们也可以使用ROW_NUMBER函数实现

SELECT *
  FROM (SELECT STATISTICAL_DATE,
               PROVINCE_CODE,
               TOTAL_SCORE,
               ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) AS SCORE_ROWNUMBER
          FROM XXX) T1
 WHERE T1.SCORE_ROWNUMBER = 1

teradata 扩展了qualify ，提供了一种更为简便的方式

SELECT STATISTICAL_DATE, PROVINCE_CODE, TOTAL_SCORE
  FROM XXX 
QUALIFY ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) = 1

或者可以这么写：

SELECT STATISTICAL_DATE,
       PROVINCE_CODE,
       TOTAL_SCORE,
       ROW_NUMBER() OVER(PARTITION BY PROVINCE_CODE ORDER BY TOTAL_SCORE) AS SCORE_RANK
  FROM XXX 
QUALIFY SCORE_RANK = 1

QUALIFY使用：

1. 不能使用在LOB字段上
2. 用于子查询不能使用OR
3. 必须和Ordered Analytical Functions一起使用

当WHERE, GROUP BY和QUALIFY顺序：WHERE –> GROUP–>QUALIFY

QUALIFY与WHERE、HAVING的不同在于QUALIFY和Ordered Analytical Functions一起使用。后面两中方式任何一种换做WHERE或HAVING都是报错的。