Using cross join and string_split in Vertica-CodePudding

I'm trying to a CROSS JOIN in Vertica from a string, something like this:

SELECT *
FROM table
CROSS JOIN string_split(code,',')

But it doesn't seem to work...

table:

| name | codes  |
| ---- | ------ |
| Luis | 1,2,3  |
| Maria| 4,5,6  |

expected result:
| name | code|
| ---- | --  |
| Luis |  1  |
| Luis |  2  |
| Luis |  3  |
| Maria|  4  |
| Maria|  5  |
| Maria|  6  |

Any ideas on how to do this?

CodePudding user response：

Is below what you expect?

=> SELECT * FROM tab;
 name  | codes
------- -------
 Luis  | 1,2,3
 Maria | 4,5,6

=> SELECT name, explode(codes) OVER(PARTITION BY name) AS (position, code)
-> FROM (
->   SELECT name, string_to_array(codes) codes FROM tab
-> ) subq;
 name  | position | code
------- ---------- ------
 Luis  |        0 | 1
 Luis  |        1 | 2
 Luis  |        2 | 3
 Maria |        0 | 4
 Maria |        1 | 5
 Maria |        2 | 6

CodePudding user response：

I don't have a Vertica to test it, try this.

Create a Numbers table, just one integer column 'n' with 0 to 100 (or whatever max codes you might have). For this example, 0 to 5 will suffice.

   select name, SPLIT_PART ( codes , ',' , n.n ) code 
   from tab1
      cross join Numbers n

split_part()

CodePudding user response：

Three ways, actually, in Vertica:

-- test table ...
CREATE LOCAL TEMPORARY TABLE                                                                                                                                                                                       
indata("name",codes) 
ON COMMIT PRESERVE ROWS AS
          SELECT 'Luis','1,2,3'
UNION ALL SELECT 'Maria','4,5,6'
;

CROSS JOIN method:

WITH
i(i) AS (
            SELECT 1
  UNION ALL SELECT 2
  UNION ALL SELECT 3
)
SELECT
  "name"
, SPLIT_PART(codes,',',i)::INT AS code
FROM indata CROSS JOIN i;
-- out  name  | code 
-- out ------- ------
-- out  Luis  |    1
-- out  Luis  |    2
-- out  Luis  |    3
-- out  Maria |    4
-- out  Maria |    5
-- out  Maria |    6

EXPLODE() an array, as by Hibiki:

SELECT
  EXPLODE(
    "name"
  , STRING_TO_ARRAY(codes)
  ) OVER(PARTITION BEST)
FROM indata;
-- out  name  | position | value 
-- out ------- ---------- -------
-- out  Luis  |        0 | 1
-- out  Luis  |        1 | 2
-- out  Luis  |        2 | 3
-- out  Maria |        0 | 4
-- out  Maria |        1 | 5
-- out  Maria |        2 | 6

Or with StringTokenizerDelim() of our Text-Indexing package:

SELECT
  "name"
, v_txtindex.StringTokenizerDelim(codes,',') OVER(PARTITION BY "name") AS code
FROM indata;
-- out  name  | code 
-- out ------- ------
-- out  Luis  | 1
-- out  Luis  | 2
-- out  Luis  | 3
-- out  Maria | 4
-- out  Maria | 5
-- out  Maria | 6