:rocket: ExoQuery <1.3.0> is out, supporting SQL w...

# feed

[Alexander Ioffe]

🚀 ExoQuery 1.3.0 is out, supporting SQL window functions!
Basic window functions such as ranking and aggregation are supported out of the box. Custom window functions are supported via a special pattern.

[Alexander Ioffe]

Custom windows are also supported. Use .frame(customWindow) together with .free("custom sql").

[Arjan van Wieringen]

That is some pretty esoteric DSL. What is the main benefit of this over SQL? Especially since Postgres, for instance, is very well documented

[Alexander Ioffe]

Same as any other thing in ExoQuery, it's about the code-reuse (a.k.a. composition).

data class Customer(val name: String, val age: Int, val membership: String)

@CapturedFunction
fun ntile(c: Customer, n: Int) = capture.expression {
  over()
    .partitionBy(c.membership)
    .orderBy(c.age)
    .frame(free("NTILE($n)").invoke<Int>())
}

val q =
  capture.select {
    val c = from(Table<Customer>())
    Data(
      c.name,
      ntile(c, 5).use,
      ntile(c, 10).use,
      ntile(c, 20).use,
    )
  }

// SELECT 
//   c.name, 
//   NTILE(5) OVER(PARTITION BY c.membership ORDER BY c.age) AS a, 
//   NTILE(10) OVER(PARTITION BY c.membership ORDER BY c.age) AS b, 
//   NTILE(20) OVER(PARTITION BY c.membership ORDER BY c.age) AS c 
// FROM Customer c

[Alexander Ioffe]

Ah... but perhaps you wish to say "Just use WINDOW clauses", right? Firstly, not all DB vendors even support them. Secondly, want to see some fun examples when that doesn't work?