How to use ClickHouse partition value in SQL query?

user_7417070 0
Viewed 655

I have a table with tuple partitions: (0, 0), (0, 1), (1, 0), (1, 1), (2, 0), (2, 1), (3, 0), ...

CREATE TABLE my_table
(
    id Int32,
    a Int32,
    b Float32,
    c Int32
)
ENGINE = MergeTree
PARTITION BY
(
    intDiv(id, 1000000),
    a < 20000 AND b > 0.6 AND c >= 100
)
ORDER BY id;

I need only rows with partition (<any number>, 1) and I'm looking for a way to use partition value in a query like

SELECT *
FROM my_table
WHERE my_table.partition[2] == 1;

Does ClickHouse have such a feature?

clickhouse
2 Answers

In version 21.6 was added virtual columns _partition_id and _partition_value that can help you:

SELECT
    *,
    _partition_id,
    _partition_value
FROM my_table
WHERE (_partition_value.2) = 1
user_3032980

And what is the problem with

where (a < 20000 AND b > 0.6 AND c >= 100) = 1

???

insert into my_table select 1, 3000000, 0, 0 from numbers(100000000);
insert into my_table select 1, 0, 10, 200 from numbers(100);

SET send_logs_level = 'debug';
set force_index_by_date=1;

select sum(id) from my_table where (a < 20000 AND b > 0.6 AND c >= 100) = 1;
           
...Selected 1/7 parts by partition key...

┌─sum(id)─┐
│     100 │
└─────────┘
1 rows in set. Elapsed: 0.002 sec.

Though (_partition_value.2) = 1 will be faster because it does not require to read columns a,b,c for filtering.

user_116443080
Related