HQL的几个排名函数用法

排序窗口函数

NTILE、ROW_NUMBER、RANK、DENSE_RANK

1、NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布
2、ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列
3、RANK()生成数据项在分组中的排名从1开始，排名相等会在名次中留下空位
4、DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

这4项都不支持row between and

NTILE

CREATE TABLE IF NOT EXISTS test_dp_price(
    id                    STRING COMMENT '店铺id',
    price                 BIGINT COMMENT '价格'
) COMMENT '店铺价格表';

INSERT INTO test_dp_price 
SELECT '1', 10
UNION
SELECT '2', 20
UNION
SELECT '3', 30
UNION
SELECT '4', 40
UNION
SELECT '5', 50
UNION
SELECT '6', 60
UNION
SELECT '7', 70
UNION
SELECT '8', 80
UNION
SELECT '9', 90
UNION
SELECT '10', 100
;



CREATE TABLE test_dp_price_rk AS
SELECT  id
        ,price
        ,NTILE(10) OVER (ORDER BY price DESC ) AS rn
FROM    test_dp_price
;

-- 2 按片取30%和70%，分别计算平均值 
SELECT  new_rn
        ,MAX(
            CASE    WHEN new_rn = 1 THEN 'avg_price_first_30%'
                    WHEN new_rn = 2 THEN 'avg_price_last_70%'
            END
        ) AS avg_price_name
        ,AVG(price) avg_price
FROM    (
            SELECT  id
                    ,price
                    ,rn
                    ,CASE   WHEN rn IN (1,2,3) THEN 1
                            ELSE 2
                    END AS new_rn
            FROM    test_dp_price_rk
        ) a
GROUP BY new_rn
;

SELECT  id
        ,price
        ,NTILE(3) OVER (ORDER BY price DESC ) AS rn
FROM    test_dp_price
;