ROW_NUMBER
ROW_NUMBER() –从1开始,按照顺序,生成分组内记录的序列
ROW_NUMBER() 的应用场景非常多,再比如,获取分组内排序第一的记录;SELECT id,
order_date,update_date,
state,
fee,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY update_date) AS num
FROM tmp_order
NTILE
NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值
NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀,默认增加第一个切片的分布
窗口内分为2组SELECT id,
order_date,
update_date,
state,
fee,
NTILE(2) OVER(PARTITION BY id ORDER BY update_date) AS rn1
FROM tmp_order
idorder_dateupdate_datestatefeern1110012017010120170101创建1501
210012017010120170104支付1501
310012017010120170106支付1501
410012017010120170107完成1502
510012017010120170108完成1502