Support for AS OF joins

[kzk2000]

Feature request

Please support AS OF JOIN, similar to what kdb, QuestDB, and ClickHouse already offer. It’s a critical feature for financial use cases such as matching trades & quotes, for instance, or IoT use cases where different time-series streams don’t have matching time stamps.

Describe the solution you'd like
I don’t know what AS OF join implementation works best. QuestDb code is Apache v2 open-source, so that might be a good start.

Alternatively, I found this pure Postgres SQL solution that was posted on a TimeScaleDB thread that might be a good starting point as well: timescale/timescaledb#271 (comment)

Additional context
Per this thread on Starrocks Slack, this feature has been requested by others before as well: https://starrocks.slack.com/archives/C02FACZSNJV/p1682199052128039

[kzk2000]

here are a few references for DBs that support this:

• kdb reference: https://code.kx.com/q/ref/aj/

• Clickhouse reference: https://clickhouse.com/docs/en/sql-reference/statements/select/join#asof-join-usage

• QuestDB reference: https://questdb.io/docs/reference/sql/join/#asof-join

[github-actions]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!

[kzk2000]

Please keep it active, still waiting for this to compare Starrocks vs Clickhouse

[kzk2000]

Snowflake added AS OF JOINS too by now: https://docs.snowflake.com/en/sql-reference/constructs/asof-join

[github-actions]

We have marked this issue as stale because it has been inactive for 6 months. If this issue is still relevant, removing the stale label or adding a comment will keep it active. Otherwise, we'll close it in 10 days to keep the issue queue tidy. Thank you for your contribution to StarRocks!