其他COUNT、AVG，MIN，MAX，和SUM用法一样。
FIRST_VALUE和LAST_VALUE案例分析 select user_id, user_type, sales, ROW_NUMBER() OVER(PARTITION BY user_type ORDER BY sales) AS row_num, first_value(user_id) over (partition by user_type order by sales desc) as max_sales_user, first_value(user_id) over (partition by user_type order by sales asc) as min_sales_user, last_value(user_id) over (partition by user_type order by sales desc) as curr_last_min_user, last_value(user_id) over (partition by user_type order by sales asc) as curr_last_max_user from orders order by user_type, sales; ##上述查询结果如下: | user_id | user_type | sales | row_num | max_sales_user | min_sales_user | curr_last_min_user | curr_last_max_user | | ------- | --------- | ----- | ------- | -------------- | -------------- | ------------------ | ------------------ | | tom | new | 1 | 1 | tom1 | tom | tom | liliu | | liliu | new | 1 | 2 | tom1 | tom | tom | liliu | | zhangsa | new | 2 | 3 | tom1 | tom | zhangsa | zhangsa | | wanger | new | 3 | 4 | tom1 | tom | wanger | wanger | | tom3 | new | 5 | 5 | tom1 | tom | tom3 | tom2 | | tom2 | new | 5 | 6 | tom1 | tom | tom3 | tom2 | | tom1 | new | 6 | 7 | tom1 | tom | tom1 | tom1 | | lisi | old | 1 | 1 | tomson | lisi | lisi | lisi | | tomas | old | 2 | 2 | tomson | lisi | tomas | tomas | | tomson | old | 3 | 3 | tomson | lisi | tomson | tomson | LEAD与LAG select user_id, device_id, sales, ROW_NUMBER() OVER(ORDER BY sales) AS row_num, lead(device_id) over (order by sales) as default_after_one_line, lag(device_id) over (order by sales) as default_before_one_line, lead(device_id,2) over (order by sales) as after_two_line, lag(device_id,2,'abc') over (order by sales) as before_two_line from orders order by sales; 上述查询结果如下 | user_id | device_id | sales | row_num | default_after_one_line | default_before_one_line | after_two_line | before_two_line | |----------|------------|--------|----------|-------------------------|--------------------------|-----------------|------------------| | lisi | test2 | 1 | 3 | test6 | test4 | test1 | test5 | | liliu | test4 | 1 | 2 | test2 | test5 | test6 | abc | | tom | test5 | 1 | 1 | test4 | NULL | test2 | abc | | zhangsa | test1 | 2 | 5 | test7 | test6 | test3 | test2 | | tomas | test6 | 2 | 4 | test1 | test2 | test7 | test4 | | wanger | test3 | 3 | 7 | test10 | test7 | test9 | test1 | | tomson | test7 | 3 | 6 | test3 | test1 | test10 | test6 | | tom2 | test9 | 5 | 9 | test8 | test10 | NULL | test3 | | tom3 | test10 | 5 | 8 | test9 | test3 | test8 | test7 | | tom1 | test8 | 6 | 10 | NULL | test9 | NULL | test10 | RANK、ROW_NUMBER、DENSE_RANK select user_id,user_type,sales, RANK() over (partition by user_type order by sales desc) as r, ROW_NUMBER() over (partition by user_type order by sales desc) as rn, DENSE_RANK() over (partition by user_type order by sales desc) as dr from orders; ##上述查询结果如下 | user_id | user_type | sales | r | rn | dr | | ------- | --------- | ----- | --- | --- | --- | | tom1 | new | 6 | 1 | 1 | 1 | | tom3 | new | 5 | 2 | 2 | 2 | | tom2 | new | 5 | 2 | 3 | 2 | | wanger | new | 3 | 4 | 4 | 3 | | zhangsa | new | 2 | 5 | 5 | 4 | | tom | new | 1 | 6 | 6 | 5 | | liliu | new | 1 | 6 | 7 | 5 | | tomson | old | 3 | 1 | 1 | 1 | | tomas | old | 2 | 2 | 2 | 2 | | lisi | old | 1 | 3 | 3 | 3 | NTILE select user_type,sales, --分组内将数据分成2片 NTILE(2) OVER(PARTITION BY user_type ORDER BY sales) AS nt2, --分组内将数据分成3片 NTILE(3) OVER(PARTITION BY user_type ORDER BY sales) AS nt3, --分组内将数据分成4片 NTILE(4) OVER(PARTITION BY user_type ORDER BY sales) AS nt4, --将所有数据分成4片 NTILE(4) OVER(ORDER BY sales) AS all_nt4 from orders order by user_type, sales; ##上述查询结果如下 | user_type | sales | nt2 | nt3 | nt4 | all_nt4 | | --------- | ----- | --- | --- | --- | ------- | | new | 1 | 1 | 1 | 1 | 1 | | new | 1 | 1 | 1 | 1 | 1 | | new | 2 | 1 | 1 | 2 | 2 | | new | 3 | 1 | 2 | 2 | 3 | | new | 5 | 2 | 2 | 3 | 4 | | new | 5 | 2 | 3 | 3 | 3 | | new | 6 | 2 | 3 | 4 | 4 | | old | 1 | 1 | 1 | 1 | 1 | | old | 2 | 1 | 2 | 2 | 2 | | old | 3 | 2 | 3 | 3 | 2 |