Query used to create the table:
CREATE TABLE default.ntest2(job_name String, list_data Array(Tuple(s UInt64, e UInt64, name String))) ENGINE = MergeTree ORDER BY (job_name) SETTINGS index_granularity = 8192;
Table Data:
job_name | list_data.s | list_data.e | list_data.name |
---|---|---|---|
job1 | [19,22] | [38,92] | ['test1','test2'] |
job2 | [28,63] | [49,87] | ['test3''test4'] |
Expected Output:
job_name | list_data.s | list_data.e | list_data.name |
---|---|---|---|
job1 | 19 | 38 | 'test1' |
job1 | 22 | 92 | 'test2' |
job2 | 28 | 49 | 'test3' |
job2 | 63 | 87 | 'test4' |
How can I achieve this with less query time?
ARRAY JOIN https://clickhouse.com/docs/en/sql-reference/statements/select/array-join/
SELECT
job_name,
`list_data.s`,
`list_data.e`,
`list_data.name`
FROM
(
SELECT
c1 AS job_name,
c2 AS list_data
FROM values(('job1', ([19, 22], [38, 92], ['test1', 'test2'])), ('job2', ([28, 63], [49, 87], ['test3', 'test4'])))
) AS T
ARRAY JOIN
list_data.1 AS `list_data.s`,
list_data.2 AS `list_data.e`,
list_data.3 AS `list_data.name`
┌─job_name─┬─list_data.s─┬─list_data.e─┬─list_data.name─┐
│ job1 │ 19 │ 38 │ test1 │
│ job1 │ 22 │ 92 │ test2 │
│ job2 │ 28 │ 49 │ test3 │
│ job2 │ 63 │ 87 │ test4 │
└──────────┴─────────────┴─────────────┴────────────────┘
SELECT
job_name,
list_data.s,
list_data.e,
list_data.name
FROM
(
SELECT
c1 AS job_name,
c2 AS `list_data.s`,
c3 AS `list_data.e`,
c4 AS `list_data.name`
FROM values(('job1', [19, 22], [38, 92], ['test1', 'test2']), ('job2', [28, 63], [49, 87], ['test3', 'test4']))
) AS T
ARRAY JOIN
`list_data.s` AS `list_data.s`,
`list_data.e` AS `list_data.e`,
`list_data.name` AS `list_data.name`
┌─job_name─┬─list_data.s─┬─list_data.e─┬─list_data.name─┐
│ job1 │ 19 │ 38 │ test1 │
│ job1 │ 22 │ 92 │ test2 │
│ job2 │ 28 │ 49 │ test3 │
│ job2 │ 63 │ 87 │ test4 │
└──────────┴─────────────┴─────────────┴────────────────┘