Repository: incubator-madlib-site
Updated Branches:
  refs/heads/asf-site 26ad2acff -> bed9253d9


http://git-wip-us.apache.org/repos/asf/incubator-madlib-site/blob/bed9253d/docs/v1.9.1/group__grp__path.html
----------------------------------------------------------------------
diff --git a/docs/v1.9.1/group__grp__path.html 
b/docs/v1.9.1/group__grp__path.html
new file mode 100644
index 0000000..78e9f56
--- /dev/null
+++ b/docs/v1.9.1/group__grp__path.html
@@ -0,0 +1,474 @@
+<!-- HTML header for doxygen 1.8.4-->
+<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd";>
+<html xmlns="http://www.w3.org/1999/xhtml";>
+<head>
+<meta http-equiv="Content-Type" content="text/xhtml;charset=UTF-8"/>
+<meta http-equiv="X-UA-Compatible" content="IE=9"/>
+<meta name="generator" content="Doxygen 1.8.10"/>
+<meta name="keywords" content="madlib,postgres,greenplum,machine learning,data 
mining,deep learning,ensemble methods,data science,market basket 
analysis,affinity analysis,pca,lda,regression,elastic net,huber 
white,proportional hazards,k-means,latent dirichlet allocation,bayes,support 
vector machines,svm"/>
+<title>MADlib: Path</title>
+<link href="tabs.css" rel="stylesheet" type="text/css"/>
+<script type="text/javascript" src="jquery.js"></script>
+<script type="text/javascript" src="dynsections.js"></script>
+<link href="navtree.css" rel="stylesheet" type="text/css"/>
+<script type="text/javascript" src="resize.js"></script>
+<script type="text/javascript" src="navtreedata.js"></script>
+<script type="text/javascript" src="navtree.js"></script>
+<script type="text/javascript">
+  $(document).ready(initResizable);
+  $(window).load(resizeHeight);
+</script>
+<link href="search/search.css" rel="stylesheet" type="text/css"/>
+<script type="text/javascript" src="search/searchdata.js"></script>
+<script type="text/javascript" src="search/search.js"></script>
+<script type="text/javascript">
+  $(document).ready(function() { init_search(); });
+</script>
+<!-- hack in the navigation tree -->
+<script type="text/javascript" src="eigen_navtree_hacks.js"></script>
+<link href="doxygen.css" rel="stylesheet" type="text/css" />
+<link href="madlib_extra.css" rel="stylesheet" type="text/css"/>
+<!-- google analytics -->
+<script>
+  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
+  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new 
Date();a=s.createElement(o),
+  
m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
+  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');
+  ga('create', 'UA-45382226-1', 'madlib.net');
+  ga('send', 'pageview');
+</script>
+</head>
+<body>
+<div id="top"><!-- do not remove this div, it is closed by doxygen! -->
+<div id="titlearea">
+<table cellspacing="0" cellpadding="0">
+ <tbody>
+ <tr style="height: 56px;">
+  <td id="projectlogo"><a href="http://madlib.net";><img alt="Logo" 
src="madlib.png" height="50" style="padding-left:0.5em;" border="0"/ ></a></td>
+  <td style="padding-left: 0.5em;">
+   <div id="projectname">
+   <span id="projectnumber">1.9.1</span>
+   </div>
+   <div id="projectbrief">User Documentation for MADlib</div>
+  </td>
+   <td>        <div id="MSearchBox" class="MSearchBoxInactive">
+        <span class="left">
+          <img id="MSearchSelect" src="search/mag_sel.png"
+               onmouseover="return searchBox.OnSearchSelectShow()"
+               onmouseout="return searchBox.OnSearchSelectHide()"
+               alt=""/>
+          <input type="text" id="MSearchField" value="Search" accesskey="S"
+               onfocus="searchBox.OnSearchFieldFocus(true)" 
+               onblur="searchBox.OnSearchFieldFocus(false)" 
+               onkeyup="searchBox.OnSearchFieldChange(event)"/>
+          </span><span class="right">
+            <a id="MSearchClose" 
href="javascript:searchBox.CloseResultsWindow()"><img id="MSearchCloseImg" 
border="0" src="search/close.png" alt=""/></a>
+          </span>
+        </div>
+</td>
+ </tr>
+ </tbody>
+</table>
+</div>
+<!-- end header part -->
+<!-- Generated by Doxygen 1.8.10 -->
+<script type="text/javascript">
+var searchBox = new SearchBox("searchBox", "search",false,'Search');
+</script>
+</div><!-- top -->
+<div id="side-nav" class="ui-resizable side-nav-resizable">
+  <div id="nav-tree">
+    <div id="nav-tree-contents">
+      <div id="nav-sync" class="sync"></div>
+    </div>
+  </div>
+  <div id="splitbar" style="-moz-user-select:none;" 
+       class="ui-resizable-handle">
+  </div>
+</div>
+<script type="text/javascript">
+$(document).ready(function(){initNavTree('group__grp__path.html','');});
+</script>
+<div id="doc-content">
+<!-- window showing the filter options -->
+<div id="MSearchSelectWindow"
+     onmouseover="return searchBox.OnSearchSelectShow()"
+     onmouseout="return searchBox.OnSearchSelectHide()"
+     onkeydown="return searchBox.OnSearchSelectKey(event)">
+</div>
+
+<!-- iframe showing the search results (closed by default) -->
+<div id="MSearchResultsWindow">
+<iframe src="javascript:void(0)" frameborder="0" 
+        name="MSearchResults" id="MSearchResults">
+</iframe>
+</div>
+
+<div class="header">
+  <div class="headertitle">
+<div class="title">Path<div class="ingroups"><a class="el" 
href="group__grp__utility__functions.html">Utility Functions</a></div></div>  
</div>
+</div><!--header-->
+<div class="contents">
+<div class="toc"><b>Contents</b> </p><ul>
+<li>
+<a href="#syntax">Function Syntax</a> </li>
+<li>
+<a href="#examples">Examples</a> </li>
+<li>
+<a href="#nomenclature">Nomenclature</a> </li>
+<li>
+<a href="#literature">Literature</a> </li>
+</ul>
+</div><p>The goal of the MADlib path function is to perform regular pattern 
matching over a sequence of rows, and to extract useful information about the 
pattern matches. The useful information could be a simple count of matches or 
something more involved like aggregations or window functions.</p>
+<p>Symbols are used to identify particular rows of interest. Then, standard 
PostgreSQL pattern matching using symbols can be applied to identify patterns 
across the rows of interest. (This is similar in concept to regular expressions 
which match patterns within strings of text.)</p>
+<p>For example, a symbol can be defined for purchase events by on-line 
shoppers. Then, preceding events that led to the purchase can be identified and 
operated on, perhaps to find the common actions that resulted in a purchase. Or 
conversely, to find actions that resulted in an exit without a purchase having 
been made.</p>
+<p>Steps on how to use path functions:</p>
+<ol type="1">
+<li>Partition input rows.</li>
+<li>Order the partitions.</li>
+<li>Define symbols to match rows of interest.</li>
+<li>Define regular expression of symbols and operators to define patterns to 
match in your ordered partitions.</li>
+<li>Define an aggregate function to compute for each pattern match.</li>
+<li>If desired, output the pattern matches for inspection or to operate on 
them with subsequent queries.</li>
+</ol>
+<p><a class="anchor" id="syntax"></a></p><dl class="section user"><dt>Function 
Syntax</dt><dd><pre class="syntax">
+path(
+    source_table,
+    output_table,
+    partition_expr,
+    order_expr,
+    symbol,
+    pattern,
+    aggregate_func,
+    persist_rows,
+    overlapping_patterns
+)
+</pre></dd></dl>
+<p><b>Arguments</b> </p><dl class="arglist">
+<dt>source_table </dt>
+<dd><p class="startdd">VARCHAR. Name of the source table, containing data for 
path analysis.</p>
+<p class="enddd"></p>
+</dd>
+<dt>output_table </dt>
+<dd><p class="startdd">VARCHAR. Name of the result table.</p>
+<p class="enddd"></p>
+</dd>
+<dt>partition_expr </dt>
+<dd><p class="startdd">VARCHAR. The 'partition_expr' can be a single column or 
a list of comma-separated columns/expressions to divide all rows into groups, 
or partitions. Matching is applied across the rows that fall into the same 
partition. This can be NULL or '' to indicate the matching is to be applied to 
the whole table.</p>
+<p class="enddd"></p>
+</dd>
+<dt>order_expr </dt>
+<dd><p class="startdd">VARCHAR. This expression controls the order in which 
rows are processed or matched in a partition. For example, time is a common way 
to order partitions. </p>
+<p class="enddd"></p>
+</dd>
+<dt>symbol </dt>
+<dd><p class="startdd">VARCHAR. Symbols enable you to express patterns of 
interest in a simple way (see definition of ‘pattern’ argument below). A 
symbol identifies a row of a particular type that you’re searching for as 
part of a pattern match. Symbol definition uses the standard PostgreSQL 
assignment statement 'identifier := expression;' [1]. A given row can only 
match one symbol. If a row matches multiple symbols, the symbol that comes 
first in the symbol definition list will take precedence. </p>
+<p class="enddd"></p>
+</dd>
+<dt>pattern </dt>
+<dd><p class="startdd">VARCHAR. The 'pattern' clause defines the pattern that 
the path algorithm searches for. You express the pattern using symbols and 
operators following regular PostgreSQL pattern matching syntax and rules 
[2].</p>
+<p><a class="anchor" id="note"></a></p><dl class="section 
note"><dt>Note</dt><dd>Symbols defined using more than one (1) character need 
to be enclosed in parentheses '()' when referenced in the 'pattern' argument. 
For example:<ul>
+<li>a symbol defined as 'a' in the 'symbol' argument can be used directly in 
the 'pattern' argument</li>
+<li>a symbol defined as 'abc' in the 'symbol' argument must be written as 
'(abc)' in the 'pattern' argument</li>
+</ul>
+</dd></dl>
+<p>The following pattern matching metacharacters are supported: </p><ul>
+<li>
+| denotes alternation (either of two alternatives).  </li>
+<li>
+? denotes repetition of the previous item zero or one time.  </li>
+<li>
+* denotes repetition of the previous item zero or more times.  </li>
+<li>
++ denotes repetition of the previous item one or more times.  </li>
+<li>
+{m} denotes repetition of the previous item exactly m times.  </li>
+<li>
+{m,} denotes repetition of the previous item m or more times.  </li>
+<li>
+{m,n} denotes repetition of the previous item at least m and not more than n 
times.  </li>
+<li>
+Parentheses () can be used to group items into a single logical item. </li>
+</ul>
+<p class="enddd"></p>
+</dd>
+<dt>aggregate_func (optional) </dt>
+<dd><p class="startdd">VARCHAR, default NULL. A comma-separated list of 
aggregates to be applied to the pattern matches [3]. Please note that window 
functions cannot currently be used in the parameter 'aggregate_func'. If you 
want to use a window function [4], output the pattern matches and write a SQL 
query with a window function over the output tuples (see 'persist_rows' 
parameter below).</p>
+<p>If you just want to output the pattern matched rows and not compute any 
aggregates, you can put NULL or '' in the 'aggregate_func' parameter. </p>
+<p class="enddd"></p>
+</dd>
+<dt>persist_rows (optional) </dt>
+<dd><p class="startdd">BOOLEAN, default FALSE. If TRUE the matched rows are 
persisted in a separate output table. This table is named as 
&lt;output_table&gt;_tuples (the string "_tuples" is added as suffix to the 
value of <em>output_table</em>). </p>
+<p class="enddd"></p>
+</dd>
+<dt>overlapping_patterns (optional) </dt>
+<dd><p class="startdd">BOOLEAN, default FALSE. If TRUE find every occurrence 
of the pattern in the partition, regardless of whether it might have been part 
of a previously found match. </p>
+<p class="enddd"></p>
+</dd>
+</dl>
+<p><a class="anchor" id="examples"></a></p><dl class="section 
user"><dt>Examples</dt><dd></dd></dl>
+<p>The data set describes shopper behavior on a notional web site that sells 
beer and wine. A beacon fires an event to a log file when the shopper visits 
different pages on the site: landing page, beer selection page, wine selection 
page, and checkout. Other pages on the site like help pages show up in the logs 
as well. Let’s assume that the log has been sessionized.</p>
+<p>Create the date table:</p>
+<pre class="example">
+DROP TABLE IF EXISTS eventlog;
+CREATE TABLE eventlog (event_timestamp TIMESTAMP,
+            user_id INT,
+            session_id INT,
+            page TEXT,
+            revenue FLOAT);
+INSERT INTO eventlog VALUES
+('04/15/2015 01:03:00', 100821, 100, 'LANDING', 0),
+('04/15/2015 01:04:00', 100821, 100, 'WINE', 0),
+('04/15/2015 01:05:00', 100821, 100, 'CHECKOUT', 39),
+('04/15/2015 02:06:00', 100821, 101, 'WINE', 0),
+('04/15/2015 02:09:00', 100821, 101, 'WINE', 0),
+('04/15/2015 01:15:00', 101121, 102, 'LANDING', 0),
+('04/15/2015 01:16:00', 101121, 102, 'WINE', 0),
+('04/15/2015 01:17:00', 101121, 102, 'CHECKOUT', 15),
+('04/15/2015 01:18:00', 101121, 102, 'LANDING', 0),
+('04/15/2015 01:19:00', 101121, 102, 'HELP', 0),
+('04/15/2015 01:21:00', 101121, 102, 'WINE', 0),
+('04/15/2015 01:22:00', 101121, 102, 'CHECKOUT', 23),
+('04/15/2015 02:15:00', 101331, 103, 'LANDING', 0),
+('04/15/2015 02:16:00', 101331, 103, 'WINE', 0),
+('04/15/2015 02:17:00', 101331, 103, 'HELP', 0),
+('04/15/2015 02:18:00', 101331, 103, 'WINE', 0),
+('04/15/2015 02:19:00', 101331, 103, 'CHECKOUT', 16),
+('04/15/2015 02:22:00', 101443, 104, 'BEER', 0),
+('04/15/2015 02:25:00', 101443, 104, 'CHECKOUT', 12),
+('04/15/2015 02:29:00', 101881, 105, 'LANDING', 0),
+('04/15/2015 02:30:00', 101881, 105, 'BEER', 0),
+('04/15/2015 01:05:00', 102201, 106, 'LANDING', 0),
+('04/15/2015 01:06:00', 102201, 106, 'HELP', 0),
+('04/15/2015 01:09:00', 102201, 106, 'LANDING', 0),
+('04/15/2015 02:15:00', 102201, 107, 'WINE', 0),
+('04/15/2015 02:16:00', 102201, 107, 'BEER', 0),
+('04/15/2015 02:17:00', 102201, 107, 'WINE', 0),
+('04/15/2015 02:18:00', 102871, 108, 'BEER', 0),
+('04/15/2015 02:19:00', 102871, 108, 'WINE', 0),
+('04/15/2015 02:22:00', 102871, 108, 'CHECKOUT', 21),
+('04/15/2015 02:25:00', 102871, 108, 'LANDING', 0),
+('04/15/2015 02:17:00', 103711, 109, 'BEER', 0),
+('04/15/2015 02:18:00', 103711, 109, 'LANDING', 0),
+('04/15/2015 02:19:00', 103711, 109, 'WINE', 0);
+</pre><ol type="1">
+<li>Calculate the revenue by checkout: <pre class="example">
+DROP TABLE IF EXISTS path_output, path_output_tuples;
+SELECT madlib.path(
+     'eventlog',                -- Name of input table
+     'path_output',             -- Table name to store path results
+     'session_id',              -- Partition input table by session
+     'event_timestamp ASC',     -- Order partitions in input table by time
+     'buy:=page=''CHECKOUT''',  -- Define a symbol for checkout events
+     '(buy)',                   -- Pattern search: purchase
+     'sum(revenue) as checkout_rev',    -- Aggregate:  sum revenue by checkout
+     TRUE                       -- Persist matches
+     );
+SELECT * FROM path_output ORDER BY session_id, match_id;
+</pre> Result: <pre class="result">
+ session_id | match_id | checkout_rev
+------------+----------+--------------
+        100 |        1 |           39
+        102 |        1 |           15
+        102 |        2 |           23
+        103 |        1 |           16
+        104 |        1 |           12
+        108 |        1 |           21
+(6 rows)
+</pre> Note that there are 2 checkouts within session 102, which is apparent 
from the 'match_id' column. This serves to illustrate that the 'aggregate_func' 
operates on a <em>per pattern match</em> basis, not on a <em>per partition</em> 
basis. If in fact we wanted revenue by partition ('session_id' in this 
example), then we could do: <pre class="example">
+SELECT session_id, sum(checkout_rev) FROM path_output GROUP BY session_id 
ORDER BY session_id;
+</pre> Result: <pre class="result">
+ session_id | sum
+------------+-----
+        100 |  39
+        102 |  38
+        103 |  16
+        104 |  12
+        108 |  21
+(5 rows)
+</pre> Since we set TRUE for 'persist_rows', we can view the associated 
pattern matches: <pre class="example">
+SELECT * FROM path_output_tuples ORDER BY session_id ASC, event_timestamp ASC;
+</pre> Result: <pre class="result">
+   event_timestamp   | user_id | session_id |   page   | revenue | symbol | 
match_id
+---------------------+---------+------------+----------+---------+--------+----------
+ 2015-04-15 01:05:00 |  100821 |        100 | CHECKOUT |      39 | buy    |    
    1
