| <div class="codehilite"><pre><span></span><code><span class="c1">-- cume_dist</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">cume_dist</span><span class="p">()</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">cume_dist</span><span class="p">()</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="n">RANGE</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="n">UNBOUNDED</span><span class="w"> </span><span class="n">PRECEDING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">CURRENT</span><span class="w"> </span><span class="k">ROW</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">6666666666666666</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">6666666666666666</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- dense_rank</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">dense_rank</span><span class="p">(</span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">DENSE_RANK</span><span class="p">()</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="k">ROWS</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="n">UNBOUNDED</span><span class="w"> </span><span class="n">PRECEDING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">CURRENT</span><span class="w"> </span><span class="k">ROW</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- lag</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">lag</span><span class="p">(</span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+-----------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">lag</span><span class="p">(</span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="k">ROWS</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="w"> </span><span class="n">FOLLOWING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="o">-</span><span class="mi">1</span><span class="w"> </span><span class="n">FOLLOWING</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+-----------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="k">NULL</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="k">NULL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+-----------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- lead</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">lead</span><span class="p">(</span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">lead</span><span class="p">(</span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span><span class="w"> </span><span class="k">NULL</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="k">ROWS</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="n">FOLLOWING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="mi">1</span><span class="w"> </span><span class="n">FOLLOWING</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="k">NULL</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="k">NULL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- nth_value</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">nth_value</span><span class="p">(</span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+------------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">nth_value</span><span class="p">(</span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="n">RANGE</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="n">UNBOUNDED</span><span class="w"> </span><span class="n">PRECEDING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">CURRENT</span><span class="w"> </span><span class="k">ROW</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+------------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="k">NULL</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+------------------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- ntile</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">ntile</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">ntile</span><span class="p">(</span><span class="mi">2</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="k">ROWS</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="n">UNBOUNDED</span><span class="w"> </span><span class="n">PRECEDING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">CURRENT</span><span class="w"> </span><span class="k">ROW</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- percent_rank</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">percent_rank</span><span class="p">(</span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">PERCENT_RANK</span><span class="p">()</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="k">ROWS</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="n">UNBOUNDED</span><span class="w"> </span><span class="n">PRECEDING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">CURRENT</span><span class="w"> </span><span class="k">ROW</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="mi">0</span><span class="p">.</span><span class="mi">0</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+----------------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- rank</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">rank</span><span class="p">(</span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">RANK</span><span class="p">()</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="k">ROWS</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="n">UNBOUNDED</span><span class="w"> </span><span class="n">PRECEDING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">CURRENT</span><span class="w"> </span><span class="k">ROW</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------+</span> |
| |
| <span class="c1">-- row_number</span> |
| <span class="k">SELECT</span><span class="w"> </span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">,</span><span class="w"> </span><span class="n">row_number</span><span class="p">()</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="p">)</span><span class="w"> </span><span class="k">FROM</span><span class="w"> </span><span class="k">VALUES</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">2</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A2'</span><span class="p">,</span><span class="w"> </span><span class="mi">3</span><span class="p">),</span><span class="w"> </span><span class="p">(</span><span class="s1">'A1'</span><span class="p">,</span><span class="w"> </span><span class="mi">1</span><span class="p">)</span><span class="w"> </span><span class="n">tab</span><span class="p">(</span><span class="n">a</span><span class="p">,</span><span class="w"> </span><span class="n">b</span><span class="p">);</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">a</span><span class="o">|</span><span class="w"> </span><span class="n">b</span><span class="o">|</span><span class="n">row_number</span><span class="p">()</span><span class="w"> </span><span class="n">OVER</span><span class="w"> </span><span class="p">(</span><span class="n">PARTITION</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">a</span><span class="w"> </span><span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span><span class="w"> </span><span class="n">b</span><span class="w"> </span><span class="k">ASC</span><span class="w"> </span><span class="n">NULLS</span><span class="w"> </span><span class="k">FIRST</span><span class="w"> </span><span class="k">ROWS</span><span class="w"> </span><span class="k">BETWEEN</span><span class="w"> </span><span class="n">UNBOUNDED</span><span class="w"> </span><span class="n">PRECEDING</span><span class="w"> </span><span class="k">AND</span><span class="w"> </span><span class="k">CURRENT</span><span class="w"> </span><span class="k">ROW</span><span class="p">)</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A1</span><span class="o">|</span><span class="w"> </span><span class="mi">2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span> |
| <span class="o">|</span><span class="w"> </span><span class="n">A2</span><span class="o">|</span><span class="w"> </span><span class="mi">3</span><span class="o">|</span><span class="w"> </span><span class="mi">1</span><span class="o">|</span> |
| <span class="o">+</span><span class="c1">---+---+--------------------------------------------------------------------------------------------------------------+</span> |
| </code></pre></div> |
