/* ----------------------------------------------------------------------- *//**
*
* 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
@brief Provides pivoting functions helpful for data preparation before modeling
@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.
pivot(
source_table,
output_table,
index,
pivot_cols,
pivot_values,
aggregate_func,
fill_value,
keep_null,
output_col_dictionary,
output_type
)
\b Arguments
- source_table
- VARCHAR. Name of the source table (or view) containing data to
pivot.
- output_table
- VARCHAR. Name of output table that contains the pivoted data.
The output table contains all the columns present in
the 'index' column list, plus additional columns for each
distinct value in 'pivot_cols'.
@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 'pivot_values'
- aggregate function
- name of the pivot column 'pivot_cols'
- values in the pivot column
- index
- 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.
- pivot_cols
- VARCHAR. Comma-separated columns that will form the columns of the
output pivot table.
- pivot_values
- VARCHAR. Comma-separated columns that contain the values to be
summarized in the output pivot table.
- aggregate_func (optional)
- 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.
@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].
- fill_value (optional)
- 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.
- keep_null (optional)
- 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.
- output_col_dictionary (optional)
- 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.
- output_type (optional)
- 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 'madlib.svec' 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 (output_col_dictionary=TRUE)
when 'output_type' is 'array' or 'svec' to define each index into the array.
@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:
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);
-# Pivot the table:
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
SELECT * FROM pivout ORDER BY id;
id | val_avg_piv_10 | val_avg_piv_20 | val_avg_piv_30
----+----------------+----------------+----------------
0 | 1.5 | 3 |
1 | 7 | 4 | 5.5
| 8 | |
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:
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;
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)
-# Let's use a different aggregate function on the view we
just created:
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum');
SELECT * FROM pivout ORDER BY id;
id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30
----+----------------+----------------+----------------
0 | 3 | 3 |
1 | 7 | 4 | 11
| 8 | |
-# Now create a custom aggregate. Note that the aggregate
must have a strict transition function:
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;
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} | {} | {}
-# Keep null values in the pivot column:
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', True);
SELECT * FROM pivout ORDER BY id;
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 | | |
-# Fill null results with a value of interest:
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '111');
SELECT * FROM pivout ORDER BY id;
id | val_sum_piv_10 | val_sum_piv_20 | val_sum_piv_30
----+----------------+----------------+----------------
0 | 3 | 3 | 111
1 | 7 | 4 | 11
| 8 | 111 | 111
-# Use multiple index columns:
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val');
SELECT * FROM pivout ORDER BY id,id2;
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 | |
-# Use multiple pivot columns with columnar output:
\\x on
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val');
SELECT * FROM pivout ORDER BY id;
-[ 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 |
...
-# Use multiple pivot columns (same as above) with an array output:
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;
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}
-- Use the dictionary to understand what each index of an array corresponds to
SELECT * FROM pivout_dictionary;
__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"
-# Use multiple value columns:
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2');
\\x on
SELECT * FROM pivout ORDER BY id;
-[ 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
...
-# Use multiple aggregate functions on the same value column (cross product):
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum');
\\x on
SELECT * FROM pivout ORDER BY id;
-[ 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
...
-# Use different aggregate functions for different value columns:
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;
-[ 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
...
-# Use multiple aggregate functions for different value columns:
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;
-[ 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
...
-# Combine all of the options:
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;
-[ 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
...
-# Create a dictionary for output column names:
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__;
__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)
\\x on
SELECT * FROM pivout ORDER BY id,id2;
-[ 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
...
@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 out_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,
out_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,
out_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,
out_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,
out_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,
out_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,
out_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,
out_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,
out_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', `');
--------------------------------------------------------------------------------