PostgreSQL 9.5 的 GROUPING SETS 以及 CUBE 與 ROLLUP

Zite 上看到的「Postgres finally has CUBE / ROLLUP / GROUPING SETS !」。

直接看 PostgreSQL 的文件「7.2.4. GROUPING SETS, CUBE, and ROLLUP」就可以知道用法:

=> SELECT * FROM items_sold;
 brand | size | sales
-------+------+-------
 Foo   | L    |  10
 Foo   | M    |  20
 Bar   | M    |  15
 Bar   | L    |  5
(4 rows)

=> SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
 brand | size | sum
-------+------+-----
 Foo   |      |  30
 Bar   |      |  20
       | L    |  15
       | M    |  35
       |      |  50
(5 rows)

結果就是分次 GROUP BY 的聯集。而 CUBEROLLUP 則是提供列舉的方式。

ROLLUP 的部份:

ROLLUP ( e1, e2, e3, ... )

表示階層式的列舉:

GROUPING SETS (
    ( e1, e2, e3, ... ),
    ...
    ( e1, e2 )
    ( e1 )
    ( )
)

CUBE

CUBE ( a, b, c )

則是表示 power set (所有的組合):

GROUPING SETS (
    ( a, b, c ),
    ( a, b    ),
    ( a,    c ),
    ( a       ),
    (    b, c ),
    (    b    ),
    (       c ),
    (         ),
)

也有更複雜的 CUBE ( (a,b), (c,d) )GROUP BY a, CUBE(b,c), GROUPING SETS ((d), (e)) 可以用,參考文件裡的範例即可 :p

2014 年 Turing Award 得主:Michael Stonebraker

今年的 Turing Award 頒給了在資料庫領域上有重要貢獻的 Michael Stonebraker

For fundamental contributions to the concepts and practices underlying modern database systems.

ACM 的頁面上列了不少成就,比較熟的是 PostgreSQL,他是 PostgreSQL 發展重要的關鍵人物:(出自維基百科「PostgreSQL」條目)

PostgreSQL evolved from the Ingres project at the University of California, Berkeley. In 1982 the leader of the Ingres team, Michael Stonebraker, left Berkeley to make a proprietary version of Ingres. He returned to Berkeley in 1985 and started a post-Ingres project to address the problems with contemporary database systems that had become increasingly clear during the early 1980s. The new project, POSTGRES, aimed to add the fewest features needed to completely support types. These features included the ability to define types and to fully describe relationships – something used widely before but maintained entirely by the user.

PostgreSQL 9.3...

前幾天 PostgreSQL 宣佈 9.3 發佈:「PostgreSQL 9.3 released!」。

如同預測的,9.3 對 JSON 的處理能力變強了,加上可以自訂 index (寫 function 自訂 index 的內容),這使得 PostgreSQL 可以做 JSON-based document database,可以參考 9.3 的「PostgreSQL: Documentation: 9.3: JSON Functions and Operators」以及前一版 9.2 的「PostgreSQL: Documentation: 9.2: JSON Functions」。

MongoDB 就別來亂了... XD