| /* ----------------------------------------------------------------------- */ |
| /** |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, |
| * software distributed under the License is distributed on an |
| * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| * KIND, either express or implied. See the License for the |
| * specific language governing permissions and limitations |
| * under the License. |
| * |
| * @file sessionize.sql_in |
| * |
| * @brief SQL functions for sessionization functions |
| * @date May 2016 |
| * |
| */ |
| /* ----------------------------------------------------------------------- */ |
| |
| m4_include(`SQLCommon.m4') |
| |
| /** |
| @addtogroup grp_sessionize |
| |
| <div class="toc"><b>Contents</b> |
| <ul> |
| <li><a href="#syntax">Function Syntax</a></li> |
| <li><a href="#examples">Examples</a></li> |
| <li><a href="#literature">Literature</a></li> |
| </ul> |
| </div> |
| |
| @brief Session reconstruction of data |
| consisting of a time stampled sequence of events. |
| @details The MADlib sessionize function performs time-oriented session reconstruction |
| on a data set comprising a sequence of events. A defined period of inactivity |
| indicates the end of one session and beginning of the next session. Sessions can be |
| useful in many domains including web analytics [1], network security, manufacturing, finance, |
| and operational analytics. |
| |
| @anchor syntax |
| @par Function Syntax |
| <pre class="syntax"> |
| sessionize( |
| source_table, |
| output_table, |
| partition_expr, |
| time_stamp, |
| max_time, |
| output_cols, |
| create_view |
| ) |
| </pre> |
| |
| \b Arguments |
| <dl class="arglist"> |
| <dt>source_table</dt> |
| <dd>VARCHAR. Name of the source table that contains the data to be sessionized.</dd> |
| |
| <dt>output_table</dt> |
| <dd>VARCHAR. Name of the output view or table. (The parameter create_view described below |
| defines whether the output is actually a view or a table.) In addition to the columns in the |
| source_table, the output also contains a new column called session_id: |
| <ul> |
| <li>session_id=1,2,...n where n is the number of the session in the partition.</li> |
| </ul> |
| </dd> |
| |
| <dt>partition_expr</dt> |
| <dd>VARCHAR. The 'partition_expr' is a single column or a list of |
| comma-separated columns/expressions to divide all rows into groups, |
| or partitions. Sessionization is applied across the rows that fall into |
| the same partition. This parameter can be set to NULL or '' to indicate |
| the sessionization operation is to be applied to the whole input table.</dd> |
| |
| <dt>time_stamp</dt> |
| <dd>VARCHAR. The time stamp column name that is used for sessionization calculation. |
| Note that the time_stamp column will be sorted in ascending order before |
| the session reconstruction is done within a partition.</dd> |
| |
| <dt>max_time</dt> |
| <dd>INTERVAL. Maximum delta time (i.e., time out) between subsequent events that define a session. |
| If the elapsed time between subsequent events is longer than max_time, a new session is created.</dd> |
| |
| @anchor note |
| @note |
| Note that max_time is of time type INTERVAL which is a PostgreSQL way of describing elapsed |
| time. For more information on INTERVAL please refer to reference [2]. |
| |
| <dt>output_cols (optional)</dt> |
| <dd>VARCHAR. An optional comma separated list of columns to be written to the output_table. |
| Must be a valid SELECT expression. This is set to '*' by default, which means all columns in the |
| input table will be written to the output_table plus the session_id column. |
| Note that this parameter could include a list containing the partition_expr |
| or any other expressions of interest. E.g., '*, expr1, expr2, etc.' where this means |
| output all columns from the input table plus the expressions listed plus the session_id column.</dd> |
| |
| <dt>create_view (optional)</dt> |
| <dd>BOOLEAN default: TRUE. Determines whether to create a view or materialize the output as a table. |
| If you only need session info once, creating a view could be significantly |
| faster than materializing as a table. Please note that if you set create_view to NULL |
| (allowed by PostgreSQL) it will get set to the default value of TRUE.</dd> |
| </dl> |
| |
| @anchor examples |
| @examp |
| |
| 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. Each user is identified by a a user id, |
| and every time a page is visited, the page and time stamp are logged. |
| |
| Create the data table: |
| |
| <pre class="example"> |
| DROP TABLE IF EXISTS eventlog CASCADE; -- Using CASCADE in case you are running through this example more than once (views used below) |
| CREATE TABLE eventlog (event_timestamp TIMESTAMP, |
| user_id INT, |
| page TEXT, |
| revenue FLOAT); |
| INSERT INTO eventlog VALUES |
| ('04/15/2015 02:19:00', 101331, 'CHECKOUT', 16), |
| ('04/15/2015 02:17:00', 202201, 'WINE', 0), |
| ('04/15/2015 03:18:00', 202201, 'BEER', 0), |
| ('04/15/2015 01:03:00', 100821, 'LANDING', 0), |
| ('04/15/2015 01:04:00', 100821, 'WINE', 0), |
| ('04/15/2015 01:05:00', 100821, 'CHECKOUT', 39), |
| ('04/15/2015 02:06:00', 100821, 'WINE', 0), |
| ('04/15/2015 02:09:00', 100821, 'WINE', 0), |
| ('04/15/2015 02:15:00', 101331, 'LANDING', 0), |
| ('04/15/2015 02:16:00', 101331, 'WINE', 0), |
| ('04/15/2015 02:17:00', 101331, 'HELP', 0), |
| ('04/15/2015 02:18:00', 101331, 'WINE', 0), |
| ('04/15/2015 02:29:00', 201881, 'LANDING', 0), |
| ('04/15/2015 02:30:00', 201881, 'BEER', 0), |
| ('04/15/2015 01:05:00', 202201, 'LANDING', 0), |
| ('04/15/2015 01:06:00', 202201, 'HELP', 0), |
| ('04/15/2015 01:09:00', 202201, 'LANDING', 0), |
| ('04/15/2015 02:15:00', 202201, 'WINE', 0), |
| ('04/15/2015 02:16:00', 202201, 'BEER', 0), |
| ('04/15/2015 03:19:00', 202201, 'WINE', 0), |
| ('04/15/2015 03:22:00', 202201, 'CHECKOUT', 21); |
| </pre> |
| |
| Sessionize the table by each user_id: |
| <pre class="example"> |
| DROP VIEW IF EXISTS sessionize_output_view; |
| SELECT madlib.sessionize( |
| 'eventlog', -- Name of input table |
| 'sessionize_output_view', -- View to store sessionize results |
| 'user_id', -- Partition input table by user id |
| 'event_timestamp', -- Time column used to compute sessions |
| '0:30:0' -- Use 30 minute time out to define sessions |
| ); |
| SELECT * FROM sessionize_output_view ORDER BY user_id, event_timestamp; |
| </pre> |
| |
| Result: |
| <pre class="result"> |
| event_timestamp | user_id | page | revenue | session_id |
| ---------------------+---------+----------+---------+------------ |
| 2015-04-15 01:03:00 | 100821 | LANDING | 0 | 1 |
| 2015-04-15 01:04:00 | 100821 | WINE | 0 | 1 |
| 2015-04-15 01:05:00 | 100821 | CHECKOUT | 39 | 1 |
| 2015-04-15 02:06:00 | 100821 | WINE | 0 | 2 |
| 2015-04-15 02:09:00 | 100821 | WINE | 0 | 2 |
| 2015-04-15 02:15:00 | 101331 | LANDING | 0 | 1 |
| 2015-04-15 02:16:00 | 101331 | WINE | 0 | 1 |
| 2015-04-15 02:17:00 | 101331 | HELP | 0 | 1 |
| 2015-04-15 02:18:00 | 101331 | WINE | 0 | 1 |
| 2015-04-15 02:19:00 | 101331 | CHECKOUT | 16 | 1 |
| 2015-04-15 02:29:00 | 201881 | LANDING | 0 | 1 |
| 2015-04-15 02:30:00 | 201881 | BEER | 0 | 1 |
| 2015-04-15 01:05:00 | 202201 | LANDING | 0 | 1 |
| 2015-04-15 01:06:00 | 202201 | HELP | 0 | 1 |
| 2015-04-15 01:09:00 | 202201 | LANDING | 0 | 1 |
| 2015-04-15 02:15:00 | 202201 | WINE | 0 | 2 |
| 2015-04-15 02:16:00 | 202201 | BEER | 0 | 2 |
| 2015-04-15 02:17:00 | 202201 | WINE | 0 | 2 |
| 2015-04-15 03:18:00 | 202201 | BEER | 0 | 3 |
| 2015-04-15 03:19:00 | 202201 | WINE | 0 | 3 |
| 2015-04-15 03:22:00 | 202201 | CHECKOUT | 21 | 3 |
| (21 rows) |
| </pre> |
| |
| Now let's say we want to see 3 minute sessions by a group of users |
| with a certain range of user IDs. To do this, we need to sessionize |
| the table based on a partition expression. Also, we want to persist |
| a table output with a reduced set of columns in the table. |
| <pre class="example"> |
| DROP TABLE IF EXISTS sessionize_output_table; |
| SELECT madlib.sessionize( |
| 'eventlog', -- Name of input table |
| 'sessionize_output_table', -- Table to store sessionize results |
| 'user_id < 200000', -- Partition input table by subset of users |
| 'event_timestamp', -- Order partitions in input table by time |
| '180', -- Use 180 second time out to define sessions (same as '0:03:0') |
| 'event_timestamp, user_id, user_id < 200000 AS "Department-A1"', -- Select only user_id and event_timestamp columns, along with the session id as output |
| 'f' -- create a table |
| ); |
| SELECT * FROM sessionize_output_table WHERE "Department-A1"='TRUE' ORDER BY event_timestamp; |
| </pre> |
| |
| Result showing 2 users and 3 total sessions across the group: |
| <pre class="result"> |
| event_timestamp | user_id | Department-A1 | session_id |
| ---------------------+---------+---------------+------------ |
| 2015-04-15 01:03:00 | 100821 | t | 1 |
| 2015-04-15 01:04:00 | 100821 | t | 1 |
| 2015-04-15 01:05:00 | 100821 | t | 1 |
| 2015-04-15 02:06:00 | 100821 | t | 2 |
| 2015-04-15 02:09:00 | 100821 | t | 2 |
| 2015-04-15 02:15:00 | 101331 | t | 3 |
| 2015-04-15 02:16:00 | 101331 | t | 3 |
| 2015-04-15 02:17:00 | 101331 | t | 3 |
| 2015-04-15 02:18:00 | 101331 | t | 3 |
| 2015-04-15 02:19:00 | 101331 | t | 3 |
| (10 rows) |
| </pre> |
| |
| @anchor literature |
| @par Literature |
| |
| NOTE: The following PostgreSQL link refers to documentation resources |
| for the current PostgreSQL database version. Depending upon your |
| database platform version, you may need to change "current" reference |
| in the link to your database version. |
| |
| If your database platform uses the Greenplum Database (or related |
| variants), please check with the project community and/or your |
| database vendor to identify the PostgreSQL version it is based on. |
| |
| [1] Sesssions in web analytics |
| https://en.wikipedia.org/wiki/Session_(web_analytics) |
| |
| [2] PostgreSQL date/time types |
| https://www.postgresql.org/docs/current/static/datatype-datetime.html |
| |
| */ |
| ------------------------------------------------------------------------- |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sessionize( |
| source_table VARCHAR, |
| output_table VARCHAR, |
| partition_expr VARCHAR, |
| time_stamp VARCHAR, |
| max_time INTERVAL, |
| output_cols VARCHAR, |
| create_view BOOLEAN |
| ) RETURNS void AS $$ |
| PythonFunction(utilities, sessionize, sessionize) |
| $$ LANGUAGE plpythonu |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sessionize( |
| source_table VARCHAR, |
| output_table VARCHAR, |
| partition_expr VARCHAR, |
| time_stamp VARCHAR, |
| max_time INTERVAL, |
| output_cols VARCHAR |
| ) RETURNS void AS $$ |
| SELECT MADLIB_SCHEMA.sessionize($1, $2, $3, $4, $5, $6, True); |
| $$ LANGUAGE SQL |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sessionize( |
| source_table VARCHAR, |
| output_table VARCHAR, |
| partition_expr VARCHAR, |
| time_stamp VARCHAR, |
| max_time INTERVAL |
| ) RETURNS void AS $$ |
| SELECT MADLIB_SCHEMA.sessionize($1, $2, $3, $4, $5, '*', True); |
| $$ LANGUAGE SQL |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| ------------------------------------------------------------------------- |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sessionize(message TEXT) |
| RETURNS text AS $$ |
| PythonFunction(utilities, sessionize, sessionize_help_message) |
| $$ language plpythonu |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.sessionize() |
| RETURNS text AS $$ |
| SELECT MADLIB_SCHEMA.sessionize(''); |
| $$ language SQL |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
| |
