관리-도구

편집 파일: window-functions.expect.txt

<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>);