get max order_date

get_max_order_date.md

No optimizations

create table my_tbl ( order_id int , order_date date ) 
engine = MergeTree 
order by order_id; 

insert into my_tbl select number, today() + intDiv(number, 1000000) from numbers(1e9);

select max(order_date) from my_tbl;
┌─max(order_date)─┐
│      2025-03-19 │
└─────────────────┘
1 row in set. Elapsed: 0.405 sec. Processed 1.00 billion rows, 2.00 GB (2.47 billion rows/s., 4.93 GB/s.)

drop table my_tbl;

Elapsed: 0.405 sec.

Materialized view

Downsides: slightly slower inserts, need to query another table, can diverge because mater.view does not guarantee constistency with the main table.

create table my_tbl ( order_id int , order_date date ) 
engine = MergeTree 
order by order_id; 

create table my_tbl_max_order_date(order_date SimpleAggregateFunction(max,date))
engine = AggregatingMergeTree order by tuple();

create materialized view my_tbl_max_order_date_mv to my_tbl_max_order_date
as select max(order_date) as order_date from my_tbl;

insert into my_tbl select number, today() + intDiv(number, 1000000) from numbers(1e9);

select max(order_date)  from my_tbl_max_order_date;
┌─max(order_date)─┐
│      2025-03-19 │
└─────────────────┘
1 row in set. Elapsed: 0.002 sec.

drop table my_tbl;
drop table my_tbl_max_order_date;
drop table my_tbl_max_order_date_mv;

Elapsed: 0.002 sec.

Projection

Downside: slightly slower inserts.

drop table my_tbl;
create table my_tbl(order_id int , order_date date, projection px(select max(order_date)))
engine = MergeTree 
order by order_id; 

insert into my_tbl select number, today() + intDiv(number, 1000000) from numbers(1e9);

select max(order_date) from my_tbl;
┌─max(order_date)─┐
│      2025-03-19 │
└─────────────────┘
1 row in set. Elapsed: 0.006 sec.

drop table my_tbl;

Elapsed: 0.006 sec.

Virtual projection (partition by)

Downside: table should be partitioned by that column.

create table my_tbl ( order_id int , order_date date ) 
engine = MergeTree partition by toYear(order_date)
order by order_id; 

insert into my_tbl select number, today() + intDiv(number, 1000000) from numbers(1e9);

select max(order_date)  from my_tbl;
┌─max(order_date)─┐
│      2025-03-19 │
└─────────────────┘
1 row in set. Elapsed: 0.002 sec.

drop table my_tbl;

Elapsed: 0.002 sec.