select class,student from(
select student,dense_rank()
over(partition by studnet order by score) as rn from test
)t1
where rn=2

percent_rank()计算给定行的百分比排名。可以用来计算超过了百分之多少的人
（当前行的rank值-1）/(分组内的总行数-1)
select name,subject,score,
row_number() over(partition by subject order by score) as row_number,
percent_rank() over(partition by subject order by score) as percent_rank
from t_fraction

select user_id,month_id,visit_cnt_1m,
sum(visit_cnt_1n) over (partition by user_id order by month_id) as visit_cnt_td
from
(select id,month_id,sum(vicitCount) visit_cnt_1m
from
(select user_id,substr(visit_date,1,7) as month_id,visit_count
from test
)t1
group by month_id,visit_date)

select
  max(s) cn
from
  (
    select
      user_id,
      event_ts,
      sum(cnt) over (
        order by
          event_ts rows between unbounded preceding
          and current row
      ) s
    from
      (
        select
          user_id,
          login_ts event_ts,
          1 cnt
        from
          user_login_detail
        union all
        select
          user_id,
          logout_ts,
          -1
        from
          user_login_detail
      ) t1
  ) t2