1#@!#!123s

: / home / tattooscyy / api / vendor / scrivo / highlight.php / test / markup / pgsql /
Filename : window-functions.expect.txt
back
<span class="hljs-comment">-- window functions</span>

<span class="hljs-comment">-- frame clause:</span>

<span class="hljs-keyword">RANGE</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">EXCLUDE</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span>,
<span class="hljs-keyword">RANGE</span> <span class="hljs-number">10</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">EXCLUDE</span> <span class="hljs-keyword">GROUP</span>,
<span class="hljs-keyword">RANGE</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span> <span class="hljs-keyword">EXCLUDE TIES</span>,
<span class="hljs-keyword">RANGE</span> <span class="hljs-number">10</span> <span class="hljs-keyword">FOLLOWING</span> <span class="hljs-keyword">EXCLUDE NO OTHERS</span>,
<span class="hljs-keyword">RANGE</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">FOLLOWING</span>,
<span class="hljs-keyword">RANGE</span> <span class="hljs-keyword">BETWEEN</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span>;

<span class="hljs-keyword">ROWS</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">EXCLUDE</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span>,
<span class="hljs-keyword">ROWS</span> <span class="hljs-number">10</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">EXCLUDE</span> <span class="hljs-keyword">GROUP</span>,
<span class="hljs-keyword">ROWS</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span> <span class="hljs-keyword">EXCLUDE TIES</span>,
<span class="hljs-keyword">ROWS</span> <span class="hljs-number">10</span> <span class="hljs-keyword">FOLLOWING</span> <span class="hljs-keyword">EXCLUDE NO OTHERS</span>,
<span class="hljs-keyword">ROWS</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">FOLLOWING</span>,
<span class="hljs-keyword">ROWS</span> <span class="hljs-keyword">BETWEEN</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span>;

<span class="hljs-keyword">GROUPS</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">EXCLUDE</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span>,
<span class="hljs-keyword">GROUPS</span> <span class="hljs-number">10</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">EXCLUDE</span> <span class="hljs-keyword">GROUP</span>,
<span class="hljs-keyword">GROUPS</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span> <span class="hljs-keyword">EXCLUDE TIES</span>,
<span class="hljs-keyword">GROUPS</span> <span class="hljs-number">10</span> <span class="hljs-keyword">FOLLOWING</span> <span class="hljs-keyword">EXCLUDE NO OTHERS</span>,
<span class="hljs-keyword">GROUPS</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">FOLLOWING</span>,
<span class="hljs-keyword">GROUPS</span> <span class="hljs-keyword">BETWEEN</span> <span class="hljs-keyword">UNBOUNDED</span> <span class="hljs-keyword">PRECEDING</span> <span class="hljs-keyword">AND</span> <span class="hljs-keyword">CURRENT</span> <span class="hljs-keyword">ROW</span>;

<span class="hljs-comment">-- examples</span>

<span class="hljs-keyword">SELECT</span> string_agg(empno, <span class="hljs-string">','</span> <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> a) <span class="hljs-keyword">FROM</span> empsalary;
<span class="hljs-keyword">SELECT</span> percentile_cont(<span class="hljs-number">0.5</span>) <span class="hljs-keyword">WITHIN</span> <span class="hljs-keyword">GROUP</span> (<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> income) <span class="hljs-keyword">FROM</span> households;
<span class="hljs-keyword">SELECT</span> count(*) <span class="hljs-keyword">FILTER</span> (<span class="hljs-keyword">WHERE</span> i &lt; <span class="hljs-number">5</span>) <span class="hljs-keyword">FROM</span> generate_series(<span class="hljs-number">1</span>,<span class="hljs-number">10</span>) <span class="hljs-keyword">AS</span> s(i);
<span class="hljs-keyword">SELECT</span> depname, empno, salary, avg(salary) <span class="hljs-keyword">OVER</span> (<span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> depname) <span class="hljs-keyword">FROM</span> empsalary;
<span class="hljs-keyword">SELECT</span> salary, sum(salary) <span class="hljs-keyword">OVER</span> (<span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> salary) <span class="hljs-keyword">FROM</span> empsalary;
<span class="hljs-keyword">SELECT</span> sum(salary) <span class="hljs-keyword">OVER</span> w, avg(salary) <span class="hljs-keyword">OVER</span> w
  <span class="hljs-keyword">FROM</span> empsalary
  <span class="hljs-keyword">WINDOW</span> w <span class="hljs-keyword">AS</span> (<span class="hljs-keyword">PARTITION</span> <span class="hljs-keyword">BY</span> depname <span class="hljs-keyword">ORDER</span> <span class="hljs-keyword">BY</span> salary <span class="hljs-keyword">DESC</span>);