如何在PySpark中使用窗口功能? [英] How to use window functions in PySpark?

问题描述

我正在尝试将某些Windows函数(ntile和percentRank)用于数据框，但是我不知道如何使用它们.

I'm trying to use some windows functions (ntile and percentRank) for a data frame but I don't know how to use them.

有人可以帮我吗?在 Python中API文档，没有关于它的示例.

Can anyone help me with this please? In the Python API documentation there are no examples about it.

具体来说，我正在尝试获取数据框中数字字段的分位数.

Specifically, I'm trying to get quantiles of a numeric field in my data frame.

我正在使用spark 1.4.0.

I'm using spark 1.4.0.

推荐答案

要使用窗口功能，您必须先创建一个窗口.定义与普通SQL几乎相同，这意味着您可以定义顺序，分区或同时定义两者.首先让我们创建一些虚拟数据:

To be able to use window function you have to create a window first. Definition is pretty much the same as for normal SQL it means you can define either order, partition or both. First lets create some dummy data:

import numpy as np
np.random.seed(1)

keys = ["foo"] * 10 + ["bar"] * 10
values = np.hstack([np.random.normal(0, 1, 10), np.random.normal(10, 1, 100)])

df = sqlContext.createDataFrame([
   {"k": k, "v": round(float(v), 3)} for k, v in zip(keys, values)])

确保您使用的是HiveContext(仅限Spark< 2.0):

Make sure you're using HiveContext (Spark < 2.0 only):

from pyspark.sql import HiveContext

assert isinstance(sqlContext, HiveContext)

创建一个窗口:

from pyspark.sql.window import Window

w =  Window.partitionBy(df.k).orderBy(df.v)

等效于

(PARTITION BY k ORDER BY v) 

在SQL中为

.

in SQL.

通常，窗口定义应始终包含PARTITION BY子句，否则Spark会将所有数据移至单个分区.某些功能需要ORDER BY，而在不同情况下(通常是聚合)可能是可选的.

As a rule of thumb window definitions should always contain PARTITION BY clause otherwise Spark will move all data to a single partition. ORDER BY is required for some functions, while in different cases (typically aggregates) may be optional.

还有两个可用于定义窗口范围的可选项-ROWS BETWEEN和RANGE BETWEEN.在这种特定情况下，这些对我们没有用.

There are also two optional which can be used to define window span - ROWS BETWEEN and RANGE BETWEEN. These won't be useful for us in this particular scenario.

最后，我们可以将其用于查询:

Finally we can use it for a query:

from pyspark.sql.functions import percentRank, ntile

df.select(
    "k", "v",
    percentRank().over(w).alias("percent_rank"),
    ntile(3).over(w).alias("ntile3")
)

请注意，ntile与分位数没有任何关系.

Note that ntile is not related in any way to the quantiles.

这篇关于如何在PySpark中使用窗口功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！