Object/Class

org.apache.spark.sql.expressions

Window

Related Docs: class Window | package expressions

Permalink

object Window

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)

Annotations: @Stable()
Source: Window.scala
Since: 1.4.0

Linear Supertypes

AnyRef, Any

Ordering

Alphabetic
By Inheritance

Inherited

Window
AnyRef
Any

Hide All
Show All

Visibility

Public
All

Value Members

final def !=(arg0: Any): Boolean

Definition Classes
AnyRef → Any
final def ##(): Int

Definition Classes
AnyRef → Any
final def ==(arg0: Any): Boolean

Definition Classes
AnyRef → Any
final def asInstanceOf[T0]: T0

Definition Classes
Any
def clone(): AnyRef

Attributes
protected[java.lang]
Definition Classes
AnyRef
Annotations
@throws( ... )
def currentRow: Long

Value representing the current row.
Value representing the current row. This can be used to specify the frame boundaries:
```
Window.rowsBetween(Window.unboundedPreceding, Window.currentRow)
```
Since
2.1.0
final def eq(arg0: AnyRef): Boolean

Definition Classes
AnyRef
def equals(arg0: Any): Boolean

Definition Classes
AnyRef → Any
def finalize(): Unit

Attributes
protected[java.lang]
Definition Classes
AnyRef
Annotations
@throws( classOf[java.lang.Throwable] )
final def getClass(): Class[_]

Definition Classes
AnyRef → Any
def hashCode(): Int

Definition Classes
AnyRef → Any
final def isInstanceOf[T0]: Boolean

Definition Classes
Any
final def ne(arg0: AnyRef): Boolean

Definition Classes
AnyRef
final def notify(): Unit

Definition Classes
AnyRef
final def notifyAll(): Unit

Definition Classes
AnyRef
def orderBy(cols: Column*): WindowSpec

Creates a WindowSpec with the ordering defined.
Creates a WindowSpec with the ordering defined.

Annotations
@varargs()
Since
1.4.0
def orderBy(colName: String, colNames: String*): WindowSpec

Creates a WindowSpec with the ordering defined.
Creates a WindowSpec with the ordering defined.

Annotations
@varargs()
Since
1.4.0
def partitionBy(cols: Column*): WindowSpec

Creates a WindowSpec with the partitioning defined.
Creates a WindowSpec with the partitioning defined.

Annotations
@varargs()
Since
1.4.0
def partitionBy(colName: String, colNames: String*): WindowSpec

Creates a WindowSpec with the partitioning defined.
Creates a WindowSpec with the partitioning defined.

Annotations
@varargs()
Since
1.4.0
def rangeBetween(start: Column, end: Column): WindowSpec

Creates a WindowSpec with the frame boundaries defined, from start (inclusive) to end (inclusive).
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 org.apache.spark.sql.functions to specify special boundary values, literals are not transformed to org.apache.spark.sql.catalyst.expressions.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|
+---+--------+---+
```
start
boundary start, inclusive. The frame is unbounded if the expression is org.apache.spark.sql.catalyst.expressions.UnboundedPreceding.
end
boundary end, inclusive. The frame is unbounded if the expression is org.apache.spark.sql.catalyst.expressions.UnboundedFollowing.

Since
2.3.0
def rangeBetween(start: Long, end: Long): WindowSpec

Creates a WindowSpec with the frame boundaries defined, from start (inclusive) to end (inclusive).
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|
+---+--------+---+
```
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).

Since
2.1.0
def rowsBetween(start: Long, end: Long): WindowSpec

Creates a WindowSpec with the frame boundaries defined, from start (inclusive) to end (inclusive).
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|
+---+--------+---+
```
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).

Since
2.1.0
final def synchronized[T0](arg0: ⇒ T0): T0

Definition Classes
AnyRef
def toString(): String

Definition Classes
AnyRef → Any
def unboundedFollowing: Long

Value representing the last row in the partition, equivalent to "UNBOUNDED FOLLOWING" in SQL.
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)
```
Since
2.1.0
def unboundedPreceding: Long

Value representing the first row in the partition, equivalent to "UNBOUNDED PRECEDING" in SQL.
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)
```
Since
2.1.0
final def wait(): Unit

Definition Classes
AnyRef
Annotations
@throws( ... )
final def wait(arg0: Long, arg1: Int): Unit

Definition Classes
AnyRef
Annotations
@throws( ... )
final def wait(arg0: Long): Unit

Definition Classes
AnyRef
Annotations
@throws( ... )

Inherited from AnyRef

Inherited from Any

Ungrouped