window function是用来避免self join.
举个例子来说明:
对上表中的数据按CLASS进行排序。得到以下结果:
可以用window function和self join两种方式来实现:
self join:
SELECT A.CLASS,A.SCORE,COUNT(1) RANK FROM S A JOIN
(SELECT CLASS,SCORE FROM S GROUP BY CLASS,SCORE) B
ON A.CLASS=B.CLASS AND A.SCORE<=B.SCORE
GROUP BY A.CLASS,A.SCORE,A."$rowid$"
rowid是hana中的隐藏字段,用于表示该行记录创建或更新的序列,越晚创建或更新,其值越大。
window funcation:
SELECT CLASS,SCORE,(DENSE_RANK() over (partition by CLASS order by SCORE DESC)) dense_rank FROM S
window function比self join效率要高,它避免了self join。