Window functions

Scala QL supports window functions the same way as SQL.

Start by importing:

import scalaql._

// Docs classes
import scalaql.docs.DocUtils._
import scalaql.docs.WindowData._

// Used in examples
import java.nio.file.Paths

Let's predefine the query input:

val ordersPath = Paths.get("docs/src/main/resources/_Order__202207031241.csv")
// ordersPath: java.nio.file.Path = docs/src/main/resources/_Order__202207031241.csv
val orderDetailsPath = Paths.get("docs/src/main/resources/OrderDetail_202207031246.csv")
// orderDetailsPath: java.nio.file.Path = docs/src/main/resources/OrderDetail_202207031246.csv

def ordersInput = from(
  csv
    .read[Order]
    .option(Naming.Capitalize)
    .file(ordersPath)
)  

def orderDetailsInput = from(
  csv
    .read[OrderDetail]
    .option(Naming.Capitalize)
    .file(orderDetailsPath)
)

Let's check the input data set.

Orders:

select[Order]
  .show(truncate = false)
  .run(ordersInput)
// +-----+----------+----------+----------+------------+-----------+-------+-------+--------------------------+------------------------------------------+--------------+---------------+--------------+-----------+
// |id   |customerId|employeeId|orderDate |requiredDate|shippedDate|shipVia|freight|shipName                  |shipAddress                               |shipCity      |shipRegion     |shipPostalCode|shipCountry|
// +-----+----------+----------+----------+------------+-----------+-------+-------+--------------------------+------------------------------------------+--------------+---------------+--------------+-----------+
// |10248|VINET     |5         |2012-07-04|2012-08-01  |2012-07-16 |3      |32.38  |Vins et alcools Chevalier |59 rue de l'Abbaye                        |Reims         |Western Europe |51100         |France     |
// |10249|TOMSP     |6         |2012-07-05|2012-08-16  |2012-07-10 |1      |11.61  |Toms Spezialitäten        |Luisenstr. 48                             |Münster       |Western Europe |44087         |Germany    |
// |10250|HANAR     |4         |2012-07-08|2012-08-05  |2012-07-12 |2      |65.83  |Hanari Carnes             |Rua do Paço, 67                           |Rio de Janeiro|South America  |05454-876     |Brazil     |
// |10251|VICTE     |3         |2012-07-08|2012-08-05  |2012-07-15 |1      |41.34  |Victuailles en stock      |2, rue du Commerce                        |Lyon          |Western Europe |69004         |France     |
// |10252|SUPRD     |4         |2012-07-09|2012-08-06  |2012-07-11 |2      |51.3   |Suprêmes délices          |Boulevard Tirou, 255                      |Charleroi     |Western Europe |B-6000        |Belgium    |
// |10253|HANAR     |3         |2012-07-10|2012-07-24  |2012-07-16 |2      |58.17  |Hanari Carnes             |Rua do Paço, 67                           |Rio de Janeiro|South America  |05454-876     |Brazil     |
// |10254|CHOPS     |5         |2012-07-11|2012-08-08  |2012-07-23 |2      |22.98  |Chop-suey Chinese         |Hauptstr. 31                              |Bern          |Western Europe |3012          |Switzerland|
// |10255|RICSU     |9         |2012-07-12|2012-08-09  |2012-07-15 |3      |148.33 |Richter Supermarkt        |Starenweg 5                               |Genève        |Western Europe |1204          |Switzerland|
// |10256|WELLI     |3         |2012-07-15|2012-08-12  |2012-07-17 |2      |13.97  |Wellington Importadora    |Rua do Mercado, 12                        |Resende       |South America  |08737-363     |Brazil     |
// |10257|HILAA     |4         |2012-07-16|2012-08-13  |2012-07-22 |3      |81.91  |HILARION-Abastos          |Carrera 22 con Ave. Carlos Soublette #8-35|San Cristóbal |South America  |5022          |Venezuela  |
// |10258|ERNSH     |1         |2012-07-17|2012-08-14  |2012-07-23 |1      |140.51 |Ernst Handel              |Kirchgasse 6                              |Graz          |Western Europe |8010          |Austria    |
// |10259|CENTC     |4         |2012-07-18|2012-08-15  |2012-07-25 |3      |3.25   |Centro comercial Moctezuma|Sierras de Granada 9993                   |México D.F.   |Central America|05022         |Mexico     |
// |10260|OTTIK     |4         |2012-07-19|2012-08-16  |2012-07-29 |1      |55.09  |Ottilies Käseladen        |Mehrheimerstr. 369                        |Köln          |Western Europe |50739         |Germany    |
// |10261|QUEDE     |4         |2012-07-19|2012-08-16  |2012-07-30 |2      |3.05   |Que Delícia               |Rua da Panificadora, 12                   |Rio de Janeiro|South America  |02389-673     |Brazil     |
// |10262|RATTC     |8         |2012-07-22|2012-08-19  |2012-07-25 |3      |48.29  |Rattlesnake Canyon Grocery|2817 Milton Dr.                           |Albuquerque   |North America  |87110         |USA        |
// |10263|ERNSH     |9         |2012-07-23|2012-08-20  |2012-07-31 |3      |146.06 |Ernst Handel              |Kirchgasse 6                              |Graz          |Western Europe |8010          |Austria    |
// |10264|FOLKO     |6         |2012-07-24|2012-08-21  |2012-08-23 |3      |3.67   |Folk och fä HB            |Åkergatan 24                              |Bräcke        |Northern Europe|S-844 67      |Sweden     |
// |10265|BLONP     |2         |2012-07-25|2012-08-22  |2012-08-12 |1      |55.28  |Blondel père et fils      |24, place Kléber                          |Strasbourg    |Western Europe |67000         |France     |
// |10266|WARTH     |3         |2012-07-26|2012-09-06  |2012-07-31 |3      |25.73  |Wartian Herkku            |Torikatu 38                               |Oulu          |Scandinavia    |90110         |Finland    |
// |10267|FRANK     |4         |2012-07-29|2012-08-26  |2012-08-06 |1      |208.58 |Frankenversand            |Berliner Platz 43                         |München       |Western Europe |80805         |Germany    |
// |10268|GROSR     |8         |2012-07-30|2012-08-27  |2012-08-02 |3      |66.29  |GROSELLA-Restaurante      |5ª Ave. Los Palos Grandes                 |Caracas       |South America  |1081          |Venezuela  |
// +-----+----------+----------+----------+------------+-----------+-------+-------+--------------------------+------------------------------------------+--------------+---------------+--------------+-----------+
// only showing top 20 rows
//

