| { |
| "cells": [ |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# Graph measures\n", |
| "\n", |
| "- Closeness\n", |
| "- Graph diameter\n", |
| "- Average path length\n", |
| "- In-out degree\n", |
| "\n", |
| "Graph measures were added in MADlib 1.12. Some graph measures require a valid output from a prior APSP run - both the APSP table and the associated output summary table must be present." |
| ] |
| }, |
| { |
| "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: gpdbchina@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\n", |
| "\n", |
| "# Greenplum 4.2.3.0\n", |
| "#%sql postgresql://gpdbchina@10.194.10.68:55000/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.12-dev, git revision: rel/v1.11-51-g69f7886, cmake configuration time: Fri Aug 18 16:30:51 UTC 2017, build type: Release, build system: Linux-2.6.18-238.27.1.el5.hotfix.bz516490, C compiler: gcc 4.4.0, C++ compiler: g++ 4.4.0</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(u'MADlib version: 1.12-dev, git revision: rel/v1.11-51-g69f7886, cmake configuration time: Fri Aug 18 16:30:51 UTC 2017, build type: Release, build system: Linux-2.6.18-238.27.1.el5.hotfix.bz516490, C compiler: gcc 4.4.0, C++ compiler: g++ 4.4.0',)]" |
| ] |
| }, |
| "execution_count": 3, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%sql select madlib.version();\n", |
| "#%sql select version();" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# Closeness" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# 1. Create vertex and edge tables" |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 18, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "Done.\n", |
| "Done.\n", |
| "8 rows affected.\n", |
| "12 rows affected.\n", |
| "12 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>src_id</th>\n", |
| " <th>dest_id</th>\n", |
| " <th>edge_weight</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>1.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>10.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>10.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " <td>1.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>5</td>\n", |
| " <td>1.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>6</td>\n", |
| " <td>3.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>0</td>\n", |
| " <td>1.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>0</td>\n", |
| " <td>-2.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>6</td>\n", |
| " <td>1.0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>7</td>\n", |
| " <td>1.0</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 1, 1.0),\n", |
| " (0, 2, 1.0),\n", |
| " (0, 4, 10.0),\n", |
| " (1, 2, 2.0),\n", |
| " (1, 3, 10.0),\n", |
| " (2, 3, 1.0),\n", |
| " (2, 5, 1.0),\n", |
| " (2, 6, 3.0),\n", |
| " (3, 0, 1.0),\n", |
| " (4, 0, -2.0),\n", |
| " (5, 6, 1.0),\n", |
| " (6, 7, 1.0)]" |
| ] |
| }, |
| "execution_count": 18, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql \n", |
| "DROP TABLE IF EXISTS vertex, edge;\n", |
| "\n", |
| "CREATE TABLE vertex(\n", |
| " id INTEGER,\n", |
| " name TEXT\n", |
| " );\n", |
| "\n", |
| "CREATE TABLE edge(\n", |
| " src_id INTEGER,\n", |
| " dest_id INTEGER,\n", |
| " edge_weight FLOAT8\n", |
| " );\n", |
| "\n", |
| "INSERT INTO vertex VALUES\n", |
| "(0, 'A'),\n", |
| "(1, 'B'),\n", |
| "(2, 'C'),\n", |
| "(3, 'D'),\n", |
| "(4, 'E'),\n", |
| "(5, 'F'),\n", |
| "(6, 'G'),\n", |
| "(7, 'H');\n", |
| "\n", |
| "INSERT INTO edge VALUES\n", |
| "(0, 1, 1.0),\n", |
| "(0, 2, 1.0),\n", |
| "(0, 4, 10.0),\n", |
| "(1, 2, 2.0),\n", |
| "(1, 3, 10.0),\n", |
| "(2, 3, 1.0),\n", |
| "(2, 5, 1.0),\n", |
| "(2, 6, 3.0),\n", |
| "(3, 0, 1.0),\n", |
| "(4, 0, -2.0),\n", |
| "(5, 6, 1.0),\n", |
| "(6, 7, 1.0);\n", |
| "\n", |
| "SELECT * FROM edge ORDER BY src_id, dest_id;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# 2. Calculate APSP" |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 29, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "1 rows affected.\n", |
| "64 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>src_id</th>\n", |
| " <th>dest_id</th>\n", |
| " <th>edge_weight</th>\n", |
| " <th>parent</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>0.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>1.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>10.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>3.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>4.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>4.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>0.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>3.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>14.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>3.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>6</td>\n", |
| " <td>4.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>7</td>\n", |
| " <td>5.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>0</td>\n", |
| " <td>2.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>1</td>\n", |
| " <td>3.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>2</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " <td>1.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>4</td>\n", |
| " <td>12.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>5</td>\n", |
| " <td>1.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>6</td>\n", |
| " <td>2.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>7</td>\n", |
| " <td>3.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>0</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>1</td>\n", |
| " <td>2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>3</td>\n", |
| " <td>0.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>4</td>\n", |
| " <td>11.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>5</td>\n", |
| " <td>3.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>6</td>\n", |
| " <td>4.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>7</td>\n", |
| " <td>5.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>0</td>\n", |
| " <td>-2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>1</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>2</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>3</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>4</td>\n", |
| " <td>0.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>5</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>6</td>\n", |
| " <td>1.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>7</td>\n", |
| " <td>2.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>0</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>1</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>2</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>3</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>4</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>5</td>\n", |
| " <td>0.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>6</td>\n", |
| " <td>1.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>7</td>\n", |
| " <td>2.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>0</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>1</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>2</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>3</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>4</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>5</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>6</td>\n", |
| " <td>0.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>7</td>\n", |
| " <td>1.0</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>0</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>1</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>2</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>3</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>4</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>5</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>6</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>7</td>\n", |
| " <td>0.0</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 0, 0.0, 0),\n", |
| " (0, 1, 1.0, 1),\n", |
| " (0, 2, 1.0, 2),\n", |
| " (0, 3, 2.0, 2),\n", |
| " (0, 4, 10.0, 4),\n", |
| " (0, 5, 2.0, 2),\n", |
| " (0, 6, 3.0, 5),\n", |
| " (0, 7, 4.0, 6),\n", |
| " (1, 0, 4.0, 3),\n", |
| " (1, 1, 0.0, 1),\n", |
| " (1, 2, 2.0, 2),\n", |
| " (1, 3, 3.0, 2),\n", |
| " (1, 4, 14.0, 0),\n", |
| " (1, 5, 3.0, 2),\n", |
| " (1, 6, 4.0, 5),\n", |
| " (1, 7, 5.0, 6),\n", |
| " (2, 0, 2.0, 3),\n", |
| " (2, 1, 3.0, 0),\n", |
| " (2, 2, 0.0, 2),\n", |
| " (2, 3, 1.0, 3),\n", |
| " (2, 4, 12.0, 0),\n", |
| " (2, 5, 1.0, 5),\n", |
| " (2, 6, 2.0, 5),\n", |
| " (2, 7, 3.0, 6),\n", |
| " (3, 0, 1.0, 0),\n", |
| " (3, 1, 2.0, 0),\n", |
| " (3, 2, 2.0, 0),\n", |
| " (3, 3, 0.0, 3),\n", |
| " (3, 4, 11.0, 0),\n", |
| " (3, 5, 3.0, 2),\n", |
| " (3, 6, 4.0, 5),\n", |
| " (3, 7, 5.0, 6),\n", |
| " (4, 0, -2.0, 0),\n", |
| " (4, 1, -1.0, 0),\n", |
| " (4, 2, -1.0, 0),\n", |
| " (4, 3, 0.0, 2),\n", |
| " (4, 4, 0.0, 4),\n", |
| " (4, 5, 0.0, 2),\n", |
| " (4, 6, 1.0, 5),\n", |
| " (4, 7, 2.0, 6),\n", |
| " (5, 0, inf, None),\n", |
| " (5, 1, inf, None),\n", |
| " (5, 2, inf, None),\n", |
| " (5, 3, inf, None),\n", |
| " (5, 4, inf, None),\n", |
| " (5, 5, 0.0, 5),\n", |
| " (5, 6, 1.0, 6),\n", |
| " (5, 7, 2.0, 6),\n", |
| " (6, 0, inf, None),\n", |
| " (6, 1, inf, None),\n", |
| " (6, 2, inf, None),\n", |
| " (6, 3, inf, None),\n", |
| " (6, 4, inf, None),\n", |
| " (6, 5, inf, None),\n", |
| " (6, 6, 0.0, 6),\n", |
| " (6, 7, 1.0, 7),\n", |
| " (7, 0, inf, None),\n", |
| " (7, 1, inf, None),\n", |
| " (7, 2, inf, None),\n", |
| " (7, 3, inf, None),\n", |
| " (7, 4, inf, None),\n", |
| " (7, 5, inf, None),\n", |
| " (7, 6, inf, None),\n", |
| " (7, 7, 0.0, 7)]" |
| ] |
| }, |
| "execution_count": 29, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_apsp, out_apsp_summary;\n", |
| "\n", |
| "SELECT madlib.graph_apsp('vertex', -- Vertex table\n", |
| " 'id', -- Vertix id column (NULL means use default naming)\n", |
| " 'edge', -- Edge table\n", |
| " 'src=src_id, dest=dest_id, weight=edge_weight',\n", |
| " -- Edge arguments (NULL means use default naming)\n", |
| " 'out_apsp'); -- Output table of shortest paths\n", |
| "\n", |
| "SELECT * FROM out_apsp ORDER BY src_id, dest_id;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# 3. Compute the closeness measure for all nodes" |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 20, |
| "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>src_id</th>\n", |
| " <th>inverse_sum_dist</th>\n", |
| " <th>inverse_avg_dist</th>\n", |
| " <th>sum_inverse_dist</th>\n", |
| " <th>k_degree</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0.0434782608696</td>\n", |
| " <td>0.304347826087</td>\n", |
| " <td>3.68333333333</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0.0285714285714</td>\n", |
| " <td>0.2</td>\n", |
| " <td>1.9380952381</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>0.0416666666667</td>\n", |
| " <td>0.291666666667</td>\n", |
| " <td>3.75</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>0.0357142857143</td>\n", |
| " <td>0.25</td>\n", |
| " <td>2.87424242424</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>-7.0</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>0.333333333333</td>\n", |
| " <td>0.666666666667</td>\n", |
| " <td>1.5</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>7</td>\n", |
| " <td>None</td>\n", |
| " <td>None</td>\n", |
| " <td>0.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 0.0434782608695652, 0.304347826086957, 3.68333333333333, 7L),\n", |
| " (1, 0.0285714285714286, 0.2, 1.93809523809524, 7L),\n", |
| " (2, 0.0416666666666667, 0.291666666666667, 3.75, 7L),\n", |
| " (3, 0.0357142857142857, 0.25, 2.87424242424242, 7L),\n", |
| " (4, -1.0, -7.0, -1.0, 7L),\n", |
| " (5, 0.333333333333333, 0.666666666666667, 1.5, 2L),\n", |
| " (6, 1.0, 1.0, 1.0, 1L),\n", |
| " (7, None, None, 0.0, 0L)]" |
| ] |
| }, |
| "execution_count": 20, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_closeness;\n", |
| "SELECT madlib.graph_closeness('out_apsp', 'out_closeness');\n", |
| "SELECT * FROM out_closeness ORDER BY src_id;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# 4. Create a graph with 2 groups" |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 31, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "21 rows affected.\n", |
| "1 rows affected.\n", |
| "22 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>src_id</th>\n", |
| " <th>dest_id</th>\n", |
| " <th>edge_weight</th>\n", |
| " <th>grp</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>10.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>10.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>5</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>6</td>\n", |
| " <td>3.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>0</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>0</td>\n", |
| " <td>-2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>6</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>7</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>10.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>10.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>5</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>0</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>0</td>\n", |
| " <td>-2.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>5</td>\n", |
| " <td>-20.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 1, 1.0, 0),\n", |
| " (0, 2, 1.0, 0),\n", |
| " (0, 4, 10.0, 0),\n", |
| " (1, 2, 2.0, 0),\n", |
| " (1, 3, 10.0, 0),\n", |
| " (2, 3, 1.0, 0),\n", |
| " (2, 5, 1.0, 0),\n", |
| " (2, 6, 3.0, 0),\n", |
| " (3, 0, 1.0, 0),\n", |
| " (4, 0, -2.0, 0),\n", |
| " (5, 6, 1.0, 0),\n", |
| " (6, 7, 1.0, 0),\n", |
| " (0, 1, 1.0, 1),\n", |
| " (0, 2, 1.0, 1),\n", |
| " (0, 4, 10.0, 1),\n", |
| " (1, 2, 2.0, 1),\n", |
| " (1, 3, 10.0, 1),\n", |
| " (2, 3, 1.0, 1),\n", |
| " (2, 5, 1.0, 1),\n", |
| " (3, 0, 1.0, 1),\n", |
| " (4, 0, -2.0, 1),\n", |
| " (4, 5, -20.0, 1)]" |
| ] |
| }, |
| "execution_count": 31, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql \n", |
| "DROP TABLE IF EXISTS edge_gr;\n", |
| "\n", |
| "CREATE TABLE edge_gr AS\n", |
| "(\n", |
| " SELECT *, 0 AS grp FROM edge\n", |
| " UNION\n", |
| " SELECT *, 1 AS grp FROM edge WHERE src_id < 6 AND dest_id < 6\n", |
| ");\n", |
| "\n", |
| "INSERT INTO edge_gr VALUES\n", |
| "(4,5,-20,1);\n", |
| "\n", |
| "SELECT * FROM edge_gr ORDER BY grp, src_id, dest_id;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# 5. Find APSP for all groups" |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 32, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "1 rows affected.\n", |
| "100 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>grp</th>\n", |
| " <th>src_id</th>\n", |
| " <th>dest_id</th>\n", |
| " <th>edge_weight</th>\n", |
| " <th>parent</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>0.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>1.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>10.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>3.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>4.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>4.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>0.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>3.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>14.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>3.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>6</td>\n", |
| " <td>4.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>7</td>\n", |
| " <td>5.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>0</td>\n", |
| " <td>2.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>1</td>\n", |
| " <td>3.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>2</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " <td>1.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>4</td>\n", |
| " <td>12.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>5</td>\n", |
| " <td>1.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>6</td>\n", |
| " <td>2.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>7</td>\n", |
| " <td>3.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>0</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>1</td>\n", |
| " <td>2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>3</td>\n", |
| " <td>0.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>4</td>\n", |
| " <td>11.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>5</td>\n", |
| " <td>3.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>6</td>\n", |
| " <td>4.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>7</td>\n", |
| " <td>5.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>0</td>\n", |
| " <td>-2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>1</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>2</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>3</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>4</td>\n", |
| " <td>0.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>5</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>6</td>\n", |
| " <td>1.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>7</td>\n", |
| " <td>2.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>0</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>1</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>2</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>3</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>4</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>5</td>\n", |
| " <td>0.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>6</td>\n", |
| " <td>1.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>7</td>\n", |
| " <td>2.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>0</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>1</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>2</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>3</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>4</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>5</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>6</td>\n", |
| " <td>0.0</td>\n", |
| " <td>6</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>6</td>\n", |
| " <td>7</td>\n", |
| " <td>1.0</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>0</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>1</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>2</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>3</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>4</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>5</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>6</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>7</td>\n", |
| " <td>7</td>\n", |
| " <td>0.0</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>0.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>1.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>10.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>-10.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>4.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>0.0</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>3.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>14.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>-6.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>0</td>\n", |
| " <td>2.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>1</td>\n", |
| " <td>3.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>2</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " <td>1.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>4</td>\n", |
| " <td>12.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>5</td>\n", |
| " <td>-8.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>0</td>\n", |
| " <td>1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>1</td>\n", |
| " <td>2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>2</td>\n", |
| " <td>2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>3</td>\n", |
| " <td>0.0</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>4</td>\n", |
| " <td>11.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>5</td>\n", |
| " <td>-9.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>0</td>\n", |
| " <td>-2.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>1</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>2</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>3</td>\n", |
| " <td>0.0</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>4</td>\n", |
| " <td>0.0</td>\n", |
| " <td>4</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>5</td>\n", |
| " <td>-20.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>0</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>1</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>2</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>3</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>4</td>\n", |
| " <td>inf</td>\n", |
| " <td>None</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>5</td>\n", |
| " <td>0.0</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 0, 0, 0.0, 0),\n", |
| " (0, 0, 1, 1.0, 1),\n", |
| " (0, 0, 2, 1.0, 2),\n", |
| " (0, 0, 3, 2.0, 2),\n", |
| " (0, 0, 4, 10.0, 4),\n", |
| " (0, 0, 5, 2.0, 2),\n", |
| " (0, 0, 6, 3.0, 5),\n", |
| " (0, 0, 7, 4.0, 6),\n", |
| " (0, 1, 0, 4.0, 3),\n", |
| " (0, 1, 1, 0.0, 1),\n", |
| " (0, 1, 2, 2.0, 2),\n", |
| " (0, 1, 3, 3.0, 2),\n", |
| " (0, 1, 4, 14.0, 0),\n", |
| " (0, 1, 5, 3.0, 2),\n", |
| " (0, 1, 6, 4.0, 5),\n", |
| " (0, 1, 7, 5.0, 6),\n", |
| " (0, 2, 0, 2.0, 3),\n", |
| " (0, 2, 1, 3.0, 0),\n", |
| " (0, 2, 2, 0.0, 2),\n", |
| " (0, 2, 3, 1.0, 3),\n", |
| " (0, 2, 4, 12.0, 0),\n", |
| " (0, 2, 5, 1.0, 5),\n", |
| " (0, 2, 6, 2.0, 5),\n", |
| " (0, 2, 7, 3.0, 6),\n", |
| " (0, 3, 0, 1.0, 0),\n", |
| " (0, 3, 1, 2.0, 0),\n", |
| " (0, 3, 2, 2.0, 0),\n", |
| " (0, 3, 3, 0.0, 3),\n", |
| " (0, 3, 4, 11.0, 0),\n", |
| " (0, 3, 5, 3.0, 2),\n", |
| " (0, 3, 6, 4.0, 5),\n", |
| " (0, 3, 7, 5.0, 6),\n", |
| " (0, 4, 0, -2.0, 0),\n", |
| " (0, 4, 1, -1.0, 0),\n", |
| " (0, 4, 2, -1.0, 0),\n", |
| " (0, 4, 3, 0.0, 2),\n", |
| " (0, 4, 4, 0.0, 4),\n", |
| " (0, 4, 5, 0.0, 2),\n", |
| " (0, 4, 6, 1.0, 5),\n", |
| " (0, 4, 7, 2.0, 6),\n", |
| " (0, 5, 0, inf, None),\n", |
| " (0, 5, 1, inf, None),\n", |
| " (0, 5, 2, inf, None),\n", |
| " (0, 5, 3, inf, None),\n", |
| " (0, 5, 4, inf, None),\n", |
| " (0, 5, 5, 0.0, 5),\n", |
| " (0, 5, 6, 1.0, 6),\n", |
| " (0, 5, 7, 2.0, 6),\n", |
| " (0, 6, 0, inf, None),\n", |
| " (0, 6, 1, inf, None),\n", |
| " (0, 6, 2, inf, None),\n", |
| " (0, 6, 3, inf, None),\n", |
| " (0, 6, 4, inf, None),\n", |
| " (0, 6, 5, inf, None),\n", |
| " (0, 6, 6, 0.0, 6),\n", |
| " (0, 6, 7, 1.0, 7),\n", |
| " (0, 7, 0, inf, None),\n", |
| " (0, 7, 1, inf, None),\n", |
| " (0, 7, 2, inf, None),\n", |
| " (0, 7, 3, inf, None),\n", |
| " (0, 7, 4, inf, None),\n", |
| " (0, 7, 5, inf, None),\n", |
| " (0, 7, 6, inf, None),\n", |
| " (0, 7, 7, 0.0, 7),\n", |
| " (1, 0, 0, 0.0, 0),\n", |
| " (1, 0, 1, 1.0, 1),\n", |
| " (1, 0, 2, 1.0, 2),\n", |
| " (1, 0, 3, 2.0, 2),\n", |
| " (1, 0, 4, 10.0, 4),\n", |
| " (1, 0, 5, -10.0, 4),\n", |
| " (1, 1, 0, 4.0, 3),\n", |
| " (1, 1, 1, 0.0, 1),\n", |
| " (1, 1, 2, 2.0, 2),\n", |
| " (1, 1, 3, 3.0, 2),\n", |
| " (1, 1, 4, 14.0, 0),\n", |
| " (1, 1, 5, -6.0, 4),\n", |
| " (1, 2, 0, 2.0, 3),\n", |
| " (1, 2, 1, 3.0, 0),\n", |
| " (1, 2, 2, 0.0, 2),\n", |
| " (1, 2, 3, 1.0, 3),\n", |
| " (1, 2, 4, 12.0, 0),\n", |
| " (1, 2, 5, -8.0, 4),\n", |
| " (1, 3, 0, 1.0, 0),\n", |
| " (1, 3, 1, 2.0, 0),\n", |
| " (1, 3, 2, 2.0, 0),\n", |
| " (1, 3, 3, 0.0, 3),\n", |
| " (1, 3, 4, 11.0, 0),\n", |
| " (1, 3, 5, -9.0, 4),\n", |
| " (1, 4, 0, -2.0, 0),\n", |
| " (1, 4, 1, -1.0, 0),\n", |
| " (1, 4, 2, -1.0, 0),\n", |
| " (1, 4, 3, 0.0, 2),\n", |
| " (1, 4, 4, 0.0, 4),\n", |
| " (1, 4, 5, -20.0, 5),\n", |
| " (1, 5, 0, inf, None),\n", |
| " (1, 5, 1, inf, None),\n", |
| " (1, 5, 2, inf, None),\n", |
| " (1, 5, 3, inf, None),\n", |
| " (1, 5, 4, inf, None),\n", |
| " (1, 5, 5, 0.0, 5)]" |
| ] |
| }, |
| "execution_count": 32, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_gr, out_gr_summary;\n", |
| "\n", |
| "SELECT madlib.graph_apsp(\n", |
| " 'vertex', -- Vertex table\n", |
| " NULL, -- Vertex id column (NULL means use default naming)\n", |
| " 'edge_gr', -- Edge table\n", |
| " 'src=src_id, dest=dest_id, weight=edge_weight',\n", |
| " 'out_gr', -- Output table of shortest paths\n", |
| " 'grp' -- Grouping columns\n", |
| ");\n", |
| "\n", |
| "SELECT * FROM out_gr ORDER BY grp, src_id, dest_id;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# 6. Compute closeness measure for vertex 0 to vertex 5 in every group" |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 24, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "1 rows affected.\n", |
| "12 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>grp</th>\n", |
| " <th>src_id</th>\n", |
| " <th>inverse_sum_dist</th>\n", |
| " <th>inverse_avg_dist</th>\n", |
| " <th>sum_inverse_dist</th>\n", |
| " <th>k_degree</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>0.0434782608696</td>\n", |
| " <td>0.304347826087</td>\n", |
| " <td>3.68333333333</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>0.0285714285714</td>\n", |
| " <td>0.2</td>\n", |
| " <td>1.9380952381</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>0.0416666666667</td>\n", |
| " <td>0.291666666667</td>\n", |
| " <td>3.75</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>3</td>\n", |
| " <td>0.0357142857143</td>\n", |
| " <td>0.25</td>\n", |
| " <td>2.87424242424</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>4</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>-7.0</td>\n", |
| " <td>-1.0</td>\n", |
| " <td>7</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>5</td>\n", |
| " <td>0.333333333333</td>\n", |
| " <td>0.666666666667</td>\n", |
| " <td>1.5</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>0.25</td>\n", |
| " <td>1.25</td>\n", |
| " <td>2.5</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>0.0588235294118</td>\n", |
| " <td>0.294117647059</td>\n", |
| " <td>0.988095238095</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " <td>0.1</td>\n", |
| " <td>0.5</td>\n", |
| " <td>1.79166666667</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>3</td>\n", |
| " <td>0.142857142857</td>\n", |
| " <td>0.714285714286</td>\n", |
| " <td>1.9797979798</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>4</td>\n", |
| " <td>-0.0416666666667</td>\n", |
| " <td>-0.208333333333</td>\n", |
| " <td>-2.55</td>\n", |
| " <td>5</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>5</td>\n", |
| " <td>None</td>\n", |
| " <td>None</td>\n", |
| " <td>0.0</td>\n", |
| " <td>0</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 0, 0.0434782608695652, 0.304347826086957, 3.68333333333333, 7L),\n", |
| " (0, 1, 0.0285714285714286, 0.2, 1.93809523809524, 7L),\n", |
| " (0, 2, 0.0416666666666667, 0.291666666666667, 3.75, 7L),\n", |
| " (0, 3, 0.0357142857142857, 0.25, 2.87424242424242, 7L),\n", |
| " (0, 4, -1.0, -7.0, -1.0, 7L),\n", |
| " (0, 5, 0.333333333333333, 0.666666666666667, 1.5, 2L),\n", |
| " (1, 0, 0.25, 1.25, 2.5, 5L),\n", |
| " (1, 1, 0.0588235294117647, 0.294117647058824, 0.988095238095238, 5L),\n", |
| " (1, 2, 0.1, 0.5, 1.79166666666667, 5L),\n", |
| " (1, 3, 0.142857142857143, 0.714285714285714, 1.97979797979798, 5L),\n", |
| " (1, 4, -0.0416666666666667, -0.208333333333333, -2.55, 5L),\n", |
| " (1, 5, None, None, 0.0, 0L)]" |
| ] |
| }, |
| "execution_count": 24, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_gr_closeness;\n", |
| "\n", |
| "SELECT madlib.graph_closeness(\n", |
| " 'out_gr', \n", |
| " 'out_gr_closeness', \n", |
| " 'src_id >= 0 and src_id <=5');\n", |
| "\n", |
| "SELECT * FROM out_gr_closeness ORDER BY grp, src_id;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# Graph Diameter\n", |
| "Use the same graph and APSP output from above." |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 26, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "1 rows affected.\n", |
| "1 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>diameter</th>\n", |
| " <th>diameter_end_vertices</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>14.0</td>\n", |
| " <td>[[1, 4]]</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(14.0, [[1, 4]])]" |
| ] |
| }, |
| "execution_count": 26, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_diameter;\n", |
| "SELECT madlib.graph_diameter('out_apsp', 'out_diameter');\n", |
| "SELECT * FROM out_diameter;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "For grouping, use the same graph and APSP output with grouping from above." |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 28, |
| "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>grp</th>\n", |
| " <th>diameter</th>\n", |
| " <th>diameter_end_vertices</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>14.0</td>\n", |
| " <td>[[1, 4]]</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>14.0</td>\n", |
| " <td>[[1, 4]]</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 14.0, [[1, 4]]), (1, 14.0, [[1, 4]])]" |
| ] |
| }, |
| "execution_count": 28, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_gr_path;\n", |
| "SELECT madlib.graph_diameter('out_gr', 'out_gr_diameter');\n", |
| "SELECT * FROM out_gr_diameter ORDER BY grp;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# Average Path Length\n", |
| "Use the same graph and APSP output from above." |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 35, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "1 rows affected.\n", |
| "1 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>avg_path_length</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2.01785714286</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(2.01785714285714,)]" |
| ] |
| }, |
| "execution_count": 35, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_avg_path_length;\n", |
| "SELECT madlib.graph_avg_path_length('out_apsp', 'out_avg_path_length');\n", |
| "SELECT * FROM out_avg_path_length;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "For grouping, use the same graph and APSP output with grouping from above." |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 37, |
| "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>grp</th>\n", |
| " <th>avg_path_length</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2.01785714286</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0.466666666667</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 2.01785714285714), (1, 0.466666666666667)]" |
| ] |
| }, |
| "execution_count": 37, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS out_gr_path;\n", |
| "SELECT madlib.graph_avg_path_length('out_gr', 'out_gr_path');\n", |
| "SELECT * FROM out_gr_path ORDER BY grp;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "# In-Out Degree\n", |
| "Use the same graph and APSP output from above." |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 39, |
| "metadata": {}, |
| "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>indegree</th>\n", |
| " <th>outdegree</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>2</td>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>3</td>\n", |
| " <td>2</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>4</td>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>5</td>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>6</td>\n", |
| " <td>2</td>\n", |
| " <td>1</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 2L, 3L),\n", |
| " (1, 1L, 2L),\n", |
| " (2, 2L, 3L),\n", |
| " (3, 2L, 1L),\n", |
| " (4, 1L, 1L),\n", |
| " (5, 1L, 1L),\n", |
| " (6, 2L, 1L)]" |
| ] |
| }, |
| "execution_count": 39, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql\n", |
| "DROP TABLE IF EXISTS degrees;\n", |
| "\n", |
| "SELECT madlib.graph_vertex_degrees(\n", |
| " 'vertex', -- Vertex table\n", |
| " 'id', -- Vertix id column (NULL means use default naming)\n", |
| " 'edge', -- Edge table\n", |
| " 'src=src_id, dest=dest_id, weight=edge_weight',\n", |
| " 'degrees'); -- Output table of shortest paths\n", |
| "\n", |
| "SELECT * FROM degrees ORDER BY id;" |
| ] |
| }, |
| { |
| "cell_type": "markdown", |
| "metadata": {}, |
| "source": [ |
| "For grouping, use the same graph with grouping from above." |
| ] |
| }, |
| { |
| "cell_type": "code", |
| "execution_count": 42, |
| "metadata": {}, |
| "outputs": [ |
| { |
| "name": "stdout", |
| "output_type": "stream", |
| "text": [ |
| "Done.\n", |
| "1 rows affected.\n", |
| "4 rows affected.\n" |
| ] |
| }, |
| { |
| "data": { |
| "text/html": [ |
| "<table>\n", |
| " <tr>\n", |
| " <th>grp</th>\n", |
| " <th>id</th>\n", |
| " <th>indegree</th>\n", |
| " <th>outdegree</th>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>0</td>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>0</td>\n", |
| " <td>2</td>\n", |
| " <td>3</td>\n", |
| " </tr>\n", |
| " <tr>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>1</td>\n", |
| " <td>2</td>\n", |
| " </tr>\n", |
| "</table>" |
| ], |
| "text/plain": [ |
| "[(0, 0, 2L, 3L), (0, 1, 1L, 2L), (1, 0, 2L, 3L), (1, 1, 1L, 2L)]" |
| ] |
| }, |
| "execution_count": 42, |
| "metadata": {}, |
| "output_type": "execute_result" |
| } |
| ], |
| "source": [ |
| "%%sql \n", |
| "DROP TABLE IF EXISTS out_gr;\n", |
| "\n", |
| "SELECT madlib.graph_vertex_degrees(\n", |
| " 'vertex', -- Vertex table\n", |
| " NULL, -- Vertex id column (NULL means use default naming)\n", |
| " 'edge_gr', -- Edge table\n", |
| " 'src=src_id, dest=dest_id, weight=edge_weight',\n", |
| " 'out_gr', -- Output table of shortest paths\n", |
| " 'grp' -- Grouping columns\n", |
| ");\n", |
| "\n", |
| "SELECT * FROM out_gr WHERE id < 2 ORDER BY grp, id;" |
| ] |
| } |
| ], |
| "metadata": { |
| "kernelspec": { |
| "display_name": "Python 2", |
| "language": "python", |
| "name": "python2" |
| }, |
| "language_info": { |
| "codemirror_mode": { |
| "name": "ipython", |
| "version": 2 |
| }, |
| "file_extension": ".py", |
| "mimetype": "text/x-python", |
| "name": "python", |
| "nbconvert_exporter": "python", |
| "pygments_lexer": "ipython2", |
| "version": "2.7.12" |
| } |
| }, |
| "nbformat": 4, |
| "nbformat_minor": 1 |
| } |