community-artifacts/Data-types-and-transformations/Pivot-demo-v3.ipynb (3,630 lines of code) (raw):
{
"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. This notebook includes an example for array output which was added in release 1.11."
]
},
{
"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": [
"# Greenplum 4.3.10.0\n",
"%sql postgresql://gpdbchina@10.194.10.68:61000/madlib\n",
" \n",
"# PostgreSQL local\n",
"#%sql postgresql://fmcquillan@localhost:5432/madlib\n",
"\n",
"# Greenplum 4.2.3.0\n",
"#%sql postgresql://gpdbchina@10.194.10.68:55000/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": [
"1 Create a simple dataset to demonstrate a basic pivot:"
]
},
{
"cell_type": "code",
"execution_count": 21,
"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>10</td>\n",
" <td>2.0</td>\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>1</td>\n",
" <td>30</td>\n",
" <td>5.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>10</td>\n",
" <td>None</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>20</td>\n",
" <td>4.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, 10, 2.0),\n",
" (0, 20, 3.0),\n",
" (0, 10, 1.0),\n",
" (1, 30, 5.0),\n",
" (1, 30, 6.0),\n",
" (1, 10, 7.0),\n",
" (1, 10, None),\n",
" (1, None, 9.0),\n",
" (1, 20, 4.0),\n",
" (None, 10, 8.0)]"
]
},
"execution_count": 21,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql \n",
"DROP TABLE IF EXISTS pivset CASCADE; -- View below may depend on table so use CASCADE\n",
"\n",
"CREATE TABLE pivset(\n",
" id INTEGER,\n",
" piv INTEGER,\n",
" val FLOAT8\n",
" );\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": "markdown",
"metadata": {},
"source": [
"2 Pivot the table:"
]
},
{
"cell_type": "code",
"execution_count": 22,
"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": 22,
"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": [
"3 Now let's add some more columns to our data set and create a view:"
]
},
{
"cell_type": "code",
"execution_count": 23,
"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": 23,
"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": [
"4 Let's use a different aggregate function on the view we just created:"
]
},
{
"cell_type": "code",
"execution_count": 24,
"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": 24,
"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": [
"5 Now create a custom aggregate. Note that the aggregate must have a strict transition function:"
]
},
{
"cell_type": "code",
"execution_count": 25,
"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": 25,
"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": [
"6 Keep null values in the pivot column:"
]
},
{
"cell_type": "code",
"execution_count": 26,
"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": 26,
"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": [
"7 Fill null results with a value of interest:"
]
},
{
"cell_type": "code",
"execution_count": 27,
"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>111.0</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>111.0</td>\n",
" <td>111.0</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 3.0, 3.0, 111.0), (1, 7.0, 4.0, 11.0), (None, 8.0, 111.0, 111.0)]"
]
},
"execution_count": 27,
"metadata": {},
"output_type": "execute_result"
}
],
"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": [
"8 Use multiple index columns:"
]
},
{
"cell_type": "code",
"execution_count": 28,
"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_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>0</td>\n",
" <td>1.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>2.0</td>\n",
" <td>3.0</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>None</td>\n",
" <td>4.0</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>7.0</td>\n",
" <td>None</td>\n",
" <td>5.5</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>4</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" <td>8.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 0, 1.0, None, None),\n",
" (0, 1, 2.0, 3.0, None),\n",
" (1, 0, None, None, None),\n",
" (1, 2, None, 4.0, None),\n",
" (1, 3, 7.0, None, 5.5),\n",
" (1, 4, None, None, None),\n",
" (None, 0, 8.0, None, None)]"
]
},
"execution_count": 28,
"metadata": {},
"output_type": "execute_result"
}
],
"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": [
"9 Use multiple pivot columns with columnar output:"
]
},
{
"cell_type": "code",
"execution_count": 31,
"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_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",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>1.0</td>\n",
" <td>2.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>3.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>7.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>4.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>5.5</td>\n",
" <td>None</td>\n",
" </tr>\n",
" <tr>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>8.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 1.0, 2.0, None, None, None, 3.0, None, None, None, None, None, None),\n",
" (1, None, None, 7.0, None, None, 4.0, None, None, None, None, 5.5, None),\n",
" (None, None, None, None, 8.0, None, None, None, None, None, None, None, None)]"
]
},
"execution_count": 31,
"metadata": {},
"output_type": "execute_result"
}
],
"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": [
"10 Use multiple pivot columns (same as above) with an array output:"
]
},
{
"cell_type": "code",
"execution_count": 50,
"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</th>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>[1.0, 2.0, None, None, None, 3.0, None, None, None, None, None, None]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>[None, None, 7.0, None, None, 4.0, None, None, None, None, 5.5, None]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>None</td>\n",
" <td>[None, None, None, 8.0, None, None, None, None, None, None, None, None]</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, [1.0, 2.0, None, None, None, 3.0, None, None, None, None, None, None]),\n",
" (1, [None, None, 7.0, None, None, 4.0, None, None, None, None, 5.5, None]),\n",
" (None, [None, None, None, 8.0, None, None, None, None, None, None, None, None])]"
]
},
"execution_count": 50,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"DROP TABLE IF EXISTS pivout, pivout_dictionary;\n",
"SELECT madlib.pivot('pivset_ext', 'pivout', 'id', 'piv, piv2', 'val',\n",
" NULL, NULL, FALSE, FALSE, 'array');\n",
"SELECT * FROM pivout ORDER BY id;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Use the dictionary to understand what each index of an array corresponds to:"
]
},
{
"cell_type": "code",
"execution_count": 33,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"12 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>1</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>3</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>5</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>7</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>9</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>10</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>12</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>2</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>4</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>6</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>8</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>11</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",
"</table>"
],
"text/plain": [
"[(u'1', u'val', u'avg', 10, 0, u'val_avg_piv_10_piv2_0'),\n",
" (u'3', u'val', u'avg', 10, 200, u'val_avg_piv_10_piv2_200'),\n",
" (u'5', u'val', u'avg', 20, 0, u'val_avg_piv_20_piv2_0'),\n",
" (u'7', u'val', u'avg', 20, 200, u'val_avg_piv_20_piv2_200'),\n",
" (u'9', u'val', u'avg', 30, 0, u'val_avg_piv_30_piv2_0'),\n",
" (u'10', u'val', u'avg', 30, 100, u'val_avg_piv_30_piv2_100'),\n",
" (u'12', u'val', u'avg', 30, 300, u'val_avg_piv_30_piv2_300'),\n",
" (u'2', u'val', u'avg', 10, 100, u'val_avg_piv_10_piv2_100'),\n",
" (u'4', u'val', u'avg', 10, 300, u'val_avg_piv_10_piv2_300'),\n",
" (u'6', u'val', u'avg', 20, 100, u'val_avg_piv_20_piv2_100'),\n",
" (u'8', u'val', u'avg', 20, 300, u'val_avg_piv_20_piv2_300'),\n",
" (u'11', u'val', u'avg', 30, 200, u'val_avg_piv_30_piv2_200')]"
]
},
"execution_count": 33,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT * FROM pivout_dictionary;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"11 Use multiple value columns:"
]
},
{
"cell_type": "code",
"execution_count": 38,
"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",
" <th>val2_avg_piv_10</th>\n",
" <th>val2_avg_piv_20</th>\n",
" <th>val2_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",
" <td>11.5</td>\n",
" <td>13.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",
" <td>8.5</td>\n",
" <td>14.0</td>\n",
" <td>15.5</td>\n",
" </tr>\n",
" <tr>\n",
" <td>None</td>\n",
" <td>8.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>18.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 1.5, 3.0, None, 11.5, 13.0, None),\n",
" (1, 7.0, 4.0, 5.5, 8.5, 14.0, 15.5),\n",
" (None, 8.0, None, None, 18.0, None, None)]"
]
},
"execution_count": 38,
"metadata": {},
"output_type": "execute_result"
}
],
"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": [
"12 Use multiple aggregate functions on the same value column (cross product):"
]
},
{
"cell_type": "code",
"execution_count": 39,
"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",
" <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>1.5</td>\n",
" <td>3.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>7.0</td>\n",
" <td>4.0</td>\n",
" <td>5.5</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",
" <td>8.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 1.5, 3.0, None, 3.0, 3.0, None),\n",
" (1, 7.0, 4.0, 5.5, 7.0, 4.0, 11.0),\n",
" (None, 8.0, None, None, 8.0, None, None)]"
]
},
"execution_count": 39,
"metadata": {},
"output_type": "execute_result"
}
],
"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": [
"13 Use different aggregate functions for different value columns:"
]
},
{
"cell_type": "code",
"execution_count": 40,
"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",
" <th>val2_sum_piv_10</th>\n",
" <th>val2_sum_piv_20</th>\n",
" <th>val2_sum_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",
" <td>23.0</td>\n",
" <td>13.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",
" <td>17.0</td>\n",
" <td>14.0</td>\n",
" <td>31.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>None</td>\n",
" <td>8.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" <td>18.0</td>\n",
" <td>None</td>\n",
" <td>None</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 1.5, 3.0, None, 23.0, 13.0, None),\n",
" (1, 7.0, 4.0, 5.5, 17.0, 14.0, 31.0),\n",
" (None, 8.0, None, None, 18.0, None, None)]"
]
},
"execution_count": 40,
"metadata": {},
"output_type": "execute_result"
}
],
"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": [
"14 Use multiple aggregate functions for different value columns:"
]
},
{
"cell_type": "code",
"execution_count": 41,
"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": 41,
"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": [
"15 Combine many of the options:"
]
},
{
"cell_type": "code",
"execution_count": 42,
"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": 42,
"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": [
"16 Create a dictionary for output column names:"
]
},
{
"cell_type": "code",
"execution_count": 43,
"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_2__</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_4__</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_6__</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_8__</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_11__</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_13__</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_15__</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_17__</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_19__</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_20__</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_22__</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_24__</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_26__</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_28__</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_31__</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_33__</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_35__</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_37__</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_39__</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_40__</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_42__</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_44__</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_46__</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_48__</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",
" <tr>\n",
" <td>__p_1__</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_3__</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_5__</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_7__</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_9__</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_10__</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_12__</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_14__</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_16__</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_18__</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_21__</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_23__</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_25__</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_27__</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_29__</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_30__</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_32__</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_34__</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_36__</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_38__</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_41__</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_43__</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_45__</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_47__</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",
"</table>"
],
"text/plain": [
"[(u'__p_2__', u'val', u'avg', None, 100, u'val_avg_piv_null_piv2_100'),\n",
" (u'__p_4__', u'val', u'avg', None, 300, u'val_avg_piv_null_piv2_300'),\n",
" (u'__p_6__', u'val', u'avg', 10, 100, u'val_avg_piv_10_piv2_100'),\n",
" (u'__p_8__', u'val', u'avg', 10, 300, u'val_avg_piv_10_piv2_300'),\n",
" (u'__p_11__', u'val', u'avg', 20, 200, u'val_avg_piv_20_piv2_200'),\n",
" (u'__p_13__', u'val', u'avg', 30, 0, u'val_avg_piv_30_piv2_0'),\n",
" (u'__p_15__', u'val', u'avg', 30, 200, u'val_avg_piv_30_piv2_200'),\n",
" (u'__p_17__', u'val2', u'avg', None, 0, u'val2_avg_piv_null_piv2_0'),\n",
" (u'__p_19__', u'val2', u'avg', None, 200, u'val2_avg_piv_null_piv2_200'),\n",
" (u'__p_20__', u'val2', u'avg', None, 300, u'val2_avg_piv_null_piv2_300'),\n",
" (u'__p_22__', u'val2', u'avg', 10, 100, u'val2_avg_piv_10_piv2_100'),\n",
" (u'__p_24__', u'val2', u'avg', 10, 300, u'val2_avg_piv_10_piv2_300'),\n",
" (u'__p_26__', u'val2', u'avg', 20, 100, u'val2_avg_piv_20_piv2_100'),\n",
" (u'__p_28__', u'val2', u'avg', 20, 300, u'val2_avg_piv_20_piv2_300'),\n",
" (u'__p_31__', u'val2', u'avg', 30, 200, u'val2_avg_piv_30_piv2_200'),\n",
" (u'__p_33__', u'val2', u'sum', None, 0, u'val2_sum_piv_null_piv2_0'),\n",
" (u'__p_35__', u'val2', u'sum', None, 200, u'val2_sum_piv_null_piv2_200'),\n",
" (u'__p_37__', u'val2', u'sum', 10, 0, u'val2_sum_piv_10_piv2_0'),\n",
" (u'__p_39__', u'val2', u'sum', 10, 200, u'val2_sum_piv_10_piv2_200'),\n",
" (u'__p_40__', u'val2', u'sum', 10, 300, u'val2_sum_piv_10_piv2_300'),\n",
" (u'__p_42__', u'val2', u'sum', 20, 100, u'val2_sum_piv_20_piv2_100'),\n",
" (u'__p_44__', u'val2', u'sum', 20, 300, u'val2_sum_piv_20_piv2_300'),\n",
" (u'__p_46__', u'val2', u'sum', 30, 100, u'val2_sum_piv_30_piv2_100'),\n",
" (u'__p_48__', u'val2', u'sum', 30, 300, u'val2_sum_piv_30_piv2_300'),\n",
" (u'__p_1__', u'val', u'avg', None, 0, u'val_avg_piv_null_piv2_0'),\n",
" (u'__p_3__', u'val', u'avg', None, 200, u'val_avg_piv_null_piv2_200'),\n",
" (u'__p_5__', u'val', u'avg', 10, 0, u'val_avg_piv_10_piv2_0'),\n",
" (u'__p_7__', u'val', u'avg', 10, 200, u'val_avg_piv_10_piv2_200'),\n",
" (u'__p_9__', u'val', u'avg', 20, 0, u'val_avg_piv_20_piv2_0'),\n",
" (u'__p_10__', u'val', u'avg', 20, 100, u'val_avg_piv_20_piv2_100'),\n",
" (u'__p_12__', u'val', u'avg', 20, 300, u'val_avg_piv_20_piv2_300'),\n",
" (u'__p_14__', u'val', u'avg', 30, 100, u'val_avg_piv_30_piv2_100'),\n",
" (u'__p_16__', u'val', u'avg', 30, 300, u'val_avg_piv_30_piv2_300'),\n",
" (u'__p_18__', u'val2', u'avg', None, 100, u'val2_avg_piv_null_piv2_100'),\n",
" (u'__p_21__', u'val2', u'avg', 10, 0, u'val2_avg_piv_10_piv2_0'),\n",
" (u'__p_23__', u'val2', u'avg', 10, 200, u'val2_avg_piv_10_piv2_200'),\n",
" (u'__p_25__', u'val2', u'avg', 20, 0, u'val2_avg_piv_20_piv2_0'),\n",
" (u'__p_27__', u'val2', u'avg', 20, 200, u'val2_avg_piv_20_piv2_200'),\n",
" (u'__p_29__', u'val2', u'avg', 30, 0, u'val2_avg_piv_30_piv2_0'),\n",
" (u'__p_30__', u'val2', u'avg', 30, 100, u'val2_avg_piv_30_piv2_100'),\n",
" (u'__p_32__', u'val2', u'avg', 30, 300, u'val2_avg_piv_30_piv2_300'),\n",
" (u'__p_34__', u'val2', u'sum', None, 100, u'val2_sum_piv_null_piv2_100'),\n",
" (u'__p_36__', u'val2', u'sum', None, 300, u'val2_sum_piv_null_piv2_300'),\n",
" (u'__p_38__', u'val2', u'sum', 10, 100, u'val2_sum_piv_10_piv2_100'),\n",
" (u'__p_41__', u'val2', u'sum', 20, 0, u'val2_sum_piv_20_piv2_0'),\n",
" (u'__p_43__', u'val2', u'sum', 20, 200, u'val2_sum_piv_20_piv2_200'),\n",
" (u'__p_45__', u'val2', u'sum', 30, 0, u'val2_sum_piv_30_piv2_0'),\n",
" (u'__p_47__', u'val2', u'sum', 30, 200, u'val2_sum_piv_30_piv2_200')]"
]
},
"execution_count": 43,
"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": 44,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"7 rows affected.\n"
]
},
{
"data": {
"text/html": [
"<table>\n",
" <tr>\n",
" <th>id</th>\n",
" <th>id2</th>\n",
" <th>__p_1__</th>\n",
" <th>__p_2__</th>\n",
" <th>__p_3__</th>\n",
" <th>__p_4__</th>\n",
" <th>__p_5__</th>\n",
" <th>__p_6__</th>\n",
" <th>__p_7__</th>\n",
" <th>__p_8__</th>\n",
" <th>__p_9__</th>\n",
" <th>__p_10__</th>\n",
" <th>__p_11__</th>\n",
" <th>__p_12__</th>\n",
" <th>__p_13__</th>\n",
" <th>__p_14__</th>\n",
" <th>__p_15__</th>\n",
" <th>__p_16__</th>\n",
" <th>__p_17__</th>\n",
" <th>__p_18__</th>\n",
" <th>__p_19__</th>\n",
" <th>__p_20__</th>\n",
" <th>__p_21__</th>\n",
" <th>__p_22__</th>\n",
" <th>__p_23__</th>\n",
" <th>__p_24__</th>\n",
" <th>__p_25__</th>\n",
" <th>__p_26__</th>\n",
" <th>__p_27__</th>\n",
" <th>__p_28__</th>\n",
" <th>__p_29__</th>\n",
" <th>__p_30__</th>\n",
" <th>__p_31__</th>\n",
" <th>__p_32__</th>\n",
" <th>__p_33__</th>\n",
" <th>__p_34__</th>\n",
" <th>__p_35__</th>\n",
" <th>__p_36__</th>\n",
" <th>__p_37__</th>\n",
" <th>__p_38__</th>\n",
" <th>__p_39__</th>\n",
" <th>__p_40__</th>\n",
" <th>__p_41__</th>\n",
" <th>__p_42__</th>\n",
" <th>__p_43__</th>\n",
" <th>__p_44__</th>\n",
" <th>__p_45__</th>\n",
" <th>__p_46__</th>\n",
" <th>__p_47__</th>\n",
" <th>__p_48__</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": 44,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT * FROM pivout ORDER BY id,id2;"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Some other examples"
]
},
{
"cell_type": "code",
"execution_count": 45,
"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>__p_1__</th>\n",
" <th>__p_2__</th>\n",
" <th>__p_3__</th>\n",
" <th>__p_4__</th>\n",
" <th>__p_5__</th>\n",
" <th>__p_6__</th>\n",
" <th>__p_7__</th>\n",
" <th>__p_8__</th>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>-999.0</td>\n",
" <td>1.5</td>\n",
" <td>3.0</td>\n",
" <td>-999.0</td>\n",
" <td>-999.0</td>\n",
" <td>23.0</td>\n",
" <td>13.0</td>\n",
" <td>-999.0</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>5.5</td>\n",
" <td>19.0</td>\n",
" <td>17.0</td>\n",
" <td>14.0</td>\n",
" <td>31.0</td>\n",
" </tr>\n",
" <tr>\n",
" <td>None</td>\n",
" <td>-999.0</td>\n",
" <td>8.0</td>\n",
" <td>-999.0</td>\n",
" <td>-999.0</td>\n",
" <td>-999.0</td>\n",
" <td>18.0</td>\n",
" <td>-999.0</td>\n",
" <td>-999.0</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, -999.0, 1.5, 3.0, -999.0, -999.0, 23.0, 13.0, -999.0),\n",
" (1, 9.0, 7.0, 4.0, 5.5, 19.0, 17.0, 14.0, 31.0),\n",
" (None, -999.0, 8.0, -999.0, -999.0, -999.0, 18.0, -999.0, -999.0)]"
]
},
"execution_count": 45,
"metadata": {},
"output_type": "execute_result"
}
],
"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": 47,
"metadata": {
"collapsed": false
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"8 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>col_name</th>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_2__</td>\n",
" <td>val</td>\n",
" <td>avg</td>\n",
" <td>10</td>\n",
" <td>val_avg_piv_10</td>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_4__</td>\n",
" <td>val</td>\n",
" <td>avg</td>\n",
" <td>30</td>\n",
" <td>val_avg_piv_30</td>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_6__</td>\n",
" <td>val2</td>\n",
" <td>sum</td>\n",
" <td>10</td>\n",
" <td>val2_sum_piv_10</td>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_8__</td>\n",
" <td>val2</td>\n",
" <td>sum</td>\n",
" <td>30</td>\n",
" <td>val2_sum_piv_30</td>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_1__</td>\n",
" <td>val</td>\n",
" <td>avg</td>\n",
" <td>None</td>\n",
" <td>val_avg_piv_null</td>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_3__</td>\n",
" <td>val</td>\n",
" <td>avg</td>\n",
" <td>20</td>\n",
" <td>val_avg_piv_20</td>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_5__</td>\n",
" <td>val2</td>\n",
" <td>sum</td>\n",
" <td>None</td>\n",
" <td>val2_sum_piv_null</td>\n",
" </tr>\n",
" <tr>\n",
" <td>__p_7__</td>\n",
" <td>val2</td>\n",
" <td>sum</td>\n",
" <td>20</td>\n",
" <td>val2_sum_piv_20</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(u'__p_2__', u'val', u'avg', 10, u'val_avg_piv_10'),\n",
" (u'__p_4__', u'val', u'avg', 30, u'val_avg_piv_30'),\n",
" (u'__p_6__', u'val2', u'sum', 10, u'val2_sum_piv_10'),\n",
" (u'__p_8__', u'val2', u'sum', 30, u'val2_sum_piv_30'),\n",
" (u'__p_1__', u'val', u'avg', None, u'val_avg_piv_null'),\n",
" (u'__p_3__', u'val', u'avg', 20, u'val_avg_piv_20'),\n",
" (u'__p_5__', u'val2', u'sum', None, u'val2_sum_piv_null'),\n",
" (u'__p_7__', u'val2', u'sum', 20, u'val2_sum_piv_20')]"
]
},
"execution_count": 47,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"%%sql\n",
"SELECT * FROM pivout_dictionary;"
]
},
{
"cell_type": "code",
"execution_count": 48,
"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_array_accum1_piv_10</th>\n",
" <th>val_array_accum1_piv_20</th>\n",
" <th>val_array_accum1_piv_30</th>\n",
" <th>val2_array_accum1_piv_10</th>\n",
" <th>val2_array_accum1_piv_20</th>\n",
" <th>val2_array_accum1_piv_30</th>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>1</td>\n",
" <td>[2.0]</td>\n",
" <td>[3.0]</td>\n",
" <td>[]</td>\n",
" <td>[12.0]</td>\n",
" <td>[13.0]</td>\n",
" <td>[]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" <td>[1.0]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[11.0]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>2</td>\n",
" <td>[]</td>\n",
" <td>[4.0]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[14.0]</td>\n",
" <td>[]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>3</td>\n",
" <td>[7.0]</td>\n",
" <td>[]</td>\n",
" <td>[6.0, 5.0]</td>\n",
" <td>[17.0]</td>\n",
" <td>[]</td>\n",
" <td>[16.0, 15.0]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>4</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>1</td>\n",
" <td>0</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[0.0]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" </tr>\n",
" <tr>\n",
" <td>None</td>\n",
" <td>0</td>\n",
" <td>[8.0]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" <td>[18.0]</td>\n",
" <td>[]</td>\n",
" <td>[]</td>\n",
" </tr>\n",
"</table>"
],
"text/plain": [
"[(0, 1, [2.0], [3.0], [], [12.0], [13.0], []),\n",
" (0, 0, [1.0], [], [], [11.0], [], []),\n",
" (1, 2, [], [4.0], [], [], [14.0], []),\n",
" (1, 3, [7.0], [], [6.0, 5.0], [17.0], [], [16.0, 15.0]),\n",
" (1, 4, [], [], [], [], [], []),\n",
" (1, 0, [], [], [], [0.0], [], []),\n",
" (None, 0, [8.0], [], [], [18.0], [], [])]"
]
},
"execution_count": 48,
"metadata": {},
"output_type": "execute_result"
}
],
"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;"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"collapsed": true
},
"outputs": [],
"source": []
}
],
"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.12"
}
},
"nbformat": 4,
"nbformat_minor": 0
}