How can I reduce function calls in higher order functions when query

Question

our online clickhouse, we have many higher order functions have a same func parameter.

Such as:

with groupArray(station_id) as station_list, 
     groupArray(sta_lng) as lng_list, 
     groupArray(sta_lat) as lat_list 
select arrayFirst((x,y)->(y=460642), lng_list,station_list), 
       arrayFirst((x,y)->(y=460642), lat_list,station_list) 
from table_test

have the same func:(x,y)->(y=460642) and param: station_list. so whether we can reduce the same func calls to improve the query performance.

And have 2 ideas about this.

one is use the arrayFirstIndex(). whether can use this to improve the performance. Such as

with groupArray(station_id) as station_list, 
     groupArray(sta_lng) as lng_list, 
     groupArray(sta_lat) as lat_list, 
     arrayFirstIndex((x)->(x=460642), station_list) as idx 
select lng_list[idx], lat_list[idx] from table_test

another is creating table function to return all data, and get the needed data from this table.

Thanks.

Answer 1

There is no difference. CH evaluates the same expression only once.

And you can use aliases (some_expression as ALIAS)

And you can use indexOf

with groupArray(station_id) as station_list, 
     groupArray(sta_lng) as lng_list, 
     groupArray(sta_lat) as lat_list
select lng_list[( indexOf(station_list, 460642) as idx )], 
       lat_list[idx] from table_test

You can use Tuples

with groupArray(station_id) as station_list, 
     groupArray( (sta_lng, sta_lat) ) as lng_lat_list
select (lng_lat_list[indexOf(station_list, 460642)] lng_lat).1 as lng,
       lng_lat.2 lat
from table_test