(–本文是个人学习和使用过程中的总结,如有错误欢迎指正 )
假设我们有下面表,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使用:
- 不能使用在LOB字段上
- 用于子查询不能使用OR
- 必须和Ordered Analytical Functions一起使用
当WHERE, GROUP BY和QUALIFY顺序:WHERE –> GROUP–>QUALIFY
QUALIFY与WHERE、HAVING的不同在于QUALIFY和Ordered Analytical Functions一起使用。后面两中方式任何一种换做WHERE或HAVING都是报错的。