Clickhouse JOIN date ranges

clickhouse_join_date_range.sql

  :) 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 │
└────┴─────┴────────┴─────────────┘