Window (Spark 2.3.1 JavaDoc)

Object
- org.apache.spark.sql.expressions.Window

public class Window
extends Object

Utility functions for defining window in DataFrames.


   // PARTITION BY country ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   Window.partitionBy("country").orderBy("date")
     .rowsBetween(Window.unboundedPreceding, Window.currentRow)

   // PARTITION BY country ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
   Window.partitionBy("country").orderBy("date").rowsBetween(-3, 3)

Since:: 1.4.0

Method Summary

All Methods Static Methods Concrete Methods
Modifier and Type	Method and Description
`static long`	`currentRow()` Value representing the current row.
`static WindowSpec`	`orderBy(Column... cols)` Creates a `WindowSpec` with the ordering defined.
`static WindowSpec`	`orderBy(scala.collection.Seq<Column> cols)` Creates a `WindowSpec` with the ordering defined.
`static WindowSpec`	`orderBy(String colName, scala.collection.Seq<String> colNames)` Creates a `WindowSpec` with the ordering defined.
`static WindowSpec`	`orderBy(String colName, String... colNames)` Creates a `WindowSpec` with the ordering defined.
`static WindowSpec`	`partitionBy(Column... cols)` Creates a `WindowSpec` with the partitioning defined.
`static WindowSpec`	`partitionBy(scala.collection.Seq<Column> cols)` Creates a `WindowSpec` with the partitioning defined.
`static WindowSpec`	`partitionBy(String colName, scala.collection.Seq<String> colNames)` Creates a `WindowSpec` with the partitioning defined.
`static WindowSpec`	`partitionBy(String colName, String... colNames)` Creates a `WindowSpec` with the partitioning defined.
`static WindowSpec`	`rangeBetween(Column start, Column end)` Creates a `WindowSpec` with the frame boundaries defined, from `start` (inclusive) to `end` (inclusive).
`static WindowSpec`	`rangeBetween(long start, long end)` Creates a `WindowSpec` with the frame boundaries defined, from `start` (inclusive) to `end` (inclusive).
`static WindowSpec`	`rowsBetween(long start, long end)` Creates a `WindowSpec` with the frame boundaries defined, from `start` (inclusive) to `end` (inclusive).
`static long`	`unboundedFollowing()` Value representing the last row in the partition, equivalent to "UNBOUNDED FOLLOWING" in SQL.
`static long`	`unboundedPreceding()` Value representing the first row in the partition, equivalent to "UNBOUNDED PRECEDING" in SQL.

Methods inherited from class Object
equals, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

- Method Detail
  - partitionBy
```
public static WindowSpec partitionBy(String colName,
                                     String... colNames)
```
    Creates a WindowSpec with the partitioning defined.
    
    Parameters:
    
    colName - (undocumented)
    
    colNames - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - partitionBy
```
public static WindowSpec partitionBy(Column... cols)
```
    Creates a WindowSpec with the partitioning defined.
    
    Parameters:
    
    cols - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - orderBy
```
public static WindowSpec orderBy(String colName,
                                 String... colNames)
```
    Creates a WindowSpec with the ordering defined.
    
    Parameters:
    
    colName - (undocumented)
    
    colNames - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - orderBy
```
public static WindowSpec orderBy(Column... cols)
```
    Creates a WindowSpec with the ordering defined.
    
    Parameters:
    
    cols - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - partitionBy
```
public static WindowSpec partitionBy(String colName,
                                     scala.collection.Seq<String> colNames)
```
    Creates a WindowSpec with the partitioning defined.
    
    Parameters:
    
    colName - (undocumented)
    
    colNames - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - partitionBy
```
public static WindowSpec partitionBy(scala.collection.Seq<Column> cols)
```
    Creates a WindowSpec with the partitioning defined.
    
    Parameters:
    
    cols - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - orderBy
```
public static WindowSpec orderBy(String colName,
                                 scala.collection.Seq<String> colNames)
```
    Creates a WindowSpec with the ordering defined.
    
    Parameters:
    
    colName - (undocumented)
    
    colNames - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - orderBy
```
public static WindowSpec orderBy(scala.collection.Seq<Column> cols)
```
    Creates a WindowSpec with the ordering defined.
    
    Parameters:
    
    cols - (undocumented)
    
    Returns:
    
    (undocumented)
    
    Since:
    
    1.4.0
  - unboundedPreceding
```
public static long unboundedPreceding()
```
    Value representing the first row in the partition, equivalent to "UNBOUNDED PRECEDING" in SQL. This can be used to specify the frame boundaries:
```
   Window.rowsBetween(Window.unboundedPreceding, Window.currentRow)
 
```
    Returns:
    
    (undocumented)
    
    Since:
    
    2.1.0
  - unboundedFollowing
```
public static long unboundedFollowing()
```
    Value representing the last row in the partition, equivalent to "UNBOUNDED FOLLOWING" in SQL. This can be used to specify the frame boundaries:
```
   Window.rowsBetween(Window.unboundedPreceding, Window.unboundedFollowing)
 
```
    Returns:
    
    (undocumented)
    
    Since:
    
    2.1.0
  - currentRow
```
public static long currentRow()
```
    Value representing the current row. This can be used to specify the frame boundaries:
```
   Window.rowsBetween(Window.unboundedPreceding, Window.currentRow)
 
```
    Returns:
    
    (undocumented)
    
    Since:
    
    2.1.0
  - rowsBetween
```
public static WindowSpec rowsBetween(long start,
                                     long end)
```
    Creates a WindowSpec with the frame boundaries defined, from start (inclusive) to end (inclusive).
    Both start and end are positions relative to the current row. For example, "0" means "current row", while "-1" means the row before the current row, and "5" means the fifth row after the current row.
    We recommend users use Window.unboundedPreceding, Window.unboundedFollowing, and Window.currentRow to specify special boundary values, rather than using integral values directly.
    A row based boundary is based on the position of the row within the partition. An offset indicates the number of rows above or below the current row, the frame for the current row starts or ends. For instance, given a row based sliding frame with a lower bound offset of -1 and a upper bound offset of +2. The frame for row with index 5 would range from index 4 to index 6.
