Select With Window Function (dense_rank()) In Sparksql

I have a table which contains records for customer purchases, I need to specify that purchase was made in specific datetime window one window is 8 days , so if I had purchase today

Solution 1:

Most likely you may solve this in Spark SQL using time and partition window functions:

val purchases = Seq((1,"2018-06-01 12:17:37", 1), (1,"2018-06-02 13:17:37", 2), (1,"2018-06-03 14:17:37", 3), (1,"2018-06-09 10:17:37", 2), (2,"2018-06-02 10:17:37", 1), (2,"2018-06-02 13:17:37", 2), (2,"2018-06-08 14:19:37", 3), (2,"2018-06-16 13:17:37", 2), (2,"2018-06-17 14:17:37", 3)).toDF("client_id", "transaction_ts", "store_id")

purchases.show(false)
+---------+-------------------+--------+|client_id|transaction_ts     |store_id|+---------+-------------------+--------+|1|2018-06-0112:17:37|1||1|2018-06-0213:17:37|2||1|2018-06-0314:17:37|3||1|2018-06-0910:17:37|2||2|2018-06-0210:17:37|1||2|2018-06-0213:17:37|2||2|2018-06-0814:19:37|3||2|2018-06-1613:17:37|2||2|2018-06-1714:17:37|3|+---------+-------------------+--------+



val groupedByTimeWindow = purchases.groupBy($"client_id", window($"transaction_ts", "8 days")).agg(collect_list("transaction_ts").as("transaction_tss"), collect_list("store_id").as("store_ids"))

val withWindowNumber = groupedByTimeWindow.withColumn("window_number", row_number().over(windowByClient))

withWindowNumber.orderBy("client_id", "window.start").show(false)

    +---------+---------------------------------------------+---------------------------------------------------------------+---------+-------------+|client_id|window|transaction_tss                                                |store_ids|window_number|+---------+---------------------------------------------+---------------------------------------------------------------+---------+-------------+|1|[2018-05-2817:00:00.0,2018-06-0517:00:00.0]|[2018-06-0112:17:37, 2018-06-0213:17:37, 2018-06-0314:17:37]|[1, 2, 3]|1||1|[2018-06-0517:00:00.0,2018-06-1317:00:00.0]|[2018-06-0910:17:37]                                          |[2]      |2||2|[2018-05-2817:00:00.0,2018-06-0517:00:00.0]|[2018-06-0210:17:37, 2018-06-0213:17:37]                     |[1, 2]   |1||2|[2018-06-0517:00:00.0,2018-06-1317:00:00.0]|[2018-06-0814:19:37]                                          |[3]      |2||2|[2018-06-1317:00:00.0,2018-06-2117:00:00.0]|[2018-06-1613:17:37, 2018-06-1714:17:37]                     |[2, 3]   |3|+---------+---------------------------------------------+---------------------------------------------------------------+---------+-------------+

If you need, you may explode list elements from store_ids or transaction_tss.

hope it helps!

Solution 2:

I didnt use spark solution proposed , i did this with pure sql logic and cursor. its not very efficient but i need to job be done