src/ports/postgres/modules/utilities/test/sessionize.sql_in

/* ----------------------------------------------------------------------- */
/**
 * 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 sessionizatino functions
 * @date May 2016
 *
 */
/* ----------------------------------------------------------------------- */

CREATE TABLE eventlog_installchk (
            event_timestamp TIMESTAMP,
            "user id" INT,
            original_session_id INT,
            page TEXT,
            revenue FLOAT,
            row INT,
            part_expr BOOLEAN);
INSERT INTO eventlog_installchk VALUES
(to_timestamp('04/15/2015 01:03:00', 'MM/DD/YYYY HH:MI:SS'), 100821, 1, 'LANDING', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:05:00', 'MM/DD/YYYY HH:MI:SS'), 100821, 1, 'WINE', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:07:00', 'MM/DD/YYYY HH:MI:SS'), 100821, 1, 'CHECKOUT', 39, 1, 'f'),
(to_timestamp('04/15/2015 02:06:00', 'MM/DD/YYYY HH:MI:SS'), 100821, 2, 'WINE', 0, 1, 'f'),
(to_timestamp('04/15/2015 02:07:00', 'MM/DD/YYYY HH:MI:SS'), 100821, 2, 'WINE', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:15:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 1, 'LANDING', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:16:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 1, 'WINE', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:18:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 1, 'CHECKOUT', 15, 1, 'f'),
(to_timestamp('04/15/2015 01:19:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 1, 'LANDING', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:21:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 1, 'HELP', 0, 1, 'f'),
(to_timestamp(NULL, 'MM/DD/YYYY HH:MI:SS'), 101121, NULL, 'LANDING', 0, 1, 'f'),
(to_timestamp(NULL, 'MM/DD/YYYY HH:MI:SS'), 101121, NULL, 'HELP', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:24:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 1, 'WINE', 0, 1, 'f'),
(to_timestamp('04/15/2015 01:26:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 1, 'CHECKOUT', 23, 1, 'f'),
(to_timestamp('04/15/2015 02:21:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 2, 'HELP', 0, 1, 'f'),
(to_timestamp('04/15/2015 02:24:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 2, 'WINE', 0, 1, 'f'),
(to_timestamp('04/15/2015 02:26:00', 'MM/DD/YYYY HH:MI:SS'), 101121, 2, 'CHECKOUT', 23, 1, 't'),
(to_timestamp('04/15/2015 02:15:00', 'MM/DD/YYYY HH:MI:SS'), 101331, 1, 'LANDING', 0, 1, 't'),
(to_timestamp('04/15/2015 02:16:06', 'MM/DD/YYYY HH:MI:SS'), 101331, 1, 'WINE', 0, 1, 't'),
(to_timestamp('04/15/2015 02:18:00', 'MM/DD/YYYY HH:MI:SS'), 101331, 1, 'HELP', 0, 1, 't'),
(to_timestamp('04/15/2015 02:20:00', 'MM/DD/YYYY HH:MI:SS'), 101331, 1, 'WINE', 0, 1, 't'),
(to_timestamp('04/15/2015 02:21:00', 'MM/DD/YYYY HH:MI:SS'), 101331, 1, 'CHECKOUT', 16, 1, 't'),
(to_timestamp('04/15/2015 02:22:00', 'MM/DD/YYYY HH:MI:SS'), 101443, 1, 'BEER', 0, 1, 't'),
(to_timestamp('04/15/2015 02:27:00', 'MM/DD/YYYY HH:MI:SS'), 101443, 2, 'CHECKOUT', 12, 1, 't'),
(to_timestamp('04/15/2015 02:29:00', 'MM/DD/YYYY HH:MI:SS'), 101881, 1, 'LANDING', 0, 1, 't'),
(to_timestamp('04/15/2015 02:30:00', 'MM/DD/YYYY HH:MI:SS'), 101881, 1, 'BEER', 0, 1, 't'),
(to_timestamp('04/15/2015 01:05:00', 'MM/DD/YYYY HH:MI:SS'), 102201, 1, 'LANDING', 0, 1, 't'),
(to_timestamp('04/15/2015 01:06:00', 'MM/DD/YYYY HH:MI:SS'), 102201, 1, 'HELP', 0, 1, 't'),
(to_timestamp('04/15/2015 01:10:00', 'MM/DD/YYYY HH:MI:SS'), 102201, 2, 'LANDING', 0, 1, 't'),
(to_timestamp('04/15/2015 02:15:00', 'MM/DD/YYYY HH:MI:SS'), 102201, 3, 'WINE', 0, 1, 't'),
(to_timestamp('04/15/2015 02:16:00', 'MM/DD/YYYY HH:MI:SS'), 102201, 3, 'BEER', 0, 1, 't'),
(to_timestamp('04/15/2015 02:17:00', 'MM/DD/YYYY HH:MI:SS'), 102201, 3, 'WINE', 0, 1, 't'),
(to_timestamp('04/15/2015 02:18:00', 'MM/DD/YYYY HH:MI:SS'), 102871, 1, 'BEER', 0, 1, 't'),
(to_timestamp('04/15/2015 02:19:00', 'MM/DD/YYYY HH:MI:SS'), 102871, 1, 'WINE', 0, 1, 't'),
(to_timestamp('04/15/2015 02:22:00', 'MM/DD/YYYY HH:MI:SS'), 102871, 1, 'CHECKOUT', 21, 1, 't'),
(to_timestamp('04/15/2015 02:25:00', 'MM/DD/YYYY HH:MI:SS'), 102871, 1, 'LANDING', 0, 1, 't'),
(to_timestamp(NULL, 'MM/DD/YYYY HH:MI:SS'), 103711, NULL, 'BEER', 0, 1, 't'),
(to_timestamp(NULL, 'MM/DD/YYYY HH:MI:SS'), 103711, NULL, 'LANDING', 0, 1, 't'),
(to_timestamp(NULL, 'MM/DD/YYYY HH:MI:SS'), 103711, NULL, 'WINE', 0, 1, 't'),
(to_timestamp('04/15/2016 02:17:00', 'MM/DD/YYYY HH:MI:SS'), 103711, 1, 'BEER', 0, 1, 't'),
(to_timestamp('04/15/2016 02:21:00', 'MM/DD/YYYY HH:MI:SS'), 103711, 2, 'LANDING', 0, 1, 't'),
(to_timestamp('04/15/2016 02:31:0.05', 'MM/DD/YYYY HH:MI:SS'), 103711, 3, 'WINE', 0, 1, 't');


SELECT sessionize(
        'eventlog_installchk', -- Name of the input table
        'sessionize_output_v', -- Name of the output table
        '"user id"', -- Partition expression to group the data
        'event_timestamp', -- Order expression to sort the tuples of the data table
        '0:3:0' -- Max time that can elapse between consecutive rows to be considered part of the same session
    );

SELECT
    assert(
        relative_error(array_agg(CASE WHEN original_session_id NOTNULL
                                      THEN original_session_id ELSE 0 END),
                       array_agg(CASE WHEN session_id NOTNULL
                                      THEN session_id ELSE 0 END)) < 1e-6,
        'wrong output in sessionization')
FROM sessionize_output_v;
SELECT * FROM sessionize_output_v;

SELECT sessionize(
        'eventlog_installchk', -- Name of the input table
        'sessionize_output_t', -- Name of the output table
        '"user id"<102000', -- Partition expression to group the data
        'event_timestamp', -- Order expression to sort the tuples of the data table
        '180', -- Max time that can elapse between consecutive rows to be considered part of the same session
        '*,"user id"<102000 AS uid,revenue>5 AS rid',  -- Select all columns in the input table, along with the partition expression and session id columns
        'f' -- Materialize results into a table
    );

SELECT
    assert(
        relative_error(array_agg(CASE WHEN eventlog_installchk.part_expr THEN 1 ELSE 0 END),
                       array_agg(CASE WHEN sessionize_output_t.uid THEN 1 ELSE 0 END)) != 0,
        'wrong output in sessionization')
FROM sessionize_output_t, eventlog_installchk;
SELECT * FROM sessionize_output_t;