| /* ----------------------------------------------------------------------- *//** |
| * |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| * |
| * |
| * @file pivot.sql_in |
| * |
| * @brief SQL functions for pivoting |
| * @date June 2016 |
| * |
| * @sa Creates a pivot table for data summarization. |
| * |
| *//* ----------------------------------------------------------------------- */ |
| |
| m4_include(`SQLCommon.m4') |
| |
| /** |
| @addtogroup grp_pivot |
| |
| <div class="toc"><b>Contents</b> |
| <ul> |
| <li><a href="#pivoting">Pivoting</a></li> |
| <li><a href="#notes">Notes</a></li> |
| <li><a href="#examples">Examples</a></li> |
| <li><a href="#literature">Literature</a></li> |
| </ul> |
| </div> |
| |
| @brief Pivoting and data summarization tools for preparing data |
| for modeling operations. |
| |
| @anchor pivoting |
| The goal of the MADlib pivot function is to provide a data summarization tool |
| that can do basic OLAP type operations on data stored in one table and output |
| the summarized data to a second table. |
| |
| |
| <pre class="syntax"> |
| pivot( |
| source_table, |
| output_table, |
| index, |
| pivot_cols, |
| pivot_values, |
| aggregate_func, |
| fill_value, |
| keep_null, |
| output_col_dictionary, |
| output_type |
| ) |
| </pre> |
| \b Arguments |
| <dl class="arglist"> |
| <dt>source_table</dt> |
| <dd>VARCHAR. Name of the source table (or view) containing data to |
| pivot.</dd> |
| |
| <dt>output_table</dt> |
| <dd>VARCHAR. Name of output table that contains the pivoted data. |
| The output table contains all the columns present in |
| the <em>'index'</em> column list, plus additional columns for each |
| distinct value in <em>'pivot_cols'</em>. |
| |
| @note The names of the columns in the output table are auto-generated. |
| Please see the examples section below to see how this works in practice. |
| The convention used is to concatenate the following strings and separate |
| each by an underscore '_' : |
| - name of the value column <em>'pivot_values'</em> |
| - aggregate function |
| - name of the pivot column <em>'pivot_cols'</em> |
| - values in the pivot column |
| </dd> |
| |
| <dt>index </dt> |
| <dd>VARCHAR. Comma-separated columns that will form the index of the output |
| pivot table. By index we mean the values to group by; these are the rows |
| in the output pivot table.</dd> |
| |
| <dt>pivot_cols </dt> |
| <dd>VARCHAR. Comma-separated columns that will form the columns of the |
| output pivot table.</dd> |
| |
| <dt>pivot_values </dt> |
| <dd>VARCHAR. Comma-separated columns that contain the values to be |
| summarized in the output pivot table.</dd> |
| |
| <dt>aggregate_func (optional)</dt> |
| <dd>VARCHAR. default: 'AVG'. A comma-separated list of aggregates to be |
| applied to values. These can be PostgreSQL built-in aggregates [1] or UDAs. It is |
| possible to assign a set of aggregates per value column. Please refer to the |
| examples 12\-14 below for syntax details.</dd> |
| |
| @note Only aggregates with |
| strict transition functions are permitted here. |
| A strict transition function means rows with null values are ignored; |
| the function is not called and the previous state value is retained. |
| If you need some other behavior for null inputs, this should |
| be done prior to calling the pivot function. |
| Aggregates with strict transition |
| functions are described in [2,3]. |
| |
| <dt>fill_value (optional)</dt> |
| <dd>VARCHAR. default: NULL. If specified, determines how to fill NULL |
| values resulting from pivot operation. This is a global parameter (not |
| applied per aggregate) and is applied post-aggregation to the output |
| table.</dd> |
| |
| <dt>keep_null (optional)</dt> |
| <dd>BOOLEAN. default: FALSE. If TRUE, then pivot columns are created |
| corresponding to NULL categories. If FALSE, then no pivot columns will be |
| created for NULL categories.</dd> |
| |
| <dt>output_col_dictionary (optional)</dt> |
| <dd>BOOLEAN. default: FALSE. This parameter is used to handle |
| auto-generated column names that exceed the PostgreSQL limit of 63 bytes |
| (could be a common occurrence). If TRUE, column names will be set as |
| numerical IDs and will create a dictionary table called output_table |
| appended with _dictionary. If FALSE, will auto-generate column names in |
| the usual way unless the limit of 63 bytes will be exceeded. In this case, |
| a dictionary output file will be created and a message given to the user. |
| </dd> |
| |
| <dt>output_type (optional)</dt> |
| <dd>VARCHAR. default: 'column'. This parameter controls the output format |
| of the pivoted variables. If 'column', a column is created for each pivot |
| variable. PostgreSQL limits the number of columns in a table |
| (250 - 1600 depending on column types). |
| If the total number of output columns exceeds this limit, then make this |
| parameter either 'array' (to combine the output columns into an array) or |
| 'svec' (to cast the array output to <em>'madlib.svec'</em> type). |
| If you have an 'aggregate_func' that has an array return type, |
| it cannot be combined with 'output_type'='array' or 'svec'. |
| |
| A dictionary will be created (<em>output_col_dictionary=TRUE</em>) |
| when 'output_type' is 'array' or 'svec' to define each index into the array. |
| </dd> |
| |
| </dl> |
| |
| @anchor notes |
| @note |
| - NULLs in the index column are treated like any other value. |
| - NULLs in the pivot column are ignored unless keep_null is TRUE. |
| - Only strict transition functions are |
| allowed so NULLs are ignored. |
| - It is not allowed to set the fill_value parameter without setting the |
| aggregate_func parameter due to possible ambiguity. Set |
| aggregate_func to NULL for the default behavior and use fill_value as desired. |
| Please note that full_value must be of the same type as the output of the |
| aggregate_func (or capable of being cast to the same type by PostgreSQL), |
| or else an error will result. |
| - It is not allowed to set the output_col_dictionary parameter without setting |
| the keep_null parameter due to possible ambiguity. Set |
| keep_null to NULL for the default behavior and use output_col_dictionary as |
| desired. |
| - Expressions (instead of column names) are not supported. Create a view with the |
| desired expressions and pass it as the input table (see example 3 below). |
| - It is allowed to pass a partial mapping for the aggregate_func parameter. The |
| missing value columns will be aggregated using the default function (average). |
| |
| @anchor examples |
| @examp |
| |
| -# Create a simple dataset to demonstrate a basic pivot: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivset CASCADE; -- View below may depend on table so use 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); |
| </pre> |
| |
| -# Pivot the table: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val'); |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30 |
| ----+----------------+----------------+---------------- |
| 0 | 1.5 | 3 | |
| 1 | 7 | 4 | 5.5 |
| | 8 | | |
| </pre> |
| Here NULL is showing as an empty cell in the output. |
| |
| -# Now let's add some more columns to our data set and create a view: |
| <pre class="example"> |
| DROP VIEW IF EXISTS pivset_ext; |
| CREATE VIEW pivset_ext AS |
| SELECT *, |
| COALESCE(id + (val / 3)::integer, 0) AS id2, |
| COALESCE(100*(val / 3)::integer, 0) AS piv2, |
| COALESCE(val + 10, 0) AS val2 |
| FROM pivset; |
| SELECT id,id2,piv,piv2,val,val2 FROM pivset_ext |
| ORDER BY id,id2,piv,piv2,val,val2; |
| </pre> |
| <pre class="result"> |
| id | id2 | piv | piv2 | val | val2 |
| ----+-----+-----+------+-----+------ |
| 0 | 0 | 10 | 0 | 1 | 11 |
| 0 | 1 | 10 | 100 | 2 | 12 |
| 0 | 1 | 20 | 100 | 3 | 13 |
| 1 | 0 | 10 | 0 | | 0 |
| 1 | 2 | 20 | 100 | 4 | 14 |
| 1 | 3 | 10 | 200 | 7 | 17 |
| 1 | 3 | 30 | 200 | 5 | 15 |
| 1 | 3 | 30 | 200 | 6 | 16 |
| 1 | 4 | | 300 | 9 | 19 |
| | 0 | 10 | 300 | 8 | 18 |
| (10 rows) |
| </pre> |
| |
| -# Let's use a different aggregate function on the view we |
| just created: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum'); |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30 |
| ----+----------------+----------------+---------------- |
| 0 | 3 | 3 | |
| 1 | 7 | 4 | 11 |
| | 8 | | |
| </pre> |
| |
| -# Now create a custom aggregate. Note that the aggregate |
| must have a strict transition function: |
| <pre class="example"> |
| 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; |
| </pre> |
| <pre class="result"> |
| id | val_array_accum1_piv_10 | val_array_accum1_piv_20 | val_array_accum1_piv_30 |
| ----+-------------------------+-------------------------+------------------------- |
| 0 | {1,2} | {3} | {} |
| 1 | {7} | {4} | {5,6} |
| | {8} | {} | {} |
| </pre> |
| |
| -# Keep null values in the pivot column: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', True); |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30 | val_sum_piv_null |
| ----+----------------+----------------+----------------+------------------ |
| 0 | 3 | 3 | | |
| 1 | 7 | 4 | 11 | 9 |
| | 8 | | | |
| </pre> |
| |
| -# Fill null results with a value of interest: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '111'); |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30 |
| ----+----------------+----------------+---------------- |
| 0 | 3 | 3 | 111 |
| 1 | 7 | 4 | 11 |
| | 8 | 111 | 111 |
| </pre> |
| |
| -# Use multiple index columns: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val'); |
| SELECT * FROM pivout ORDER BY id,id2; |
| </pre> |
| <pre class="result"> |
| id | id2 | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30 |
| ----+-----+----------------+----------------+---------------- |
| 0 | 0 | 1 | | |
| 0 | 1 | 2 | 3 | |
| 1 | 0 | | | |
| 1 | 2 | | 4 | |
| 1 | 3 | 7 | | 5.5 |
| 1 | 4 | | | |
| | 0 | 8 | | |
| </pre> |
| |
| -# Use multiple pivot columns with columnar output: |
| <pre class="example"> |
| \\x on |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val'); |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]-----------+---- |
| id | 0 |
| val_avg_piv_10_piv2_0 | 1 |
| val_avg_piv_10_piv2_100 | 2 |
| val_avg_piv_10_piv2_200 | |
| val_avg_piv_10_piv2_300 | |
| val_avg_piv_20_piv2_0 | |
| val_avg_piv_20_piv2_100 | 3 |
| val_avg_piv_20_piv2_200 | |
| val_avg_piv_20_piv2_300 | |
| val_avg_piv_30_piv2_0 | |
| val_avg_piv_30_piv2_100 | |
| val_avg_piv_30_piv2_200 | |
| val_avg_piv_30_piv2_300 | |
| -[ RECORD 2 ]-----------+---- |
| id | 1 |
| val_avg_piv_10_piv2_0 | |
| val_avg_piv_10_piv2_100 | |
| val_avg_piv_10_piv2_200 | 7 |
| val_avg_piv_10_piv2_300 | |
| val_avg_piv_20_piv2_0 | |
| val_avg_piv_20_piv2_100 | 4 |
| val_avg_piv_20_piv2_200 | |
| val_avg_piv_20_piv2_300 | |
| val_avg_piv_30_piv2_0 | |
| val_avg_piv_30_piv2_100 | |
| val_avg_piv_30_piv2_200 | 5.5 |
| val_avg_piv_30_piv2_300 | |
| ... |
| </pre> |
| |
| -# Use multiple pivot columns (same as above) with an array output: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout, pivout_dictionary; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val', |
| NULL, NULL, FALSE, FALSE, 'array'); |
| \\x off |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | val_avg |
| --------+------------------------------------------------------------ |
| 0 | {1,2,NULL,NULL,NULL,3,NULL,NULL,NULL,NULL,NULL,NULL} |
| 1 | {NULL,NULL,7,NULL,NULL,4,NULL,NULL,NULL,NULL,5.5,NULL} |
| [NULL] | {NULL,NULL,NULL,8,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL} |
| </pre> |
| <pre class="example"> |
| -- Use the dictionary to understand what each index of an array corresponds to |
| SELECT * FROM pivout_dictionary; |
| </pre> |
| <pre class="result"> |
| __pivot_cid__ | pval | agg | piv | piv2 | col_name |
| ---------------+------+-----+-----+------+--------------------------- |
| 1 | val | avg | 10 | 0 | "val_avg_piv_10_piv2_0" |
| 2 | val | avg | 10 | 100 | "val_avg_piv_10_piv2_100" |
| 3 | val | avg | 10 | 200 | "val_avg_piv_10_piv2_200" |
| 4 | val | avg | 10 | 300 | "val_avg_piv_10_piv2_300" |
| 5 | val | avg | 20 | 0 | "val_avg_piv_20_piv2_0" |
| 6 | val | avg | 20 | 100 | "val_avg_piv_20_piv2_100" |
| 7 | val | avg | 20 | 200 | "val_avg_piv_20_piv2_200" |
| 8 | val | avg | 20 | 300 | "val_avg_piv_20_piv2_300" |
| 9 | val | avg | 30 | 0 | "val_avg_piv_30_piv2_0" |
| 10 | val | avg | 30 | 100 | "val_avg_piv_30_piv2_100" |
| 11 | val | avg | 30 | 200 | "val_avg_piv_30_piv2_200" |
| 12 | val | avg | 30 | 300 | "val_avg_piv_30_piv2_300" |
| </pre> |
| |
| -# Use multiple value columns: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2'); |
| \\x on |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]---+----- |
| id | 0 |
| val_avg_piv_10 | 1.5 |
| val_avg_piv_20 | 3 |
| val_avg_piv_30 | |
| val2_avg_piv_10 | 11.5 |
| val2_avg_piv_20 | 13 |
| val2_avg_piv_30 | |
| -[ RECORD 2 ]---+----- |
| id | 1 |
| val_avg_piv_10 | 7 |
| val_avg_piv_20 | 4 |
| val_avg_piv_30 | 5.5 |
| val2_avg_piv_10 | 8.5 |
| val2_avg_piv_20 | 14 |
| val2_avg_piv_30 | 15.5 |
| ... |
| </pre> |
| |
| |
| -# Use multiple aggregate functions on the same value column (cross product): |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum'); |
| \\x on |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]--+---- |
| id | 0 |
| val_avg_piv_10 | 1.5 |
| val_avg_piv_20 | 3 |
| val_avg_piv_30 | |
| val_sum_piv_10 | 3 |
| val_sum_piv_20 | 3 |
| val_sum_piv_30 | |
| -[ RECORD 2 ]--+---- |
| id | 1 |
| val_avg_piv_10 | 7 |
| val_avg_piv_20 | 4 |
| val_avg_piv_30 | 5.5 |
| val_sum_piv_10 | 7 |
| val_sum_piv_20 | 4 |
| val_sum_piv_30 | 11 |
| ... |
| </pre> |
| |
| -# Use different aggregate functions for different value columns: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', |
| 'val=avg, val2=sum'); |
| \\x on |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]---+---- |
| id | 0 |
| val_avg_piv_10 | 1.5 |
| val_avg_piv_20 | 3 |
| val_avg_piv_30 | |
| val2_sum_piv_10 | 23 |
| val2_sum_piv_20 | 13 |
| val2_sum_piv_30 | |
| -[ RECORD 2 ]---+---- |
| id | 1 |
| val_avg_piv_10 | 7 |
| val_avg_piv_20 | 4 |
| val_avg_piv_30 | 5.5 |
| val2_sum_piv_10 | 17 |
| val2_sum_piv_20 | 14 |
| val2_sum_piv_30 | 31 |
| ... |
| </pre> |
| |
| -# Use multiple aggregate functions for different value columns: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2', |
| 'val=avg, val2=[avg,sum]'); |
| \\x on |
| SELECT * FROM pivout ORDER BY id; |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]---+----- |
| id | 0 |
| val_avg_piv_10 | 1.5 |
| val_avg_piv_20 | 3 |
| val_avg_piv_30 | |
| val2_avg_piv_10 | 11.5 |
| val2_avg_piv_20 | 13 |
| val2_avg_piv_30 | |
| val2_sum_piv_10 | 23 |
| val2_sum_piv_20 | 13 |
| val2_sum_piv_30 | |
| -[ RECORD 2 ]---+----- |
| id | 1 |
| val_avg_piv_10 | 7 |
| val_avg_piv_20 | 4 |
| val_avg_piv_30 | 5.5 |
| val2_avg_piv_10 | 8.5 |
| val2_avg_piv_20 | 14 |
| val2_avg_piv_30 | 15.5 |
| val2_sum_piv_10 | 17 |
| val2_sum_piv_20 | 14 |
| val2_sum_piv_30 | 31 |
| ... |
| </pre> |
| |
| -# Combine all of the options: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2', |
| 'val=avg, val2=[avg,sum]', '111', True); |
| \\x on |
| SELECT * FROM pivout ORDER BY id,id2; |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]--------------+----- |
| id | 0 |
| id2 | 0 |
| val_avg_piv_null_piv2_0 | 111 |
| val_avg_piv_null_piv2_100 | 111 |
| val_avg_piv_null_piv2_200 | 111 |
| val_avg_piv_null_piv2_300 | 111 |
| val_avg_piv_10_piv2_0 | 1 |
| val_avg_piv_10_piv2_100 | 111 |
| val_avg_piv_10_piv2_200 | 111 |
| val_avg_piv_10_piv2_300 | 111 |
| val_avg_piv_20_piv2_0 | 111 |
| val_avg_piv_20_piv2_100 | 111 |
| val_avg_piv_20_piv2_200 | 111 |
| val_avg_piv_20_piv2_300 | 111 |
| val_avg_piv_30_piv2_0 | 111 |
| val_avg_piv_30_piv2_100 | 111 |
| val_avg_piv_30_piv2_200 | 111 |
| val_avg_piv_30_piv2_300 | 111 |
| val2_avg_piv_null_piv2_0 | 111 |
| val2_avg_piv_null_piv2_100 | 111 |
| val2_avg_piv_null_piv2_200 | 111 |
| val2_avg_piv_null_piv2_300 | 111 |
| val2_avg_piv_10_piv2_0 | 11 |
| val2_avg_piv_10_piv2_100 | 111 |
| ... |
| -[ RECORD 2 ]--------------+----- |
| id | 0 |
| id2 | 1 |
| val_avg_piv_null_piv2_0 | 111 |
| val_avg_piv_null_piv2_100 | 111 |
| val_avg_piv_null_piv2_200 | 111 |
| val_avg_piv_null_piv2_300 | 111 |
| val_avg_piv_10_piv2_0 | 111 |
| val_avg_piv_10_piv2_100 | 2 |
| val_avg_piv_10_piv2_200 | 111 |
| val_avg_piv_10_piv2_300 | 111 |
| val_avg_piv_20_piv2_0 | 111 |
| val_avg_piv_20_piv2_100 | 3 |
| val_avg_piv_20_piv2_200 | 111 |
| val_avg_piv_20_piv2_300 | 111 |
| val_avg_piv_30_piv2_0 | 111 |
| val_avg_piv_30_piv2_100 | 111 |
| val_avg_piv_30_piv2_200 | 111 |
| val_avg_piv_30_piv2_300 | 111 |
| val2_avg_piv_null_piv2_0 | 111 |
| val2_avg_piv_null_piv2_100 | 111 |
| val2_avg_piv_null_piv2_200 | 111 |
| val2_avg_piv_null_piv2_300 | 111 |
| ... |
| </pre> |
| |
| -# Create a dictionary for output column names: |
| <pre class="example"> |
| DROP TABLE IF EXISTS pivout, pivout_dictionary; |
| SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2', |
| 'val=avg, val2=[avg,sum]', '111', True, True); |
| \\x off |
| SELECT * FROM pivout_dictionary order by __pivot_cid__; |
| </pre> |
| <pre class="result"> |
| __pivot_cid__ | pval | agg | piv | piv2 | col_name |
| ---------------+------+-----+--------+------+------------------------------ |
| __p_1__ | val | avg | [NULL] | 0 | "val_avg_piv_null_piv2_0" |
| __p_2__ | val | avg | [NULL] | 100 | "val_avg_piv_null_piv2_100" |
| __p_3__ | val | avg | [NULL] | 200 | "val_avg_piv_null_piv2_200" |
| __p_4__ | val | avg | [NULL] | 300 | "val_avg_piv_null_piv2_300" |
| __p_5__ | val | avg | 10 | 0 | "val_avg_piv_10_piv2_0" |
| __p_6__ | val | avg | 10 | 100 | "val_avg_piv_10_piv2_100" |
| __p_7__ | val | avg | 10 | 200 | "val_avg_piv_10_piv2_200" |
| __p_8__ | val | avg | 10 | 300 | "val_avg_piv_10_piv2_300" |
| __p_9__ | val | avg | 20 | 0 | "val_avg_piv_20_piv2_0" |
| __p_10__ | val | avg | 20 | 100 | "val_avg_piv_20_piv2_100" |
| __p_11__ | val | avg | 20 | 200 | "val_avg_piv_20_piv2_200" |
| __p_12__ | val | avg | 20 | 300 | "val_avg_piv_20_piv2_300" |
| __p_13__ | val | avg | 30 | 0 | "val_avg_piv_30_piv2_0" |
| __p_14__ | val | avg | 30 | 100 | "val_avg_piv_30_piv2_100" |
| __p_15__ | val | avg | 30 | 200 | "val_avg_piv_30_piv2_200" |
| __p_16__ | val | avg | 30 | 300 | "val_avg_piv_30_piv2_300" |
| __p_17__ | val2 | avg | [NULL] | 0 | "val2_avg_piv_null_piv2_0" |
| __p_18__ | val2 | avg | [NULL] | 100 | "val2_avg_piv_null_piv2_100" |
| __p_19__ | val2 | avg | [NULL] | 200 | "val2_avg_piv_null_piv2_200" |
| __p_20__ | val2 | avg | [NULL] | 300 | "val2_avg_piv_null_piv2_300" |
| __p_21__ | val2 | avg | 10 | 0 | "val2_avg_piv_10_piv2_0" |
| ... |
| (48 rows) |
| </pre> |
| <pre class="example"> |
| \\x on |
| SELECT * FROM pivout ORDER BY id,id2; |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]---- |
| id | 0 |
| id2 | 0 |
| __p_1__ | 111 |
| __p_2__ | 111 |
| __p_3__ | 111 |
| __p_4__ | 111 |
| __p_5__ | 1 |
| __p_6__ | 111 |
| __p_7__ | 111 |
| __p_8__ | 111 |
| __p_9__ | 111 |
| __p_10__ | 111 |
| __p_11__ | 111 |
| __p_12__ | 111 |
| __p_13__ | 111 |
| ... |
| -[ RECORD 2 ]---- |
| id | 0 |
| id2 | 1 |
| __p_1__ | 111 |
| __p_2__ | 111 |
| __p_3__ | 111 |
| __p_4__ | 111 |
| __p_5__ | 111 |
| __p_6__ | 2 |
| __p_7__ | 111 |
| __p_8__ | 111 |
| __p_9__ | 111 |
| __p_10__ | 3 |
| __p_11__ | 111 |
| __p_12__ | 111 |
| __p_13__ | 111 |
| ... |
| -[ RECORD 3 ]---- |
| id | 1 |
| id2 | 0 |
| __p_1__ | 111 |
| __p_2__ | 111 |
| __p_3__ | 111 |
| __p_4__ | 111 |
| __p_5__ | 111 |
| __p_6__ | 111 |
| __p_7__ | 111 |
| __p_8__ | 111 |
| __p_9__ | 111 |
| __p_10__ | 111 |
| __p_11__ | 111 |
| __p_12__ | 111 |
| __p_13__ | 111 |
| ... |
| </pre> |
| |
| @anchor literature |
| @literature |
| |
| NOTE: The following links refer to documentation resources for the |
| current PostgreSQL database version. Depending upon your database |
| platform version, you may need to change "current" references in the |
| links to your database version. |
| |
| If your database platform uses the Greenplum Database (or related |
| variants), please check with the project community and/or your |
| database vendor to identify the PostgreSQL version it is based on. |
| |
| @anchor svm-lit-1 |
| [1] https://www.postgresql.org/docs/current/static/functions-aggregate.html |
| |
| [2] https://www.postgresql.org/docs/current/static/sql-createaggregate.html |
| |
| [3] https://www.postgresql.org/docs/current/static/xaggr.html |
| */ |
| |
| ------------------------------------------------------------------------- |
| |
| |
| /** |
| * @brief Helper function that can be used to pivot tables |
| * |
| * @param source_table The original data table |
| * @param output_table The output table that contains the dummy |
| * variable columns |
| * @param index The index columns to group by the records by |
| * @param pivot_cols The columns to pivot the table |
| * @param pivot_values The value columns to be summarized in the |
| * pivoted table |
| * @param aggregate_func The aggregate function to be applied to the |
| * values |
| * @param fill_value If specified, determines how to fill NULL |
| * values resulting from pivot operation |
| * @param keep_null The flag for determining how to handle NULL |
| * values in pivot columns |
| * @param output_col_dictionary The flag for enabling the creation of the |
| * output dictionary for shorter column names |
| * @return Void |
| * |
| */ |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT, |
| aggregate_func TEXT, |
| fill_value TEXT, |
| keep_null BOOLEAN, |
| output_col_dictionary BOOLEAN, |
| output_type TEXT |
| |
| ) RETURNS VOID AS $$ |
| PythonFunction(utilities, pivot, pivot) |
| $$ LANGUAGE plpythonu VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `MODIFIES SQL DATA', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT, |
| aggregate_func TEXT, |
| fill_value TEXT, |
| keep_null BOOLEAN, |
| output_col_dictionary BOOLEAN |
| |
| ) RETURNS VOID AS $$ |
| SELECT MADLIB_SCHEMA.pivot($1, $2, $3, $4, $5, $6, $7, $8, $9, NULL) |
| $$ LANGUAGE sql VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT, |
| aggregate_func TEXT, |
| fill_value TEXT, |
| keep_null BOOLEAN |
| |
| ) RETURNS VOID AS $$ |
| SELECT MADLIB_SCHEMA.pivot($1, $2, $3, $4, $5, $6, $7, $8, FALSE) |
| $$ LANGUAGE sql VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT, |
| aggregate_func TEXT, |
| fill_value TEXT |
| |
| ) RETURNS VOID AS $$ |
| SELECT MADLIB_SCHEMA.pivot($1, $2, $3, $4, $5, $6, $7, FALSE, FALSE) |
| $$ LANGUAGE sql VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT, |
| aggregate_func TEXT, |
| keep_null BOOLEAN |
| |
| ) RETURNS VOID AS $$ |
| SELECT MADLIB_SCHEMA.pivot($1, $2, $3, $4, $5, $6, NULL, $7, FALSE) |
| $$ LANGUAGE sql VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT, |
| aggregate_func TEXT |
| |
| ) RETURNS VOID AS $$ |
| SELECT MADLIB_SCHEMA.pivot($1, $2, $3, $4, $5, $6, NULL, FALSE, FALSE) |
| $$ LANGUAGE sql VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT, |
| keep_null BOOLEAN |
| |
| ) RETURNS VOID AS $$ |
| SELECT MADLIB_SCHEMA.pivot($1, $2, $3, $4, $5, NULL, NULL, $6, FALSE) |
| $$ LANGUAGE sql VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| source_table TEXT, |
| output_table TEXT, |
| index TEXT, |
| pivot_cols TEXT, |
| pivot_values TEXT |
| |
| ) RETURNS VOID AS $$ |
| SELECT MADLIB_SCHEMA.pivot($1, $2, $3, $4, $5, NULL, NULL, FALSE, FALSE) |
| $$ LANGUAGE sql VOLATILE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| -- Online help |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot( |
| message VARCHAR |
| ) RETURNS VARCHAR AS $$ |
| PythonFunction(utilities, pivot, pivot_help) |
| $$ LANGUAGE plpythonu IMMUTABLE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| |
| -------------------------------------------------------------------------------- |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.pivot() |
| RETURNS VARCHAR AS $$ |
| SELECT MADLIB_SCHEMA.pivot(''); |
| $$ LANGUAGE sql IMMUTABLE |
| m4_ifdef(`\_\_HAS_FUNCTION_PROPERTIES\_\_', `CONTAINS SQL', `'); |
| -------------------------------------------------------------------------------- |
| |
