You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@madlib.apache.org by ri...@apache.org on 2016/08/29 19:28:14 UTC
[1/2] incubator-madlib git commit: Doc: Change multiple docs for
clarity
Repository: incubator-madlib
Updated Branches:
refs/heads/master cfac54772 -> 1929aedb6
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/utilities/sessionize.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/sessionize.sql_in b/src/ports/postgres/modules/utilities/sessionize.sql_in
index 788c1dd..63e4042 100644
--- a/src/ports/postgres/modules/utilities/sessionize.sql_in
+++ b/src/ports/postgres/modules/utilities/sessionize.sql_in
@@ -38,13 +38,12 @@ m4_include(`SQLCommon.m4')
</ul>
</div>
-@brief Sessionize Functions
-@details The goal of the MADlib sessionize function is to perform session reconstruction
-on a data set, so that it can be prepared for input into other algorithms such as path functions,
-or predictive analytics algorithms. Sessions for instance, can be defined based on time,
-as a sequence of events by a particular user where no more than n-seconds have elapsed between
-successive events. That is, if we don't see an event from a user for n seconds, a new session
-is started.
+@brief Sessionize
+@details The MADlib sessionize function performs time-oriented session reconstruction
+on a data set comprising a sequence of events. A defined period of inactivity
+indicates the end of one session and beginning of the next session. Sessions can be
+useful in many domains including web analytics [1], network security, manufacturing, finance,
+and operational analytics.
@anchor syntax
@par Function Syntax
@@ -63,37 +62,51 @@ sessionize(
\b Arguments
<dl class="arglist">
<dt>source_table</dt>
- <dd>VARCHAR. Name of the source table, containing data for sessionization
- analysis.</dd>
+ <dd>VARCHAR. Name of the source table that contains the data to be sessionized.</dd>
<dt>output_table</dt>
- <dd>VARCHAR. Name of the result view/table, that contains 2 new columns apart from
- the columns in the source_table: session_id and new_session.
+ <dd>VARCHAR. Name of the output view or table. (The parameter create_view described below
+ defines whether the output is actually a view or a table.) In addition to the columns in the
+ source_table, the output also contains a new column called session_id:
<ul>
- <li>session_id=1,2, ...n where n is the number of sessions in the partition.</li>
- <li>new_session=1 for start of new session, 0 otherwise.</li>
+ <li>session_id=1,2,...n where n is the number of the session in the partition.</li>
</ul>
</dd>
<dt>partition_expr</dt>
- <dd>VARCHAR. The 'partition_expr' can be a single column or a list of
+ <dd>VARCHAR. The 'partition_expr' is a single column or a list of
comma-separated columns/expressions to divide all rows into groups,
or partitions. Sessionization is applied across the rows that fall into
- the same partition.</dd>
+ the same partition. This parameter can be set to NULL or '' to indicate
+ the sessionization operation is to be applied to the whole input table.</dd>
<dt>time_stamp</dt>
- <dd>VARCHAR. The time stamp column name that is used for sessionization calculation.</dd>
+ <dd>VARCHAR. The time stamp column name that is used for sessionization calculation.
+ Note that the time_stamp column will be sorted in ascending order before
+ the session reconstruction is done within a partition.</dd>
<dt>max_time</dt>
- <dd>INTERVAL. Maximum delta time between subsequent events that fall under the same session.</dd>
+ <dd>INTERVAL. Maximum delta time (i.e., time out) between subsequent events that define a session.
+ If the elapsed time between subsequent events is longer than max_time, a new session is created.</dd>
- <dt>output_cols</dt>
- <dd>VARCHAR. An optional valid SELECT expression indicating the columns to be materialized in the output
- This is set to '*' by default, which includes all the columns in the input table and a new session id column.</dd>
+@anchor note
+@note
+ Note that max_time is of time type INTERVAL which is a PostgreSQL way of describing elapsed
+ time. For more information on INTERVAL please refer to reference [2].
- <dt>create_view</dt>
- <dd>BOOLEAN. True creates a view, and if False, the output is materialized into a table. The default
- value is TRUE, thus creating a view by default.</dd>
+ <dt>output_cols (optional)</dt>
+ <dd>VARCHAR. An optional comma separated list of columns to be written to the output_table.
+ Must be a valid SELECT expression. This is set to '*' by default, which means all columns in the
+ input table will be written to the output_table plus the session_id column.
+ Note that this parameter could include a list containing the partition_expr
+ or any other expressions of interest. E.g., '*, expr1, expr2, etc.' where this means
+ output all columns from the input table plus the expressions listed plus the session_id column.</dd>
+
+ <dt>create_view (optional)</dt>
+ <dd>BOOLEAN default: TRUE. Determines whether to create a view or materialize the output as a table.
+ If you only need session info once, creating a view could be significantly
+ faster than materializing as a table. Please note that if you set create_view to NULL
+ (allowed by PostgreSQL) it will get set to the default value of TRUE.</dd>
</dl>
@anchor examples
@@ -103,170 +116,124 @@ The data set describes shopper behavior on a notional web site that sells
beer and wine. A beacon fires an event to a log file when the shopper
visits different pages on the site: landing page, beer selection page,
wine selection page, and checkout. Each user is identified by a a user id,
-and every time a page is visited, its time stamp is also recorded.
+and every time a page is visited, the page and time stamp are logged.
Create the data table:
<pre class="example">
-DROP TABLE IF EXISTS eventlog;
+DROP TABLE IF EXISTS eventlog CASCADE; -- Using CASCADE in case you are running through this example more than once (views used below)
CREATE TABLE eventlog (event_timestamp TIMESTAMP,
user_id INT,
page TEXT,
revenue FLOAT);
INSERT INTO eventlog VALUES
+('04/15/2015 02:19:00', 101331, 'CHECKOUT', 16),
+('04/15/2015 02:17:00', 202201, 'WINE', 0),
+('04/15/2015 03:18:00', 202201, 'BEER', 0),
('04/15/2015 01:03:00', 100821, 'LANDING', 0),
('04/15/2015 01:04:00', 100821, 'WINE', 0),
('04/15/2015 01:05:00', 100821, 'CHECKOUT', 39),
('04/15/2015 02:06:00', 100821, 'WINE', 0),
('04/15/2015 02:09:00', 100821, 'WINE', 0),
-('04/15/2015 01:15:00', 101121, 'LANDING', 0),
-('04/15/2015 01:16:00', 101121, 'WINE', 0),
-('04/15/2015 01:17:00', 101121, 'CHECKOUT', 15),
-('04/15/2015 01:18:00', 101121, 'LANDING', 0),
-('04/15/2015 01:19:00', 101121, 'HELP', 0),
-('04/15/2015 01:21:00', 101121, 'WINE', 0),
-('04/15/2015 01:22:00', 101121, 'CHECKOUT', 23),
('04/15/2015 02:15:00', 101331, 'LANDING', 0),
('04/15/2015 02:16:00', 101331, 'WINE', 0),
('04/15/2015 02:17:00', 101331, 'HELP', 0),
('04/15/2015 02:18:00', 101331, 'WINE', 0),
-('04/15/2015 02:19:00', 101331, 'CHECKOUT', 16),
-('04/15/2015 02:22:00', 101443, 'BEER', 0),
-('04/15/2015 02:25:00', 101443, 'CHECKOUT', 12),
-('04/15/2015 02:29:00', 101881, 'LANDING', 0),
-('04/15/2015 02:30:00', 101881, 'BEER', 0),
-('04/15/2015 01:05:00', 102201, 'LANDING', 0),
-('04/15/2015 01:06:00', 102201, 'HELP', 0),
-('04/15/2015 01:09:00', 102201, 'LANDING', 0),
-('04/15/2015 02:15:00', 102201, 'WINE', 0),
-('04/15/2015 02:16:00', 102201, 'BEER', 0),
-('04/15/2015 02:17:00', 102201, 'WINE', 0),
-('04/15/2015 02:18:00', 102871, 'BEER', 0),
-('04/15/2015 02:19:00', 102871, 'WINE', 0),
-('04/15/2015 02:22:00', 102871, 'CHECKOUT', 21),
-('04/15/2015 02:25:00', 102871, 'LANDING', 0),
-('04/15/2015 02:17:00', 103711, 'BEER', 0),
-('04/15/2015 02:18:00', 103711, 'LANDING', 0),
-('04/15/2015 02:19:00', 103711, 'WINE', 0);
+('04/15/2015 02:29:00', 201881, 'LANDING', 0),
+('04/15/2015 02:30:00', 201881, 'BEER', 0),
+('04/15/2015 01:05:00', 202201, 'LANDING', 0),
+('04/15/2015 01:06:00', 202201, 'HELP', 0),
+('04/15/2015 01:09:00', 202201, 'LANDING', 0),
+('04/15/2015 02:15:00', 202201, 'WINE', 0),
+('04/15/2015 02:16:00', 202201, 'BEER', 0),
+('04/15/2015 03:19:00', 202201, 'WINE', 0),
+('04/15/2015 03:22:00', 202201, 'CHECKOUT', 21);
</pre>
-Sessionize the table for each user_id:
+Sessionize the table by each user_id:
<pre class="example">
- DROP TABLE IF EXISTS sessionize_output;
+ DROP VIEW IF EXISTS sessionize_output_view;
SELECT madlib.sessionize(
- 'eventlog', -- Name of input table
- 'sessionize_output', -- Table name to store sessionized results
- 'user_id', -- Partition input table by session
- 'event_timestamp', -- Order partitions in input table by time
- '0:3:0' -- Events within a window of this time unit (3 minutes) must be in the same session
- );
-SELECT * FROM sessionize_output;
+ 'eventlog', -- Name of input table
+ 'sessionize_output_view', -- View to store sessionize results
+ 'user_id', -- Partition input table by user id
+ 'event_timestamp', -- Time column used to compute sessions
+ '0:30:0' -- Use 30 minute time out to define sessions
+ );
+SELECT * FROM sessionize_output_view ORDER BY user_id, event_timestamp;
</pre>
Result:
<pre class="result">
- event_timestamp | user_id | page | revenue | session_id
+ event_timestamp | user_id | page | revenue | session_id
---------------------+---------+----------+---------+------------
2015-04-15 01:03:00 | 100821 | LANDING | 0 | 1
2015-04-15 01:04:00 | 100821 | WINE | 0 | 1
2015-04-15 01:05:00 | 100821 | CHECKOUT | 39 | 1
2015-04-15 02:06:00 | 100821 | WINE | 0 | 2
2015-04-15 02:09:00 | 100821 | WINE | 0 | 2
- 2015-04-15 01:15:00 | 101121 | LANDING | 0 | 1
- 2015-04-15 01:16:00 | 101121 | WINE | 0 | 1
- 2015-04-15 01:17:00 | 101121 | CHECKOUT | 15 | 1
- 2015-04-15 01:18:00 | 101121 | LANDING | 0 | 1
- 2015-04-15 01:19:00 | 101121 | HELP | 0 | 1
- 2015-04-15 01:21:00 | 101121 | WINE | 0 | 1
- 2015-04-15 01:22:00 | 101121 | CHECKOUT | 23 | 1
2015-04-15 02:15:00 | 101331 | LANDING | 0 | 1
2015-04-15 02:16:00 | 101331 | WINE | 0 | 1
2015-04-15 02:17:00 | 101331 | HELP | 0 | 1
2015-04-15 02:18:00 | 101331 | WINE | 0 | 1
2015-04-15 02:19:00 | 101331 | CHECKOUT | 16 | 1
- 2015-04-15 02:22:00 | 101443 | BEER | 0 | 1
- 2015-04-15 02:25:00 | 101443 | CHECKOUT | 12 | 1
- 2015-04-15 02:29:00 | 101881 | LANDING | 0 | 1
- 2015-04-15 02:30:00 | 101881 | BEER | 0 | 1
- 2015-04-15 01:05:00 | 102201 | LANDING | 0 | 1
- 2015-04-15 01:06:00 | 102201 | HELP | 0 | 1
- 2015-04-15 01:09:00 | 102201 | LANDING | 0 | 1
- 2015-04-15 02:15:00 | 102201 | WINE | 0 | 2
- 2015-04-15 02:16:00 | 102201 | BEER | 0 | 2
- 2015-04-15 02:17:00 | 102201 | WINE | 0 | 2
- 2015-04-15 02:18:00 | 102871 | BEER | 0 | 1
- 2015-04-15 02:19:00 | 102871 | WINE | 0 | 1
- 2015-04-15 02:22:00 | 102871 | CHECKOUT | 21 | 1
- 2015-04-15 02:25:00 | 102871 | LANDING | 0 | 1
- 2015-04-15 02:17:00 | 103711 | BEER | 0 | 1
- 2015-04-15 02:18:00 | 103711 | LANDING | 0 | 1
- 2015-04-15 02:19:00 | 103711 | WINE | 0 | 1
-(34 rows)
+ 2015-04-15 02:29:00 | 201881 | LANDING | 0 | 1
+ 2015-04-15 02:30:00 | 201881 | BEER | 0 | 1
+ 2015-04-15 01:05:00 | 202201 | LANDING | 0 | 1
+ 2015-04-15 01:06:00 | 202201 | HELP | 0 | 1
+ 2015-04-15 01:09:00 | 202201 | LANDING | 0 | 1
+ 2015-04-15 02:15:00 | 202201 | WINE | 0 | 2
+ 2015-04-15 02:16:00 | 202201 | BEER | 0 | 2
+ 2015-04-15 02:17:00 | 202201 | WINE | 0 | 2
+ 2015-04-15 03:18:00 | 202201 | BEER | 0 | 3
+ 2015-04-15 03:19:00 | 202201 | WINE | 0 | 3
+ 2015-04-15 03:22:00 | 202201 | CHECKOUT | 21 | 3
+(21 rows)
</pre>
-Sessionize the table based on a partition expression:
+Now let's say we want to see 3 minute sessions by a group of users
+with a certain range of user IDs. To do this, we need to sessionize
+the table based on a partition expression. Also, we want to persist
+a table output with a reduced set of columns in the table.
<pre class="example">
- DROP VIEW IF EXISTS sessionize_output;
+ DROP TABLE IF EXISTS sessionize_output_table;
SELECT madlib.sessionize(
'eventlog', -- Name of input table
- 'sessionize_output', -- Table name to store sessionized results
- 'user_id < 101500', -- Partition input table by session
+ 'sessionize_output_table', -- Table to store sessionize results
+ 'user_id < 200000', -- Partition input table by subset of users
'event_timestamp', -- Order partitions in input table by time
- '180', -- Events within a window of this time unit (180 seconds) must be in the same session
- -- Note that this is the same as '0:3:0'
- 'user_id, event_timestamp, user_id < 101500 AS "user_id < 101500"', -- Select only user_id and event_timestamp columns, along with the session id as output
- 'f' -- Materialize the results into a table
+ '180', -- Use 180 second time out to define sessions (same as '0:03:0')
+ 'event_timestamp, user_id, user_id < 200000 AS "Department-A1"', -- Select only user_id and event_timestamp columns, along with the session id as output
+ 'f' -- create a table
);
-SELECT eventlog.*, sessionize_output.session_id, sessionize_output."user_id < 101500" FROM sessionize_output INNER JOIN eventlog ON
-(eventlog.user_id=sessionize_output.user_id AND eventlog.event_timestamp=sessionize_output.event_timestamp) ORDER BY "user_id < 101500", session_id;
+ SELECT * FROM sessionize_output_table WHERE "Department-A1"='TRUE' ORDER BY event_timestamp;
</pre>
-Result:
+Result showing 2 users and 3 total sessions across the group:
<pre class="result">
- event_timestamp | user_id | page | revenue | session_id | user_id < 101500
----------------------+---------+----------+---------+------------+------------------
- 2015-04-15 01:05:00 | 102201 | LANDING | 0 | 1 | f
- 2015-04-15 01:09:00 | 102201 | LANDING | 0 | 1 | f
- 2015-04-15 01:06:00 | 102201 | HELP | 0 | 1 | f
- 2015-04-15 02:19:00 | 103711 | WINE | 0 | 2 | f
- 2015-04-15 02:18:00 | 103711 | LANDING | 0 | 2 | f
- 2015-04-15 02:17:00 | 103711 | BEER | 0 | 2 | f
- 2015-04-15 02:25:00 | 102871 | LANDING | 0 | 2 | f
- 2015-04-15 02:22:00 | 102871 | CHECKOUT | 21 | 2 | f
- 2015-04-15 02:19:00 | 102871 | WINE | 0 | 2 | f
- 2015-04-15 02:18:00 | 102871 | BEER | 0 | 2 | f
- 2015-04-15 02:17:00 | 102201 | WINE | 0 | 2 | f
- 2015-04-15 02:16:00 | 102201 | BEER | 0 | 2 | f
- 2015-04-15 02:15:00 | 102201 | WINE | 0 | 2 | f
- 2015-04-15 02:30:00 | 101881 | BEER | 0 | 3 | f
- 2015-04-15 02:29:00 | 101881 | LANDING | 0 | 3 | f
- 2015-04-15 01:03:00 | 100821 | LANDING | 0 | 1 | t
- 2015-04-15 01:04:00 | 100821 | WINE | 0 | 1 | t
- 2015-04-15 01:05:00 | 100821 | CHECKOUT | 39 | 1 | t
- 2015-04-15 01:22:00 | 101121 | CHECKOUT | 23 | 2 | t
- 2015-04-15 01:15:00 | 101121 | LANDING | 0 | 2 | t
- 2015-04-15 01:16:00 | 101121 | WINE | 0 | 2 | t
- 2015-04-15 01:17:00 | 101121 | CHECKOUT | 15 | 2 | t
- 2015-04-15 01:18:00 | 101121 | LANDING | 0 | 2 | t
- 2015-04-15 01:19:00 | 101121 | HELP | 0 | 2 | t
- 2015-04-15 01:21:00 | 101121 | WINE | 0 | 2 | t
- 2015-04-15 02:06:00 | 100821 | WINE | 0 | 3 | t
- 2015-04-15 02:09:00 | 100821 | WINE | 0 | 3 | t
- 2015-04-15 02:16:00 | 101331 | WINE | 0 | 4 | t
- 2015-04-15 02:17:00 | 101331 | HELP | 0 | 4 | t
- 2015-04-15 02:18:00 | 101331 | WINE | 0 | 4 | t
- 2015-04-15 02:19:00 | 101331 | CHECKOUT | 16 | 4 | t
- 2015-04-15 02:22:00 | 101443 | BEER | 0 | 4 | t
- 2015-04-15 02:25:00 | 101443 | CHECKOUT | 12 | 4 | t
- 2015-04-15 02:15:00 | 101331 | LANDING | 0 | 4 | t
-(34 rows)
+ event_timestamp | user_id | Department-A1 | session_id
+---------------------+---------+---------------+------------
+ 2015-04-15 01:03:00 | 100821 | t | 1
+ 2015-04-15 01:04:00 | 100821 | t | 1
+ 2015-04-15 01:05:00 | 100821 | t | 1
+ 2015-04-15 02:06:00 | 100821 | t | 2
+ 2015-04-15 02:09:00 | 100821 | t | 2
+ 2015-04-15 02:15:00 | 101331 | t | 3
+ 2015-04-15 02:16:00 | 101331 | t | 3
+ 2015-04-15 02:17:00 | 101331 | t | 3
+ 2015-04-15 02:18:00 | 101331 | t | 3
+ 2015-04-15 02:19:00 | 101331 | t | 3
+(10 rows)
</pre>
@anchor literature
@par Literature
-[1] PostgreSQL window functions,
-http://www.postgresql.org/docs/8.4/static/tutorial-window.html
+[1] Sesssions in web analytics
+https://en.wikipedia.org/wiki/Session_(web_analytics)
+
+[2] PostgreSQL date/time types
+https://www.postgresql.org/docs/8.4/static/datatype-datetime.html
*/
-------------------------------------------------------------------------
[2/2] incubator-madlib git commit: Doc: Change multiple docs for
clarity
Posted by ri...@apache.org.
Doc: Change multiple docs for clarity
Closes #67
Project: http://git-wip-us.apache.org/repos/asf/incubator-madlib/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-madlib/commit/1929aedb
Tree: http://git-wip-us.apache.org/repos/asf/incubator-madlib/tree/1929aedb
Diff: http://git-wip-us.apache.org/repos/asf/incubator-madlib/diff/1929aedb
Branch: refs/heads/master
Commit: 1929aedb665cfac101f37539c7d12ca1894de8d3
Parents: cfac547
Author: Frank McQuillan <fm...@pivotal.io>
Authored: Fri Aug 26 17:28:01 2016 -0700
Committer: Rahul Iyer <ri...@apache.org>
Committed: Mon Aug 29 12:27:12 2016 -0700
----------------------------------------------------------------------
doc/mainpage.dox.in | 8 +-
.../postgres/modules/pmml/table_to_pmml.sql_in | 4 +-
.../recursive_partitioning/decision_tree.sql_in | 3 +
.../recursive_partitioning/random_forest.sql_in | 5 +
.../postgres/modules/regress/linear.sql_in | 8 +-
.../postgres/modules/regress/logistic.sql_in | 4 +
.../postgres/modules/stats/pred_metrics.sql_in | 131 +++---
src/ports/postgres/modules/svm/svm.sql_in | 382 ++++++++-------
.../postgres/modules/utilities/path.sql_in | 67 ++-
.../postgres/modules/utilities/pivot.sql_in | 468 +++++++++++++------
.../modules/utilities/sessionize.sql_in | 237 ++++------
11 files changed, 761 insertions(+), 556 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/doc/mainpage.dox.in
----------------------------------------------------------------------
diff --git a/doc/mainpage.dox.in b/doc/mainpage.dox.in
index e1725f9..d8b6b4c 100644
--- a/doc/mainpage.dox.in
+++ b/doc/mainpage.dox.in
@@ -113,7 +113,7 @@ complete matrix stored as a distributed table.
@defgroup grp_data_prep Encoding Categorical Variables
@ingroup grp_datatrans
- @defgroup grp_pivot Pivoting
+ @defgroup grp_pivot Pivot
@ingroup grp_datatrans
@defgroup grp_stemmer Stemming
@@ -221,13 +221,13 @@ complete matrix stored as a distributed table.
@defgroup grp_sparse_linear_solver Sparse Linear Systems
@ingroup grp_linear_solver
- @defgroup grp_path Path Functions
+ @defgroup grp_path Path
@ingroup grp_utility_functions
- @defgroup grp_sessionize Sessionize Functions
+ @defgroup grp_pmml PMML Export
@ingroup grp_utility_functions
- @defgroup grp_pmml PMML Export
+ @defgroup grp_sessionize Sessionize
@ingroup grp_utility_functions
@defgroup grp_text_analysis Text Analysis
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/pmml/table_to_pmml.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/pmml/table_to_pmml.sql_in b/src/ports/postgres/modules/pmml/table_to_pmml.sql_in
index 48ec67c..9dc0559 100644
--- a/src/ports/postgres/modules/pmml/table_to_pmml.sql_in
+++ b/src/ports/postgres/modules/pmml/table_to_pmml.sql_in
@@ -112,7 +112,7 @@ Result:
<pre class="result">
<?xml version="1.0" standalone="yes"?>
<PMML version="4.1" xmlns="http://www.dmg.org/pmml-v4-1.html">
- <Header copyright="Copyright (c) 2014 gpadmin">
+ <Header copyright="redacted for this example">
<Extension extender="MADlib" name="user" value="gpadmin"/>
<Application name="MADlib" version="1.7"/>
<Timestamp>
@@ -171,7 +171,7 @@ Result:
<pre class="result">
<?xml version="1.0" standalone="yes"?>
<PMML version="4.1" xmlns="http://www.dmg.org/pmml-v4-1.html">
- <Header copyright="Copyright (c) 2014 gpadmin">
+ <Header copyright="redacted for this example">
<Extension extender="MADlib" name="user" value="gpadmin"/>
<Application name="MADlib" version="1.7"/>
<Timestamp>
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/recursive_partitioning/decision_tree.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/recursive_partitioning/decision_tree.sql_in b/src/ports/postgres/modules/recursive_partitioning/decision_tree.sql_in
index 798de03..a2538cf 100644
--- a/src/ports/postgres/modules/recursive_partitioning/decision_tree.sql_in
+++ b/src/ports/postgres/modules/recursive_partitioning/decision_tree.sql_in
@@ -349,6 +349,9 @@ provided <em>cp</em> and explore all possible sub-trees (upto a single-node tree
to compute the optimal sub-tree. The optimal sub-tree and the 'cp' corresponding
to this optimal sub-tree is placed in the <em>output_table</em>, with their
columns named as <em>tree</em> and <em>pruning_cp</em> respectively.
+- The main parameters that affect memory usage are: depth of tree, number
+of features, and number of values per feature. If you are hitting VMEM limits,
+consider reducing one or more of these parameters.
@anchor predict
@par Prediction Function
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/recursive_partitioning/random_forest.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/recursive_partitioning/random_forest.sql_in b/src/ports/postgres/modules/recursive_partitioning/random_forest.sql_in
index 22f1ddf..e463225 100644
--- a/src/ports/postgres/modules/recursive_partitioning/random_forest.sql_in
+++ b/src/ports/postgres/modules/recursive_partitioning/random_forest.sql_in
@@ -356,6 +356,11 @@ forest_train(training_table_name,
table is expected to be used for training each tree in the forest. A ratio that
is close to 0 may result in trees with only the root node.
This allows users to experiment with the function in a speedy fashion.</DD>
+
+ @note The main parameters that affect memory usage are: depth of tree, number
+ of features, and number of values per feature. If you are hitting VMEM limits,
+ consider reducing one or more of these parameters.
+
</DL>
@anchor predict
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/regress/linear.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/regress/linear.sql_in b/src/ports/postgres/modules/regress/linear.sql_in
index d794d5b..81261ce 100644
--- a/src/ports/postgres/modules/regress/linear.sql_in
+++ b/src/ports/postgres/modules/regress/linear.sql_in
@@ -203,7 +203,7 @@ COPY houses FROM STDIN WITH DELIMITER '|';
15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000
\\.
</pre>
--# Train a regression model. First, a single regression for all the data.
+-# Train a regression model. First, we generate a single regression for all data.
<pre class="example">
SELECT madlib.linregr_train( 'houses',
'houses_linregr',
@@ -211,7 +211,11 @@ SELECT madlib.linregr_train( 'houses',
'ARRAY[1, tax, bath, size]'
);
</pre>
--# Generate three output models, one for each value of "bedroom".
+(Note that in this example we are dynamically creating the array of independent variables
+from column names. If you have large numbers of independent variables beyond the PostgreSQL
+limit of maximum columns per table, you would pre-build the arrays and store them in a
+single column.)
+-# Next we generate three output models, one for each value of "bedroom".
<pre class="example">
SELECT madlib.linregr_train( 'houses',
'houses_linregr_bedroom',
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/regress/logistic.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/regress/logistic.sql_in b/src/ports/postgres/modules/regress/logistic.sql_in
index 705bf70..a27e154 100644
--- a/src/ports/postgres/modules/regress/logistic.sql_in
+++ b/src/ports/postgres/modules/regress/logistic.sql_in
@@ -305,6 +305,10 @@ SELECT madlib.logregr_train( 'patients',
'irls'
);
</pre>
+(Note that in this example we are dynamically creating the array of independent variables
+from column names. If you have large numbers of independent variables beyond the PostgreSQL
+limit of maximum columns per table, you would pre-build the arrays and store them in a
+single column.)
-# View the regression results.
<pre class="example">
-- Set extended display on for easier reading of output
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/stats/pred_metrics.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/stats/pred_metrics.sql_in b/src/ports/postgres/modules/stats/pred_metrics.sql_in
index 1227f1a..5358263 100644
--- a/src/ports/postgres/modules/stats/pred_metrics.sql_in
+++ b/src/ports/postgres/modules/stats/pred_metrics.sql_in
@@ -38,41 +38,41 @@ m4_include(`SQLCommon.m4')
<li><a href="#list">List of Prediction Metric Functions</a></li>
<li><a href="#specs">Function Specific Details</a></li>
<li><a href="#examples">Examples</a></li>
+<li><a href="#literature">Literature</a></li>
<li><a href="#related">Related Topics</a></li>
</ul>
</div>
@brief Provides various prediction accuracy metrics.
-This module provides a set of prediction accuracy metrics. It is a support
-module for several machine learning algorithms that require metrics to validate
-their models. A typical function will take a set of "prediction" and
-"observation" values to calculate the desired metric, unless noted otherwise.
-Grouping is supported by all of these functions (except confusion matrix).
+This module provides a set of metrics to evaluate the quality of predictions of a model.
+A typical function will take a set of "prediction" and
+"observation" values and use them to calculate the desired metric, unless noted otherwise.
+Grouping is supported for all functions (except confusion matrix).
@anchor list
@par Prediction Metrics Functions
<table class="output">
<tr><th>mean_abs_error(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> Mean Absolute Error. </td></tr>
+ grouping_cols)</th><td> Mean absolute error </td></tr>
<tr><th>mean_abs_perc_error(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> Mean Absolute Percentage Error. </td></tr>
+ grouping_cols)</th><td> Mean absolute percentage error </td></tr>
<tr><th>mean_perc_error(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> Mean Percentage Error. </td></tr>
+ grouping_cols)</th><td> Mean percentage error </td></tr>
<tr><th>mean_squared_error(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> Mean Squared Error.</td></tr>
+ grouping_cols)</th><td> Mean squared error</td></tr>
<tr><th>r2_score(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> R-squared. </td></tr>
+ grouping_cols)</th><td> R-squared </td></tr>
<tr><th>adjusted_r2_score(table_in, table_out, prediction_col, observed_col,
- num_predictors, training_size, grouping_cols)</th><td> Adjusted R-squared.
+ num_predictors, training_size, grouping_cols)</th><td> Adjusted R-squared
</td></tr>
<tr><th>binary_classifier(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> Collection of prediction metrics related to binary classification.</td></tr>
+ grouping_cols)</th><td> Collection of prediction metrics related to binary classification</td></tr>
<tr><th>area_under_roc(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> Area under the ROC curve (in binary classification).
+ grouping_cols)</th><td> Area under the ROC curve (in binary classification)
</td></tr>
<tr><th>confusion_matrix(table_in, table_out, prediction_col, observed_col,
- grouping_cols)</th><td> Confusion matrix for a multi-class classifier.
+ grouping_cols)</th><td> Confusion matrix for a multi-class classifier
</td></tr>
</table>
@@ -81,15 +81,15 @@ Grouping is supported by all of these functions (except confusion matrix).
<DT>table_in</DT>
<DD>TEXT. Name of the input table.</DD>
<DT>table_out</DT>
-<DD>TEXT. Name of the output table.</DD>
+<DD>TEXT. Name of the output table. For consistency, a table is created for all metric outputs even when grouping is not used, which may mean there is only a single value in the output table in some cases. </DD>
<DT>prediction_col</DT>
<DD>TEXT. Name of the column of predicted values from input table.</DD>
<DT>observed_col</DT>
<DD>TEXT. Name of the column of observed values from input table.</DD>
-<DT>num_predictors</DT>
+<DT>num_predictors (for adjusted R-squared score only)</DT>
<DD>INTEGER. The number of parameters in the predicting model, not counting the
constant term.</DD>
-<DT>training_size</DT>
+<DT>training_size (for adjusted R-squared score only)</DT>
<DD>INTEGER. The number of rows used for training, excluding any NULL rows.</DD>
<DT>grouping_cols (optional)</DT>
<DD>TEXT, default: NULL. Name of the column of grouping values from input
@@ -105,28 +105,27 @@ This function returns the coefficient of determination (R2) between the
predicted and observed values. An R2 of 1 indicates that the regression line
perfectly fits the data, while an R2 of 0 indicates that the line does not fit
the data at all. Negative values of R2 may occur when fitting non-linear
-functions to data. Please refer to the reference <a href="#r2">[1]</a> for
-further details.
+functions to data. Please refer to reference <a href="#r2">[1]</a> for
+more details.
<b>Adjusted R-squared Score</b>
-This function returns the adjusted R2 score. Adjusted R2 score is used to
+This function returns the adjusted R2 score in addition to the R-squared score
+described above. Adjusted R2 score is used to
counter the problem of the R2 automatically increasing when extra explanatory
-variables are added to the model. It takes additional two integers describing
-the degrees of freedom of the model and the size of the training set over which
-it was developed, and returning the adjusted R-squared prediction accuracy
-metric. Please refer to the reference <a href="#r2">[1]</a> for further details.
-
-Arguments:
+variables are added to the model. It takes two additional parameters describing
+the degrees of freedom of the model (num_predictors) and the size of the
+training set over which it was developed (training_size):
- num_predictors: Indicates the number of parameters the model has other than
the constant term. For example, if it is set to '3' the model may take the
-following form, 7 + 5x + 39y + 0.91z.
+following form as an example: 7 + 5x + 39y + 0.91z.
- training_size: Indicates the number of rows in the training set (excluding
any NULL rows).
Neither of these arguments can be deduced from the predicted values and the test
- data alone.
+data alone which is why they are explicit inputs. Please refer to
+reference <a href="#r2">[1]</a> for more details.
@anchor bc
<b>Binary Classification</b>
@@ -134,7 +133,7 @@ Neither of these arguments can be deduced from the predicted values and the test
This function returns an output table with a number of metrics commonly used in
binary classification.
-The definitions of the various metrics are as follows.
+The definitions of the various metrics are as follows:
- \f$\textit{tp}\f$ is the count of correctly-classified positives.
- \f$\textit{tn}\f$ is the count of correctly-classified negatives.
@@ -152,27 +151,24 @@ The definitions of the various metrics are as follows.
- \f$\textit{f1}=2*\textit{tp}/(2*\textit{tp}+\textit{fp}+\textit{fn})\f$.
-<b>Area under ROC Curve</b>
+<b>Area Under ROC Curve</b>
This function returns the area under the Receiver Operating Characteristic curve
for binary classification (the AUC). The ROC curve is the curve relating the
classifier's TPR and FPR metrics. (See <a href="#bc">Binary Classification</a>
-for a definition of these metrics). Please refer to the reference <a
-href="#aoc">[2]</a> for further details. Note that the binary classification
-function can be used to obtain the data (tpr and fpr values) required for
+above for a definition of these metrics). Please refer to reference <a
+href="#aoc">[2]</a> for more details. Note that the binary classification
+function can be used to obtain the data (TPR and FPR values) required for
drawing the ROC curve.
-@note For 'binary_classifier' and 'area_under_roc' functions,
-
-The 'observed_col' column is assumed to be a numeric column with
-two levels: 0 and 1 or a boolean column. For the purposes of the metric
-calculation 0 is considered a negative and 1 is a positive.
-
-The 'pred_col' column is expected to contain numeric values corresponding to
-likelihood/probability: a larger value corresponds to greater certainty that the
-observed value will be '1', lower value corresponds to a greater certainty that
-it will be '0'
-
+@note For 'binary_classifier' and 'area_under_roc' functions:
+- The 'observed_col' column is assumed to be a numeric column with
+two values: 0 and 1, or a Boolean column. For the purposes of the metric
+calculation, 0 is considered to be negative and 1 to be positive.
+- The 'pred_col' column is expected to contain numeric values corresponding to
+likelihood/probability. A larger value corresponds to greater certainty that the
+observed value will be '1', and a lower value corresponds to a greater certainty that
+it will be '0'.
<b>Confusion Matrix</b>
@@ -180,13 +176,13 @@ This function returns the confusion matrix of a multi-class classification. Each
column of the matrix represents the instances in a predicted class while each
row represents the instances in an actual class. This allows more detailed
analysis than mere proportion of correct guesses (accuracy). Please refer to the
-reference <a href="#cm">[3]</a> for further details. As noted earlier, grouping
-is not supported for confusion matrix.
+reference <a href="#cm">[3]</a> for more details. Please note that grouping
+is not supported for the confusion matrix.
@anchor examples
@examp
--# Create the sample data.
+-# Create the sample data:
<pre class="example">
DROP TABLE IF EXISTS test_set;
CREATE TABLE test_set(
@@ -200,7 +196,7 @@ INSERT INTO test_set VALUES
-# Run the Mean Absolute Error function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT mean_abs_error( 'test_set', 'table_out', 'pred', 'obs');
+SELECT madlib.mean_abs_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
</pre>
Result
@@ -213,7 +209,7 @@ Result
-# Run the Mean Absolute Percentage Error function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT mean_abs_perc_error( 'test_set', 'table_out', 'pred', 'obs');
+SELECT madlib.mean_abs_perc_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
</pre>
Result
@@ -226,7 +222,7 @@ Result
-# Run the Mean Percentage Error function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT mean_perc_error( 'test_set', 'table_out', 'pred', 'obs');
+SELECT madlib.mean_perc_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
</pre>
Result
@@ -239,7 +235,7 @@ Result
-# Run the Mean Squared Error function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT mean_squared_error( 'test_set', 'table_out', 'pred', 'obs');
+SELECT madlib.mean_squared_error( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
</pre>
Result
@@ -252,7 +248,7 @@ Result
-# Run the R2 Score function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT r2_score( 'test_set', 'table_out', 'pred', 'obs');
+SELECT madlib.r2_score( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
</pre>
Result
@@ -265,17 +261,17 @@ Result
-# Run the Adjusted R2 Score function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT adjusted_r2_score( 'test_set', 'table_out', 'pred', 'obs', 3, 100);
+SELECT madlib.adjusted_r2_score( 'test_set', 'table_out', 'pred', 'obs', 3, 100);
SELECT * FROM table_out;
</pre>
Result
<pre class="result">
- adjusted_r2_score
- ------------------------
- 0.25742687245723248861
+ r2_score | adjusted_r2_score
+ --------------------+------------------
+ 0.279929088443375 | 0.257426872457231
</pre>
--# Create the sample data for binary classifier metrics.
+-# Create the sample data for binary classifier metrics:
<pre class="example">
DROP TABLE IF EXISTS test_set;
CREATE TABLE test_set AS
@@ -287,14 +283,14 @@ CREATE TABLE test_set AS
-# Run the Binary Classifier metrics function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT binary_classifier( 'test_set', 'table_out', 'pred', 'obs');
+SELECT madlib.binary_classifier( 'test_set', 'table_out', 'pred', 'obs');
</pre>
--# View the True Positive Rate and the False Positive Rate.:
+-# View the True Positive Rate and the False Positive Rate:
<pre class="example">
SELECT threshold, tpr, fpr FROM table_out ORDER BY threshold;
</pre>
-Result
+Result (your results for this and other functions below will look different due to the presence of the random function in sample data generator):
<pre class="result">
threshold | tpr | fpr
------------------------+------------------------+------------------------
@@ -337,7 +333,7 @@ f1 | 0.63736263736263736264
-# Run the Area Under ROC curve function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT area_under_roc( 'test_set', 'table_out', 'pred', 'obs');
+SELECT madlib.area_under_roc( 'test_set', 'table_out', 'pred', 'obs');
SELECT * FROM table_out;
</pre>
Result
@@ -356,11 +352,11 @@ CREATE TABLE test_set AS
FROM generate_series(1,5) x,
generate_series(1,5) y;
</pre>
--# Run the Confusion Matrix Function:
+-# Run the confusion matrix function:
<pre class="example">
DROP TABLE IF EXISTS table_out;
-SELECT confusion_matrix( 'test_set', 'table_out', 'pred', 'obs');
-SELECT * FROM table_out;
+SELECT madlib.confusion_matrix( 'test_set', 'table_out', 'pred', 'obs');
+SELECT * FROM table_out ORDER BY class;
</pre>
Result
<pre class="result">
@@ -374,8 +370,8 @@ Result
5 | {0,0,0,0,0,0}
</pre>
-@anchor related
-@par Related Topics
+@anchor literature
+@par Literature
@anchor r2
@@ -389,6 +385,9 @@ Result
[3] https://en.wikipedia.org/wiki/Confusion_matrix
+@anchor related
+@par Related Topics
+
File pred_metrics.sql_in for list of functions and usage.
**/
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/svm/svm.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/svm/svm.sql_in b/src/ports/postgres/modules/svm/svm.sql_in
index ce7ec69..f3948a8 100644
--- a/src/ports/postgres/modules/svm/svm.sql_in
+++ b/src/ports/postgres/modules/svm/svm.sql_in
@@ -79,10 +79,8 @@ svm_classification(
<DD>TEXT. Expression list to evaluate for the
independent variables. An intercept variable should not be included as part
of this expression. See 'fit_intercept' in the kernel params for info on
- intercepts.
-
- @note Expression should be able to be cast into DOUBLE PRECISION[].
- </DD>
+ intercepts. Please note that expression should be able to be cast
+ to DOUBLE PRECISION[].
<DT>kernel_func (optional)</DT>
<DD>TEXT, default: 'linear'.
@@ -245,20 +243,18 @@ svm_regression(source_table,
verbose
)
</pre>
+
\b Arguments
Specifications for regression are largely the same as for classification. In the
model table, there is no dependent variable mapping. The following
arguments have specifications which differ from svm_classification:
-
<DL class="arglist">
-
<DT>dependent_varname</DT>
<DD>TEXT. Name of the dependent variable column. For regression, this column
can contain only values or expressions that can be cast to DOUBLE PRECISION.
Otherwise, an error will be thrown.
</DD>
-
<DT>params (optional)</DT>
<DD>TEXT, default: NULL.
The parameters \e epsilon and \e eps_table are only meaningful for regression.
@@ -343,17 +339,20 @@ the parameter is ignored.
Hyperparameter optimization can be carried out using the built-in cross
validation mechanism, which is activated by assigning a value greater than 1 to
-the parameter \e n_folds in \e params. Please note that cross validation is not
+the parameter \e n_folds in \e params.
+Please note that cross validation is not
supported if grouping is used.
+
The values of a parameter to cross validate should be provided in a list. For
example, if one wanted to regularize with the L1 norm and use a lambda value
from the set {0.3, 0.4, 0.5}, one might input 'lambda={0.3, 0.4, 0.5}, norm=L1,
n_folds=10' in \e params. Note that the use of '{}' and '[]' are both valid
here.
-
-Please note that not all of the parameters below can be cross-validated. For
+@note
+Note that not all of the parameters below can be cross-validated. For
parameters where cross validation is allowed, their default values are presented
in list format; e.g., [0.01].
+
<pre class="syntax">
'init_stepsize = <value>,
decay_factor = <value>,
@@ -364,7 +363,8 @@ in list format; e.g., [0.01].
epsilon = <value>,
eps_table = <value>,
validation_result = <value>,
- n_folds = <value>'
+ n_folds = <value>,
+ class_weight = <value>'
</pre>
\b Parameters
<DL class="arglist">
@@ -442,7 +442,8 @@ while the other k - 1 folds form the training set.
</DD>
<DT>class_weight</dt>
-<DD>Default: 1
+<DD>Default: 1 for classification, 'balanced' for one-class novelty detection,
+n/a for regression.
Set the weight for the positive and negative classes. If not given, all classes
are set to have weight one.
@@ -462,7 +463,8 @@ For regression, the class weights are always one.
@anchor predict
@par Prediction Function
The prediction function is used to estimate the conditional mean given a new
-predictor. It has the following syntax:
+predictor. The same syntax is used for classification, regression and novelty
+detection:
<pre class="syntax">
svm_predict(model_table,
new_data_table,
@@ -485,15 +487,29 @@ svm_predict(model_table,
<DT>output_table</DT>
<DD>TEXT. Name of the table where output predictions are written. If this
-table name is already in use, then an error is returned. The table contains the
-id_col_name column giving the 'id' for each prediction and the prediction
-columns for the dependent variable.</DD>
+table name is already in use, then an error is returned. Table contains:</DD>
+ <table class="output">
+ <tr>
+ <th>id</th>
+ <td>Gives the 'id' for each prediction, corresponding to each row from the new_data_table.</td>
+ </tr>
+ <tr>
+ <th>prediction</th>
+ <td>Provides the prediction for each row in new_data_table.
+ For regression this would be the same as decision_function. For classification,
+ this will be one of the dependent variable values.</td>
+ </tr>
+ <tr>
+ <th>decision_function</th>
+ <td>Provides the distance between each point and the separating hyperplane.</td>
+ </tr>
</DL>
-
+</table>
@anchor example
@par Examples
-# Create an input data set.
<pre class="example">
+DROP TABLE IF EXISTS houses;
CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
size INT, lot INT);
COPY houses FROM STDIN WITH DELIMITER '|';
@@ -516,12 +532,30 @@ COPY houses FROM STDIN WITH DELIMITER '|';
</pre>
-# Train a classification model. First, use a linear model.
<pre class="example">
+DROP TABLE IF EXISTS houses_svm, houses_svm_summary;
SELECT madlib.svm_classification('houses',
'houses_svm',
'price < 100000',
'ARRAY[1, tax, bath, size]'
);
</pre>
+-# View the result for the linear classification model.
+<pre class="example">
+-- Set extended display on for easier reading of output
+\\x ON
+SELECT * FROM houses_svm;
+</pre>
+Result:
+<pre class="result">
+-[ RECORD 1 ]------+---------------------------------------------------------------
+coef | {0.152192069515,-0.29631947495,0.0968619000065,0.362682248051}
+loss | 601.279740124
+norm_of_gradient | 1300.96615851627
+num_iterations | 100
+num_rows_processed | 15
+num_rows_skipped | 0
+dep_var_mapping | {f,t}
+</pre>
-# Next generate a nonlinear model using a Gaussian kernel. This time we specify
the initial step size and maximum number of iterations to run. As part of the
kernel parameter, we choose 10 as the dimension of the space where we train
@@ -530,6 +564,7 @@ overfitting. As a result, the model will be a 10 dimensional vector, instead
of 4 as in the case of linear model, which we will verify when we examine the
models.
<pre class="example">
+DROP TABLE IF EXISTS houses_svm_gaussian, houses_svm_gaussian_summary, houses_svm_gaussian_random;
SELECT madlib.svm_classification( 'houses',
'houses_svm_gaussian',
'price < 100000',
@@ -540,16 +575,35 @@ SELECT madlib.svm_classification( 'houses',
'init_stepsize=1, max_iter=200'
);
</pre>
--# Now train a regression model. First, use a linear model.
+-# View the results from kernel SVM for classification.
+<pre class="example">
+-- Set extended display on for easier reading of output
+\\x ON
+SELECT * FROM houses_svm_gaussian;
+</pre>
+Result:
+<pre class="result">
+-[ RECORD 1 ]------+--------------------------------------------------------------------------------------------------------------------------------------------------
+coef | {0.183800813574,-0.78724997813,1.54121854068,1.24432527042,4.01230959334,1.07061097224,-4.92576349408,0.437699542875,0.3128600981,-1.63880635658}
+loss | 0.998735180388
+norm_of_gradient | 0.729823950583579
+num_iterations | 196
+num_rows_processed | 15
+num_rows_skipped | 0
+dep_var_mapping | {f,t}
+</pre>
+-# The regression models have a similar format (model output not shown). First, for a linear model:
<pre class="example">
+DROP TABLE IF EXISTS houses_svm_regression, houses_svm_regression_summary;
SELECT madlib.svm_regression('houses',
'houses_svm_regression',
'price',
'ARRAY[1, tax, bath, size]'
);
</pre>
--# Next train a non-linear regression model using a Gaussian kernel.
+For a non-linear regression model using a Gaussian kernel:
<pre class="example">
+DROP TABLE IF EXISTS houses_svm_gaussian_regression, houses_svm_gaussian_regression_summary, houses_svm_gaussian_regression_random;
SELECT madlib.svm_regression( 'houses',
'houses_svm_gaussian_regression',
'price',
@@ -560,214 +614,157 @@ SELECT madlib.svm_regression( 'houses',
'init_stepsize=1, max_iter=200'
);
</pre>
--# Now train a non-linear one-class SVM for novelty detection, using a Gaussian kernel.
+-# Now train a non-linear one-class SVM for novelty detection, using a Gaussian kernel.
+Note that the dependent variable is not a parameter for one-class:
<pre class="example">
+DROP TABLE IF EXISTS houses_one_class_gaussian, houses_one_class_gaussian_summary, houses_one_class_gaussian_random;
select madlib.svm_one_class('houses',
'houses_one_class_gaussian',
'ARRAY[1,tax,bedroom,bath,size,lot,price]',
'gaussian',
- 'gamma=0.01,n_components=10',
+ 'gamma=0.5,n_components=55, random_state=3',
NULL,
- 'max_iter=250, init_stepsize=100,lambda=0.9'
+ 'max_iter=100, init_stepsize=10,lambda=10, tolerance=0'
);
</pre>
--# View the result for the linear classification model.
+-# View the result for the Gaussian novelty detection model.
<pre class="example">
-- Set extended display on for easier reading of output
\\x ON
-SELECT * FROM houses_svm;
+SELECT * FROM houses_one_class_gaussian;
</pre>
Result:
<pre class="result">
--[ RECORD 1 ]+---------------------------------------------------------------------------
-coef | [0.113989576847, -0.00226133300602, -0.0676303607996, 0.00179440841072]
-loss | 9.21745071385
-norm_of_gradient | 108.171180769
+-[ RECORD 1 ]------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+coef | {redacted for brevity}
+loss | 15.1053343738
+norm_of_gradient | 13.9133653663837
num_iterations | 100
-num_rows_processed | 15
-num_rows_skipped | 0
-dep_var_mapping | [False, True]
+num_rows_processed | 16
+num_rows_skipped | -1
+dep_var_mapping | {-1,1}
</pre>
--# View the results from kernel SVM for classification.
-<pre class="example">
--- Set extended display on for easier reading of output
-\\x ON
-SELECT * FROM houses_svm_gaussian;
-</pre>
-Result:
-<pre class="result">
--[ RECORD 1 ]+---------------------------------------------------------------------------
-coef | [-2.00789985255, 2.02625531256, -1.09903715824, 2.04431020735, 3.14208435644, 0.14838741816, 2.07527256499, 3.0816372960, 0.853428649407, 3.63747384926]
-loss | 0.255909866744
-norm_of_gradient | 0.0715415776655
-num_iterations | 184
-num_rows_processed | 15
-num_rows_skipped | 0
-dep_var_mapping | [False, True]
-</pre>
--# View the result for the linear regression model.
+-# Now let's look at the prediction functions. We want to predict if house price
+is less than $100,000. In the following examples we will
+use the training data set for prediction as well, which is not usual but serves to
+show the syntax. The predicted results are in the \e prediction column and the
+actual data is in the \e target column.
+For the linear model:
<pre class="example">
--- Set extended display on for easier reading of output
-\\x ON
-SELECT * FROM houses_svm_regression;
+DROP TABLE IF EXISTS houses_pred;
+SELECT madlib.svm_predict('houses_svm', 'houses', 'id', 'houses_pred');
+SELECT *, price < 100000 AS target FROM houses JOIN houses_pred USING (id) ORDER BY id;
</pre>
Result:
<pre class="result">
--[ RECORD 1 ]------+-----------------------------------------------------------
-coef | {0.108331895548,38.7056695139,0.40444087325,43.3271927438}
-loss | 342988.843452
-norm_of_gradient | 1659.20424823876
-num_iterations | 183
-num_rows_processed | 15
-num_rows_skipped | 0
-dep_var_mapping | {NULL}
+ id | tax | bedroom | bath | price | size | lot | prediction | decision_function | target
+----+------+---------+------+--------+------+-------+------------+--------------------+--------
+ 1 | 590 | 2 | 1 | 50000 | 770 | 22100 | t | 104.685894748292 | t
+ 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 | t | 200.592436923938 | t
+ 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 | t | 378.765847404582 | t
+ 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 | t | 214.034895129328 | t
+ 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 | t | 153.227581012028 | f
+ 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 | f | -102.382793811158 | t
+ 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 | f | -53.8237999423388 | f
+ 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 | t | 223.090041223192 | f
+ 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 | f | -0.858545961972027 | f
+ 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 | f | -78.226279884182 | f
+ 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 | f | -118.078558954948 | t
+ 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 | f | -26.3388234857219 | f
+ 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | f | -279.923699905712 | f
+ 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | f | -50.7810508979155 | f
+ 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | t | 333.579085875975 | t
</pre>
--# View the results from kernel SVM for regression.
+Prediction using the Gaussian model:
<pre class="example">
--- Set extended display on for easier reading of output
-\\x ON
-SELECT * FROM houses_svm_gaussian_regression;
+DROP TABLE IF EXISTS houses_pred_gaussian;
+SELECT madlib.svm_predict('houses_svm_gaussian', 'houses', 'id', 'houses_pred_gaussian');
+SELECT *, price < 100000 AS target FROM houses JOIN houses_pred_gaussian USING (id) ORDER BY id;
</pre>
-Result:
+This produces a more accurate result than the linear case for this small data set:
<pre class="result">
--[ RECORD 1 ]------+----------------------------------------------------------------------------------------------------------------------------------------------------
-coef | {12.6287285299,-12.9733002831,0.848080861156,5.94293328578,-6.34552545847,-19.7726249109,5.50362313774,-10.3908022228,-3.36031145266,16.0655950574}
-loss | 1831979.57085
-norm_of_gradient | 3.29486194349202
-num_iterations | 107
-num_rows_processed | 15
-num_rows_skipped | 0
-dep_var_mapping | {NULL}
+ id | tax | bedroom | bath | price | size | lot | prediction | decision_function | target
+----+------+---------+------+--------+------+-------+------------+-------------------+--------
+ 1 | 590 | 2 | 1 | 50000 | 770 | 22100 | t | 1.00338548176312 | t
+ 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 | t | 1.00000000098154 | t
+ 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 | t | 0.246566699635389 | t
+ 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 | t | 1.0000000003367 | t
+ 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 | f | -1.98940593324397 | f
+ 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 | t | 3.74336995109761 | t
+ 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 | f | -1.01574407296086 | f
+ 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 | f | -1.0000000002071 | f
+ 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 | f | -3.88267069310101 | f
+ 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 | f | -3.44507576539002 | f
+ 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 | t | 2.3409866081761 | t
+ 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 | f | -3.51563221173085 | f
+ 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | f | -1.00000000011163 | f
+ 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | f | -1.87710363254055 | f
+ 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | t | 1.34334834982263 | t
</pre>
--# View the result for the Gaussian novelty detection model.
+-# Prediction using the linear regression model:
<pre class="example">
--- Set extended display on for easier reading of output
-\\x ON
-SELECT * FROM houses_one_class_gaussian;
+DROP TABLE IF EXISTS houses_regr;
+SELECT madlib.svm_predict('houses_svm_regression', 'houses', 'id', 'houses_regr');
+SELECT * FROM houses JOIN houses_regr USING (id) ORDER BY id;
</pre>
-Result:
+Result for the linear regression model:
<pre class="result">
--[ RECORD 1 ]------+-----------------------------------------------------------------------------------------------------------------------------------------------------------
-coef | {0.653884911436,-0.963113029604,-0.49030220733,-0.352594122599,-1.29858359915,0.821120203574,-0.0657750847612,0.563678190211,0.822382924741,1.08544389673}
-loss | 7.29444919305
-norm_of_gradient | 0.76407243171229
-num_iterations | 208
-num_rows_processed | 15
-num_rows_skipped | 0
-dep_var_mapping | {1,-1}
+ id | tax | bedroom | bath | price | size | lot | prediction | decision_function
+----+------+---------+------+--------+------+-------+------------------+-------------------
+ 1 | 590 | 2 | 1 | 50000 | 770 | 22100 | 55288.6992755623 | 55288.6992755623
+ 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 | 99978.8137019119 | 99978.8137019119
+ 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 | 43157.5130381023 | 43157.5130381023
+ 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 | 88098.9557296729 | 88098.9557296729
+ 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 | 114803.884262468 | 114803.884262468
+ 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 | 88899.5186193813 | 88899.5186193813
+ 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 | 201108.397013076 | 201108.397013076
+ 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 | 75004.3236915733 | 75004.3236915733
+ 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 | 136434.749667136 | 136434.749667136
+ 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 | 264483.856987395 | 264483.856987395
+ 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 | 110180.048139857 | 110180.048139857
+ 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 | 117300.841695563 | 117300.841695563
+ 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | 199229.683967752 | 199229.683967752
+ 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | 147998.930271016 | 147998.930271016
+ 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | 84936.7661235861 | 84936.7661235861
</pre>
--# Use the prediction function to evaluate the models. The predicted results are in the \e prediction column and the actual data is in the \e target column.
-For the linear model:
+For the non-linear Gaussian regression model (output not shown):
<pre class="example">
-SELECT madlib.svm_predict('houses_svm', 'houses', 'id', 'houses_pred');
-SELECT *, price < 100000 AS target FROM houses JOIN houses_pred USING (id) ORDER BY id;
-</pre>
-For the Gaussian model:
-<pre class="example">
-SELECT madlib.svm_predict('houses_svm_gaussian', 'houses', 'id', 'houses_pred_gaussian');
-SELECT *, price < 100000 AS target FROM houses JOIN houses_pred_gaussian USING (id) ORDER BY id;
-</pre>
-For the linear regression model:
-<pre class="example">'m1','houses','id','regre'
-SELECT madlib.svm_predict('houses_svm_regression', 'houses', 'id', 'houses_regr');
-SELECT * FROM houses JOIN houses_regr USING (id) ORDER BY id;
-</pre>
-For the non-linear Gaussian regression model:
-<pre class="example">'m1','houses','id','regre'
+DROP TABLE IF EXISTS houses_gaussian_regr;
SELECT madlib.svm_predict('houses_svm_gaussian_regression', 'houses', 'id', 'houses_gaussian_regr');
SELECT * FROM houses JOIN houses_gaussian_regr USING (id) ORDER BY id;
</pre>
--# Create test data set for novelty detection.
+-# For the novelty detection using one-class, let's create a test data set using
+the last 3 values from the training set plus an outlier at the end (10x price):
<pre class="example">
-DROP TABLE IF EXISTS houses_novelty_test;
-CREATE TABLE houses_novelty_test (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
+DROP TABLE IF EXISTS houses_one_class_test;
+CREATE TABLE houses_one_class_test (id INT, tax INT, bedroom INT, bath FLOAT, price INT,
size INT, lot INT);
-COPY houses_novelty_test FROM STDIN WITH DELIMITER '|';
- 1 | 33590 | 12 | 11 | 5000000 | 12770 | 221100
- 2 | 1050 | 31 | 21 | 85000000 | 141210 | 120010
- 3 | 233330 | 13 | 11 | 22500000 | 112060 | 351100
- 4 | 833370 | 12 | 12 | 9000000 | 130120 | 1751100
- 5 | 132330 | 31 | 12 | 133000000 | 150120 | 30011100
- 6 | 135330 | 21 | 11 | 90500000 | 8212120 | 25711100
- 7 | 279330 | 31 | 21.5 | 260000000 | 213012 | 25011100
- 8 | 6803333 | 12 | 11 | 142500000 | 117012 | 22111000
- 9 | 33331840 | 31 | 12 | 160000000 | 150120 | 19011100
- 10 | 3780 | 4 | 2 | 220000 | 2790 | 21000
- 11 | 1760 | 3 | 1 | 77000 | 1030 | 18500
- 12 | 1520 | 3 | 2 | 128600 | 1250 | 21000
- 13 | 3000 | 3 | 2 | 130000 | 1760 | 37000
- 14 | 2170 | 2 | 3 | 138000 | 1550 | 13000
- 15 | 750 | 3 | 1.5 | 75000 | 1450 | 13000
+COPY houses_one_class_test FROM STDIN WITH DELIMITER '|';
+ 1 | 3100 | 3 | 2 | 140000 | 1760 | 38000
+ 2 | 2070 | 2 | 3 | 148000 | 1550 | 14000
+ 3 | 650 | 3 | 1.5 | 65000 | 1450 | 12000
+ 4 | 650 | 3 | 1.5 | 650000 | 1450 | 12000
\\.
</pre>
-Evaluate the Gaussian one-class novelty detection model with the prediction function:
+Now run prediction on the Gaussian one-class novelty detection model:
<pre class="example">
-SELECT madlib.svm_predict('houses_one_class_gaussian', 'houses_novelty_test', 'id', 'houses_novelty_predict');
-SELECT * FROM houses_novelty_test JOIN houses_novelty_predict USING (id) ORDER BY id;
-</pre>
-Result for the Gaussian classification model:
-<pre class="result">
- id | tax | bedroom | bath | price | size | lot | prediction | target
-----+------+---------+------+--------+------+-------+------------+--------
- 1 | 590 | 2 | 1 | 50000 | 770 | 22100 | t | t
- 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 | t | t
- 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 | t | t
- 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 | t | t
- 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 | f | f
- 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 | t | t
- 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 | f | f
- 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 | f | f
- 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 | f | f
- 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 | f | f
- 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 | t | t
- 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 | f | f
- 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | f | f
- 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | f | f
- 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | t | t
+DROP TABLE IF EXISTS houses_once_class_pred;
+SELECT madlib.svm_predict('houses_one_class_gaussian', 'houses_one_class_test', 'id', 'houses_one_class_pred');
+SELECT * FROM houses_one_class_test JOIN houses_one_class_pred USING (id) ORDER BY id;
</pre>
-Result for the linear regression model:
-<pre class="result">
- id | tax | bedroom | bath | price | size | lot | prediction
-----+------+---------+------+--------+------+-------+------------------
- 1 | 590 | 2 | 1 | 50000 | 770 | 22100 | 56191.8809366501
- 2 | 1050 | 3 | 2 | 85000 | 1410 | 12000 | 101723.720832136
- 3 | 20 | 3 | 1 | 22500 | 1060 | 3500 | 46774.6724252011
- 4 | 870 | 2 | 2 | 90000 | 1300 | 17500 | 90001.6946038879
- 5 | 1320 | 3 | 2 | 133000 | 1500 | 30000 | 116051.892408163
- 6 | 1350 | 2 | 1 | 90500 | 820 | 25700 | 87698.7223915241
- 7 | 2790 | 3 | 2.5 | 260000 | 2130 | 25000 | 200136.782523288
- 8 | 680 | 2 | 1 | 142500 | 1170 | 22000 | 77025.2857759612
- 9 | 1840 | 3 | 2 | 160000 | 1500 | 19000 | 136124.528457551
- 10 | 3680 | 4 | 2 | 240000 | 2790 | 20000 | 263134.504674981
- 11 | 1660 | 3 | 1 | 87000 | 1030 | 17500 | 108778.731320579
- 12 | 1620 | 3 | 2 | 118600 | 1250 | 20000 | 116782.700138583
- 13 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | 196045.611244141
- 14 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | 147173.127261586
- 15 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | 88018.958223626
-</pre>
-Result for the Gaussian one-class novelty detection model:
+Result showing the last row predicted to be novel:
<pre class="result">
- id | tax | bedroom | bath | price | size | lot | prediction
-----+----------+---------+------+-----------+---------+----------+------------
- 1 | 33590 | 12 | 11 | 5000000 | 12770 | 221100 | 1
- 2 | 1050 | 31 | 21 | 85000000 | 141210 | 120010 | -1
- 3 | 233330 | 13 | 11 | 22500000 | 112060 | 351100 | 1
- 4 | 833370 | 12 | 12 | 9000000 | 130120 | 1751100 | -1
- 5 | 132330 | 31 | 12 | 133000000 | 150120 | 30011100 | -1
- 6 | 135330 | 21 | 11 | 90500000 | 8212120 | 25711100 | -1
- 7 | 279330 | 31 | 21.5 | 260000000 | 213012 | 25011100 | -1
- 8 | 6803333 | 12 | 11 | 142500000 | 117012 | 22111000 | -1
- 9 | 33331840 | 31 | 12 | 160000000 | 150120 | 19011100 | -1
- 10 | 3780 | 4 | 2 | 220000 | 2790 | 21000 | 1
- 11 | 1760 | 3 | 1 | 77000 | 1030 | 18500 | -1
- 12 | 1520 | 3 | 2 | 128600 | 1250 | 21000 | 1
- 13 | 3000 | 3 | 2 | 130000 | 1760 | 37000 | 1
- 14 | 2170 | 2 | 3 | 138000 | 1550 | 13000 | 1
- 15 | 750 | 3 | 1.5 | 75000 | 1450 | 13000 | -1
+ id | tax | bedroom | bath | price | size | lot | prediction | decision_function
+----+------+---------+------+--------+------+-------+------------+---------------------
+ 1 | 3100 | 3 | 2 | 140000 | 1760 | 38000 | 1 | 0.111497008121437
+ 2 | 2070 | 2 | 3 | 148000 | 1550 | 14000 | 1 | 0.0996021345169148
+ 3 | 650 | 3 | 1.5 | 65000 | 1450 | 12000 | 1 | 0.0435064008756942
+ 4 | 650 | 3 | 1.5 | 650000 | 1450 | 12000 | -1 | -0.0168967845338403
</pre>
-Note the result may vary somewhat with the platform configuration you are using.
--# Create a model for an unbalanced class-size dataset.
+-# Create a model for an unbalanced class-size dataset, then use the 'balanced' parameter
+to classify:
<pre class="example">
+DROP TABLE IF EXISTS houses_svm_gaussian, houses_svm_gaussian_summary, houses_svm_gaussian_random;
SELECT madlib.svm_classification( 'houses',
'houses_svm_gaussian',
'price < 150000',
@@ -780,15 +777,16 @@ SELECT madlib.svm_classification( 'houses',
SELECT * FROM houses_svm_gaussian;
</pre>
<pre class="result">
--[ RECORD 1 ]------+-------------------------------------------------------------------------------------------------------------------------------------------------
-coef | {1.07190643807,1.18400471239,-6.13468402103,2.36919976711,-3.04505555661,-1.17673688936,-4.21987167614,1.6623660907,0.527193673617,6.9942801013}
-loss | 15.5928677291
-norm_of_gradient | 3.20435758296471
-num_iterations | 178
-num_rows_processed | 45
-num_rows_skipped | 1
+-[ RECORD 1 ]------+----------------------------------------------------------------------------------------------------------------------------------------------------
+coef | {-0.621843913637,2.4166374426,-1.54726833725,-1.74512599505,1.16231799548,-0.54019307285,-4.14373293694,-0.623069170717,3.59669949057,-1.005501237}
+loss | 1.87657250199
+norm_of_gradient | 1.41148000266816
+num_iterations | 174
+num_rows_processed | 15
+num_rows_skipped | 0
dep_var_mapping | {f,t}
</pre>
+Note that the results you get for all examples may vary with the platform you are using.
@anchor background
@par Technical Background
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/utilities/path.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/path.sql_in b/src/ports/postgres/modules/utilities/path.sql_in
index d832ab5..9f58536 100644
--- a/src/ports/postgres/modules/utilities/path.sql_in
+++ b/src/ports/postgres/modules/utilities/path.sql_in
@@ -100,8 +100,8 @@ path(
<dt>partition_expr</dt>
<dd>VARCHAR. The 'partition_expr' can be a single column or a list of
comma-separated columns/expressions to divide all rows into groups,
- or partitions. Matching is applied across the rows that fall into t
- he same partition. This can be NULL or '' to indicate the matching is
+ or partitions. Matching is applied across the rows that fall into
+ the same partition. This can be NULL or '' to indicate the matching is
to be applied to the whole table.</dd>
<dt>order_expr</dt>
@@ -188,7 +188,7 @@ sessionized.
Create the date table:
<pre class="example">
-DROP TABLE IF EXISTS eventlog, path_output, path_output_tuples;
+DROP TABLE IF EXISTS eventlog;
CREATE TABLE eventlog (event_timestamp TIMESTAMP,
user_id INT,
session_id INT,
@@ -233,7 +233,8 @@ INSERT INTO eventlog VALUES
-# Calculate the revenue by checkout:
<pre class="example">
- SELECT madlib.path(
+DROP TABLE IF EXISTS path_output, path_output_tuples;
+SELECT madlib.path(
'eventlog', -- Name of input table
'path_output', -- Table name to store path results
'session_id', -- Partition input table by session
@@ -302,6 +303,7 @@ regular expression: '(land)[^(land)(buy)]{0,2}(buy)'. In other words, visit
to the landing page followed by from 0 to 2 non-entry, non-sale pages,
followed by a purchase. The SQL is as follows:
<pre class="example">
+DROP TABLE IF EXISTS path_output, path_output_tuples;
SELECT madlib.path(
'eventlog', -- Name of input table
'path_output', -- Table name to store path results
@@ -379,6 +381,7 @@ daily averages, although our sample data set only has a single day.
shopper paths through the site. Since our data set is small, we decide
this means the most frequently viewed page just before a checkout is made:
<pre class="example">
+DROP TABLE IF EXISTS path_output, path_output_tuples;
SELECT madlib.path(
'eventlog', -- Name of input table
'path_output', -- Table name to store path results
@@ -394,9 +397,7 @@ SELECT madlib.path(
FALSE -- Don't persist matches
);
</pre>
-
Now count the common paths and print the most frequent:
-
<pre class="example">
SELECT count(*), page_path from
(SELECT * FROM path_output) q
@@ -404,7 +405,6 @@ GROUP BY page_path
ORDER BY count(*) DESC
LIMIT 10;
</pre>
-
Result:
<pre class="result">
count | page_path
@@ -413,10 +413,61 @@ Result:
1 | {BEER,CHECKOUT}
(2 rows)
</pre>
-
There are only 2 different paths. The wine page is viewed more frequently
than the beer page just before checkout.
+-# To demonstrate the use of 'overlapping_patterns', consider
+a pattern with at least one page followed by and ending with a checkout:
+<pre class="example">
+DROP TABLE IF EXISTS path_output, path_output_tuples;
+SELECT madlib.path(
+ 'eventlog', -- Name of the table
+ 'path_output', -- Table name to store the path results
+ 'session_id', -- Partition by session
+ 'event_timestamp ASC', -- Order partitions in input table by time
+ $$ nobuy:=page<>'CHECKOUT',
+ buy:=page='CHECKOUT'
+ $$, -- Definition of symbols used in the pattern definition
+ '(nobuy)+(buy)', -- At least one page followed by and ending with a CHECKOUT.
+ 'array_agg(page ORDER BY session_id ASC, event_timestamp ASC) as page_path',
+ FALSE, -- Don't persist matches
+ TRUE -- Turn on overlapping patterns
+ );
+SELECT * FROM path_output ORDER BY session_id, match_id;
+</pre>
+Result with overlap turned on:
+<pre class="result">
+ session_id | match_id | page_path
+------------+----------+-----------------------------------
+ 100 | 1 | {LANDING,WINE,CHECKOUT}
+ 100 | 2 | {WINE,CHECKOUT}
+ 102 | 1 | {LANDING,WINE,CHECKOUT}
+ 102 | 2 | {WINE,CHECKOUT}
+ 102 | 3 | {LANDING,HELP,WINE,CHECKOUT}
+ 102 | 4 | {HELP,WINE,CHECKOUT}
+ 102 | 5 | {WINE,CHECKOUT}
+ 103 | 1 | {LANDING,WINE,HELP,WINE,CHECKOUT}
+ 103 | 2 | {WINE,HELP,WINE,CHECKOUT}
+ 103 | 3 | {HELP,WINE,CHECKOUT}
+ 103 | 4 | {WINE,CHECKOUT}
+ 104 | 1 | {BEER,CHECKOUT}
+ 108 | 1 | {BEER,WINE,CHECKOUT}
+ 108 | 2 | {WINE,CHECKOUT}
+(14 rows)
+</pre>
+With overlap turned off, the result would be:
+<pre class="result">
+ session_id | match_id | page_path
+------------+----------+-----------------------------------
+ 100 | 1 | {LANDING,WINE,CHECKOUT}
+ 102 | 1 | {LANDING,WINE,CHECKOUT}
+ 102 | 2 | {LANDING,HELP,WINE,CHECKOUT}
+ 103 | 1 | {LANDING,WINE,HELP,WINE,CHECKOUT}
+ 104 | 1 | {BEER,CHECKOUT}
+ 108 | 1 | {BEER,WINE,CHECKOUT}
+(6 rows)
+</pre>
+
@anchor note
@note
Please note some current limitations of the path algorithm.
http://git-wip-us.apache.org/repos/asf/incubator-madlib/blob/1929aedb/src/ports/postgres/modules/utilities/pivot.sql_in
----------------------------------------------------------------------
diff --git a/src/ports/postgres/modules/utilities/pivot.sql_in b/src/ports/postgres/modules/utilities/pivot.sql_in
index c8164cb..2cadab0 100644
--- a/src/ports/postgres/modules/utilities/pivot.sql_in
+++ b/src/ports/postgres/modules/utilities/pivot.sql_in
@@ -37,6 +37,7 @@ m4_include(`SQLCommon.m4')
<li><a href="#categorical">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>
@@ -51,7 +52,7 @@ the summarized data to a second table.
<pre class="syntax">
pivot(
source_table,
- out_table,
+ output_table,
index,
pivot_cols,
pivot_values,
@@ -64,19 +65,28 @@ pivot(
\b Arguments
<dl class="arglist">
<dt>source_table</dt>
- <dd>VARCHAR. Name of the source table (or view), containing data for
- pivoting.</dd>
+ <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 ('output_table' above) has all the columns present in
- index column list, plus additional columns for each distinct value in
- pivot_cols. The column name for the pivot is set as
- <em>'value column'</em>_<em>'aggregate function'</em> +
- _<em>'pivot column'</em>_<em>'pivot value'</em> for every pivot column.
+ 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.</dd>
+ 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>
@@ -85,9 +95,19 @@ pivot(
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 aggregates or UDAs. It is
- possible to assign a set of aggregate per value column. Please refer to the
- examples 12\-14 for syntax details.</dd>
+ 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
@@ -107,39 +127,32 @@ pivot(
a dictionary output file will be created and a message given to the user.
</dd>
-
</dl>
@anchor notes
@note
-
-NULL values in the index column are treated as any other value.
-
-NULL values in the pivot column are ignored unless keep_null is TRUE.
-
-NULL values in the value column are handled by the aggregate function.
-
-It is not allowed to set the fill_value parameter without setting the
-aggregate_func parameter due to the possible ambiguity. Set
+- 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.
-
-It is not allowed to set the output_col_dictionary parameter without setting
-the keep_null parameter due to the possible ambiguity. Set
+- 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).
-
-It is allowed to pass a partial mapping for the aggregate_func parameter. The
+- 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 toy dataset
+-# Create a simple dataset to demonstrate a basic pivot:
<pre class="example">
-DROP TABLE IF EXISTS pivset;
+DROP TABLE IF EXISTS pivset CASCADE; -- View below may depend on table so use CASCADE
CREATE TABLE pivset(
id INTEGER,
piv INTEGER,
@@ -158,7 +171,7 @@ INSERT INTO pivset VALUES
(1, 10, NULL);
</pre>
--# Pivot the table
+-# Pivot the table:
<pre class="example">
DROP TABLE IF EXISTS pivout;
SELECT madlib.pivot('pivset', 'pivout', 'id', 'piv', 'val');
@@ -171,8 +184,9 @@ SELECT * FROM pivout ORDER BY id;
1 | 7 | 4 | 5.5
| 8 | |
</pre>
+Here NULL is showing as an empty cell in the output.
--# Create a view to tailor the columns as required
+-# 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
@@ -185,21 +199,23 @@ 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
+ id | id2 | piv | piv2 | val | val2
----+-----+-----+------+-----+------
0 | 0 | 10 | 0 | 1 | 11
- 0 | 0 | 10 | 0 | | 0
0 | 1 | 10 | 100 | 2 | 12
0 | 1 | 20 | 100 | 3 | 13
- 0 | 3 | | 300 | 9 | 19
+ 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>
--# Specify the aggregate function:
+-# 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');
@@ -213,25 +229,29 @@ SELECT * FROM pivout ORDER BY id;
| 8 | |
</pre>
--# Create a custom aggregate:
+-# Now create a custom aggregate. Note that the aggregate
+must have a strict transition function:
<pre class="example">
-DROP AGGREGATE IF EXISTS array_accum (anyelement);
-CREATE AGGREGATE array_accum (anyelement)
-(
- sfunc = array_append,
+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 = '{}'
+ initcond = '{}'
);
DROP TABLE IF EXISTS pivout;
-SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'array_accum');
+SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'array_accum1');
SELECT * FROM pivout ORDER BY id;
</pre>
<pre class="result">
- id | val_array_accum_piv_10 | val_array_accum_piv_20 | val_array_accum_piv_30
-----+------------------------------+------------------------------+---------------------------
- 0 | {1,2,NULL} | {NULL,NULL,3} | {NULL,NULL,NULL}
- 1 | {NULL,NULL,NULL,7,NULL,NULL} | {4,NULL,NULL,NULL,NULL,NULL} | {NULL,5,6,NULL,NULL,NULL}
- | {8} | {NULL} | {NULL}
+ 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:
@@ -248,7 +268,7 @@ SELECT * FROM pivout ORDER BY id;
| 8 | | |
</pre>
--# Fill null results:
+-# 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');
@@ -294,26 +314,32 @@ SELECT * FROM pivout ORDER BY id;
<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_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_100 |
-val_avg_piv_30_piv2_200 |
-val_avg_piv_30_piv2_300 |
+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_100 |
+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_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_100 |
+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 |
+val_avg_piv_30_piv2_300 |
...
</pre>
@@ -328,10 +354,10 @@ SELECT * FROM pivout ORDER BY id;
id | 0
val_avg_piv_10 | 1.5
val_avg_piv_20 | 3
-val_avg_piv_30 |
+val_avg_piv_30 |
val2_avg_piv_10 | 11.5
val2_avg_piv_20 | 13
-val2_avg_piv_30 |
+val2_avg_piv_30 |
-[ RECORD 2 ]---+-----
id | 1
val_avg_piv_10 | 7
@@ -344,7 +370,7 @@ val2_avg_piv_30 | 15.5
</pre>
--# Use multiple aggregate functions (cross product):
+-# 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');
@@ -355,10 +381,10 @@ SELECT * FROM pivout ORDER BY id;
id | 0
val_avg_piv_10 | 1.5
val_avg_piv_20 | 3
-val_avg_piv_30 |
+val_avg_piv_30 |
val_sum_piv_10 | 3
val_sum_piv_20 | 3
-val_sum_piv_30 |
+val_sum_piv_30 |
-[ RECORD 2 ]--+----
id | 1
val_avg_piv_10 | 7
@@ -370,7 +396,7 @@ val_sum_piv_30 | 11
...
</pre>
--# Use value columnn specific aggregate functions:
+-# 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',
@@ -397,7 +423,7 @@ val2_sum_piv_30 | 31
...
</pre>
--# Use multiple value columnn specific aggregate functions:
+-# 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',
@@ -409,13 +435,13 @@ SELECT * FROM pivout ORDER BY id;
id | 0
val_avg_piv_10 | 1.5
val_avg_piv_20 | 3
-val_avg_piv_30 |
+val_avg_piv_30 |
val2_avg_piv_10 | 11.5
val2_avg_piv_20 | 13
-val2_avg_piv_30 |
+val2_avg_piv_30 |
val2_sum_piv_10 | 23
val2_sum_piv_20 | 13
-val2_sum_piv_30 |
+val2_sum_piv_30 |
-[ RECORD 2 ]---+-----
id | 1
val_avg_piv_10 | 7
@@ -441,6 +467,10 @@ 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
@@ -453,10 +483,10 @@ 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
-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
+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
val2_avg_piv_10_piv2_200 | 111
@@ -469,10 +499,10 @@ val2_avg_piv_30_piv2_0 | 111
val2_avg_piv_30_piv2_100 | 111
val2_avg_piv_30_piv2_200 | 111
val2_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_sum_piv_null_piv2_0 | 111
+val2_sum_piv_null_piv2_100 | 111
+val2_sum_piv_null_piv2_200 | 111
+val2_sum_piv_null_piv2_300 | 111
val2_sum_piv_10_piv2_0 | 11
val2_sum_piv_10_piv2_100 | 111
val2_sum_piv_10_piv2_200 | 111
@@ -485,37 +515,119 @@ val2_sum_piv_30_piv2_0 | 111
val2_sum_piv_30_piv2_100 | 111
val2_sum_piv_30_piv2_200 | 111
val2_sum_piv_30_piv2_300 | 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
+val2_avg_piv_10_piv2_0 | 111
+val2_avg_piv_10_piv2_100 | 12
+val2_avg_piv_10_piv2_200 | 111
+val2_avg_piv_10_piv2_300 | 111
+val2_avg_piv_20_piv2_0 | 111
+val2_avg_piv_20_piv2_100 | 13
+val2_avg_piv_20_piv2_200 | 111
+val2_avg_piv_20_piv2_300 | 111
+val2_avg_piv_30_piv2_0 | 111
+val2_avg_piv_30_piv2_100 | 111
+val2_avg_piv_30_piv2_200 | 111
+val2_avg_piv_30_piv2_300 | 111
val2_sum_piv_null_piv2_0 | 111
val2_sum_piv_null_piv2_100 | 111
val2_sum_piv_null_piv2_200 | 111
val2_sum_piv_null_piv2_300 | 111
+val2_sum_piv_10_piv2_0 | 111
+val2_sum_piv_10_piv2_100 | 12
+val2_sum_piv_10_piv2_200 | 111
+val2_sum_piv_10_piv2_300 | 111
+val2_sum_piv_20_piv2_0 | 111
+val2_sum_piv_20_piv2_100 | 13
+val2_sum_piv_20_piv2_200 | 111
+val2_sum_piv_20_piv2_300 | 111
+val2_sum_piv_30_piv2_0 | 111
+val2_sum_piv_30_piv2_100 | 111
+val2_sum_piv_30_piv2_200 | 111
+val2_sum_piv_30_piv2_300 | 111
...
</pre>
-# Create a dictionary for output column names:
<pre class="example">
-DROP TABLE IF EXISTS pivout_dictionary;
-DROP TABLE IF EXISTS pivout;
+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);
SELECT * FROM pivout_dictionary;
</pre>
<pre class="result">
--[ RECORD 1 ]-+-----------------------------
-\_\_pivot_cid\_\_ | \_\_p_0\_\_
-pval | val
-agg | avg
-piv | 10
-piv2 | 0
-col_name | "val_avg_piv_10_piv2_0"
--[ RECORD 2 ]-+-----------------------------
-\_\_pivot_cid\_\_ | \_\_p_1\_\_
-pval | val
-agg | avg
-piv | 10
-piv2 | 100
-col_name | "val_avg_piv_10_piv2_100"
-...
+ __pivot_cid__ | pval | agg | piv | piv2 | col_name
+---------------+------+-----+-----+------+------------------------------
+ __p_1__ | val | avg | | 100 | "val_avg_piv_null_piv2_100"
+ __p_5__ | val | avg | 10 | 100 | "val_avg_piv_10_piv2_100"
+ __p_9__ | val | avg | 20 | 100 | "val_avg_piv_20_piv2_100"
+ __p_12__ | val | avg | 30 | 0 | "val_avg_piv_30_piv2_0"
+ __p_16__ | val2 | avg | | 0 | "val2_avg_piv_null_piv2_0"
+ __p_23__ | val2 | avg | 10 | 300 | "val2_avg_piv_10_piv2_300"
+ __p_27__ | val2 | avg | 20 | 300 | "val2_avg_piv_20_piv2_300"
+ __p_30__ | val2 | avg | 30 | 200 | "val2_avg_piv_30_piv2_200"
+ __p_34__ | val2 | sum | | 200 | "val2_sum_piv_null_piv2_200"
+ __p_38__ | val2 | sum | 10 | 200 | "val2_sum_piv_10_piv2_200"
+ __p_41__ | val2 | sum | 20 | 100 | "val2_sum_piv_20_piv2_100"
+ __p_45__ | val2 | sum | 30 | 100 | "val2_sum_piv_30_piv2_100"
+ __p_2__ | val | avg | | 200 | "val_avg_piv_null_piv2_200"
+ __p_6__ | val | avg | 10 | 200 | "val_avg_piv_10_piv2_200"
+ __p_11__ | val | avg | 20 | 300 | "val_avg_piv_20_piv2_300"
+ __p_15__ | val | avg | 30 | 300 | "val_avg_piv_30_piv2_300"
+ __p_19__ | val2 | avg | | 300 | "val2_avg_piv_null_piv2_300"
+ __p_20__ | val2 | avg | 10 | 0 | "val2_avg_piv_10_piv2_0"
+ __p_24__ | val2 | avg | 20 | 0 | "val2_avg_piv_20_piv2_0"
+ __p_28__ | val2 | avg | 30 | 0 | "val2_avg_piv_30_piv2_0"
+ __p_33__ | val2 | sum | | 100 | "val2_sum_piv_null_piv2_100"
+ __p_37__ | val2 | sum | 10 | 100 | "val2_sum_piv_10_piv2_100"
+ __p_42__ | val2 | sum | 20 | 200 | "val2_sum_piv_20_piv2_200"
+ __p_46__ | val2 | sum | 30 | 200 | "val2_sum_piv_30_piv2_200"
+ __p_3__ | val | avg | | 300 | "val_avg_piv_null_piv2_300"
+ __p_7__ | val | avg | 10 | 300 | "val_avg_piv_10_piv2_300"
+ __p_10__ | val | avg | 20 | 200 | "val_avg_piv_20_piv2_200"
+ __p_14__ | val | avg | 30 | 200 | "val_avg_piv_30_piv2_200"
+ __p_18__ | val2 | avg | | 200 | "val2_avg_piv_null_piv2_200"
+ __p_21__ | val2 | avg | 10 | 100 | "val2_avg_piv_10_piv2_100"
+ __p_25__ | val2 | avg | 20 | 100 | "val2_avg_piv_20_piv2_100"
+ __p_29__ | val2 | avg | 30 | 100 | "val2_avg_piv_30_piv2_100"
+ __p_32__ | val2 | sum | | 0 | "val2_sum_piv_null_piv2_0"
+ __p_36__ | val2 | sum | 10 | 0 | "val2_sum_piv_10_piv2_0"
+ __p_43__ | val2 | sum | 20 | 300 | "val2_sum_piv_20_piv2_300"
+ __p_47__ | val2 | sum | 30 | 300 | "val2_sum_piv_30_piv2_300"
+ __p_0__ | val | avg | | 0 | "val_avg_piv_null_piv2_0"
+ __p_4__ | val | avg | 10 | 0 | "val_avg_piv_10_piv2_0"
+ __p_8__ | val | avg | 20 | 0 | "val_avg_piv_20_piv2_0"
+ __p_13__ | val | avg | 30 | 100 | "val_avg_piv_30_piv2_100"
+ __p_17__ | val2 | avg | | 100 | "val2_avg_piv_null_piv2_100"
+ __p_22__ | val2 | avg | 10 | 200 | "val2_avg_piv_10_piv2_200"
+ __p_26__ | val2 | avg | 20 | 200 | "val2_avg_piv_20_piv2_200"
+ __p_31__ | val2 | avg | 30 | 300 | "val2_avg_piv_30_piv2_300"
+ __p_35__ | val2 | sum | | 300 | "val2_sum_piv_null_piv2_300"
+ __p_39__ | val2 | sum | 10 | 300 | "val2_sum_piv_10_piv2_300"
+ __p_40__ | val2 | sum | 20 | 0 | "val2_sum_piv_20_piv2_0"
+ __p_44__ | val2 | sum | 30 | 0 | "val2_sum_piv_30_piv2_0"
+(48 rows)
</pre>
<pre class="example">
SELECT * FROM pivout ORDER BY id,id2;
@@ -524,55 +636,117 @@ SELECT * FROM pivout ORDER BY id,id2;
-[ RECORD 1 ]--
id | 0
id2 | 0
-\_\_p_0\_\_ | 1
-\_\_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
-\_\_p_14\_\_ | 111
-\_\_p_15\_\_ | 111
-\_\_p_16\_\_ | 5.5
-\_\_p_17\_\_ | 111
-\_\_p_18\_\_ | 111
-\_\_p_19\_\_ | 111
-\_\_p_20\_\_ | 111
-\_\_p_21\_\_ | 111
-\_\_p_22\_\_ | 111
-\_\_p_23\_\_ | 111
-\_\_p_24\_\_ | 111
-\_\_p_25\_\_ | 111
-\_\_p_26\_\_ | 111
-\_\_p_27\_\_ | 111
-\_\_p_28\_\_ | 111
-\_\_p_29\_\_ | 111
-\_\_p_30\_\_ | 111
-\_\_p_31\_\_ | 111
-\_\_p_32\_\_ | 11
-\_\_p_33\_\_ | 111
-\_\_p_34\_\_ | 111
-\_\_p_35\_\_ | 111
-\_\_p_36\_\_ | 111
-\_\_p_37\_\_ | 111
-\_\_p_38\_\_ | 111
-\_\_p_39\_\_ | 111
-\_\_p_40\_\_ | 111
-\_\_p_41\_\_ | 111
-\_\_p_42\_\_ | 111
-\_\_p_43\_\_ | 111
-\_\_p_44\_\_ | 111
-\_\_p_45\_\_ | 111
-\_\_p_46\_\_ | 111
-\_\_p_47\_\_ | 111
+__p_0__ | 111
+__p_1__ | 111
+__p_2__ | 111
+__p_3__ | 111
+__p_4__ | 1
+__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
+__p_14__ | 111
+__p_15__ | 111
+__p_16__ | 111
+__p_17__ | 111
+__p_18__ | 111
+__p_19__ | 111
+__p_20__ | 11
+__p_21__ | 111
+__p_22__ | 111
+__p_23__ | 111
+__p_24__ | 111
+__p_25__ | 111
+__p_26__ | 111
+__p_27__ | 111
+__p_28__ | 111
+__p_29__ | 111
+__p_30__ | 111
+__p_31__ | 111
+__p_32__ | 111
+__p_33__ | 111
+__p_34__ | 111
+__p_35__ | 111
+__p_36__ | 11
+__p_37__ | 111
+__p_38__ | 111
+__p_39__ | 111
+__p_40__ | 111
+__p_41__ | 111
+__p_42__ | 111
+__p_43__ | 111
+__p_44__ | 111
+__p_45__ | 111
+__p_46__ | 111
+__p_47__ | 111
+-[ RECORD 2 ]--
+id | 0
+id2 | 1
+__p_0__ | 111
+__p_1__ | 111
+__p_2__ | 111
+__p_3__ | 111
+__p_4__ | 111
+__p_5__ | 2
+__p_6__ | 111
+__p_7__ | 111
+__p_8__ | 111
+__p_9__ | 3
+__p_10__ | 111
+__p_11__ | 111
+__p_12__ | 111
+__p_13__ | 111
+__p_14__ | 111
+__p_15__ | 111
+__p_16__ | 111
+__p_17__ | 111
+__p_18__ | 111
+__p_19__ | 111
+__p_20__ | 111
+__p_21__ | 12
+__p_22__ | 111
+__p_23__ | 111
+__p_24__ | 111
+__p_25__ | 13
+__p_26__ | 111
+__p_27__ | 111
+__p_28__ | 111
+__p_29__ | 111
+__p_30__ | 111
+__p_31__ | 111
+__p_32__ | 111
+__p_33__ | 111
+__p_34__ | 111
+__p_35__ | 111
+__p_36__ | 111
+__p_37__ | 12
+__p_38__ | 111
+__p_39__ | 111
+__p_40__ | 111
+__p_41__ | 13
+__p_42__ | 111
+__p_43__ | 111
+__p_44__ | 111
+__p_45__ | 111
+__p_46__ | 111
+__p_47__ | 111
...
+</pre>
+
+@anchor literature
+@literature
+
+@anchor svm-lit-1
+[1] https://www.postgresql.org/docs/8.2/static/functions-aggregate.html
+
+[2] https://www.postgresql.org/docs/8.2/static/sql-createaggregate.html
+
+[3] https://www.postgresql.org/docs/8.2/static/xaggr.html
*/
-------------------------------------------------------------------------