row-wise min and max

[drtconway]

Is your feature request related to a problem or challenge?

Thanks for an awesome platform! I'm really loving it!

In R pmin and pmax do row-wise min and max respectively. They're reasonably verbose to replicate, and probably much less efficient than a direct implementation. At the moment, I use the following code to achieve the same effect:

pub fn ifelse(cond: Expr, then: Expr, otherwise: Expr) -> datafusion::common::Result<Expr> {
    case(cond)
        .when(lit(true), then)
        .when(lit(false), otherwise)
        .end()
}

pub fn pmin(lhs: Expr, rhs: Expr) -> datafusion::common::Result<Expr> {
    ifelse(lhs.clone().lt_eq(rhs.clone()), lhs, rhs)
}

pub fn pmax(lhs: Expr, rhs: Expr) -> datafusion::common::Result<Expr> {
    ifelse(lhs.clone().gt_eq(rhs.clone()), lhs, rhs)
}

Describe the solution you'd like

A direct, builtin implementation would be faster, and more concise, since it could support a signature:
fn pmin(lhs: Expr, rhs: Expr) -> Expr.

The ifelse function would be nice too (I like the R equivalent too!), but is a lower priority.

Describe alternatives you've considered

The above code works, and isn't too awful, but a direct implementation would be nicer.

Additional context

No response

[kosiew]

hi @drtconway ,

pmin sounds a lot like
https://datafusion.apache.org/user-guide/sql/scalar_functions.html#least
and
pmax like
https://datafusion.apache.org/user-guide/sql/scalar_functions.html#greatest

[kosiew]

DataFusion CLI v48.0.0
> -- Define sample data
CREATE TABLE t1 (a INT, b INT, c INT) AS VALUES
  (4, NULL, NULL),
  (1, 2, 3),
  (3, 1, 2),
  (1, NULL, -1),
  (NULL, NULL, NULL),
  (3, 0, -1);

-- Display per-row minima and maxima
SELECT a, b, c,
       least(a, b, c) AS row_min,
       greatest(a, b, c) AS row_max
FROM t1;

-- A few scalar examples
SELECT greatest(4, 7, 5) AS g1, least(4, 7, 5) AS l1;
SELECT greatest(-1, 10, 3), least(-1, 10, 3);

0 row(s) fetched.
Elapsed 0.036 seconds.

+------+------+------+---------+---------+
| a    | b    | c    | row_min | row_max |
+------+------+------+---------+---------+
| 4    | NULL | NULL | 4       | 4       |
| 1    | 2    | 3    | 1       | 3       |
| 3    | 1    | 2    | 1       | 3       |
| 1    | NULL | -1   | -1      | 1       |
| NULL | NULL | NULL | NULL    | NULL    |
| 3    | 0    | -1   | -1      | 3       |
+------+------+------+---------+---------+
6 row(s) fetched.
Elapsed 0.008 seconds.

+----+----+
| g1 | l1 |
+----+----+
| 7  | 4  |
+----+----+
1 row(s) fetched.
Elapsed 0.002 seconds.

[drtconway]

Ah yes! Thank you!

But they're not in the Rust part of the documentation (https://datafusion.apache.org/user-guide/expressions.html), which is why I didn't know they were there; though I see they are in the prelude.

[kosiew]

@drtconway
You're welcome.

Can you close this issue?

[drtconway]

Sure!