+ 2015-04-15 01:17:00 |  101121 |        102 | CHECKOUT |      15 | buy    |    
    1
+ 2015-04-15 01:22:00 |  101121 |        102 | CHECKOUT |      23 | buy    |    
    2
+ 2015-04-15 02:19:00 |  101331 |        103 | CHECKOUT |      16 | buy    |    
    1
+ 2015-04-15 02:25:00 |  101443 |        104 | CHECKOUT |      12 | buy    |    
    1
+ 2015-04-15 02:22:00 |  102871 |        108 | CHECKOUT |      21 | buy    |    
    1
+(6 rows)
+</pre> Notice that the 'symbol' and 'match_id' columns are added to the right 
of the matched rows.</li>
+<li>We are interested in sessions with an order placed within 4 pages of 
entering the shopping site via the landing page. We represent this by the 
regular expression: '(land)[^(land)(buy)]{0,2}(buy)'. In other words, visit to 
the landing page followed by from 0 to 2 non-entry, non-sale pages, followed by 
a purchase. The SQL is as follows: <pre class="example">
+DROP TABLE IF EXISTS path_output, path_output_tuples;
+SELECT madlib.path(
+     'eventlog',                -- Name of input table
+     'path_output',             -- Table name to store path results
+     'session_id',              -- Partition input table by session
+     'event_timestamp ASC',     -- Order partitions in input table by time
+     'land:=page=''LANDING'',
+        wine:=page=''WINE'',
+        beer:=page=''BEER'',
+        buy:=page=''CHECKOUT'',
+        other:=page&lt;&gt;''LANDING'' AND page&lt;&gt;''WINE'' AND 
page&lt;&gt;''BEER'' AND  page&lt;&gt;''CHECKOUT''',    -- Symbols for  page 
types
+      '(land)[^(land)(buy)]{0,2}(buy)', -- Purchase within 4 pages entering 
site
+     'sum(revenue) as checkout_rev',    -- Aggregate:  sum revenue by checkout
+     TRUE                       -- Persist matches
+     );
+SELECT * FROM path_output ORDER BY session_id, match_id;
+</pre> Result: <pre class="result">
+ session_id | match_id | session_rev
+------------+----------+-------------
+        100 |        1 |          39
+        102 |        1 |          15
+        102 |        2 |          23
+(3 rows)
+</pre> Now view the associated pattern matches: <pre class="example">
+SELECT * FROM path_output_tuples ORDER BY session_id ASC, event_timestamp ASC;
+</pre> Result: <pre class="result">
+   event_timestamp   | user_id | session_id |   page   | revenue | symbol | 
match_id
+---------------------+---------+------------+----------+---------+--------+----------
+ 2015-04-15 01:03:00 |  100821 |        100 | LANDING  |       0 | land   |    
    1
+ 2015-04-15 01:04:00 |  100821 |        100 | WINE     |       0 | wine   |    
    1
+ 2015-04-15 01:05:00 |  100821 |        100 | CHECKOUT |      39 | buy    |    
    1
+ 2015-04-15 01:15:00 |  101121 |        102 | LANDING  |       0 | land   |    
    1
+ 2015-04-15 01:16:00 |  101121 |        102 | WINE     |       0 | wine   |    
    1
+ 2015-04-15 01:17:00 |  101121 |        102 | CHECKOUT |      15 | buy    |    
    1
+ 2015-04-15 01:18:00 |  101121 |        102 | LANDING  |       0 | land   |    
    2
+ 2015-04-15 01:19:00 |  101121 |        102 | HELP     |       0 | other  |    
    2
+ 2015-04-15 01:21:00 |  101121 |        102 | WINE     |       0 | wine   |    
    2
+ 2015-04-15 01:22:00 |  101121 |        102 | CHECKOUT |      23 | buy    |    
    2
+(10 rows)
+</pre></li>
+<li>We may want to use a window function instead of an aggregate. Currently, 
only aggregates are supported in the core path function in the parameter 
'aggregate_func'. However, you can write window functions on the output tuples 
to achieve the desired result. &#160; Continuing the previous example, let’s 
say we want to compute average revenue for checkouts within 4 pages of entering 
the shopping site via the landing page: <pre class="example">
+SELECT DATE(event_timestamp), user_id, session_id, revenue,
+    avg(revenue) OVER (PARTITION BY DATE(event_timestamp)) as avg_checkout_rev
+    FROM path_output_tuples
+    WHERE page='CHECKOUT'
+    ORDER BY user_id, session_id;
+</pre> Result: <pre class="result">
+    date    | user_id | session_id | revenue | avg_checkout_rev
+------------+---------+------------+---------+------------------
+ 2015-04-15 |  100821 |        100 |      39 | 25.6666666666667
+ 2015-04-15 |  101121 |        102 |      15 | 25.6666666666667
+ 2015-04-15 |  101121 |        102 |      23 | 25.6666666666667
+(3 rows)
+</pre> Here we are partitioning the window function by day because we want 
daily averages, although our sample data set only has a single day.</li>
+<li>Now we want to do a golden path analysis to find the most successful 
shopper paths through the site. Since our data set is small, we decide this 
means the most frequently viewed page just before a checkout is made: <pre 
class="example">
+DROP TABLE IF EXISTS path_output, path_output_tuples;
+SELECT madlib.path(
+     'eventlog',                -- Name of input table
+     'path_output',             -- Table name to store path results
+     'session_id',              -- Partition input table by session
+     'event_timestamp ASC',     -- Order partitions in input table by time
+     'land:=page=''LANDING'',
+        wine:=page=''WINE'',
+        beer:=page=''BEER'',
+        buy:=page=''CHECKOUT'',
+        other:=page&lt;&gt;''LANDING'' AND page&lt;&gt;''WINE'' AND 
page&lt;&gt;''BEER'' AND  page&lt;&gt;''CHECKOUT''',    -- Symbols for  page 
types
+      '[^(buy)](buy)',          -- Pattern to match
+     'array_agg(page ORDER BY session_id ASC, event_timestamp ASC) as 
page_path',    -- Build array with shopper paths
+     FALSE                       -- Don't persist matches
+     );
+</pre> Now count the common paths and print the most frequent: <pre 
class="example">
+SELECT count(*), page_path from
+    (SELECT * FROM path_output) q
+GROUP BY page_path
+ORDER BY count(*) DESC
+LIMIT 10;
+</pre> Result: <pre class="result">
+ count |    page_path
+-------+-----------------
+     5 | {WINE,CHECKOUT}
+     1 | {BEER,CHECKOUT}
+(2 rows)
+</pre> There are only 2 different paths. The wine page is viewed more 
frequently than the beer page just before checkout.</li>
+<li>To demonstrate the use of 'overlapping_patterns', consider a pattern with 
at least one page followed by and ending with a checkout: <pre class="example">
+DROP TABLE IF EXISTS path_output, path_output_tuples;
+SELECT madlib.path(                                                            
       
+     'eventlog',                    -- Name of the table                       
                    
+     'path_output',                 -- Table name to store the path results    
                     
+     'session_id',                  -- Partition by session                 
+     'event_timestamp ASC',         -- Order partitions in input table by time 
      
+     $$ nobuy:=page&lt;&gt;'CHECKOUT',
+        buy:=page='CHECKOUT'
+     $$,  -- Definition of symbols used in the pattern definition 
+     '(nobuy)+(buy)',         -- At least one page followed by and ending with 
a CHECKOUT.
+     'array_agg(page ORDER BY session_id ASC, event_timestamp ASC) as 
page_path',  
+     FALSE,                        -- Don't persist matches
+     TRUE                          -- Turn on overlapping patterns
+     );
+SELECT * FROM path_output ORDER BY session_id, match_id;
+</pre> Result with overlap turned on: <pre class="result">
+ session_id | match_id |             page_path             
+------------+----------+-----------------------------------
+        100 |        1 | {LANDING,WINE,CHECKOUT}
+        100 |        2 | {WINE,CHECKOUT}
+        102 |        1 | {LANDING,WINE,CHECKOUT}
+        102 |        2 | {WINE,CHECKOUT}
+        102 |        3 | {LANDING,HELP,WINE,CHECKOUT}
+        102 |        4 | {HELP,WINE,CHECKOUT}
+        102 |        5 | {WINE,CHECKOUT}
+        103 |        1 | {LANDING,WINE,HELP,WINE,CHECKOUT}
+        103 |        2 | {WINE,HELP,WINE,CHECKOUT}
+        103 |        3 | {HELP,WINE,CHECKOUT}
+        103 |        4 | {WINE,CHECKOUT}
+        104 |        1 | {BEER,CHECKOUT}
+        108 |        1 | {BEER,WINE,CHECKOUT}
+        108 |        2 | {WINE,CHECKOUT}
+(14 rows)
+</pre> With overlap turned off, the result would be: <pre class="result">
+ session_id | match_id |             page_path             
+------------+----------+-----------------------------------
+        100 |        1 | {LANDING,WINE,CHECKOUT}
+        102 |        1 | {LANDING,WINE,CHECKOUT}
+        102 |        2 | {LANDING,HELP,WINE,CHECKOUT}
+        103 |        1 | {LANDING,WINE,HELP,WINE,CHECKOUT}
+        104 |        1 | {BEER,CHECKOUT}
+        108 |        1 | {BEER,WINE,CHECKOUT}
+(6 rows)
+</pre></li>
+</ol>
+<p><a class="anchor" id="note"></a></p><dl class="section 
note"><dt>Note</dt><dd>Please note some current limitations of the path 
algorithm.<ul>
+<li>Window functions cannot currently be used in the parameter 
'aggregate_func'. Instead, output the pattern matches and write a SQL query 
with a window function over the output tuples.</li>
+<li>A given row can only match one symbol. If a row matches multiple symbols, 
the symbol that comes <em>first</em> in the symbol definition list will take 
precedence.</li>
+<li>Maximum number of symbols that can be defined is 35.</li>
+<li>The columns 'match_id' and 'symbol' are generated by the path algorithm. 
If coincidently you have columns in your input data named 'match_id' or 
'symbol', the system generated column names will be changed to 
"__madlib_path_match_id__" and "__madlib_path_symbol__"</li>
+</ul>
+</dd></dl>
+<p><a class="anchor" id="nomenclature"></a></p><dl class="section 
user"><dt>Nomenclature</dt><dd></dd></dl>
+<p>Partition</p><ul>
+<li>scope of rows to be searched for pattern match</li>
+<li>typical examples: user id, session id, portfolio id</li>
+</ul>
+<p>Order</p><ul>
+<li>sort order of input rows in partition</li>
+<li>typical example: time</li>
+</ul>
+<p>Symbol</p><ul>
+<li>a row of a particular type that you’re searching for, that you want to 
include in a pattern</li>
+</ul>
+<p>Pattern</p><ul>
+<li>regular PostgreSQL pattern match expression of symbols and operators that 
you want to match across rows</li>
+</ul>
+<p>Pattern match</p><ul>
+<li>rows that result from a pattern match expression of symbols</li>
+<li>can be multiple matches per partition</li>
+</ul>
+<p><a class="anchor" id="literature"></a></p><dl class="section 
user"><dt>Literature</dt><dd></dd></dl>
+<p>[1] PostgreSQL basic statements/assignment operator, <a 
href="http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html";>http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html</a></p>
+<p>[2] PostgreSQL pattern matching, <a 
href="http://www.postgresql.org/docs/current/static/functions-matching.html";>http://www.postgresql.org/docs/current/static/functions-matching.html</a></p>
+<p>[3] PostgreSQL aggregate functions, <a 
href="http://www.postgresql.org/docs/8.2/static/tutorial-agg.html";>http://www.postgresql.org/docs/8.2/static/tutorial-agg.html</a></p>
+<p>[4] PostgreSQL window functions, <a 
href="http://www.postgresql.org/docs/8.4/static/tutorial-window.html";>http://www.postgresql.org/docs/8.4/static/tutorial-window.html</a>
 </p>
+</div><!-- contents -->
+</div><!-- doc-content -->
+<!-- start footer part -->
+<div id="nav-path" class="navpath"><!-- id is needed for treeview function! -->
+  <ul>
+    <li class="footer">Generated on Tue Sep 20 2016 11:27:01 for MADlib by
+    <a href="http://www.doxygen.org/index.html";>
+    <img class="footer" src="doxygen.png" alt="doxygen"/></a> 1.8.10 </li>
+  </ul>
+</div>
+</body>
+</html>

Reply via email to