Невозможно COUNT DISTINCT с помощью WINDOW-функций (Spark SQL)

Допустим, у меня есть образец набора данных (таблица 1), как показано ниже —

Таблица 1

Здесь один клиент может использовать несколько токенов, а один токен может использоваться несколькими клиентами. Я пытаюсь получить для каждого токена, клиента и даты создания записи количество клиентов, использовавших этот токен до даты создания.

Когда я пытаюсь выполнить этот запрос в Spark SQL, я получаю следующую ошибку:

Вариант 1 (коррелированный подзапрос)

SELECT 
t1.token, 
t1.customer_id, 
t1.creation_date,
(SELECT COUNT(DISTINCT t2.customer_id) FROM Table 1  t2
AND t1.token = t2.token 
AND t2.creation_date < t1.creation_date) cust_cnt
FROM Table 1  t1;

Ошибка: Коррелированный столбец не допускается в предикате, отличном от равенства.

Вариант 2 (перекрёстное соединение)

SELECT 
t1.token, 
t1.customer_id, 
t1.creation_date, 
COUNT(DISTINCT t2.customer_id) AS cust_cnt
FROM Table 1 t1, Table 1 t2
WHERE t1.token = t2.token
AND t2.creation_date < t1.creation_date 
GROUP BY t1.token, t1.customer_id, t1.creation_date;

Проблема: длительный запрос, так как таблица 1 содержит миллионы строк.

Есть ли обходной путь (например, с использованием функции окна) для оптимизации этого запроса в Spark SQL? Примечание: оконные функции не допускают отдельного подсчета.


sql apache-spark-sql count distinct-values distinct
person Anirban Majumder    schedule 13.06.2021    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Подсчитайте первое появление клиента:

SELECT t1.token, t1.customer_id, t1.creation_date,
       SUM(CASE WHEN seqnum = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY token ORDER BY creation_date) as cust_cnt
FROM (SELECT t1.*,
             ROW_NUMBER() OVER (PARTITION BY token, customer_id ORDER BY creation_date) as seqnum
      FROM Table1  t1
     ) t1;

Примечание. Это также учитывает текущую строку. Я предполагаю, что это приемлемо для того, что вы хотите сделать.

person Gordon Linoff    schedule 13.06.2021