| /* ----------------------------------------------------------------------- */ |
| /** |
| * 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 path.sql_in |
| * |
| * @brief SQL functions for pathing functions |
| * @date Sep 2015 |
| * |
| */ |
| /* ----------------------------------------------------------------------- */ |
| |
| DROP TABLE IF EXISTS "Weblog", "Path_output", "Path_output_tuples" CASCADE; |
| CREATE TABLE "Weblog" (event_timestamp TIMESTAMP, |
| user_id INT, |
| age_group INT, |
| income_group INT, |
| gender TEXT, |
| region TEXT, |
| household_size INT, |
| "Click_event" INT, |
| purchase_event INT, |
| revenue FLOAT, |
| "Margin" FLOAT); |
| |
| INSERT INTO "Weblog" VALUES |
| (to_timestamp('04/14/2012 23:43:00', 'MM/DD/YYYY HH24:MI:SS'), 102201, 3, 3, 'Female', 'East', 3, 1, 1, 112, 36), |
| (to_timestamp('04/14/2012 23:56:00', 'MM/DD/YYYY HH24:MI:SS'), 101881, 2, 4, 'Male', 'West', 5, 0, 0, 0, 0), |
| (to_timestamp('04/15/2012 01:04:00', 'MM/DD/YYYY HH24:MI:SS'), 100821, 1, 4, 'Unknown', 'West', 3, 0, 0, 0, 0), |
| (to_timestamp('04/16/2012 23:57:00', 'MM/DD/YYYY HH24:MI:SS'), 101331, 2, 4, 'Female', 'East', 5, 1, 1, 456, 77); |
| |
| /* |
| SELECT * FROM weblog ORDER BY event_timestamp ASC; |
| */ |
| SELECT path( |
| '"Weblog"', -- Name of the table |
| '"Path_output"', -- Table name to store the path results |
| 'user_id, age_group > 1, income_group > 1', -- Partition expression to group the data table |
| 'event_timestamp ASC', -- Order expression to sort the tuples of the data table |
| 'I:="Click_event"=0 AND purchase_event=0, Click:="Click_event"=1 AND purchase_event=0, Conv:=purchase_event=1', -- Definition of various symbols used in the pattern definition |
| 'I(click){1}(CONV){1}', -- Definition of the path pattern to search for |
| 'COUNT(*)' -- Aggregate/window functions to be applied on the matched paths |
| ,TRUE |
| ); |