```
   import org.apache.spark.sql.expressions.Window
   val df = Seq((1, "a"), (1, "a"), (2, "a"), (1, "b"), (2, "b"), (3, "b"))
     .toDF("id", "category")
   val byCategoryOrderedById =
     Window.partitionBy('category).orderBy('id).rowsBetween(Window.currentRow, 1)
   df.withColumn("sum", sum('id) over byCategoryOrderedById).show()

   +---+--------+---+
   | id|category|sum|
   +---+--------+---+
   |  1|       b|  3|
   |  2|       b|  5|
   |  3|       b|  3|
   |  1|       a|  2|
   |  1|       a|  3|
   |  2|       a|  2|
   +---+--------+---+
 
```
    Parameters:
    
    start - boundary start, inclusive. The frame is unbounded if this is the minimum long value (Window.unboundedPreceding).
    
    end - boundary end, inclusive. The frame is unbounded if this is the maximum long value (Window.unboundedFollowing).
    
    Returns:
    
    (undocumented)
    
    Since:
    
    2.1.0
  - rangeBetween
```
public static WindowSpec rangeBetween(long start,
                                      long end)
```
    Creates a WindowSpec with the frame boundaries defined, from start (inclusive) to end (inclusive).
    Both start and end are relative to the current row. For example, "0" means "current row", while "-1" means one off before the current row, and "5" means the five off after the current row.
    We recommend users use Window.unboundedPreceding, Window.unboundedFollowing, and Window.currentRow to specify special boundary values, rather than using long values directly.
    A range-based boundary is based on the actual value of the ORDER BY expression(s). An offset is used to alter the value of the ORDER BY expression, for instance if the current order by expression has a value of 10 and the lower bound offset is -3, the resulting lower bound for the current row will be 10 - 3 = 7. This however puts a number of constraints on the ORDER BY expressions: there can be only one expression and this expression must have a numerical data type. An exception can be made when the offset is unbounded, because no value modification is needed, in this case multiple and non-numeric ORDER BY expression are allowed.
```
   import org.apache.spark.sql.expressions.Window
   val df = Seq((1, "a"), (1, "a"), (2, "a"), (1, "b"), (2, "b"), (3, "b"))
     .toDF("id", "category")
   val byCategoryOrderedById =
     Window.partitionBy('category).orderBy('id).rangeBetween(Window.currentRow, 1)
   df.withColumn("sum", sum('id) over byCategoryOrderedById).show()

   +---+--------+---+
   | id|category|sum|
   +---+--------+---+
   |  1|       b|  3|
   |  2|       b|  5|
   |  3|       b|  3|
   |  1|       a|  4|
   |  1|       a|  4|
   |  2|       a|  2|
   +---+--------+---+
 
```
    Parameters:
    
    start - boundary start, inclusive. The frame is unbounded if this is the minimum long value (Window.unboundedPreceding).
    
    end - boundary end, inclusive. The frame is unbounded if this is the maximum long value (Window.unboundedFollowing).
    
    Returns:
    
    (undocumented)
    
    Since:
    
    2.1.0
  - rangeBetween
```
public static WindowSpec rangeBetween(Column start,
                                      Column end)
```
    Creates a WindowSpec with the frame boundaries defined, from start (inclusive) to end (inclusive).
    Both start and end are relative to the current row. For example, "lit(0)" means "current row", while "lit(-1)" means one off before the current row, and "lit(5)" means the five off after the current row.
    Users should use unboundedPreceding(), unboundedFollowing(), and currentRow() from functions to specify special boundary values, literals are not transformed to SpecialFrameBoundarys.
    A range-based boundary is based on the actual value of the ORDER BY expression(s). An offset is used to alter the value of the ORDER BY expression, for instance if the current order by expression has a value of 10 and the lower bound offset is -3, the resulting lower bound for the current row will be 10 - 3 = 7. This however puts a number of constraints on the ORDER BY expressions: there can be only one expression and this expression must have a numerical/date/timestamp data type. An exception can be made when the offset is unbounded, because no value modification is needed, in this case multiple and non-numerical/date/timestamp data type ORDER BY expression are allowed.
```
   import org.apache.spark.sql.expressions.Window
   val df = Seq((1, "a"), (1, "a"), (2, "a"), (1, "b"), (2, "b"), (3, "b"))
     .toDF("id", "category")
   val byCategoryOrderedById =
     Window.partitionBy('category).orderBy('id).rangeBetween(currentRow(), lit(1))
   df.withColumn("sum", sum('id) over byCategoryOrderedById).show()

   +---+--------+---+
   | id|category|sum|
   +---+--------+---+
   |  1|       b|  3|
   |  2|       b|  5|
   |  3|       b|  3|
   |  1|       a|  4|
   |  1|       a|  4|
   |  2|       a|  2|
   +---+--------+---+
 
```
    Parameters:
    
    start - boundary start, inclusive. The frame is unbounded if the expression is UnboundedPreceding.
    
    end - boundary end, inclusive. The frame is unbounded if the expression is UnboundedFollowing.
    
    Returns:
    
    (undocumented)
    
    Since:
    
    2.3.0

Class Window

Method Summary

Methods inherited from class Object

Method Detail

partitionBy

partitionBy

orderBy

orderBy

partitionBy

partitionBy

orderBy

orderBy

unboundedPreceding

unboundedFollowing

currentRow

rowsBetween

rangeBetween

rangeBetween