查询时如何减少更高级函数的功能调用

发布于 2025-01-20 21:00:40 字数 895 浏览 2 评论 0原文

我们的在线 clickhouse，我们有许多高阶函数具有相同的 func 参数。

如：

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

具有相同的 func:(x,y)->(y=460642) 和 param: station_list。那么我们是否可以减少相同的func调用来提高查询性能。

对此有两个想法。

一种是使用 arrayFirstIndex()。是否可以利用它来提高性能。比如

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

另一个是创建表函数来返回所有数据，并从这个表中获取需要的数据。

谢谢。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

赏烟花じ飞满天 2025-01-27 21:00:40

没有区别。 CH仅评估相同的表达式一次。

您可以使用别名（some_expression作为别名）

，您可以使用索引

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

，可以使用元组

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

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

回复收藏 0 原文

~没有更多了~