CODEWITHSUNDEEP
sqlclickhouse
AmyChodorowski
AmyChodorowski

Reputation: 402

Clickhouse: Mapping BETWEEN filtering from an array

I understand if I want to filter a column between two numbers I can use BETWEEN:

SELECT a
FROM table
WHERE a BETWEEN 1 AND 5

Is there a way of mapping the filtering to an array of values, for instance, if the array was [1, 10, ... , N]:

SELECT a
FROM table
WHERE (a BETWEEN 1 AND 1+4) AND (a BETWEEN 10 AND 10+4) AND ... AND (a BETWEEN N AND N+4)

Upvotes: 1

Views: 895

Answers (1)

vladimir
vladimir

Reputation: 15218

Try this query:

WITH
    [1, 10, 75] AS starts_from,
    4 AS step,
    arrayMap(x -> (x, x + step), starts_from) AS intervals
SELECT number
FROM numbers(100)
WHERE arrayFirstIndex(x -> number >= x.1 AND number <= x.2, intervals) != 0

/*
┌─number─┐
│      1 │
│      2 │
│      3 │
│      4 │
│      5 │
│     10 │
│     11 │
│     12 │
│     13 │
│     14 │
│     75 │
│     76 │
│     77 │
│     78 │
│     79 │
└────────┘
*/

Upvotes: 2

Related Questions