| /* ----------------------------------------------------------------------- */ |
| /** |
| * 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_vec2cols |
| |
| @brief Converts a feature array in a single column of an output table into multiple columns. |
| |
| <div class="toc"><b>Contents</b> |
| <ul> |
| <li class="level1"><a href="#vec2cols_syntax">Syntax</a> |
| <li class="level1"><a href="#vec2cols_usage">Usage</a> |
| <li class="level1"><a href="#vec2cols_example">Examples</a> |
| </ul> |
| </div> |
| |
| @about |
| Converts a feature array in a single column into multiple columns. |
| This process can be used to reverse the function cols2vec. |
| |
| Given a table with a column of type array, this function will create an output |
| table that splits this array into multiple columns, one per array element. |
| It includes the option to name the new feature columns, and to include |
| columns from the original table in the output. |
| |
| @anchor vec2cols_usage |
| @usage |
| |
| <pre class="syntax"> |
| vec2cols( |
| source_table, |
| output_table, |
| vector_col, |
| feature_names, |
| cols_to_output |
| ) |
| </pre> |
| |
| \b Arguments |
| <dl class="arglist"> |
| <dt>source_table</dt> |
| <dd>TEXT. Name of the table containing the source data.</tt>. |
| |
| <dt>output_table</dt> |
| <dd>TEXT. Name of the generated table containing the output. If a table with the |
| same name already exists, an error will be returned.</tt> |
| |
| <dt>vector_col</dt> |
| <dd>TEXT. Name of the column containing the feature array. Must be a one-dimensional array.</tt> |
| |
| <dt>feature_names (optional)</dt> |
| <dd>TEXT[]. Array of names associated with the feature array. Note that |
| this array exists in the summary table created by the function 'cols2vec'. If |
| the 'feature_names' array is not specified, |
| column names will be automatically generated of |
| the form 'f1, f2, ...fn'.</tt> |
| @note If you specify the 'feature_names' parameter, you will get exactly that number of |
| feature columns in the 'output_table'. It means feature arrays from the 'vector_col' may be |
| padded or truncated, if a particular feature array size does not match the target |
| number of feature columns. <br><br>If you do not specify the 'feature names' parameter, |
| the number of feature columns generated |
| in the 'output_table' will be the maximum array size from 'vector_col'. |
| Feature arrays that are less than this maximum will be padded. |
| |
| <dt>cols_to_output (optional)</dt> |
| <dd>TEXT, default NULL. Comma-separated string of column names |
| from the source table to keep in the |
| output table, in addition to the feature columns. |
| To keep all columns from the source table, use '*'. |
| The total number of columns in a table cannot exceed the |
| PostgreSQL limits.</tt> |
| </dd> |
| </dl> |
| |
| <b>Output table</b> |
| <br> |
| The output table produced by the vec2cols function contains the following columns: |
| <table class="output"> |
| <tr> |
| <th><...></th> |
| <td>Columns from source table, depending on which ones are kept (if any). |
| </td> |
| </tr> |
| <tr> |
| <th>feature columns</th> |
| <td>Columns for each of the features in 'vector_col'. Column type |
| will depend on the feature array type in the source table. Column |
| naming will depend on whether the parameter 'feature_names' is used. |
| </tr> |
| </table> |
| |
| |
| @anchor vec2cols_example |
| @par Examples |
| -# Load sample data: |
| <pre class="example"> |
| DROP TABLE IF EXISTS golf CASCADE; |
| CREATE TABLE golf ( |
| id integer NOT NULL, |
| "OUTLOOK" text, |
| temperature double precision, |
| humidity double precision, |
| "Temp_Humidity" double precision[], |
| clouds_airquality text[], |
| windy boolean, |
| class text, |
| observation_weight double precision |
| ); |
| INSERT INTO golf VALUES |
| (1,'sunny', 85, 85, ARRAY[85, 85],ARRAY['none', 'unhealthy'], 'false','Don''t Play', 5.0), |
| (2, 'sunny', 80, 90, ARRAY[80, 90], ARRAY['none', 'moderate'], 'true', 'Don''t Play', 5.0), |
| (3, 'overcast', 83, 78, ARRAY[83, 78], ARRAY['low', 'moderate'], 'false', 'Play', 1.5), |
| (4, 'rain', 70, 96, ARRAY[70, 96], ARRAY['low', 'moderate'], 'false', 'Play', 1.0), |
| (5, 'rain', 68, 80, ARRAY[68, 80], ARRAY['medium', 'good'], 'false', 'Play', 1.0), |
| (6, 'rain', 65, 70, ARRAY[65, 70], ARRAY['low', 'unhealthy'], 'true', 'Don''t Play', 1.0), |
| (7, 'overcast', 64, 65, ARRAY[64, 65], ARRAY['medium', 'moderate'], 'true', 'Play', 1.5), |
| (8, 'sunny', 72, 95, ARRAY[72, 95], ARRAY['high', 'unhealthy'], 'false', 'Don''t Play', 5.0), |
| (9, 'sunny', 69, 70, ARRAY[69, 70], ARRAY['high', 'good'], 'false', 'Play', 5.0), |
| (10, 'rain', 75, 80, ARRAY[75, 80], ARRAY['medium', 'good'], 'false', 'Play', 1.0), |
| (11, 'sunny', 75, 70, ARRAY[75, 70], ARRAY['none', 'good'], 'true', 'Play', 5.0), |
| (12, 'overcast', 72, 90, ARRAY[72, 90], ARRAY['medium', 'moderate'], 'true', 'Play', 1.5), |
| (13, 'overcast', 81, 75, ARRAY[81, 75], ARRAY['medium', 'moderate'], 'false', 'Play', 1.5), |
| (14, 'rain', 71, 80, ARRAY[71, 80], ARRAY['low', 'unhealthy'], 'true', 'Don''t Play', 1.0); |
| </pre> |
| |
| -# Split the column "clouds_airquality" into new columns |
| called "clouds" and "air_quality". Also keep columns id |
| and "OUTLOOK" from the source table |
| <pre class="example"> |
| DROP TABLE IF EXISTS vec2cols_result; |
| SELECT madlib.vec2cols( |
| 'golf', -- source table |
| 'vec2cols_result', -- output table |
| 'clouds_airquality', -- column with array entries to split |
| ARRAY['clouds', 'air_quality'], -- feature names |
| 'id, "OUTLOOK"' -- columns to keep from source table |
| ); |
| SELECT * FROM vec2cols_result ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | OUTLOOK | clouds | air_quality |
| ----+----------+--------+------------- |
| 1 | sunny | none | unhealthy |
| 2 | sunny | none | moderate |
| 3 | overcast | low | moderate |
| 4 | rain | low | moderate |
| 5 | rain | medium | good |
| 6 | rain | low | unhealthy |
| 7 | overcast | medium | moderate |
| 8 | sunny | high | unhealthy |
| 9 | sunny | high | good |
| 10 | rain | medium | good |
| 11 | sunny | none | good |
| 12 | overcast | medium | moderate |
| 13 | overcast | medium | moderate |
| 14 | rain | low | unhealthy |
| (14 rows) |
| </pre> |
| |
| -# Similar to the previous example, except now |
| we keep all columns from source table and do not |
| specify the feature names, so that default names |
| are created. |
| <pre class="example"> |
| DROP TABLE IF EXISTS vec2cols_result; |
| SELECT madlib.vec2cols( |
| 'golf', -- source table |
| 'vec2cols_result', -- output table |
| 'clouds_airquality', -- column with array entries to split |
| NULL, -- feature names |
| '*' -- columns to keep from source table |
| ); |
| SELECT * FROM vec2cols_result ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | OUTLOOK | temperature | humidity | Temp_Humidity | clouds_airquality | windy | class | observation_weight | f1 | f2 |
| ----+----------+-------------+----------+---------------+-------------------+-------+------------+--------------------+--------+----------- |
| 1 | sunny | 85 | 85 | {85,85} | {none,unhealthy} | f | Don't Play | 5 | none | unhealthy |
| 2 | sunny | 80 | 90 | {80,90} | {none,moderate} | t | Don't Play | 5 | none | moderate |
| 3 | overcast | 83 | 78 | {83,78} | {low,moderate} | f | Play | 1.5 | low | moderate |
| 4 | rain | 70 | 96 | {70,96} | {low,moderate} | f | Play | 1 | low | moderate |
| 5 | rain | 68 | 80 | {68,80} | {medium,good} | f | Play | 1 | medium | good |
| 6 | rain | 65 | 70 | {65,70} | {low,unhealthy} | t | Don't Play | 1 | low | unhealthy |
| 7 | overcast | 64 | 65 | {64,65} | {medium,moderate} | t | Play | 1.5 | medium | moderate |
| 8 | sunny | 72 | 95 | {72,95} | {high,unhealthy} | f | Don't Play | 5 | high | unhealthy |
| 9 | sunny | 69 | 70 | {69,70} | {high,good} | f | Play | 5 | high | good |
| 10 | rain | 75 | 80 | {75,80} | {medium,good} | f | Play | 1 | medium | good |
| 11 | sunny | 75 | 70 | {75,70} | {none,good} | t | Play | 5 | none | good |
| 12 | overcast | 72 | 90 | {72,90} | {medium,moderate} | t | Play | 1.5 | medium | moderate |
| 13 | overcast | 81 | 75 | {81,75} | {medium,moderate} | f | Play | 1.5 | medium | moderate |
| 14 | rain | 71 | 80 | {71,80} | {low,unhealthy} | t | Don't Play | 1 | low | unhealthy |
| (14 rows) |
| </pre> |
| |
| -# Now let's run cols2vec then reverse it using vec2cols. |
| In this case we will get feature names from the cols2vec summary table. |
| First run cols2vec: |
| <pre class="example"> |
| DROP TABLE IF EXISTS cols2vec_result, cols2vec_result_summary; |
| SELECT madlib.cols2vec( |
| 'golf', |
| 'cols2vec_result', |
| 'temperature, humidity', |
| NULL, |
| 'id, temperature, humidity' |
| ); |
| SELECT * FROM cols2vec_result ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | temperature | humidity | feature_vector |
| ----+-------------+----------+---------------- |
| 1 | 85 | 85 | {85,85} |
| 2 | 80 | 90 | {80,90} |
| 3 | 83 | 78 | {83,78} |
| 4 | 70 | 96 | {70,96} |
| 5 | 68 | 80 | {68,80} |
| 6 | 65 | 70 | {65,70} |
| 7 | 64 | 65 | {64,65} |
| 8 | 72 | 95 | {72,95} |
| 9 | 69 | 70 | {69,70} |
| 10 | 75 | 80 | {75,80} |
| 11 | 75 | 70 | {75,70} |
| 12 | 72 | 90 | {72,90} |
| 13 | 81 | 75 | {81,75} |
| 14 | 71 | 80 | {71,80} |
| (14 rows) |
| </pre> |
| View the summary table with the feature_names dictionary: |
| <pre class="example"> |
| \\x on |
| SELECT * FROM cols2vec_result_summary; |
| \\x off |
| </pre> |
| <pre class="result"> |
| -[ RECORD 1 ]---------------+----------------------- |
| source_table | golf |
| list_of_features | temperature, humidity |
| list_of_features_to_exclude | None |
| feature_names | {temperature,humidity} |
| </pre> |
| Now use feature_names from the summary table above |
| to name the columns of the split array: |
| <pre class="example"> |
| DROP TABLE IF EXISTS vec2cols_result; |
| SELECT madlib.vec2cols( |
| 'cols2vec_result', -- source table containing the feature vector |
| 'vec2cols_result', -- output table |
| 'feature_vector', -- column with array entries to split |
| (SELECT feature_names from cols2vec_result_summary), -- feature_names from summary table of cols2vec |
| 'id' -- columns to keep from source table |
| ); |
| SELECT * FROM vec2cols_result ORDER BY id; |
| </pre> |
| <pre class="result"> |
| id | temperature | humidity |
| ----+-------------+---------- |
| 1 | 85 | 85 |
| 2 | 80 | 90 |
| 3 | 83 | 78 |
| 4 | 70 | 96 |
| 5 | 68 | 80 |
| 6 | 65 | 70 |
| 7 | 64 | 65 |
| 8 | 72 | 95 |
| 9 | 69 | 70 |
| 10 | 75 | 80 |
| 11 | 75 | 70 |
| 12 | 72 | 90 |
| 13 | 81 | 75 |
| 14 | 71 | 80 |
| (14 rows) |
| </pre> |
| This is the same as the format of the original 'golf' dataset |
| that we started with. |
| |
| */ |
| |
| ------------------------------------------------------------------------- |
| -- vec2cols |
| ------------------------------------------------------------------------- |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.vec2cols( |
| source_table VARCHAR, |
| output_table VARCHAR, |
| vector_col VARCHAR, |
| feature_names TEXT[], |
| cols_to_output VARCHAR |
| ) RETURNS void AS $$ |
| PythonFunctionBodyOnly(utilities, transform_vec_cols) |
| from utilities.control import MinWarning |
| with MinWarning('warning'): |
| vec2cols_obj = transform_vec_cols.vec2cols() |
| return vec2cols_obj.vec2cols(**globals()) |
| $$ LANGUAGE plpythonu VOLATILE |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.vec2cols( |
| source_table VARCHAR, |
| output_table VARCHAR, |
| vector_col VARCHAR, |
| feature_names TEXT[] |
| ) RETURNS void AS $$ |
| SELECT MADLIB_SCHEMA.vec2cols($1,$2,$3,$4,NULL) |
| $$ LANGUAGE SQL |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.vec2cols( |
| source_table VARCHAR, |
| output_table VARCHAR, |
| vector_col VARCHAR |
| ) RETURNS void AS $$ |
| SELECT MADLIB_SCHEMA.vec2cols($1,$2,$3,NULL,NULL) |
| $$ LANGUAGE SQL |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `MODIFIES SQL DATA', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.vec2cols(message TEXT) |
| RETURNS text AS $$ |
| PythonFunctionBodyOnly(utilities, transform_vec_cols) |
| return transform_vec_cols.vec2cols().vec2cols_help_message(schema_madlib, message) |
| $$ LANGUAGE plpythonu VOLATILE |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
| |
| CREATE OR REPLACE FUNCTION MADLIB_SCHEMA.vec2cols() |
| RETURNS text AS $$ |
| SELECT MADLIB_SCHEMA.vec2cols(''); |
| $$ language SQL |
| m4_ifdef(`__HAS_FUNCTION_PROPERTIES__', `CONTAINS SQL', `'); |
