Github user njayaram2 commented on a diff in the pull request: https://github.com/apache/incubator-madlib/pull/44#discussion_r65578128 --- Diff: src/ports/postgres/modules/utilities/sessionize.py_in --- @@ -0,0 +1,101 @@ +# 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. + +import plpy +import string + +from control import MinWarning +from utilities import unique_string, _assert +from validate_args import get_cols +from validate_args import input_tbl_valid, output_tbl_valid, is_var_valid + +m4_changequote(`<!', `!>') + +def sessionize(schema_madlib, source_table, output_table, partition_expr, + time_stamp, time_out, **kwargs): + """ + Perform sessionization over a sequence of rows. + + Args: + @param schema_madlib: str, Name of the MADlib schema + @param source_table: str, Name of the input table/view + @param output_table: str, Name of the table to store result + @param partition_expr: str, Expression to partition (group) the input data + @param time_stamp: float, Column name with time used for sessionization calculation + @param time_out: float, Delta time between subsequent events to define a sessions + + """ + with MinWarning("error"): + _validate(source_table, output_table, partition_expr, time_stamp, time_out) + + all_input_cols_str = ', '.join([i.strip() for i in get_cols(source_table, schema_madlib)]) + session_id = 'session_id' if not is_var_valid(source_table, 'session_id') else unique_string('session_id') + + plpy.execute(""" + CREATE TABLE {output_table} AS + SELECT + {all_input_cols_str}, + CASE WHEN {time_stamp} NOTNULL + THEN (SUM(new_event_boundary) OVER (PARTITION BY {partition_expr} ORDER BY {time_stamp})) END AS {session_id} + FROM ( + SELECT *, + CASE WHEN {time_stamp} NOTNULL and ({time_stamp}-LAG({time_stamp},1) OVER (w) > '{time_out}' OR ROW_NUMBER() OVER (w) = '1') --- End diff -- _Likewise, it will be a lot more efficient if '{time_out}' is cast to the correct data type._ Consider the example where {time_stamp} = "'04/15/2015 01:03:0.5'" and LAG({time_stamp},1) = "'04/15/2015 01:05:00'". If the {time_out} = "0:3:0", the following operation tries to check if the difference between the two time_stamps is greater than 3 minutes or not: ({time_stamp}-LAG({time_stamp},1) OVER (w) > '{time_out}' Trying to cast {time_out} to timestamp (in this example) throws an error. I cannot expect {time_out} to be of type timestamp, since the difference between two timestamps seems to be of type interval. If time_stamp was of type date instead, having a time_out/min_time of type date may have similar issues. Enforcing the type of time_out/min_time to "interval" (instead of varchar or the type of time_stamp) might be a better approach, since time_stamp can then be any of the date/time types. Do you think we should mandate time_out/min_time to always be of type interval? Another option is to force it to be of type float (representing seconds), then we may have to use epoch() while computing the time_stamp diffs.
--- If your project is set up for it, you can reply to this email and have your reply appear on GitHub as well. If your project does not have this feature enabled and wishes so, or if the feature is enabled but not working, please contact infrastructure at infrastruct...@apache.org or file a JIRA ticket with INFRA. ---