round function's behavior?

[eitsupi]

The round function implemented in the major RDBMS seems to do rounding, is rounding ideal for the behavior of the PRQL's round function?
In other words, I am wondering if the behavior of the round function should be changed to bankers' rounding.

In general, rounding is very easy to implement on the user side compared to bankers' rounding, since all we have to do is add 0.5 and trunc that.

Related duckdb's issue: duckdb/duckdb#2348

[max-sixty]

(no strong view on this end. Unless anyone has a strong view, I would tend to delegate to the DB's implementation until we have one...)

[snth]

From that DuckDB thread:

• Banker's rounding is more numerically stable (https://stackoverflow.com/questions/45223778/is-bankers-rounding-really-more-numerically-stable)
  • In particular from that SO thread, looks like Banker's rounding has no "bias" (in the statistical sense) but slightly larger variance (no idea why that might be).

Sounds like a good idea to default to Banker's rounding then. There should probably be an option/different function to default to the RDBMs target default though for consistency with existing SQL code.

[eitsupi]

There should probably be an option/different function to default to the RDBMs target default though for consistency with existing SQL code.

Perhaps simply using an S-string s"round()" would suffice. (If not, we may have to provide two versions of every function.)

[aljazerzen]

There does not seem to be a consensus between languages.

Rust has "common rounding": https://doc.rust-lang.org/std/primitive.f64.html#method.round

Python has "bankers rounding": https://docs.python.org/3/library/functions.html#round

From reading a bit of discussion around this, it seems like "bankers rounding" is the preferred behavior, but it is being held back by concerns of backward compatibility. As we don't have those (yet), I think that std.round should do "bankers rounding".

---

I also think we should make up a strategy on how exhaustive we want std to be.
If the answer is "it should be minimal", then one way of rounding is enough.
If the answer is "it should provide you with everything you need", then we can add other implementations.

This question depends heavily on how hard it is to implement your own prqlc extension that can add new functions and their provide their SQL implementations.

---

If we decide on "multiple ways of rounding", the "common rounding" could be named
std.round_ties_infinity, as it rounds ties toward infinity.

Refs:
https://en.wikipedia.org/wiki/Rounding

select 0.5 + i, round(0.5 + i) from generate_series(-4,3) tbl(i);

[max-sixty]

As discussed on dev call, the tentative proposal is:

• Implement round as "bankers rounding"
• Possibly implement round_ties_up / rounds_upward
• Don't add any of the others

[varunu28]

@max-sixty I will like to take this up if someone else is not already working on it?

[max-sixty]

That sounds great @varunu28

We'd want to write functions such as

prql/prqlc/prqlc/src/sql/std.sql.prql

Lines 330 to 335 in 7c51044

	# Mathematical functions
	module math {
	# GlareDB: https://glaredb.com/docs/sql-reference/functions/math-functions
	@{binding_strength=100}
	let round = n_digits column -> s"ROUND(({column:0})::numeric, {n_digits:0})"
	}

for dialects that don't do bankers' rounding by default, and confirm tests such as

prql/prqlc/prqlc/tests/integration/queries/arithmetic.prql

Line 28 in 3dcace7

(q_fi * k_int + r_fi | math.round 0),

work with this.

Always feel free to ask any questions / push partially complete code...