Order details:

select[OrderDetail]
  .show(truncate = false)
  .run(orderDetailsInput)
// +--------+-------+---------+---------+--------+--------+
// |id      |orderId|productId|unitPrice|quantity|discount|
// +--------+-------+---------+---------+--------+--------+
// |10248/11|10248  |11       |14.0     |12      |0.0     |
// |10248/42|10248  |42       |9.8      |10      |0.0     |
// |10248/72|10248  |72       |34.8     |5       |0.0     |
// |10249/14|10249  |14       |18.6     |9       |0.0     |
// |10249/51|10249  |51       |42.4     |40      |0.0     |
// |10250/41|10250  |41       |7.7      |10      |0.0     |
// |10250/51|10250  |51       |42.4     |35      |0.15    |
// |10250/65|10250  |65       |16.8     |15      |0.15    |
// |10251/22|10251  |22       |16.8     |6       |0.05    |
// |10251/57|10251  |57       |15.6     |15      |0.05    |
// |10251/65|10251  |65       |16.8     |20      |0.0     |
// |10252/20|10252  |20       |64.8     |40      |0.05    |
// |10252/33|10252  |33       |2.0      |25      |0.05    |
// |10252/60|10252  |60       |27.2     |40      |0.0     |
// |10253/31|10253  |31       |10.0     |20      |0.0     |
// |10253/39|10253  |39       |14.4     |42      |0.0     |
// |10253/49|10253  |49       |16.0     |40      |0.0     |
// |10254/24|10254  |24       |3.6      |15      |0.15    |
// |10254/55|10254  |55       |19.2     |21      |0.15    |
// |10254/74|10254  |74       |8.0      |21      |0.0     |
// |10255/2 |10255  |2        |15.2     |20      |0.0     |
// +--------+-------+---------+---------+--------+--------+
// only showing top 20 rows
//

