宝马娱乐最新网站

書名： SQL機器學習庫MADlib技術解析
作者名：王雪迎
本章字數： 1915字
更新時間： 2020-06-29 18:08:07

3.3 透視表

MADlib提供了一個名為pivot的函數（一個基礎的數據匯總工具）。熟悉SQL的用戶一定不會對pivot一詞陌生，它的中文譯作“透視表”或“樞軸表”，通常用來實現OLAP或報表系統中一類常見的行列轉置需求。pivot函數能夠對一個表中存儲的數據執行基本行轉列操作，并將匯總后的結果輸出到另一個表中。嚴格說pivot函數并不是一個機器學習模型或算法，但它確實使行列轉置操作變得更為簡單與靈活。

1. 函數語法

     pivot(
         source_table,
         output_table,
        index,
         pivot_cols,
         pivot_values,
         aggregate_func,
         fill_value,
         keep_null,
         output_col_dictionary
     )

2. 參數說明

pivot函數中的參數如表3-5所示。

表3-5 pivot函數參數

3. 示例

（1）建立示例數據表并添加數據

drop table if exists pivset cascade;
create table pivset
( id integer, piv integer, val float8 );
insert into pivset values
(0, 10, 1), (0, 10, 2), (0, 20, 3), (1, 20, 4), (1, 30, 5),
(1, 30, 6), (1, 10, 7), (null, 10, 8), (1, null, 9), (1, 10, null);
select * from pivset order by id, piv;

結果：

可以看到，pivset表的三列中各有一行的值為NULL。

（2）執行聚合操作并生成透視表

drop table if exists pivout;
select madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
select * from pivout order by id;

結果：

id是分組列，piv是需要按值做行轉列操作的列，val是需要執行AVG聚合的列，其他參數并未顯式賦值，均使用默認值。可以看到，這里的輸出中將NULL顯示為空串。單從這個簡單的例子看，pivot與下面的查詢語句結果是一樣的：

顯然pivot函數更簡潔也更靈活，因為實際上我們一般事先不會知道結果有多少列。pivot為用戶提供了一種非常簡單的方式，實現了所謂的動態行轉列功能。

（3）增加源表列并創建相應的視圖

結果：

視圖定義查詢中的coalesce函數將NULL值替換為0，功能類似于Oracle的NVL函數或SQL Server的ISNULL函數。

（4）在視圖上應用聚合函數

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum');
select * from pivout order by id;

結果：

和（2）的查詢類似，只不過這次我們把集合函數換成了‘sum’。

（5）創建一個用戶自定義聚合函數（注意函數必須定義為嚴格的）

drop function if exists array_add1 (anyarray, anyelement) cascade;
create function array_add1(anyarray, anyelement) returns anyarray as $$
  select $1 || $2
$$ language sql strict;
drop aggregate if exists array_accum1 (anyelement);
create aggregate array_accum1 (anyelement) (
    sfunc=array_add1,
    stype=anyarray,
    initcond='{}'
);
drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val',
'array_accum1');
select * from pivout order by id;

結果：

array_add1函數被聲明為嚴格的，將一個元素添加到一個數組中。array_accum1以‘val’的值為參數，調用array_add1函數生成相應的數組，并忽略val列中的NULL值。

（6）在轉置列中保持NULL值

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', true);
select * from pivout order by id;

結果：

這次我們在調用pivot函數時，將keep_null參數設置為‘true’。從結果可以看出，比前面的查詢多了一列val_sum_piv_null，用于表示piv為NULL時val的聚合值。

（7）替換結果中的NULL值

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '999');
select * from pivout order by id;

結果：

從查詢結果看到，我們用999替換了聚合結果中的NULL值。

（8）使用多個分組列

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val');
select * from pivout order by id,id2;

結果：

我們將分組列定義為id和id2兩列，從結果可以看出，結果數據由3行變為7行，與下面的查詢邏輯上等價，結果相同。

select id, id2,
       sum(case when piv=10 then val else 0 end)
       /sum(case when piv=10 and val is not null then 1 else null end)
as val_avg_piv_10,
       sum(case when piv=20 then val else 0 end)
       /sum(case when piv=20 and val is not null then 1 else null end)
as val_avg_piv_20,
       sum(case when piv=30 then val else 0 end)
       /sum(case when piv=30 and val is not null then 1 else null end)
as val_avg_piv_30
  from pivset_ext
 group by id, id2 order by id, id2;

（9）對多列進行行轉列

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val');
\x on
select * from pivout order by id;

結果：

我們將根據piv和piv2兩列的值進行行轉列，piv有3個不同值，piv2有4個不同值，因此結果中將包含12個由行轉成的列，共3行。

（10）聚合多列

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2');
select * from pivout order by id;

結果：

按照id分組，每個piv值（不含NULL）對應兩列，分別代表對val與val2的聚合值，結果中共有6個由行轉成的列，共3行。

（11）同一列使用多個聚合函數

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum');
select * from pivout order by id;

結果：

可以對同一列執行不同的聚合函數，按參數給出的聚合函數順序，pivot函數為每個聚合函數生成由行轉成的列。本例中piv有3個不同值，分別執行avg與sum兩種聚合操作，因此結果中有6個由行轉成的列，共3行。

（12）對不同列使用不同的單一聚合函數

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',
'val=avg, val2=sum');
select * from pivout order by id;

結果：

將（10）和（11）中的兩個例子相結合，對兩列分別執行不同的聚合操作，結果也是3行6列。pivot函數能夠對不同的數據列執行不同的聚合操作，為獲取用戶關心的匯總數據提供了較大的靈活性。

（13）為不同列使用多個聚合函數

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',
    'val=avg, val2=[avg,sum]');
select * from pivout order by id;

結果：

這個例子更復雜些，對val列執行單一avg聚合，而對val2列執行avg與sum兩種聚合操作。與前面的例子同理，本次結果中包含9個由行轉成的列，共3行。

（14）聯合所有選項

drop table if exists pivout;
select madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2',
    'val=avg, val2=[avg,sum]', '999', true);
select * from pivout order by id,id2;

結果：

本例結合了前面介紹的所有參數選項，結果行數為7（按id、id2兩列分組）、列數為48（等于“piv的不同值個數”（4，包括一個NULL列）×“piv2的不同值個數”（4）×聚合操作列數（3，val列avg聚合、val2列avg和sum聚合））。

（15）創建一個輸出列名字典表

drop table if exists pivout, pivout_dictionary;
select madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val,
val2', 'val=avg, val2=[avg,sum]', '999', true, true);
\x off
select * from pivout_dictionary;

結果：

這里生成的字典表包含6列，分別是行轉列后生成的數字列名、聚合列名、聚合函數名、原表中需要轉置的列名（本例有兩列）、行轉列后生成的慣用列名。此時查詢輸出的結果表，生成的列名是以數字ID表示的：

\x on
select * from pivout order by id,id2;

結果：

官术网_书友最值得收藏!

SQL機器學習庫MADlib技術解析

3.3 透視表