выберите с помощью оконной функции (dens_rank ()) в SparkSQL

Question

выберите с помощью оконной функции (dens_rank ()) в SparkSQL

0

У меня есть таблица, которая содержит записи для покупок клиентов. Мне нужно указать, что покупка была сделана в определенном окне даты и времени, одно окно - 8 дней, поэтому, если бы у меня была покупка сегодня, а через 5 дней моя покупка была моей покупкой, если бы номер окна 1, но если я сделал это в первый день сегодня, а затем через 8 дней, первая покупка будет в окне 1 и последняя покупка в окне 2

create temporary table transactions
 (client_id int,
 transaction_ts datetime,
 store_id int)

 insert into transactions values 
 (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)

окно составляет 8 дней, проблема в том, что я не понимаю, как указать для dense_rank() OVER (PARTITION BY), чтобы посмотреть на datetime и сделать окно за 8 дней, в результате мне нужно что-то вроде этого

1,'2018-06-01 12:17:37', 1,1
1,'2018-06-02 13:17:37', 2,1
1,'2018-06-03 14:17:37', 3,1
1,'2018-06-09 10:17:37', 2,2
2,'2018-06-02 10:17:37', 1,1
2,'2018-06-02 13:17:37', 2,1
2,'2018-06-08 14:19:37', 3,2
2,'2018-06-16 13:17:37', 2,3
2,'2018-06-17 14:17:37', 3,3

любая идея, как ее получить? Я могу запустить его в Mysql или Spark SQL, но Mysql не поддерживает раздел. Все еще не можете найти решение! любая помощь

Andrey 25 июль 2018, в 08:16

Источник

0

Чтобы делать то, что вы хотите, вам нужны рекурсивные CTE. MySQL поддерживает их в версии 8+. Я не знаю, поддерживает ли SparkSQL их.
Gordon Linoff 25 июль 2018, в 11:40
0

@GordonLinoff есть идеи, как это сделать? Я знаю концепцию CTE, но не знаю, как ее использовать. в этом случае
Andrey 25 июль 2018, в 20:33

Теги:

mysql

sql

apache-spark-sql

dense-rank

2 ответа

0

Я не использовал предложенное искровое решение, я сделал это с чистой логикой и курсором sql. это не очень эффективно, но мне нужно сделать работу

Andrey 27 июль 2018, в 01:32

Ещё вопросы

Чтобы делать то, что вы хотите, вам нужны рекурсивные CTE. MySQL поддерживает их в версии 8+. Я не знаю, поддерживает ли SparkSQL их.
@GordonLinoff есть идеи, как это сделать? Я знаю концепцию CTE, но не знаю, как ее использовать. в этом случае

Mikhail Dubkov · Accepted Answer · 2018-07-26T18-25-00.000Z

Скорее всего, вы можете решить эту проблему в Spark SQL, используя функции окна времени и раздела:

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



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-28 17:00:00.0,2018-06-05 17:00:00.0]|[2018-06-01 12:17:37, 2018-06-02 13:17:37, 2018-06-03 14:17:37]|[1, 2, 3]|1            |
|1        |[2018-06-05 17:00:00.0,2018-06-13 17:00:00.0]|[2018-06-09 10:17:37]                                          |[2]      |2            |
|2        |[2018-05-28 17:00:00.0,2018-06-05 17:00:00.0]|[2018-06-02 10:17:37, 2018-06-02 13:17:37]                     |[1, 2]   |1            |
|2        |[2018-06-05 17:00:00.0,2018-06-13 17:00:00.0]|[2018-06-08 14:19:37]                                          |[3]      |2            |
|2        |[2018-06-13 17:00:00.0,2018-06-21 17:00:00.0]|[2018-06-16 13:17:37, 2018-06-17 14:17:37]                     |[2, 3]   |3            |
+---------+---------------------------------------------+---------------------------------------------------------------+---------+-------------+

Если вам нужно, вы можете explode элементы списка из store_ids или transaction_tss.

Надеюсь, поможет!