Using window functions in Scala QL looks pretty similar to just plain SQL.
Let's assume you'd like to run the following SQL equivalent:

SELECT CustomerId,
       OrderDate,
     UnitPrice,
     AVG(UnitPrice) OVER (PARTITION BY CustomerId ORDER BY OrderDate DESC) AS AvgUnitPrice
FROM "Order"
INNER JOIN OrderDetail ON [Order].Id = OrderDetail.OrderId

This is how it will look like using Scala QL:

val query = select[Order]
  .join(select[OrderDetail])
  .on(_.id == _.orderId)
  .map((OrderWithDetails.apply _).tupled)
  .window(
    _.avgBy(_.details.unitPrice)
  )
  .over(
    _.partitionBy(_.order.customerId)
      .orderBy(_.order.orderDate.desc)
  )
  .map { case (data, avgUnitPrice) =>
    OrderStats(
      customerId = data.order.customerId,
      orderDate = data.order.orderDate,
      unitPrice = data.details.unitPrice,
      avgUnitPrice = avgUnitPrice
    )
  }
// query: Query[From[Order] with From[OrderDetail], OrderStats] = FROM(WindowData::Order) INNER JOIN FROM(WindowData::OrderDetail) -> MAP(WindowData::OrderWithDetails) -> WINDOW(PARTITION BY (String) ORDER BY (LocalDate) => Tuple2[+WindowData::OrderWithDetails,+Double]) -> MAP(WindowData::OrderStats)

Let's run the Query:

query
  .show(truncate = false)
  .run(ordersInput & orderDetailsInput)
// +----------+----------+---------+------------------+
// |customerId|orderDate |unitPrice|avgUnitPrice      |
// +----------+----------+---------+------------------+
// |TRAIH     |2014-01-08|49.3     |22.944444444444443|
// |TRAIH     |2014-01-08|15.0     |22.944444444444443|
// |TRAIH     |2013-06-23|18.0     |22.944444444444443|
// |TRAIH     |2013-06-23|13.0     |22.944444444444443|
// |TRAIH     |2013-06-23|7.75     |22.944444444444443|
// |TRAIH     |2013-06-19|18.4     |22.944444444444443|
// |TRAIH     |2013-06-19|2.5      |22.944444444444443|
// |TRAIH     |2013-06-19|49.3     |22.944444444444443|
// |TRAIH     |2013-06-19|33.25    |22.944444444444443|
// |LINOD     |2014-04-21|45.6     |18.045714285714283|
// |LINOD     |2014-04-21|18.0     |18.045714285714283|
// |LINOD     |2014-04-21|20.0     |18.045714285714283|
// |LINOD     |2014-04-21|19.5     |18.045714285714283|
// |LINOD     |2014-04-10|9.65     |18.045714285714283|
// |LINOD     |2014-03-17|34.0     |18.045714285714283|
// |LINOD     |2014-03-17|17.45    |18.045714285714283|
// |LINOD     |2014-03-17|12.5     |18.045714285714283|
// |LINOD     |2014-03-17|9.5      |18.045714285714283|
// |LINOD     |2014-03-02|14.0     |18.045714285714283|
// |LINOD     |2014-03-02|18.4     |18.045714285714283|
// |LINOD     |2014-03-02|17.45    |18.045714285714283|
// +----------+----------+---------+------------------+
// only showing top 20 rows
//