SQL/Pyspark查询以组合同一组的聚合中的每个值

Question

假设我有一个类似此

ID	日期	值
1	01/04	7
1	01/03	2
1	01/02	10
1	01/01	4
2	01/01/04	7
2	01/01/03	7
2	01/02	8
2	01/01	5

我要获得的是另一个表，该表具有相同数量的记录，每个记录都有一个额外的列，其中最后一个 n 记录的平均值具有相同的 id 这只是在给定记录之前的日期。例如，如果 n = 2 对于第二行，则使用 id = 1 和 date = 01/03 我想拥有在具有相同 ID 的两个行中 value 的平均值，并且只是 01/03 之前的两个日期（10+4）/2 = 7。因此，对于此特定记录，我将拥有：

ID	日期	值	AVG最后2个记录相同组
1	01/04	7	6
1	01/01/03	2	7
1	01/02	10	4
1	01/01	4	NENULL
2	01/04	7	7.5 7.5 7.5 7.5
2	01/03	7	6.5
2	01/02	8	5
2	01/01	5	我的问题

是如何以关系（SQL）逻辑实现此问题（Pyspark中的实现也有很大帮助）。

提前致谢

Answer 1

您可以使用，通过 ID 进行分区，并通过 date 进行排序。

设置

from pyspark.sql import functions as F
from pyspark.sql import Window

df = spark.createDataFrame(
    [(1, '01/04', 7),
    (1, '01/03', 2),
    (1, '01/02', 10),
    (1, '01/01', 4),
    (2, '01/04', 7),
    (2, '01/03', 7),
    (2, '01/02', 8),
    (2, '01/01', 5)],
    ['id', 'date', 'value']
)
df.createOrReplaceTempView('sample_data')

Pyspark（DataFrame API）

w = Window().partitionBy('id').orderBy('date').rowsBetween(-2, -1)

df = df.withColumn('avg', F.avg('value').over(w))

df.orderBy(F.col('id').asc(), F.col('date').desc()).show()

+---+-----+-----+----+
| id| date|value| avg|
+---+-----+-----+----+
|  1|01/04|    7| 6.0|
|  1|01/03|    2| 7.0|
|  1|01/02|   10| 4.0|
|  1|01/01|    4|null|
|  2|01/04|    7| 7.5|
|  2|01/03|    7| 6.5|
|  2|01/02|    8| 5.0|
|  2|01/01|    5|null|
+---+-----+-----+----+

SQL

spark.sql(
    '''
    SELECT
    id,
    date,
    value,
    AVG(value) OVER (PARTITION BY id ORDER BY date ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS avg
    FROM sample_data
    ORDER BY id, date DESC
    ''').show()

+---+-----+-----+----+
| id| date|value| avg|
+---+-----+-----+----+
|  1|01/04|    7| 6.0|
|  1|01/03|    2| 7.0|
|  1|01/02|   10| 4.0|
|  1|01/01|    4|null|
|  2|01/04|    7| 7.5|
|  2|01/03|    7| 6.5|
|  2|01/02|    8| 5.0|
|  2|01/01|    5|null|
+---+-----+-----+----+