Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save redsquare/ff74313ebcefc16829742678e4522596 to your computer and use it in GitHub Desktop.
Save redsquare/ff74313ebcefc16829742678e4522596 to your computer and use it in GitHub Desktop.
Clickhouse JOIN date ranges
:) CREATE TABLE ranges (id UInt64, date_start Date, date_end Date) ENGINE = TinyLog;
:) INSERT INTO ranges SELECT rowNumberInAllBlocks() AS id, least(dates[1], dates[2]) AS date_start, greatest(dates[1], dates[2]) AS date_end FROM (SELECT arrayJoin(arrayMap(d -> [today() - d - rand64(d) % 10, yesterday() - d - rand(d) % 10], range(10))) AS dates);
:) CREATE TABLE dates (id UInt64, date Date) ENGINE = TinyLog;
:) INSERT INTO dates SELECT rowNumberInAllBlocks() AS id, date FROM (SELECT arrayJoin(arrayMap(d -> today() - rand64(d) % 10, range(10))) AS date);
:) SELECT * FROM dates;
┌─id─┬───────date─┐
│ 0 │ 2017-04-27 │
│ 1 │ 2017-04-26 │
│ 2 │ 2017-05-01 │
│ 3 │ 2017-04-30 │
│ 4 │ 2017-04-29 │
│ 5 │ 2017-04-29 │
│ 6 │ 2017-04-26 │
│ 7 │ 2017-05-02 │
│ 8 │ 2017-04-30 │
│ 9 │ 2017-04-28 │
└────┴────────────┘
:) SELECT * FROM ranges;
┌─id─┬─date_start─┬───date_end─┐
│ 0 │ 2017-04-29 │ 2017-04-29 │
│ 1 │ 2017-05-01 │ 2017-05-02 │
│ 2 │ 2017-04-24 │ 2017-04-25 │
│ 3 │ 2017-04-26 │ 2017-04-27 │
│ 4 │ 2017-04-22 │ 2017-04-29 │
│ 5 │ 2017-04-21 │ 2017-04-25 │
│ 6 │ 2017-04-19 │ 2017-04-22 │
│ 7 │ 2017-04-20 │ 2017-04-26 │
│ 8 │ 2017-04-18 │ 2017-04-25 │
│ 9 │ 2017-04-17 │ 2017-04-19 │
└────┴────────────┴────────────┘
:) SELECT id, id2, joined, date AS date_ranges FROM dates ANY LEFT JOIN (SELECT *, 1 AS joined FROM (SELECT id AS id2, date_end - date_start AS date_delta, arrayMap(d -> date_start + d, range(toUInt64(date_delta)+1)) AS date_ranges FROM ranges) ARRAY JOIN date_ranges) USING date_ranges;
┌─id─┬─id2─┬─joined─┬─date_ranges─┐
│ 0 │ 3 │ 1 │ 2017-04-27 │
│ 1 │ 3 │ 1 │ 2017-04-26 │
│ 2 │ 1 │ 1 │ 2017-05-01 │
│ 3 │ 0 │ 0 │ 2017-04-30 │
│ 4 │ 0 │ 1 │ 2017-04-29 │
│ 5 │ 0 │ 1 │ 2017-04-29 │
│ 6 │ 3 │ 1 │ 2017-04-26 │
│ 7 │ 1 │ 1 │ 2017-05-02 │
│ 8 │ 0 │ 0 │ 2017-04-30 │
│ 9 │ 4 │ 1 │ 2017-04-28 │
└────┴─────┴────────┴─────────────┘
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment