domain specific functions for finance and aggregation

[nwoolmer]

Is your feature request related to a problem?

Many users of QuestDB are in the finance industry. Within this domain, there are common operations that can be complicated to represent in standard SQL.

We should add functions to make these operations easier for our users, and start with the most common operations.

Such functions should support ARRAY types in future, where useful for performance improvements.

This is a tracking issue, and we welcome suggestions from users!

Describe the solution you'd like.

More domain-specific finance functions around trading and order books.

• [x] l2price: level two order-book price
  • PR: https://github.com/questdb/questdb/pull/4462
  • Issue: https://github.com/questdb/questdb/issues/4454
• [x] vwap: volume-weighted average price
  • PR: https://github.com/questdb/questdb/pull/3885
• [ ] twap: time-weighted average price
  • PR: https://github.com/questdb/questdb/pull/4670
• [x] avg: avg window function, can be used to calculate simple moving average
  • docs: https://questdb.io/docs/reference/function/aggregation/#avg
• [ ] vwma: volume-weighted moving average (window)
  • Issue: https://github.com/questdb/questdb/issues/4727
  • not looking for contributions, some internal work needed
• [x] mid(bid,ask): average(bid, ask) = bid+ask / 2
  • Issue: https://github.com/questdb/questdb/issues/4730
  • PR: https://github.com/questdb/questdb/pull/4734
• [x] spread(bid, ask): ask-bid
  • Issue: https://github.com/questdb/questdb/issues/4731
  • PR: https://github.com/questdb/questdb/pull/4744
• [x] wmid(bid_size, bid, ask, ask_size): weighted mid point:
  • wmid = (imbalance * ask_price) + (1 - imbalance) * bid_price where imbalance = bid_size / (bid_size + ask_size)
  • Issue: https://github.com/questdb/questdb/issues/4800
  • PR: https://github.com/questdb/questdb/pull/4801
• [x] spread_bps(bid, ask): spread(bid,ask)/mid(bid,ask) * 10,000
  • PR: https://github.com/questdb/questdb/pull/4939
• [ ] format_price(decimal_form, tick_size): treasury price format, decimal to fraction, e.g. format_price(100.5, 32)= 100-16
  • more detail: https://www.cmegroup.com/education/courses/introduction-to-treasuries/calculating-us-treasury-pricing.html
  • PR: https://github.com/questdb/questdb/pull/4960
• [ ] get_price(fraction_form, tick_size): treasury price format, fraction to decimal, e.g. format_price(100-16, 32)= 100.5
  • more detail: https://www.cmegroup.com/education/courses/introduction-to-treasuries/calculating-us-treasury-pricing.html

More aggregation functions:

• [ ] normalised_perf(column, base_value): base_value x column[i] / column[0]
• [x] regr_slope(y, x): https://duckdb.org/docs/sql/aggregates.html#regr_slopey-x
  • [x] PR: https://github.com/questdb/questdb/pull/5021
• [ ] regr_intercept(y, x): https://duckdb.org/docs/sql/aggregates.html#regr_intercepty-x
  • [ ] PR: https://github.com/questdb/questdb/pull/5071

Convenience functions:

• [ ] to_bbg(Ø): convert a RIC code to a Bloomberg symbol
  • Issue: https://github.com/questdb/questdb/issues/4802
• [ ] to_ric(Ø): convert a Bloomberg symbol to a RIC code
  • Issue: https://github.com/questdb/questdb/issues/4802

And similar. Names are flexible!

Aggregation functions should be linked in the finance section where relevant, with an associated finance example.

• [ ] GT Join
  • [ ] Issue: https://github.com/questdb/questdb/issues/4995

Describe alternatives you've considered.

No response

Full Name:

Nick Woolmer

Affiliation:

QuestDB

Additional context

No response