row number,rank,densize rank差異

結果テーブルに任意の行番号を指定するときに使用する3つの方法

row_number

反復値(同点)でも、順番に番号が付けられます.

rank

が重複する値(同点)がある場合、その数をスキップして次の番号を付与する.

e.g.は、2位が共同1位となり、次は3位となった.

dense_rank

反復値(同点)があっても、その数を超えることなく、次の番号が連続して与えられる.

e.g.は、2位が共同1位になった後、次は2位にランクインした.

例
米野球でマリナーズが試合時間(分)で並んだ結果.

row_number

with 
t1 as (

    select 
                row_number() over( partition by homeTeamName order by duration_minutes desc ) seq,
                homeTeamName home_team_name,
                duration_minutes,

      from 
                `bigquery-public-data.baseball.schedules` a

     where      1=1

)
    select 
                *

      from 
                t1

     where      1=1
       and      seq <= 5
       and      home_team_name = 'Mariners'

結果

rank

with 
t1 as (

    select 
                rank() over( partition by homeTeamName order by duration_minutes desc ) seq,
                homeTeamName home_team_name,
                duration_minutes,

      from 
                `bigquery-public-data.baseball.schedules` a

     where      1=1

)
    select 
                *

      from 
                t1

     where      1=1
       and      seq <= 5
       and      home_team_name = 'Mariners'

結果

dense_rank

with 
t1 as (

    select 
                dense_rank() over( partition by homeTeamName order by duration_minutes desc ) seq,
                homeTeamName home_team_name,
                duration_minutes,

      from 
                `bigquery-public-data.baseball.schedules` a

     where      1=1

)
    select 
                *

      from 
                t1

     where      1=1
       and      seq <= 5
       and      home_team_name = 'Mariners'

結果