<ruby id="bdb3f"></ruby>

    <p id="bdb3f"><cite id="bdb3f"></cite></p>

      <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
        <p id="bdb3f"><cite id="bdb3f"></cite></p>

          <pre id="bdb3f"></pre>
          <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

          <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
          <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

          <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                <ruby id="bdb3f"></ruby>

                合規國際互聯網加速 OSASE為企業客戶提供高速穩定SD-WAN國際加速解決方案。 廣告
                # 8. Window Functions 這節來介紹**PostgreSQL**的一個特性,叫"Window Functions",這個功能有點類似于"group by",它很強大,能夠實現意想不到的功能。而且這個功能不是所有數據庫系統都有的,例如**MySQL**就沒有。它結合統計圖來用更為強大。 它的[官方](http://www.postgresql.org/docs/9.4/static/tutorial-window.html)定義是這樣的:"A window function performs a calculation across a set of table rows that are somehow related to the current row. "。 說那么多沒什么用,直接看例子。 ``` SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary; ``` ``` depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows) ``` 假如把上面的sql語句中的"OVER (PARTITION BY depname)"改成"GROUP BY depname"的話,結果就是只有三條記錄,它會根據depname(develop、personnel、sales)合并成三條的。 ``` depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 (10 rows) ``` 在某些場合下,這種肯定沒有**"Window Functions"**,因為salary和empno只有一個了。假如我們需要輸出salary和empno的話,只能再查一次,然后用程序循環出來,只能這樣組合了。在實際的開發中,是遇到過這種問題的。而**PostgreSQL**默認就提供了**"Window Function"**機制來解決這一問題,很方便。 還支持排序。 ``` SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary; ``` ``` depname | empno | salary | rank -----------+-------+--------+------ develop | 8 | 6000 | 1 develop | 10 | 5200 | 2 develop | 11 | 5200 | 2 develop | 9 | 4500 | 4 develop | 7 | 4200 | 5 personnel | 2 | 3900 | 1 personnel | 5 | 3500 | 2 sales | 1 | 5000 | 1 sales | 4 | 4800 | 2 sales | 3 | 4800 | 2 (10 rows) ``` 在**Rails**中可以這樣用 ``` Article.find_by_sql("SELECT *, rank() OVER (ORDER BY group_id DESC) FROM articles") ``` 結合一些圖表統計的庫,比如hightcharts,可以實現類似這樣的效果。 ![](https://box.kancloud.cn/ecbd621cc7645b76d7e409e293e78b17_750x290.png)
                  <ruby id="bdb3f"></ruby>

                  <p id="bdb3f"><cite id="bdb3f"></cite></p>

                    <p id="bdb3f"><cite id="bdb3f"><th id="bdb3f"></th></cite></p><p id="bdb3f"></p>
                      <p id="bdb3f"><cite id="bdb3f"></cite></p>

                        <pre id="bdb3f"></pre>
                        <pre id="bdb3f"><del id="bdb3f"><thead id="bdb3f"></thead></del></pre>

                        <ruby id="bdb3f"><mark id="bdb3f"></mark></ruby><ruby id="bdb3f"></ruby>
                        <pre id="bdb3f"><pre id="bdb3f"><mark id="bdb3f"></mark></pre></pre><output id="bdb3f"></output><p id="bdb3f"></p><p id="bdb3f"></p>

                        <pre id="bdb3f"><del id="bdb3f"><progress id="bdb3f"></progress></del></pre>

                              <ruby id="bdb3f"></ruby>

                              哎呀哎呀视频在线观看