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 2018/04/23 21:57:49 UTC
[05/15] madlib-site git commit: jupyter notebooks for 1.14 release
http://git-wip-us.apache.org/repos/asf/madlib-site/blob/3f849b9e/community-artifacts/Summary-v1.ipynb
----------------------------------------------------------------------
diff --git a/community-artifacts/Summary-v1.ipynb b/community-artifacts/Summary-v1.ipynb
deleted file mode 100644
index 57c3611..0000000
--- a/community-artifacts/Summary-v1.ipynb
+++ /dev/null
@@ -1,1026 +0,0 @@
-{
- "cells": [
- {
- "cell_type": "code",
- "execution_count": 13,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "The sql extension is already loaded. To reload it, use:\n",
- " %reload_ext sql\n"
- ]
- }
- ],
- "source": [
- "%load_ext sql"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 14,
- "metadata": {},
- "outputs": [
- {
- "data": {
- "text/plain": [
- "u'Connected: fmcquillan@madlib'"
- ]
- },
- "execution_count": 14,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "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"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 15,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "1 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>version</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>MADlib version: 1.12, git revision: unknown, cmake configuration time: Wed Aug 23 23:07:18 UTC 2017, build type: Release, build system: Darwin-16.7.0, C compiler: Clang, C++ compiler: Clang</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'MADlib version: 1.12, git revision: unknown, cmake configuration time: Wed Aug 23 23:07:18 UTC 2017, build type: Release, build system: Darwin-16.7.0, C compiler: Clang, C++ compiler: Clang',)]"
- ]
- },
- "execution_count": 15,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%sql select madlib.version();\n",
- "#%sql select version();"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# 1. On-line help"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 16,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "1 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>summary</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td><br> 'summary' is a generic function used to produce summary statistics<br> of any data table. The function invokes particular 'methods' from<br> the MADlib library to provide an overview of the data.<br> -------<br> For an overview on usage, run:<br> SELECT madlib.summary('usage');<br> -------<br> For an example, run:<br> SELECT madlib.summary('example')<br> </td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u\"\\n 'summary' is a generic function used to produce summary statistics\\n of any data table. The function invokes particular 'methods' from\\n the MADlib library to provide an overview of the data.\\n -------\\n For an overview on usage, run:\\n SELECT madlib.summary('usage');\\n -------\\n For an example, run:\\n SELECT madlib.summary('example')\\n \",)]"
- ]
- },
- "execution_count": 16,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%sql SELECT * FROM madlib.summary();"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# 2. Load data\n",
- "Using part of the popular iris data set from https://archive.ics.uci.edu/ml/datasets/iris"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 51,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "Done.\n",
- "30 rows affected.\n",
- "30 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>id</th>\n",
- " <th>sepal_length</th>\n",
- " <th>sepal_width</th>\n",
- " <th>petal_length</th>\n",
- " <th>petal_width</th>\n",
- " <th>class_name</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>1</td>\n",
- " <td>5.1</td>\n",
- " <td>3.5</td>\n",
- " <td>1.4</td>\n",
- " <td>0.2</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>2</td>\n",
- " <td>4.9</td>\n",
- " <td>3.0</td>\n",
- " <td>1.4</td>\n",
- " <td>0.2</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>3</td>\n",
- " <td>4.7</td>\n",
- " <td>3.2</td>\n",
- " <td>1.3</td>\n",
- " <td>0.2</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>4</td>\n",
- " <td>4.6</td>\n",
- " <td>3.1</td>\n",
- " <td>1.5</td>\n",
- " <td>0.2</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>5</td>\n",
- " <td>5.0</td>\n",
- " <td>3.6</td>\n",
- " <td>1.4</td>\n",
- " <td>0.2</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>6</td>\n",
- " <td>5.4</td>\n",
- " <td>3.9</td>\n",
- " <td>1.7</td>\n",
- " <td>0.4</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>7</td>\n",
- " <td>4.6</td>\n",
- " <td>3.4</td>\n",
- " <td>1.4</td>\n",
- " <td>0.3</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>8</td>\n",
- " <td>5.0</td>\n",
- " <td>3.4</td>\n",
- " <td>1.5</td>\n",
- " <td>0.2</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>9</td>\n",
- " <td>4.4</td>\n",
- " <td>2.9</td>\n",
- " <td>1.4</td>\n",
- " <td>0.2</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>10</td>\n",
- " <td>4.9</td>\n",
- " <td>3.1</td>\n",
- " <td>1.5</td>\n",
- " <td>0.1</td>\n",
- " <td>Iris-setosa</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>11</td>\n",
- " <td>7.0</td>\n",
- " <td>3.2</td>\n",
- " <td>4.7</td>\n",
- " <td>1.4</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>12</td>\n",
- " <td>6.4</td>\n",
- " <td>3.2</td>\n",
- " <td>4.5</td>\n",
- " <td>1.5</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>13</td>\n",
- " <td>6.9</td>\n",
- " <td>3.1</td>\n",
- " <td>4.9</td>\n",
- " <td>1.5</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>14</td>\n",
- " <td>5.5</td>\n",
- " <td>2.3</td>\n",
- " <td>4.0</td>\n",
- " <td>1.3</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>15</td>\n",
- " <td>6.5</td>\n",
- " <td>2.8</td>\n",
- " <td>4.6</td>\n",
- " <td>1.5</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>16</td>\n",
- " <td>5.7</td>\n",
- " <td>2.8</td>\n",
- " <td>4.5</td>\n",
- " <td>1.3</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>17</td>\n",
- " <td>6.3</td>\n",
- " <td>3.3</td>\n",
- " <td>4.7</td>\n",
- " <td>1.6</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>18</td>\n",
- " <td>4.9</td>\n",
- " <td>2.4</td>\n",
- " <td>3.3</td>\n",
- " <td>1.0</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>19</td>\n",
- " <td>6.6</td>\n",
- " <td>2.9</td>\n",
- " <td>4.6</td>\n",
- " <td>1.3</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>20</td>\n",
- " <td>5.2</td>\n",
- " <td>2.7</td>\n",
- " <td>3.9</td>\n",
- " <td>1.4</td>\n",
- " <td>Iris-versicolor</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>21</td>\n",
- " <td>6.3</td>\n",
- " <td>3.3</td>\n",
- " <td>6.0</td>\n",
- " <td>2.5</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>22</td>\n",
- " <td>5.8</td>\n",
- " <td>2.7</td>\n",
- " <td>5.1</td>\n",
- " <td>1.9</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>23</td>\n",
- " <td>7.1</td>\n",
- " <td>3.0</td>\n",
- " <td>5.9</td>\n",
- " <td>2.1</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>24</td>\n",
- " <td>6.3</td>\n",
- " <td>2.9</td>\n",
- " <td>5.6</td>\n",
- " <td>1.8</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>25</td>\n",
- " <td>6.5</td>\n",
- " <td>3.0</td>\n",
- " <td>5.8</td>\n",
- " <td>2.2</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>26</td>\n",
- " <td>7.6</td>\n",
- " <td>3.0</td>\n",
- " <td>6.6</td>\n",
- " <td>2.1</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>27</td>\n",
- " <td>4.9</td>\n",
- " <td>2.5</td>\n",
- " <td>4.5</td>\n",
- " <td>1.7</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>28</td>\n",
- " <td>7.3</td>\n",
- " <td>2.9</td>\n",
- " <td>6.3</td>\n",
- " <td>1.8</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>29</td>\n",
- " <td>6.7</td>\n",
- " <td>2.5</td>\n",
- " <td>5.8</td>\n",
- " <td>1.8</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>30</td>\n",
- " <td>7.2</td>\n",
- " <td>3.6</td>\n",
- " <td>6.1</td>\n",
- " <td>2.5</td>\n",
- " <td>Iris-virginica</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(1, 5.1, 3.5, 1.4, 0.2, u'Iris-setosa'),\n",
- " (2, 4.9, 3.0, 1.4, 0.2, u'Iris-setosa'),\n",
- " (3, 4.7, 3.2, 1.3, 0.2, u'Iris-setosa'),\n",
- " (4, 4.6, 3.1, 1.5, 0.2, u'Iris-setosa'),\n",
- " (5, 5.0, 3.6, 1.4, 0.2, u'Iris-setosa'),\n",
- " (6, 5.4, 3.9, 1.7, 0.4, u'Iris-setosa'),\n",
- " (7, 4.6, 3.4, 1.4, 0.3, u'Iris-setosa'),\n",
- " (8, 5.0, 3.4, 1.5, 0.2, u'Iris-setosa'),\n",
- " (9, 4.4, 2.9, 1.4, 0.2, u'Iris-setosa'),\n",
- " (10, 4.9, 3.1, 1.5, 0.1, u'Iris-setosa'),\n",
- " (11, 7.0, 3.2, 4.7, 1.4, u'Iris-versicolor'),\n",
- " (12, 6.4, 3.2, 4.5, 1.5, u'Iris-versicolor'),\n",
- " (13, 6.9, 3.1, 4.9, 1.5, u'Iris-versicolor'),\n",
- " (14, 5.5, 2.3, 4.0, 1.3, u'Iris-versicolor'),\n",
- " (15, 6.5, 2.8, 4.6, 1.5, u'Iris-versicolor'),\n",
- " (16, 5.7, 2.8, 4.5, 1.3, u'Iris-versicolor'),\n",
- " (17, 6.3, 3.3, 4.7, 1.6, u'Iris-versicolor'),\n",
- " (18, 4.9, 2.4, 3.3, 1.0, u'Iris-versicolor'),\n",
- " (19, 6.6, 2.9, 4.6, 1.3, u'Iris-versicolor'),\n",
- " (20, 5.2, 2.7, 3.9, 1.4, u'Iris-versicolor'),\n",
- " (21, 6.3, 3.3, 6.0, 2.5, u'Iris-virginica'),\n",
- " (22, 5.8, 2.7, 5.1, 1.9, u'Iris-virginica'),\n",
- " (23, 7.1, 3.0, 5.9, 2.1, u'Iris-virginica'),\n",
- " (24, 6.3, 2.9, 5.6, 1.8, u'Iris-virginica'),\n",
- " (25, 6.5, 3.0, 5.8, 2.2, u'Iris-virginica'),\n",
- " (26, 7.6, 3.0, 6.6, 2.1, u'Iris-virginica'),\n",
- " (27, 4.9, 2.5, 4.5, 1.7, u'Iris-virginica'),\n",
- " (28, 7.3, 2.9, 6.3, 1.8, u'Iris-virginica'),\n",
- " (29, 6.7, 2.5, 5.8, 1.8, u'Iris-virginica'),\n",
- " (30, 7.2, 3.6, 6.1, 2.5, u'Iris-virginica')]"
- ]
- },
- "execution_count": 51,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS iris;\n",
- "\n",
- "CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,\n",
- " petal_length FLOAT, petal_width FLOAT, \n",
- " class_name text);\n",
- " \n",
- "INSERT INTO iris VALUES \n",
- "(1,5.1,3.5,1.4,0.2,'Iris-setosa'),\n",
- "(2,4.9,3.0,1.4,0.2,'Iris-setosa'),\n",
- "(3,4.7,3.2,1.3,0.2,'Iris-setosa'),\n",
- "(4,4.6,3.1,1.5,0.2,'Iris-setosa'),\n",
- "(5,5.0,3.6,1.4,0.2,'Iris-setosa'),\n",
- "(6,5.4,3.9,1.7,0.4,'Iris-setosa'),\n",
- "(7,4.6,3.4,1.4,0.3,'Iris-setosa'),\n",
- "(8,5.0,3.4,1.5,0.2,'Iris-setosa'),\n",
- "(9,4.4,2.9,1.4,0.2,'Iris-setosa'),\n",
- "(10,4.9,3.1,1.5,0.1,'Iris-setosa'),\n",
- "(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),\n",
- "(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),\n",
- "(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),\n",
- "(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),\n",
- "(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),\n",
- "(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),\n",
- "(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),\n",
- "(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),\n",
- "(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),\n",
- "(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),\n",
- "(21,6.3,3.3,6.0,2.5,'Iris-virginica'),\n",
- "(22,5.8,2.7,5.1,1.9,'Iris-virginica'),\n",
- "(23,7.1,3.0,5.9,2.1,'Iris-virginica'),\n",
- "(24,6.3,2.9,5.6,1.8,'Iris-virginica'),\n",
- "(25,6.5,3.0,5.8,2.2,'Iris-virginica'),\n",
- "(26,7.6,3.0,6.6,2.1,'Iris-virginica'),\n",
- "(27,4.9,2.5,4.5,1.7,'Iris-virginica'),\n",
- "(28,7.3,2.9,6.3,1.8,'Iris-virginica'),\n",
- "(29,6.7,2.5,5.8,1.8,'Iris-virginica'),\n",
- "(30,7.2,3.6,6.1,2.5,'Iris-virginica');\n",
- "\n",
- "SELECT * FROM iris ORDER BY id;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# 3. Get summary data \n",
- "Basic summary data using all defaults."
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 52,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>output_table</th>\n",
- " <th>row_count</th>\n",
- " <th>duration</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>iris_summary</td>\n",
- " <td>6</td>\n",
- " <td>0.0161118507385</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'iris_summary', 6, 0.0161118507385254)]"
- ]
- },
- "execution_count": 52,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS iris_summary;\n",
- "\n",
- "SELECT * FROM madlib.summary( 'iris', -- Source table\n",
- " 'iris_summary' -- Output table\n",
- " );"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 53,
- "metadata": {
- "scrolled": true
- },
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "2 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>group_by</th>\n",
- " <th>group_by_value</th>\n",
- " <th>target_column</th>\n",
- " <th>column_number</th>\n",
- " <th>data_type</th>\n",
- " <th>row_count</th>\n",
- " <th>distinct_values</th>\n",
- " <th>missing_values</th>\n",
- " <th>blank_values</th>\n",
- " <th>fraction_missing</th>\n",
- " <th>fraction_blank</th>\n",
- " <th>mean</th>\n",
- " <th>variance</th>\n",
- " <th>min</th>\n",
- " <th>max</th>\n",
- " <th>quantile_array</th>\n",
- " <th>most_frequent_values</th>\n",
- " <th>mfv_frequencies</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " <td>sepal_length</td>\n",
- " <td>2</td>\n",
- " <td>float8</td>\n",
- " <td>30</td>\n",
- " <td>22</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>5.84333333333</td>\n",
- " <td>0.929436781609</td>\n",
- " <td>4.4</td>\n",
- " <td>7.6</td>\n",
- " <td>[5.057, 6.414]</td>\n",
- " <td>[u'4.9', u'6.3', u'5']</td>\n",
- " <td>[4L, 3L, 2L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " <td>sepal_width</td>\n",
- " <td>3</td>\n",
- " <td>float8</td>\n",
- " <td>30</td>\n",
- " <td>14</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>3.04</td>\n",
- " <td>0.139034482759</td>\n",
- " <td>2.3</td>\n",
- " <td>3.9</td>\n",
- " <td>[2.9, 3.2]</td>\n",
- " <td>[u'3', u'2.9', u'3.2']</td>\n",
- " <td>[4L, 4L, 3L]</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, [5.057, 6.414], [u'4.9', u'6.3', u'5'], [4L, 3L, 2L]),\n",
- " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, [2.9, 3.2], [u'3', u'2.9', u'3.2'], [4L, 4L, 3L])]"
- ]
- },
- "execution_count": 53,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "SELECT * FROM iris_summary;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# 4. Grouping"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 45,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n",
- "8 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>group_by</th>\n",
- " <th>group_by_value</th>\n",
- " <th>target_column</th>\n",
- " <th>column_number</th>\n",
- " <th>data_type</th>\n",
- " <th>row_count</th>\n",
- " <th>distinct_values</th>\n",
- " <th>missing_values</th>\n",
- " <th>blank_values</th>\n",
- " <th>fraction_missing</th>\n",
- " <th>fraction_blank</th>\n",
- " <th>mean</th>\n",
- " <th>variance</th>\n",
- " <th>min</th>\n",
- " <th>max</th>\n",
- " <th>first_quartile</th>\n",
- " <th>median</th>\n",
- " <th>third_quartile</th>\n",
- " <th>most_frequent_values</th>\n",
- " <th>mfv_frequencies</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>class_name</td>\n",
- " <td>Iris-setosa</td>\n",
- " <td>sepal_length</td>\n",
- " <td>2</td>\n",
- " <td>float8</td>\n",
- " <td>10</td>\n",
- " <td>7</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>4.86</td>\n",
- " <td>0.0848888888889</td>\n",
- " <td>4.4</td>\n",
- " <td>5.4</td>\n",
- " <td>4.625</td>\n",
- " <td>4.9</td>\n",
- " <td>5.0</td>\n",
- " <td>[u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7']</td>\n",
- " <td>[2L, 2L, 2L, 1L, 1L, 1L, 1L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>class_name</td>\n",
- " <td>Iris-setosa</td>\n",
- " <td>sepal_width</td>\n",
- " <td>3</td>\n",
- " <td>float8</td>\n",
- " <td>10</td>\n",
- " <td>8</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>3.31</td>\n",
- " <td>0.0943333333333</td>\n",
- " <td>2.9</td>\n",
- " <td>3.9</td>\n",
- " <td>3.1</td>\n",
- " <td>3.3</td>\n",
- " <td>3.475</td>\n",
- " <td>[u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3']</td>\n",
- " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>class_name</td>\n",
- " <td>Iris-versicolor</td>\n",
- " <td>sepal_length</td>\n",
- " <td>2</td>\n",
- " <td>float8</td>\n",
- " <td>10</td>\n",
- " <td>10</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>6.1</td>\n",
- " <td>0.528888888889</td>\n",
- " <td>4.9</td>\n",
- " <td>7.0</td>\n",
- " <td>5.55</td>\n",
- " <td>6.35</td>\n",
- " <td>6.575</td>\n",
- " <td>[u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2']</td>\n",
- " <td>[1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>class_name</td>\n",
- " <td>Iris-versicolor</td>\n",
- " <td>sepal_width</td>\n",
- " <td>3</td>\n",
- " <td>float8</td>\n",
- " <td>10</td>\n",
- " <td>8</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>2.87</td>\n",
- " <td>0.115666666667</td>\n",
- " <td>2.3</td>\n",
- " <td>3.3</td>\n",
- " <td>2.725</td>\n",
- " <td>2.85</td>\n",
- " <td>3.175</td>\n",
- " <td>[u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1']</td>\n",
- " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>class_name</td>\n",
- " <td>Iris-virginica</td>\n",
- " <td>sepal_length</td>\n",
- " <td>2</td>\n",
- " <td>float8</td>\n",
- " <td>10</td>\n",
- " <td>9</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>6.57</td>\n",
- " <td>0.646777777778</td>\n",
- " <td>4.9</td>\n",
- " <td>7.6</td>\n",
- " <td>6.3</td>\n",
- " <td>6.6</td>\n",
- " <td>7.175</td>\n",
- " <td>[u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2']</td>\n",
- " <td>[2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>class_name</td>\n",
- " <td>Iris-virginica</td>\n",
- " <td>sepal_width</td>\n",
- " <td>3</td>\n",
- " <td>float8</td>\n",
- " <td>10</td>\n",
- " <td>6</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>2.94</td>\n",
- " <td>0.113777777778</td>\n",
- " <td>2.5</td>\n",
- " <td>3.6</td>\n",
- " <td>2.75</td>\n",
- " <td>2.95</td>\n",
- " <td>3.0</td>\n",
- " <td>[u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6']</td>\n",
- " <td>[3L, 2L, 2L, 1L, 1L, 1L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " <td>sepal_length</td>\n",
- " <td>2</td>\n",
- " <td>float8</td>\n",
- " <td>30</td>\n",
- " <td>22</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>5.84333333333</td>\n",
- " <td>0.929436781609</td>\n",
- " <td>4.4</td>\n",
- " <td>7.6</td>\n",
- " <td>4.925</td>\n",
- " <td>5.75</td>\n",
- " <td>6.575</td>\n",
- " <td>[u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4']</td>\n",
- " <td>[4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " <td>sepal_width</td>\n",
- " <td>3</td>\n",
- " <td>float8</td>\n",
- " <td>30</td>\n",
- " <td>14</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>3.04</td>\n",
- " <td>0.139034482759</td>\n",
- " <td>2.3</td>\n",
- " <td>3.9</td>\n",
- " <td>2.825</td>\n",
- " <td>3.0</td>\n",
- " <td>3.275</td>\n",
- " <td>[u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4']</td>\n",
- " <td>[4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(u'class_name', u'Iris-setosa', u'sepal_length', 2, u'float8', 10L, 7L, 0L, None, 0.0, None, 4.86, 0.0848888888888976, 4.4, 5.4, 4.625, 4.9, 5.0, [u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7'], [2L, 2L, 2L, 1L, 1L, 1L, 1L]),\n",
- " (u'class_name', u'Iris-setosa', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 3.31, 0.094333333333336, 2.9, 3.9, 3.1, 3.3, 3.475, [u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
- " (u'class_name', u'Iris-versicolor', u'sepal_length', 2, u'float8', 10L, 10L, 0L, None, 0.0, None, 6.1, 0.528888888888893, 4.9, 7.0, 5.55, 6.35, 6.575, [u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2'], [1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
- " (u'class_name', u'Iris-versicolor', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 2.87, 0.115666666666666, 2.3, 3.3, 2.725, 2.85, 3.175, [u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
- " (u'class_name', u'Iris-virginica', u'sepal_length', 2, u'float8', 10L, 9L, 0L, None, 0.0, None, 6.57, 0.646777777777778, 4.9, 7.6, 6.3, 6.6, 7.175, [u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2'], [2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
- " (u'class_name', u'Iris-virginica', u'sepal_width', 3, u'float8', 10L, 6L, 0L, None, 0.0, None, 2.94, 0.11377777777778, 2.5, 3.6, 2.75, 2.95, 3.0, [u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6'], [3L, 2L, 2L, 1L, 1L, 1L]),\n",
- " (None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, 4.925, 5.75, 6.575, [u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4'], [4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]),\n",
- " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, 2.825, 3.0, 3.275, [u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4'], [4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L])]"
- ]
- },
- "execution_count": 45,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS iris_summary;\n",
- "\n",
- "SELECT * FROM madlib.summary( 'iris', -- Source table\n",
- " 'iris_summary', -- Output table\n",
- " 'sepal_length, sepal_width', -- Columns to summarize\n",
- " 'class_name' -- Grouping column\n",
- " );\n",
- "\n",
- "SELECT * FROM iris_summary;"
- ]
- },
- {
- "cell_type": "markdown",
- "metadata": {},
- "source": [
- "# 5. Other parameters"
- ]
- },
- {
- "cell_type": "code",
- "execution_count": 50,
- "metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Done.\n",
- "1 rows affected.\n",
- "2 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "<table>\n",
- " <tr>\n",
- " <th>group_by</th>\n",
- " <th>group_by_value</th>\n",
- " <th>target_column</th>\n",
- " <th>column_number</th>\n",
- " <th>data_type</th>\n",
- " <th>row_count</th>\n",
- " <th>distinct_values</th>\n",
- " <th>missing_values</th>\n",
- " <th>blank_values</th>\n",
- " <th>fraction_missing</th>\n",
- " <th>fraction_blank</th>\n",
- " <th>mean</th>\n",
- " <th>variance</th>\n",
- " <th>min</th>\n",
- " <th>max</th>\n",
- " <th>quantile_array</th>\n",
- " <th>most_frequent_values</th>\n",
- " <th>mfv_frequencies</th>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " <td>sepal_length</td>\n",
- " <td>2</td>\n",
- " <td>float8</td>\n",
- " <td>30</td>\n",
- " <td>22</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>5.84333333333</td>\n",
- " <td>0.929436781609</td>\n",
- " <td>4.4</td>\n",
- " <td>7.6</td>\n",
- " <td>[5.057, 6.414]</td>\n",
- " <td>[u'4.9', u'6.3', u'5']</td>\n",
- " <td>[4L, 3L, 2L]</td>\n",
- " </tr>\n",
- " <tr>\n",
- " <td>None</td>\n",
- " <td>None</td>\n",
- " <td>sepal_width</td>\n",
- " <td>3</td>\n",
- " <td>float8</td>\n",
- " <td>30</td>\n",
- " <td>14</td>\n",
- " <td>0</td>\n",
- " <td>None</td>\n",
- " <td>0.0</td>\n",
- " <td>None</td>\n",
- " <td>3.04</td>\n",
- " <td>0.139034482759</td>\n",
- " <td>2.3</td>\n",
- " <td>3.9</td>\n",
- " <td>[2.9, 3.2]</td>\n",
- " <td>[u'3', u'2.9', u'3.2']</td>\n",
- " <td>[4L, 4L, 3L]</td>\n",
- " </tr>\n",
- "</table>"
- ],
- "text/plain": [
- "[(None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, [5.057, 6.414], [u'4.9', u'6.3', u'5'], [4L, 3L, 2L]),\n",
- " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, [2.9, 3.2], [u'3', u'2.9', u'3.2'], [4L, 4L, 3L])]"
- ]
- },
- "execution_count": 50,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
- "source": [
- "%%sql\n",
- "DROP TABLE IF EXISTS iris_summary;\n",
- "\n",
- "SELECT * FROM madlib.summary( 'iris', -- Source table\n",
- " 'iris_summary', -- Output table\n",
- " 'sepal_length, sepal_width', -- Columns to summarize\n",
- " NULL, -- No grouping\n",
- " TRUE, -- Get distinct values\n",
- " FALSE, -- Dont get quartiles\n",
- " ARRAY[0.33, 0.66], -- Get ntiles\n",
- " 3, -- Number of MFV to compute\n",
- " FALSE -- Get exact values\n",
- " );\n",
- "\n",
- "SELECT * FROM iris_summary;"
- ]
- }
- ],
- "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": 1
-}
http://git-wip-us.apache.org/repos/asf/madlib-site/blob/3f849b9e/community-artifacts/Summary-v2.ipynb
----------------------------------------------------------------------
diff --git a/community-artifacts/Summary-v2.ipynb b/community-artifacts/Summary-v2.ipynb
new file mode 100644
index 0000000..f8c7a39
--- /dev/null
+++ b/community-artifacts/Summary-v2.ipynb
@@ -0,0 +1,1017 @@
+{
+ "cells": [
+ {
+ "cell_type": "code",
+ "execution_count": 1,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stderr",
+ "output_type": "stream",
+ "text": [
+ "/Users/fmcquillan/anaconda/lib/python2.7/site-packages/IPython/config.py:13: ShimWarning: The `IPython.config` package has been deprecated. You should import from traitlets.config instead.\n",
+ " \"You should import from traitlets.config instead.\", ShimWarning)\n",
+ "/Users/fmcquillan/anaconda/lib/python2.7/site-packages/IPython/utils/traitlets.py:5: UserWarning: IPython.utils.traitlets has moved to a top-level traitlets package.\n",
+ " warn(\"IPython.utils.traitlets has moved to a top-level traitlets package.\")\n"
+ ]
+ }
+ ],
+ "source": [
+ "%load_ext sql"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 2,
+ "metadata": {},
+ "outputs": [
+ {
+ "data": {
+ "text/plain": [
+ "u'Connected: fmcquillan@madlib'"
+ ]
+ },
+ "execution_count": 2,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "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"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 3,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>version</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>MADlib version: 1.13, git revision: unknown, cmake configuration time: Wed Dec 20 08:02:21 UTC 2017, build type: Release, build system: Darwin-17.3.0, C compiler: Clang, C++ compiler: Clang</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'MADlib version: 1.13, git revision: unknown, cmake configuration time: Wed Dec 20 08:02:21 UTC 2017, build type: Release, build system: Darwin-17.3.0, C compiler: Clang, C++ compiler: Clang',)]"
+ ]
+ },
+ "execution_count": 3,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%sql select madlib.version();\n",
+ "#%sql select version();"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# 1. On-line help"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "%sql SELECT * FROM madlib.summary();"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# 2. Load data\n",
+ "Using part of the popular iris data set from https://archive.ics.uci.edu/ml/datasets/iris"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 4,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "Done.\n",
+ "30 rows affected.\n",
+ "30 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>id</th>\n",
+ " <th>sepal_length</th>\n",
+ " <th>sepal_width</th>\n",
+ " <th>petal_length</th>\n",
+ " <th>petal_width</th>\n",
+ " <th>class_name</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>1</td>\n",
+ " <td>5.1</td>\n",
+ " <td>3.5</td>\n",
+ " <td>1.4</td>\n",
+ " <td>0.2</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>2</td>\n",
+ " <td>4.9</td>\n",
+ " <td>3.0</td>\n",
+ " <td>1.4</td>\n",
+ " <td>0.2</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>3</td>\n",
+ " <td>4.7</td>\n",
+ " <td>3.2</td>\n",
+ " <td>1.3</td>\n",
+ " <td>0.2</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>4</td>\n",
+ " <td>4.6</td>\n",
+ " <td>3.1</td>\n",
+ " <td>1.5</td>\n",
+ " <td>0.2</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>5</td>\n",
+ " <td>5.0</td>\n",
+ " <td>3.6</td>\n",
+ " <td>1.4</td>\n",
+ " <td>0.2</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>6</td>\n",
+ " <td>5.4</td>\n",
+ " <td>3.9</td>\n",
+ " <td>1.7</td>\n",
+ " <td>0.4</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>7</td>\n",
+ " <td>4.6</td>\n",
+ " <td>3.4</td>\n",
+ " <td>1.4</td>\n",
+ " <td>0.3</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>8</td>\n",
+ " <td>5.0</td>\n",
+ " <td>3.4</td>\n",
+ " <td>1.5</td>\n",
+ " <td>0.2</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>9</td>\n",
+ " <td>4.4</td>\n",
+ " <td>2.9</td>\n",
+ " <td>1.4</td>\n",
+ " <td>0.2</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>10</td>\n",
+ " <td>4.9</td>\n",
+ " <td>3.1</td>\n",
+ " <td>1.5</td>\n",
+ " <td>0.1</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>11</td>\n",
+ " <td>7.0</td>\n",
+ " <td>3.2</td>\n",
+ " <td>4.7</td>\n",
+ " <td>1.4</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>12</td>\n",
+ " <td>6.4</td>\n",
+ " <td>3.2</td>\n",
+ " <td>4.5</td>\n",
+ " <td>1.5</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>13</td>\n",
+ " <td>6.9</td>\n",
+ " <td>3.1</td>\n",
+ " <td>4.9</td>\n",
+ " <td>1.5</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>14</td>\n",
+ " <td>5.5</td>\n",
+ " <td>2.3</td>\n",
+ " <td>4.0</td>\n",
+ " <td>1.3</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>15</td>\n",
+ " <td>6.5</td>\n",
+ " <td>2.8</td>\n",
+ " <td>4.6</td>\n",
+ " <td>1.5</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>16</td>\n",
+ " <td>5.7</td>\n",
+ " <td>2.8</td>\n",
+ " <td>4.5</td>\n",
+ " <td>1.3</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>17</td>\n",
+ " <td>6.3</td>\n",
+ " <td>3.3</td>\n",
+ " <td>4.7</td>\n",
+ " <td>1.6</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>18</td>\n",
+ " <td>4.9</td>\n",
+ " <td>2.4</td>\n",
+ " <td>3.3</td>\n",
+ " <td>1.0</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>19</td>\n",
+ " <td>6.6</td>\n",
+ " <td>2.9</td>\n",
+ " <td>4.6</td>\n",
+ " <td>1.3</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>20</td>\n",
+ " <td>5.2</td>\n",
+ " <td>2.7</td>\n",
+ " <td>3.9</td>\n",
+ " <td>1.4</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>21</td>\n",
+ " <td>6.3</td>\n",
+ " <td>3.3</td>\n",
+ " <td>6.0</td>\n",
+ " <td>2.5</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>22</td>\n",
+ " <td>5.8</td>\n",
+ " <td>2.7</td>\n",
+ " <td>5.1</td>\n",
+ " <td>1.9</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>23</td>\n",
+ " <td>7.1</td>\n",
+ " <td>3.0</td>\n",
+ " <td>5.9</td>\n",
+ " <td>2.1</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>24</td>\n",
+ " <td>6.3</td>\n",
+ " <td>2.9</td>\n",
+ " <td>5.6</td>\n",
+ " <td>1.8</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>25</td>\n",
+ " <td>6.5</td>\n",
+ " <td>3.0</td>\n",
+ " <td>5.8</td>\n",
+ " <td>2.2</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>26</td>\n",
+ " <td>7.6</td>\n",
+ " <td>3.0</td>\n",
+ " <td>6.6</td>\n",
+ " <td>2.1</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>27</td>\n",
+ " <td>4.9</td>\n",
+ " <td>2.5</td>\n",
+ " <td>4.5</td>\n",
+ " <td>1.7</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>28</td>\n",
+ " <td>7.3</td>\n",
+ " <td>2.9</td>\n",
+ " <td>6.3</td>\n",
+ " <td>1.8</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>29</td>\n",
+ " <td>6.7</td>\n",
+ " <td>2.5</td>\n",
+ " <td>5.8</td>\n",
+ " <td>1.8</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>30</td>\n",
+ " <td>7.2</td>\n",
+ " <td>3.6</td>\n",
+ " <td>6.1</td>\n",
+ " <td>2.5</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(1, 5.1, 3.5, 1.4, 0.2, u'Iris-setosa'),\n",
+ " (2, 4.9, 3.0, 1.4, 0.2, u'Iris-setosa'),\n",
+ " (3, 4.7, 3.2, 1.3, 0.2, u'Iris-setosa'),\n",
+ " (4, 4.6, 3.1, 1.5, 0.2, u'Iris-setosa'),\n",
+ " (5, 5.0, 3.6, 1.4, 0.2, u'Iris-setosa'),\n",
+ " (6, 5.4, 3.9, 1.7, 0.4, u'Iris-setosa'),\n",
+ " (7, 4.6, 3.4, 1.4, 0.3, u'Iris-setosa'),\n",
+ " (8, 5.0, 3.4, 1.5, 0.2, u'Iris-setosa'),\n",
+ " (9, 4.4, 2.9, 1.4, 0.2, u'Iris-setosa'),\n",
+ " (10, 4.9, 3.1, 1.5, 0.1, u'Iris-setosa'),\n",
+ " (11, 7.0, 3.2, 4.7, 1.4, u'Iris-versicolor'),\n",
+ " (12, 6.4, 3.2, 4.5, 1.5, u'Iris-versicolor'),\n",
+ " (13, 6.9, 3.1, 4.9, 1.5, u'Iris-versicolor'),\n",
+ " (14, 5.5, 2.3, 4.0, 1.3, u'Iris-versicolor'),\n",
+ " (15, 6.5, 2.8, 4.6, 1.5, u'Iris-versicolor'),\n",
+ " (16, 5.7, 2.8, 4.5, 1.3, u'Iris-versicolor'),\n",
+ " (17, 6.3, 3.3, 4.7, 1.6, u'Iris-versicolor'),\n",
+ " (18, 4.9, 2.4, 3.3, 1.0, u'Iris-versicolor'),\n",
+ " (19, 6.6, 2.9, 4.6, 1.3, u'Iris-versicolor'),\n",
+ " (20, 5.2, 2.7, 3.9, 1.4, u'Iris-versicolor'),\n",
+ " (21, 6.3, 3.3, 6.0, 2.5, u'Iris-virginica'),\n",
+ " (22, 5.8, 2.7, 5.1, 1.9, u'Iris-virginica'),\n",
+ " (23, 7.1, 3.0, 5.9, 2.1, u'Iris-virginica'),\n",
+ " (24, 6.3, 2.9, 5.6, 1.8, u'Iris-virginica'),\n",
+ " (25, 6.5, 3.0, 5.8, 2.2, u'Iris-virginica'),\n",
+ " (26, 7.6, 3.0, 6.6, 2.1, u'Iris-virginica'),\n",
+ " (27, 4.9, 2.5, 4.5, 1.7, u'Iris-virginica'),\n",
+ " (28, 7.3, 2.9, 6.3, 1.8, u'Iris-virginica'),\n",
+ " (29, 6.7, 2.5, 5.8, 1.8, u'Iris-virginica'),\n",
+ " (30, 7.2, 3.6, 6.1, 2.5, u'Iris-virginica')]"
+ ]
+ },
+ "execution_count": 4,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS iris;\n",
+ "\n",
+ "CREATE TABLE iris (id INT, sepal_length FLOAT, sepal_width FLOAT,\n",
+ " petal_length FLOAT, petal_width FLOAT, \n",
+ " class_name text);\n",
+ " \n",
+ "INSERT INTO iris VALUES \n",
+ "(1,5.1,3.5,1.4,0.2,'Iris-setosa'),\n",
+ "(2,4.9,3.0,1.4,0.2,'Iris-setosa'),\n",
+ "(3,4.7,3.2,1.3,0.2,'Iris-setosa'),\n",
+ "(4,4.6,3.1,1.5,0.2,'Iris-setosa'),\n",
+ "(5,5.0,3.6,1.4,0.2,'Iris-setosa'),\n",
+ "(6,5.4,3.9,1.7,0.4,'Iris-setosa'),\n",
+ "(7,4.6,3.4,1.4,0.3,'Iris-setosa'),\n",
+ "(8,5.0,3.4,1.5,0.2,'Iris-setosa'),\n",
+ "(9,4.4,2.9,1.4,0.2,'Iris-setosa'),\n",
+ "(10,4.9,3.1,1.5,0.1,'Iris-setosa'),\n",
+ "(11,7.0,3.2,4.7,1.4,'Iris-versicolor'),\n",
+ "(12,6.4,3.2,4.5,1.5,'Iris-versicolor'),\n",
+ "(13,6.9,3.1,4.9,1.5,'Iris-versicolor'),\n",
+ "(14,5.5,2.3,4.0,1.3,'Iris-versicolor'),\n",
+ "(15,6.5,2.8,4.6,1.5,'Iris-versicolor'),\n",
+ "(16,5.7,2.8,4.5,1.3,'Iris-versicolor'),\n",
+ "(17,6.3,3.3,4.7,1.6,'Iris-versicolor'),\n",
+ "(18,4.9,2.4,3.3,1.0,'Iris-versicolor'),\n",
+ "(19,6.6,2.9,4.6,1.3,'Iris-versicolor'),\n",
+ "(20,5.2,2.7,3.9,1.4,'Iris-versicolor'),\n",
+ "(21,6.3,3.3,6.0,2.5,'Iris-virginica'),\n",
+ "(22,5.8,2.7,5.1,1.9,'Iris-virginica'),\n",
+ "(23,7.1,3.0,5.9,2.1,'Iris-virginica'),\n",
+ "(24,6.3,2.9,5.6,1.8,'Iris-virginica'),\n",
+ "(25,6.5,3.0,5.8,2.2,'Iris-virginica'),\n",
+ "(26,7.6,3.0,6.6,2.1,'Iris-virginica'),\n",
+ "(27,4.9,2.5,4.5,1.7,'Iris-virginica'),\n",
+ "(28,7.3,2.9,6.3,1.8,'Iris-virginica'),\n",
+ "(29,6.7,2.5,5.8,1.8,'Iris-virginica'),\n",
+ "(30,7.2,3.6,6.1,2.5,'Iris-virginica');\n",
+ "\n",
+ "SELECT * FROM iris ORDER BY id;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# 3. Get summary data \n",
+ "Basic summary data using all defaults."
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 6,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>output_table</th>\n",
+ " <th>row_count</th>\n",
+ " <th>duration</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>iris_summary</td>\n",
+ " <td>6</td>\n",
+ " <td>0.0278480052948</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'iris_summary', 6, 0.0278480052947998)]"
+ ]
+ },
+ "execution_count": 6,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS iris_summary;\n",
+ "\n",
+ "SELECT * FROM madlib.summary( 'iris', -- Source table\n",
+ " 'iris_summary' -- Output table\n",
+ " );"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 7,
+ "metadata": {
+ "scrolled": true
+ },
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "6 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>group_by</th>\n",
+ " <th>group_by_value</th>\n",
+ " <th>target_column</th>\n",
+ " <th>column_number</th>\n",
+ " <th>data_type</th>\n",
+ " <th>row_count</th>\n",
+ " <th>distinct_values</th>\n",
+ " <th>missing_values</th>\n",
+ " <th>blank_values</th>\n",
+ " <th>fraction_missing</th>\n",
+ " <th>fraction_blank</th>\n",
+ " <th>mean</th>\n",
+ " <th>variance</th>\n",
+ " <th>min</th>\n",
+ " <th>max</th>\n",
+ " <th>first_quartile</th>\n",
+ " <th>median</th>\n",
+ " <th>third_quartile</th>\n",
+ " <th>most_frequent_values</th>\n",
+ " <th>mfv_frequencies</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>id</td>\n",
+ " <td>1</td>\n",
+ " <td>int4</td>\n",
+ " <td>30</td>\n",
+ " <td>30</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>15.5</td>\n",
+ " <td>77.5</td>\n",
+ " <td>1.0</td>\n",
+ " <td>30.0</td>\n",
+ " <td>8.25</td>\n",
+ " <td>15.5</td>\n",
+ " <td>22.75</td>\n",
+ " <td>[u'1', u'2', u'3', u'4', u'5', u'6', u'7', u'8', u'9', u'10']</td>\n",
+ " <td>[1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>sepal_length</td>\n",
+ " <td>2</td>\n",
+ " <td>float8</td>\n",
+ " <td>30</td>\n",
+ " <td>22</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>5.84333333333</td>\n",
+ " <td>0.929436781609</td>\n",
+ " <td>4.4</td>\n",
+ " <td>7.6</td>\n",
+ " <td>4.925</td>\n",
+ " <td>5.75</td>\n",
+ " <td>6.575</td>\n",
+ " <td>[u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4']</td>\n",
+ " <td>[4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>sepal_width</td>\n",
+ " <td>3</td>\n",
+ " <td>float8</td>\n",
+ " <td>30</td>\n",
+ " <td>14</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>3.04</td>\n",
+ " <td>0.139034482759</td>\n",
+ " <td>2.3</td>\n",
+ " <td>3.9</td>\n",
+ " <td>2.825</td>\n",
+ " <td>3.0</td>\n",
+ " <td>3.275</td>\n",
+ " <td>[u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4']</td>\n",
+ " <td>[4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>petal_length</td>\n",
+ " <td>4</td>\n",
+ " <td>float8</td>\n",
+ " <td>30</td>\n",
+ " <td>19</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>3.86333333333</td>\n",
+ " <td>3.53964367816</td>\n",
+ " <td>1.3</td>\n",
+ " <td>6.6</td>\n",
+ " <td>1.5</td>\n",
+ " <td>4.5</td>\n",
+ " <td>5.475</td>\n",
+ " <td>[u'1.4', u'1.5', u'4.5', u'5.8', u'4.6', u'4.7', u'3.3', u'4.9', u'1.7', u'4']</td>\n",
+ " <td>[5L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>petal_width</td>\n",
+ " <td>5</td>\n",
+ " <td>float8</td>\n",
+ " <td>30</td>\n",
+ " <td>15</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>1.21333333333</td>\n",
+ " <td>0.622574712644</td>\n",
+ " <td>0.1</td>\n",
+ " <td>2.5</td>\n",
+ " <td>0.225</td>\n",
+ " <td>1.4</td>\n",
+ " <td>1.8</td>\n",
+ " <td>[u'0.2', u'1.8', u'1.5', u'1.3', u'1.4', u'2.1', u'2.5', u'1.6', u'1', u'0.1']</td>\n",
+ " <td>[7L, 3L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>class_name</td>\n",
+ " <td>6</td>\n",
+ " <td>text</td>\n",
+ " <td>30</td>\n",
+ " <td>3</td>\n",
+ " <td>0</td>\n",
+ " <td>0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>11.0</td>\n",
+ " <td>15.0</td>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>[u'Iris-setosa', u'Iris-versicolor', u'Iris-virginica']</td>\n",
+ " <td>[10L, 10L, 10L]</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(None, None, u'id', 1, u'int4', 30L, 30L, 0L, None, 0.0, None, 15.5, 77.5, 1.0, 30.0, 8.25, 15.5, 22.75, [u'1', u'2', u'3', u'4', u'5', u'6', u'7', u'8', u'9', u'10'], [1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
+ " (None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, 4.925, 5.75, 6.575, [u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4'], [4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]),\n",
+ " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, 2.825, 3.0, 3.275, [u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4'], [4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]),\n",
+ " (None, None, u'petal_length', 4, u'float8', 30L, 19L, 0L, None, 0.0, None, 3.86333333333333, 3.53964367816093, 1.3, 6.6, 1.5, 4.5, 5.475, [u'1.4', u'1.5', u'4.5', u'5.8', u'4.6', u'4.7', u'3.3', u'4.9', u'1.7', u'4'], [5L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L]),\n",
+ " (None, None, u'petal_width', 5, u'float8', 30L, 15L, 0L, None, 0.0, None, 1.21333333333333, 0.622574712643679, 0.1, 2.5, 0.225, 1.4, 1.8, [u'0.2', u'1.8', u'1.5', u'1.3', u'1.4', u'2.1', u'2.5', u'1.6', u'1', u'0.1'], [7L, 3L, 3L, 3L, 2L, 2L, 2L, 1L, 1L, 1L]),\n",
+ " (None, None, u'class_name', 6, u'text', 30L, 3L, 0L, 0L, 0.0, 0.0, None, None, 11.0, 15.0, None, None, None, [u'Iris-setosa', u'Iris-versicolor', u'Iris-virginica'], [10L, 10L, 10L])]"
+ ]
+ },
+ "execution_count": 7,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "SELECT * FROM iris_summary;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# 4. Grouping"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 8,
+ "metadata": {},
+ "outputs": [
+ {
+ "name": "stdout",
+ "output_type": "stream",
+ "text": [
+ "Done.\n",
+ "1 rows affected.\n",
+ "8 rows affected.\n"
+ ]
+ },
+ {
+ "data": {
+ "text/html": [
+ "<table>\n",
+ " <tr>\n",
+ " <th>group_by</th>\n",
+ " <th>group_by_value</th>\n",
+ " <th>target_column</th>\n",
+ " <th>column_number</th>\n",
+ " <th>data_type</th>\n",
+ " <th>row_count</th>\n",
+ " <th>distinct_values</th>\n",
+ " <th>missing_values</th>\n",
+ " <th>blank_values</th>\n",
+ " <th>fraction_missing</th>\n",
+ " <th>fraction_blank</th>\n",
+ " <th>mean</th>\n",
+ " <th>variance</th>\n",
+ " <th>min</th>\n",
+ " <th>max</th>\n",
+ " <th>first_quartile</th>\n",
+ " <th>median</th>\n",
+ " <th>third_quartile</th>\n",
+ " <th>most_frequent_values</th>\n",
+ " <th>mfv_frequencies</th>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>class_name</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " <td>sepal_length</td>\n",
+ " <td>2</td>\n",
+ " <td>float8</td>\n",
+ " <td>10</td>\n",
+ " <td>7</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>4.86</td>\n",
+ " <td>0.0848888888889</td>\n",
+ " <td>4.4</td>\n",
+ " <td>5.4</td>\n",
+ " <td>4.625</td>\n",
+ " <td>4.9</td>\n",
+ " <td>5.0</td>\n",
+ " <td>[u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7']</td>\n",
+ " <td>[2L, 2L, 2L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>class_name</td>\n",
+ " <td>Iris-setosa</td>\n",
+ " <td>sepal_width</td>\n",
+ " <td>3</td>\n",
+ " <td>float8</td>\n",
+ " <td>10</td>\n",
+ " <td>8</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>3.31</td>\n",
+ " <td>0.0943333333333</td>\n",
+ " <td>2.9</td>\n",
+ " <td>3.9</td>\n",
+ " <td>3.1</td>\n",
+ " <td>3.3</td>\n",
+ " <td>3.475</td>\n",
+ " <td>[u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3']</td>\n",
+ " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>class_name</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " <td>sepal_length</td>\n",
+ " <td>2</td>\n",
+ " <td>float8</td>\n",
+ " <td>10</td>\n",
+ " <td>10</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>6.1</td>\n",
+ " <td>0.528888888889</td>\n",
+ " <td>4.9</td>\n",
+ " <td>7.0</td>\n",
+ " <td>5.55</td>\n",
+ " <td>6.35</td>\n",
+ " <td>6.575</td>\n",
+ " <td>[u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2']</td>\n",
+ " <td>[1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>class_name</td>\n",
+ " <td>Iris-versicolor</td>\n",
+ " <td>sepal_width</td>\n",
+ " <td>3</td>\n",
+ " <td>float8</td>\n",
+ " <td>10</td>\n",
+ " <td>8</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>2.87</td>\n",
+ " <td>0.115666666667</td>\n",
+ " <td>2.3</td>\n",
+ " <td>3.3</td>\n",
+ " <td>2.725</td>\n",
+ " <td>2.85</td>\n",
+ " <td>3.175</td>\n",
+ " <td>[u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1']</td>\n",
+ " <td>[2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>class_name</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " <td>sepal_length</td>\n",
+ " <td>2</td>\n",
+ " <td>float8</td>\n",
+ " <td>10</td>\n",
+ " <td>9</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>6.57</td>\n",
+ " <td>0.646777777778</td>\n",
+ " <td>4.9</td>\n",
+ " <td>7.6</td>\n",
+ " <td>6.3</td>\n",
+ " <td>6.6</td>\n",
+ " <td>7.175</td>\n",
+ " <td>[u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2']</td>\n",
+ " <td>[2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>class_name</td>\n",
+ " <td>Iris-virginica</td>\n",
+ " <td>sepal_width</td>\n",
+ " <td>3</td>\n",
+ " <td>float8</td>\n",
+ " <td>10</td>\n",
+ " <td>6</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>2.94</td>\n",
+ " <td>0.113777777778</td>\n",
+ " <td>2.5</td>\n",
+ " <td>3.6</td>\n",
+ " <td>2.75</td>\n",
+ " <td>2.95</td>\n",
+ " <td>3.0</td>\n",
+ " <td>[u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6']</td>\n",
+ " <td>[3L, 2L, 2L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>sepal_length</td>\n",
+ " <td>2</td>\n",
+ " <td>float8</td>\n",
+ " <td>30</td>\n",
+ " <td>22</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>5.84333333333</td>\n",
+ " <td>0.929436781609</td>\n",
+ " <td>4.4</td>\n",
+ " <td>7.6</td>\n",
+ " <td>4.925</td>\n",
+ " <td>5.75</td>\n",
+ " <td>6.575</td>\n",
+ " <td>[u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4']</td>\n",
+ " <td>[4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]</td>\n",
+ " </tr>\n",
+ " <tr>\n",
+ " <td>None</td>\n",
+ " <td>None</td>\n",
+ " <td>sepal_width</td>\n",
+ " <td>3</td>\n",
+ " <td>float8</td>\n",
+ " <td>30</td>\n",
+ " <td>14</td>\n",
+ " <td>0</td>\n",
+ " <td>None</td>\n",
+ " <td>0.0</td>\n",
+ " <td>None</td>\n",
+ " <td>3.04</td>\n",
+ " <td>0.139034482759</td>\n",
+ " <td>2.3</td>\n",
+ " <td>3.9</td>\n",
+ " <td>2.825</td>\n",
+ " <td>3.0</td>\n",
+ " <td>3.275</td>\n",
+ " <td>[u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4']</td>\n",
+ " <td>[4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L]</td>\n",
+ " </tr>\n",
+ "</table>"
+ ],
+ "text/plain": [
+ "[(u'class_name', u'Iris-setosa', u'sepal_length', 2, u'float8', 10L, 7L, 0L, None, 0.0, None, 4.86, 0.0848888888888976, 4.4, 5.4, 4.625, 4.9, 5.0, [u'4.6', u'4.9', u'5', u'5.1', u'4.4', u'5.4', u'4.7'], [2L, 2L, 2L, 1L, 1L, 1L, 1L]),\n",
+ " (u'class_name', u'Iris-setosa', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 3.31, 0.094333333333336, 2.9, 3.9, 3.1, 3.3, 3.475, [u'3.4', u'3.1', u'3.2', u'3.6', u'3.9', u'3.5', u'2.9', u'3'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
+ " (u'class_name', u'Iris-versicolor', u'sepal_length', 2, u'float8', 10L, 10L, 0L, None, 0.0, None, 6.1, 0.528888888888893, 4.9, 7.0, 5.55, 6.35, 6.575, [u'7', u'6.4', u'6.9', u'5.5', u'6.5', u'5.7', u'6.3', u'4.9', u'6.6', u'5.2'], [1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
+ " (u'class_name', u'Iris-versicolor', u'sepal_width', 3, u'float8', 10L, 8L, 0L, None, 0.0, None, 2.87, 0.115666666666666, 2.3, 3.3, 2.725, 2.85, 3.175, [u'2.8', u'3.2', u'2.3', u'3.3', u'2.4', u'2.9', u'2.7', u'3.1'], [2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
+ " (u'class_name', u'Iris-virginica', u'sepal_length', 2, u'float8', 10L, 9L, 0L, None, 0.0, None, 6.57, 0.646777777777778, 4.9, 7.6, 6.3, 6.6, 7.175, [u'6.3', u'5.8', u'7.1', u'6.5', u'7.6', u'4.9', u'7.3', u'6.7', u'7.2'], [2L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L]),\n",
+ " (u'class_name', u'Iris-virginica', u'sepal_width', 3, u'float8', 10L, 6L, 0L, None, 0.0, None, 2.94, 0.11377777777778, 2.5, 3.6, 2.75, 2.95, 3.0, [u'3', u'2.9', u'2.5', u'3.3', u'2.7', u'3.6'], [3L, 2L, 2L, 1L, 1L, 1L]),\n",
+ " (None, None, u'sepal_length', 2, u'float8', 30L, 22L, 0L, None, 0.0, None, 5.84333333333333, 0.9294367816092, 4.4, 7.6, 4.925, 5.75, 6.575, [u'4.9', u'6.3', u'6.5', u'4.6', u'5', u'6.9', u'5.4', u'4.4', u'7', u'6.4'], [4L, 3L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 1L]),\n",
+ " (None, None, u'sepal_width', 3, u'float8', 30L, 14L, 0L, None, 0.0, None, 3.04, 0.13903448275862, 2.3, 3.9, 2.825, 3.0, 3.275, [u'2.9', u'3', u'3.1', u'3.2', u'3.6', u'3.3', u'2.8', u'2.7', u'2.5', u'3.4'], [4L, 4L, 3L, 3L, 2L, 2L, 2L, 2L, 2L, 2L])]"
+ ]
+ },
+ "execution_count": 8,
+ "metadata": {},
+ "output_type": "execute_result"
+ }
+ ],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS iris_summary;\n",
+ "\n",
+ "SELECT * FROM madlib.summary( 'iris', -- Source table\n",
+ " 'iris_summary', -- Output table\n",
+ " 'sepal_length, sepal_width', -- Columns to summarize\n",
+ " 'class_name' -- Grouping column\n",
+ " );\n",
+ "\n",
+ "SELECT * FROM iris_summary;"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "metadata": {},
+ "source": [
+ "# 5. Other parameters"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": null,
+ "metadata": {
+ "collapsed": true
+ },
+ "outputs": [],
+ "source": [
+ "%%sql\n",
+ "DROP TABLE IF EXISTS iris_summary;\n",
+ "\n",
+ "SELECT * FROM madlib.summary( 'iris', -- Source table\n",
+ " 'iris_summary', -- Output table\n",
+ " 'sepal_length, sepal_width', -- Columns to summarize\n",
+ " NULL, -- No grouping\n",
+ " TRUE, -- Get distinct values\n",
+ " FALSE, -- Dont get quartiles\n",
+ " ARRAY[0.33, 0.66], -- Get ntiles\n",
+ " 3, -- Number of MFV to compute\n",
+ " FALSE -- Get exact values\n",
+ " );\n",
+ "\n",
+ "SELECT * FROM iris_summary;"
+ ]
+ }
+ ],
+ "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": 1
+}