之前看到keep函数,一直不明白函数含义,找到一个英文版解释,感觉很清晰,特翻译下。
MIN ( sal ) KEEP ( DENSE_RANK FIRST ORDER BY sal ) OVER ( PARTITION BY deptno )
MIN ( sal ) KEEP ( DENSE_RANK FIRST ORDER BY sal ) OVER ( PARTITION BY deptno )
The statement can be considered in (roughly) right-to-left order:
这个语句可以被大致认为是从右到左排序
OVER (PARTITION BY deptno)
means partition the rows into distinct groups ofdeptno
; then- OVER (PARTITION BY deptno)意味着按照deptno分组
ORDER BY sal
means, for each partition, order the rows bysal
(implicitly usingASC
ending order); thenORDER BY sal
means意味着对于每个组,按照sal排序(默认?升序)KEEP (DENSE_RANK FIRST
means give an "olympic" rank to the ordered rows for each partition (rows with identical values for the ordering columns will be given the same rank) and discard all rows which are not ranked first; and finally- KEEP (DENSE_RANK FIRST 意味着对每个组用“奥林匹克”方式排序(具有相同值的行具有相同的排名),并舍弃所有不是排名第一的行。
MIN(sal)
for the remaining rows of each partition, return the minimum salary.- MIN(sal)对于每个组余下的行,保留其最小值
原帖地址:http://stackoverflow.com/questions/20145952/partition-by-with-and-without-keep-in-oracle