SQL/Pyspark query to compre each value in a group with an aggregation of the same group

user_14214659 0
Viewed 19

Suppose I have a table like this

id date value
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

What I want to get is another table which the same number of records and that for each record has an extra column with the average value of the last n records with the same id that hast just dates before the given record. For example, if n=2, for the second row, with id=1 and date=01/03 I would like to have the value of the average of value in the two rows that have the same idand are just the two dates just before 01/03, that is (10+4)/2=7. So, for this specific record, I would have:

id date value avg last 2 records same group
1 01/04 7 6
1 01/03 2 7
1 01/02 10 4
1 01/01 4 null
2 01/04 7 7.5
2 01/03 7 6.5
2 01/02 8 5
2 01/01 5 null

My question is how to implement this in relational (SQL) logic (also implementations in Pyspark would help a lot).

Thanks in advance

pyspark logic group-by sql
1 Answers

You can do so with a window function, partitioning by id and sorting by date.

Setup

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|
+---+-----+-----+----+
user_47003270
Related