You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@madlib.apache.org by fm...@apache.org on 2017/03/31 18:42:27 UTC
[2/2] incubator-madlib-site git commit: add array output to pivot for
1dot11
add array output to pivot for 1dot11
Project: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/commit/8b8ad38d
Tree: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/tree/8b8ad38d
Diff: http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/diff/8b8ad38d
Branch: refs/heads/asf-site
Commit: 8b8ad38db0a7e5140f5edec9ed2e1559ae5ad44c
Parents: c77eb22
Author: Frank McQuillan <fm...@pivotal.io>
Authored: Fri Mar 31 11:41:33 2017 -0700
Committer: Frank McQuillan <fm...@pivotal.io>
Committed: Fri Mar 31 11:41:33 2017 -0700
----------------------------------------------------------------------
community-artifacts/Pivot-demo-2.ipynb | 1818 -------------
community-artifacts/Pivot-demo-v3.ipynb | 3630 ++++++++++++++++++++++++++
2 files changed, 3630 insertions(+), 1818 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/8b8ad38d/community-artifacts/Pivot-demo-2.ipynb
----------------------------------------------------------------------
diff --git a/community-artifacts/Pivot-demo-2.ipynb b/community-artifacts/Pivot-demo-2.ipynb
deleted file mode 100644
index b6f3162..0000000
--- a/community-artifacts/Pivot-demo-2.ipynb
+++ /dev/null
@@ -1,1818 +0,0 @@
-{
- "cells": [
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# Pivot\n",
- "\n",
- "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."
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%load_ext sql"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "# %sql postgresql://gpdbchina@10.194.10.68:55000/madlib\n",
- "%sql postgresql://fmcquillan@localhost:5432/madlib"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%sql select madlib.version();\n",
- "# %sql select version();"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# User docs examples"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {
- "collapsed": true
- },
- "source": [
- "Create a simple dataset to demonstrate a basic pivot:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 14,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "Done.\n",
- "10 rows affected.\n",
- "10 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>piv</th>\n",
- " <th>val</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>20</td>\n",
- " <td>3.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>10</td>\n",
- " <td>1.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>10</td>\n",
- " <td>2.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>30</td>\n",
- " <td>6.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>10</td>\n",
- " <td>7.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>None</td>\n",
- " <td>9.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>10</td>\n",
- " <td>None</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>20</td>\n",
- " <td>4.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>30</td>\n",
- " <td>5.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>10</td>\n",
- " <td>8.0</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(0, 20, 3.0),\n",
- " (0, 10, 1.0),\n",
- " (0, 10, 2.0),\n",
- " (1, 30, 6.0),\n",
- " (1, 10, 7.0),\n",
- " (1, None, 9.0),\n",
- " (1, 10, None),\n",
- " (1, 20, 4.0),\n",
- " (1, 30, 5.0),\n",
- " (None, 10, 8.0)]"
- ]
- },
- "execution_count": 14,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql \n",
- "DROP TABLE IF EXISTS pivset CASCADE;\n",
- "CREATE TABLE pivset(\n",
- " id INTEGER,\n",
- " piv INTEGER,\n",
- " val FLOAT8\n",
- " );\n",
- "INSERT INTO pivset VALUES\n",
- " (0, 10, 1),\n",
- " (0, 10, 2),\n",
- " (0, 20, 3),\n",
- " (1, 20, 4),\n",
- " (1, 30, 5),\n",
- " (1, 30, 6),\n",
- " (1, 10, 7),\n",
- " (NULL, 10, 8),\n",
- " (1, NULL, 9),\n",
- " (1, 10, NULL);\n",
- "\n",
- "SELECT * FROM pivset ORDER BY id;"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 15,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n",
- "3 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>val_avg_piv_10</th>\n",
- " <th>val_avg_piv_20</th>\n",
- " <th>val_avg_piv_30</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>1.5</td>\n",
- " <td>3.0</td>\n",
- " <td>None</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>7.0</td>\n",
- " <td>4.0</td>\n",
- " <td>5.5</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>8.0</td>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(0, 1.5, 3.0, None), (1, 7.0, 4.0, 5.5), (None, 8.0, None, None)]"
- ]
- },
- "execution_count": 15,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset', -- source data\n",
- " 'pivout', -- output data\n",
- " 'id', -- index (rows in the output table)\n",
- " 'piv', -- pivot columns\n",
- " 'val'); -- values to be summarized in the output table\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Now let's add some more columns to our data set and create a view:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 16,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "Done.\n",
- "10 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>id2</th>\n",
- " <th>piv</th>\n",
- " <th>piv2</th>\n",
- " <th>val</th>\n",
- " <th>val2</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>0</td>\n",
- " <td>10</td>\n",
- " <td>0</td>\n",
- " <td>1.0</td>\n",
- " <td>11.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>1</td>\n",
- " <td>10</td>\n",
- " <td>100</td>\n",
- " <td>2.0</td>\n",
- " <td>12.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>1</td>\n",
- " <td>20</td>\n",
- " <td>100</td>\n",
- " <td>3.0</td>\n",
- " <td>13.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>0</td>\n",
- " <td>10</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>2</td>\n",
- " <td>20</td>\n",
- " <td>100</td>\n",
- " <td>4.0</td>\n",
- " <td>14.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>3</td>\n",
- " <td>10</td>\n",
- " <td>200</td>\n",
- " <td>7.0</td>\n",
- " <td>17.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>3</td>\n",
- " <td>30</td>\n",
- " <td>200</td>\n",
- " <td>5.0</td>\n",
- " <td>15.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>3</td>\n",
- " <td>30</td>\n",
- " <td>200</td>\n",
- " <td>6.0</td>\n",
- " <td>16.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>4</td>\n",
- " <td>None</td>\n",
- " <td>300</td>\n",
- " <td>9.0</td>\n",
- " <td>19.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>0</td>\n",
- " <td>10</td>\n",
- " <td>300</td>\n",
- " <td>8.0</td>\n",
- " <td>18.0</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(0, 0, 10, 0, 1.0, 11.0),\n",
- " (0, 1, 10, 100, 2.0, 12.0),\n",
- " (0, 1, 20, 100, 3.0, 13.0),\n",
- " (1, 0, 10, 0, None, 0.0),\n",
- " (1, 2, 20, 100, 4.0, 14.0),\n",
- " (1, 3, 10, 200, 7.0, 17.0),\n",
- " (1, 3, 30, 200, 5.0, 15.0),\n",
- " (1, 3, 30, 200, 6.0, 16.0),\n",
- " (1, 4, None, 300, 9.0, 19.0),\n",
- " (None, 0, 10, 300, 8.0, 18.0)]"
- ]
- },
- "execution_count": 16,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP VIEW IF EXISTS pivset_ext;\n",
- "CREATE VIEW pivset_ext AS\n",
- " SELECT *,\n",
- " COALESCE(id + (val / 3)::integer, 0) AS id2,\n",
- " COALESCE(100*(val / 3)::integer, 0) AS piv2,\n",
- " COALESCE(val + 10, 0) AS val2\n",
- " FROM pivset;\n",
- "SELECT id,id2,piv,piv2,val,val2 FROM pivset_ext\n",
- "ORDER BY id,id2,piv,piv2,val,val2;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Let's use a different aggregate function on the view we just created:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 17,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n",
- "3 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>val_sum_piv_10</th>\n",
- " <th>val_sum_piv_20</th>\n",
- " <th>val_sum_piv_30</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>3.0</td>\n",
- " <td>3.0</td>\n",
- " <td>None</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>7.0</td>\n",
- " <td>4.0</td>\n",
- " <td>11.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>8.0</td>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(0, 3.0, 3.0, None), (1, 7.0, 4.0, 11.0), (None, 8.0, None, None)]"
- ]
- },
- "execution_count": 17,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum');\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Now create a custom aggregate. Note that the aggregate must have a strict transition function:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 18,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "Done.\n",
- "Done.\n",
- "Done.\n",
- "Done.\n",
- "1 rows affected.\n",
- "3 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>val_array_accum1_piv_10</th>\n",
- " <th>val_array_accum1_piv_20</th>\n",
- " <th>val_array_accum1_piv_30</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>[1.0, 2.0]</td>\n",
- " <td>[3.0]</td>\n",
- " <td>[]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>[7.0]</td>\n",
- " <td>[4.0]</td>\n",
- " <td>[5.0, 6.0]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>[8.0]</td>\n",
- " <td>[]</td>\n",
- " <td>[]</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(0, [1.0, 2.0], [3.0], []),\n",
- " (1, [7.0], [4.0], [5.0, 6.0]),\n",
- " (None, [8.0], [], [])]"
- ]
- },
- "execution_count": 18,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP FUNCTION IF EXISTS array_add1 (ANYARRAY, ANYELEMENT) CASCADE;\n",
- "CREATE FUNCTION array_add1(ANYARRAY, ANYELEMENT) RETURNS ANYARRAY AS $$\n",
- " SELECT $1 || $2\n",
- "$$ LANGUAGE sql STRICT;\n",
- "\n",
- "DROP AGGREGATE IF EXISTS array_accum1 (anyelement);\n",
- "CREATE AGGREGATE array_accum1 (anyelement) (\n",
- " sfunc = array_add1, -- state transition function\n",
- " stype = anyarray, -- current internal state of the aggregate (temp variable)\n",
- " initcond = '{}' -- IC is empty array \n",
- ");\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'array_accum1'); -- OK since STRICT\n",
- "-- SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'array_agg'); -- this will throw error since not STRICT\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Keep null values in the pivot column:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 19,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n",
- "3 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>val_sum_piv_null</th>\n",
- " <th>val_sum_piv_10</th>\n",
- " <th>val_sum_piv_20</th>\n",
- " <th>val_sum_piv_30</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>3.0</td>\n",
- " <td>3.0</td>\n",
- " <td>None</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>9.0</td>\n",
- " <td>7.0</td>\n",
- " <td>4.0</td>\n",
- " <td>11.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " <td>8.0</td>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(0, None, 3.0, 3.0, None),\n",
- " (1, 9.0, 7.0, 4.0, 11.0),\n",
- " (None, None, 8.0, None, None)]"
- ]
- },
- "execution_count": 19,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', True);\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Fill null results with a value of interest:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'sum', '111');\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Use multiple index columns:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id,id2', 'piv', 'val');\n",
- "SELECT * FROM pivout ORDER BY id,id2;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Use multiple pivot columns:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val');\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Use multiple value columns:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2');\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Use multiple aggregate functions on the same value column (cross product):"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val', 'avg, sum');\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Use different aggregate functions for different value columns:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',\n",
- " 'val=avg, val2=sum');\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Use multiple aggregate functions for different value columns:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2',\n",
- " 'val=avg, val2=[avg,sum]', '111', True);\n",
- "SELECT * FROM pivout ORDER BY id,id2;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Combine all of the options:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 21,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n",
- "7 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>id2</th>\n",
- " <th>val_avg_piv_null_piv2_0</th>\n",
- " <th>val_avg_piv_null_piv2_100</th>\n",
- " <th>val_avg_piv_null_piv2_200</th>\n",
- " <th>val_avg_piv_null_piv2_300</th>\n",
- " <th>val_avg_piv_10_piv2_0</th>\n",
- " <th>val_avg_piv_10_piv2_100</th>\n",
- " <th>val_avg_piv_10_piv2_200</th>\n",
- " <th>val_avg_piv_10_piv2_300</th>\n",
- " <th>val_avg_piv_20_piv2_0</th>\n",
- " <th>val_avg_piv_20_piv2_100</th>\n",
- " <th>val_avg_piv_20_piv2_200</th>\n",
- " <th>val_avg_piv_20_piv2_300</th>\n",
- " <th>val_avg_piv_30_piv2_0</th>\n",
- " <th>val_avg_piv_30_piv2_100</th>\n",
- " <th>val_avg_piv_30_piv2_200</th>\n",
- " <th>val_avg_piv_30_piv2_300</th>\n",
- " <th>val2_avg_piv_null_piv2_0</th>\n",
- " <th>val2_avg_piv_null_piv2_100</th>\n",
- " <th>val2_avg_piv_null_piv2_200</th>\n",
- " <th>val2_avg_piv_null_piv2_300</th>\n",
- " <th>val2_avg_piv_10_piv2_0</th>\n",
- " <th>val2_avg_piv_10_piv2_100</th>\n",
- " <th>val2_avg_piv_10_piv2_200</th>\n",
- " <th>val2_avg_piv_10_piv2_300</th>\n",
- " <th>val2_avg_piv_20_piv2_0</th>\n",
- " <th>val2_avg_piv_20_piv2_100</th>\n",
- " <th>val2_avg_piv_20_piv2_200</th>\n",
- " <th>val2_avg_piv_20_piv2_300</th>\n",
- " <th>val2_avg_piv_30_piv2_0</th>\n",
- " <th>val2_avg_piv_30_piv2_100</th>\n",
- " <th>val2_avg_piv_30_piv2_200</th>\n",
- " <th>val2_avg_piv_30_piv2_300</th>\n",
- " <th>val2_sum_piv_null_piv2_0</th>\n",
- " <th>val2_sum_piv_null_piv2_100</th>\n",
- " <th>val2_sum_piv_null_piv2_200</th>\n",
- " <th>val2_sum_piv_null_piv2_300</th>\n",
- " <th>val2_sum_piv_10_piv2_0</th>\n",
- " <th>val2_sum_piv_10_piv2_100</th>\n",
- " <th>val2_sum_piv_10_piv2_200</th>\n",
- " <th>val2_sum_piv_10_piv2_300</th>\n",
- " <th>val2_sum_piv_20_piv2_0</th>\n",
- " <th>val2_sum_piv_20_piv2_100</th>\n",
- " <th>val2_sum_piv_20_piv2_200</th>\n",
- " <th>val2_sum_piv_20_piv2_300</th>\n",
- " <th>val2_sum_piv_30_piv2_0</th>\n",
- " <th>val2_sum_piv_30_piv2_100</th>\n",
- " <th>val2_sum_piv_30_piv2_200</th>\n",
- " <th>val2_sum_piv_30_piv2_300</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>1.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>11.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>11.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>0</td>\n",
- " <td>1</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>2.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>3.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>12.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>13.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>12.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>13.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>0.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>0.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>2</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>4.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>14.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>14.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>3</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>7.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>5.5</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>17.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>15.5</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>17.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>31.0</td>\n",
- " <td>111.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>4</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>9.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>19.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>19.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>8.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>18.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>18.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " <td>111.0</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(0, 0, 111.0, 111.0, 111.0, 111.0, 1.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 11.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 11.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
- " (0, 1, 111.0, 111.0, 111.0, 111.0, 111.0, 2.0, 111.0, 111.0, 111.0, 3.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 12.0, 111.0, 111.0, 111.0, 13.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 12.0, 111.0, 111.0, 111.0, 13.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
- " (1, 0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 0.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 0.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
- " (1, 2, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 4.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 14.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 14.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
- " (1, 3, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 7.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 5.5, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 17.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 15.5, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 17.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 31.0, 111.0),\n",
- " (1, 4, 111.0, 111.0, 111.0, 9.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 19.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 19.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0),\n",
- " (None, 0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 8.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 18.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 18.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0, 111.0)]"
- ]
- },
- "execution_count": 21,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2',\n",
- " 'val=avg, val2=[avg,sum]', '111', True);\n",
- "SELECT * FROM pivout ORDER BY id,id2;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "Create a dictionary for output column names:"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 20,
- "metadata": {
- "collapsed": false
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n",
- "48 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>__pivot_cid__</th>\n",
- " <th>pval</th>\n",
- " <th>agg</th>\n",
- " <th>piv</th>\n",
- " <th>piv2</th>\n",
- " <th>col_name</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_0__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>0</td>\n",
- " <td>\"val_avg_piv_null_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_1__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>100</td>\n",
- " <td>\"val_avg_piv_null_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_2__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>200</td>\n",
- " <td>\"val_avg_piv_null_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_3__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>300</td>\n",
- " <td>\"val_avg_piv_null_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_4__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>0</td>\n",
- " <td>\"val_avg_piv_10_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_5__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>100</td>\n",
- " <td>\"val_avg_piv_10_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_6__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>200</td>\n",
- " <td>\"val_avg_piv_10_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_7__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>300</td>\n",
- " <td>\"val_avg_piv_10_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_8__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>0</td>\n",
- " <td>\"val_avg_piv_20_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_9__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>100</td>\n",
- " <td>\"val_avg_piv_20_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_10__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>200</td>\n",
- " <td>\"val_avg_piv_20_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_11__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>300</td>\n",
- " <td>\"val_avg_piv_20_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_12__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>0</td>\n",
- " <td>\"val_avg_piv_30_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_13__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>100</td>\n",
- " <td>\"val_avg_piv_30_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_14__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>200</td>\n",
- " <td>\"val_avg_piv_30_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_15__</td>\n",
- " <td>val</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>300</td>\n",
- " <td>\"val_avg_piv_30_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_16__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_avg_piv_null_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_17__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_avg_piv_null_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_18__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_avg_piv_null_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_19__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>None</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_avg_piv_null_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_20__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_avg_piv_10_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_21__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_avg_piv_10_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_22__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_avg_piv_10_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_23__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>10</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_avg_piv_10_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_24__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_avg_piv_20_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_25__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_avg_piv_20_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_26__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_avg_piv_20_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_27__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>20</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_avg_piv_20_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_28__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_avg_piv_30_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_29__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_avg_piv_30_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_30__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_avg_piv_30_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_31__</td>\n",
- " <td>val2</td>\n",
- " <td>avg</td>\n",
- " <td>30</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_avg_piv_30_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_32__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>None</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_sum_piv_null_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_33__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>None</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_sum_piv_null_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_34__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>None</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_sum_piv_null_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_35__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>None</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_sum_piv_null_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_36__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>10</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_sum_piv_10_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_37__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>10</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_sum_piv_10_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_38__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>10</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_sum_piv_10_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_39__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>10</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_sum_piv_10_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_40__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>20</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_sum_piv_20_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_41__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>20</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_sum_piv_20_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_42__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>20</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_sum_piv_20_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_43__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>20</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_sum_piv_20_piv2_300\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_44__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>30</td>\n",
- " <td>0</td>\n",
- " <td>\"val2_sum_piv_30_piv2_0\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_45__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>30</td>\n",
- " <td>100</td>\n",
- " <td>\"val2_sum_piv_30_piv2_100\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_46__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>30</td>\n",
- " <td>200</td>\n",
- " <td>\"val2_sum_piv_30_piv2_200\"</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>__p_47__</td>\n",
- " <td>val2</td>\n",
- " <td>sum</td>\n",
- " <td>30</td>\n",
- " <td>300</td>\n",
- " <td>\"val2_sum_piv_30_piv2_300\"</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'__p_0__', u'val', u'avg', None, 0, u'\"val_avg_piv_null_piv2_0\"'),\n",
- " (u'__p_1__', u'val', u'avg', None, 100, u'\"val_avg_piv_null_piv2_100\"'),\n",
- " (u'__p_2__', u'val', u'avg', None, 200, u'\"val_avg_piv_null_piv2_200\"'),\n",
- " (u'__p_3__', u'val', u'avg', None, 300, u'\"val_avg_piv_null_piv2_300\"'),\n",
- " (u'__p_4__', u'val', u'avg', 10, 0, u'\"val_avg_piv_10_piv2_0\"'),\n",
- " (u'__p_5__', u'val', u'avg', 10, 100, u'\"val_avg_piv_10_piv2_100\"'),\n",
- " (u'__p_6__', u'val', u'avg', 10, 200, u'\"val_avg_piv_10_piv2_200\"'),\n",
- " (u'__p_7__', u'val', u'avg', 10, 300, u'\"val_avg_piv_10_piv2_300\"'),\n",
- " (u'__p_8__', u'val', u'avg', 20, 0, u'\"val_avg_piv_20_piv2_0\"'),\n",
- " (u'__p_9__', u'val', u'avg', 20, 100, u'\"val_avg_piv_20_piv2_100\"'),\n",
- " (u'__p_10__', u'val', u'avg', 20, 200, u'\"val_avg_piv_20_piv2_200\"'),\n",
- " (u'__p_11__', u'val', u'avg', 20, 300, u'\"val_avg_piv_20_piv2_300\"'),\n",
- " (u'__p_12__', u'val', u'avg', 30, 0, u'\"val_avg_piv_30_piv2_0\"'),\n",
- " (u'__p_13__', u'val', u'avg', 30, 100, u'\"val_avg_piv_30_piv2_100\"'),\n",
- " (u'__p_14__', u'val', u'avg', 30, 200, u'\"val_avg_piv_30_piv2_200\"'),\n",
- " (u'__p_15__', u'val', u'avg', 30, 300, u'\"val_avg_piv_30_piv2_300\"'),\n",
- " (u'__p_16__', u'val2', u'avg', None, 0, u'\"val2_avg_piv_null_piv2_0\"'),\n",
- " (u'__p_17__', u'val2', u'avg', None, 100, u'\"val2_avg_piv_null_piv2_100\"'),\n",
- " (u'__p_18__', u'val2', u'avg', None, 200, u'\"val2_avg_piv_null_piv2_200\"'),\n",
- " (u'__p_19__', u'val2', u'avg', None, 300, u'\"val2_avg_piv_null_piv2_300\"'),\n",
- " (u'__p_20__', u'val2', u'avg', 10, 0, u'\"val2_avg_piv_10_piv2_0\"'),\n",
- " (u'__p_21__', u'val2', u'avg', 10, 100, u'\"val2_avg_piv_10_piv2_100\"'),\n",
- " (u'__p_22__', u'val2', u'avg', 10, 200, u'\"val2_avg_piv_10_piv2_200\"'),\n",
- " (u'__p_23__', u'val2', u'avg', 10, 300, u'\"val2_avg_piv_10_piv2_300\"'),\n",
- " (u'__p_24__', u'val2', u'avg', 20, 0, u'\"val2_avg_piv_20_piv2_0\"'),\n",
- " (u'__p_25__', u'val2', u'avg', 20, 100, u'\"val2_avg_piv_20_piv2_100\"'),\n",
- " (u'__p_26__', u'val2', u'avg', 20, 200, u'\"val2_avg_piv_20_piv2_200\"'),\n",
- " (u'__p_27__', u'val2', u'avg', 20, 300, u'\"val2_avg_piv_20_piv2_300\"'),\n",
- " (u'__p_28__', u'val2', u'avg', 30, 0, u'\"val2_avg_piv_30_piv2_0\"'),\n",
- " (u'__p_29__', u'val2', u'avg', 30, 100, u'\"val2_avg_piv_30_piv2_100\"'),\n",
- " (u'__p_30__', u'val2', u'avg', 30, 200, u'\"val2_avg_piv_30_piv2_200\"'),\n",
- " (u'__p_31__', u'val2', u'avg', 30, 300, u'\"val2_avg_piv_30_piv2_300\"'),\n",
- " (u'__p_32__', u'val2', u'sum', None, 0, u'\"val2_sum_piv_null_piv2_0\"'),\n",
- " (u'__p_33__', u'val2', u'sum', None, 100, u'\"val2_sum_piv_null_piv2_100\"'),\n",
- " (u'__p_34__', u'val2', u'sum', None, 200, u'\"val2_sum_piv_null_piv2_200\"'),\n",
- " (u'__p_35__', u'val2', u'sum', None, 300, u'\"val2_sum_piv_null_piv2_300\"'),\n",
- " (u'__p_36__', u'val2', u'sum', 10, 0, u'\"val2_sum_piv_10_piv2_0\"'),\n",
- " (u'__p_37__', u'val2', u'sum', 10, 100, u'\"val2_sum_piv_10_piv2_100\"'),\n",
- " (u'__p_38__', u'val2', u'sum', 10, 200, u'\"val2_sum_piv_10_piv2_200\"'),\n",
- " (u'__p_39__', u'val2', u'sum', 10, 300, u'\"val2_sum_piv_10_piv2_300\"'),\n",
- " (u'__p_40__', u'val2', u'sum', 20, 0, u'\"val2_sum_piv_20_piv2_0\"'),\n",
- " (u'__p_41__', u'val2', u'sum', 20, 100, u'\"val2_sum_piv_20_piv2_100\"'),\n",
- " (u'__p_42__', u'val2', u'sum', 20, 200, u'\"val2_sum_piv_20_piv2_200\"'),\n",
- " (u'__p_43__', u'val2', u'sum', 20, 300, u'\"val2_sum_piv_20_piv2_300\"'),\n",
- " (u'__p_44__', u'val2', u'sum', 30, 0, u'\"val2_sum_piv_30_piv2_0\"'),\n",
- " (u'__p_45__', u'val2', u'sum', 30, 100, u'\"val2_sum_piv_30_piv2_100\"'),\n",
- " (u'__p_46__', u'val2', u'sum', 30, 200, u'\"val2_sum_piv_30_piv2_200\"'),\n",
- " (u'__p_47__', u'val2', u'sum', 30, 300, u'\"val2_sum_piv_30_piv2_300\"')]"
- ]
- },
- "execution_count": 20,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout, pivout_dictionary;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id, id2', 'piv, piv2', 'val, val2',\n",
- " 'val=avg, val2=[avg,sum]', '111', True, True);\n",
- "SELECT * FROM pivout_dictionary;"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "SELECT * FROM pivout ORDER BY id,id2;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# Some other examples"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout, pivout_dictionary;\n",
- "SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv', 'val, val2',\n",
- " 'val=avg, val2=sum', '-999', TRUE, TRUE);\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "SELECT * FROM pivout_dictionary;"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": null,
- "metadata": {
- "collapsed": false
- },
- "outputs": [],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS pivout;\n",
- "SELECT madlib.pivot(\n",
- " 'pivset_ext', \n",
- " 'pivout', \n",
- " 'id, id2', \n",
- " 'piv', \n",
- " 'val, val2',\n",
- " 'val=array_accum1, val2=array_accum1',\n",
- " '''{20000, 25000, 25000, 25000}''');\n",
- "SELECT * FROM pivout ORDER BY id;"
- ]
- }
- ],
- "metadata": {
- "kernelspec": {
- "display_name": "Python 2",
- "language": "python",
- "name": "python2"
- },
- "language_info": {
- "codemirror_mode": {
- "name": "ipython",
- "version": 2
- },
- "file_extension": ".py",
- "mimetype": "text/x-python",
- "name": "python",
- "nbconvert_exporter": "python",
- "pygments_lexer": "ipython2",
- "version": "2.7.11"
- }
- },
- "nbformat": 4,
- "nbformat_minor": 0
-}