Skip to main content

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
//