Блог

В моей таблице Hive у меня есть следующее:

 mydate,user_id,source,timestamp
20160901,10293,google,2016-09-01 00:21:06
20160901,34393,yelp,2016-09-01 05:23:00
20160901,38437,fb,2016-09-01 12:03:24
20160902,34393,google,2016-09-02 13:44:55
  

…

Источник обозначает местоположение пользователя и может быть из yelp, google, fb, bing и т. Д. — Этот реферал обозначает, какая учетная запись у пользователя. Идентификатор пользователя уникален во всей таблице, и я пытаюсь найти распределение источников за последний год.

Я мог бы сделать что-то вроде

 sum(IF(source = 'fb',1,0)) fb_count,
sum(IF(source = 'yelp',1,0)) yelp_count
... from my_table where mydate between blah and foo.
  

Однако сложность здесь в том, что пользователи могут переключать типы своих учетных записей. Например, пользователь может переключить свой тип пользователя с (скажем) google на bing после создания своей учетной записи. Итак, чтобы исправить это, мне нужно выбрать источник, соответствующий первой временной метке в таблице, которая соответствовала бы времени создания учетной записи пользователя.

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

Результат будет выглядеть так:

 month,fb_count,google_count,yelp_count,bing_count
201601,1667,3403,304,4340
201602,367,343,34,434
  

…

Я мог бы использовать самосоединение, но это было бы довольно дорого. Есть ли лучший способ?

Вы можете получить первое зарегистрированное время пользователя с помощью row_number функции window, а затем использовать условную агрегацию.

 select year(mydate),month(mydate), 
sum(IF(source = 'fb',1,0)) fb_count,
sum(IF(source = 'yelp',1,0)) yelp_count
from (select t.*, row_number() over(partition by user_id order by timestamp) rn
      from tablename t) x
where rn = 1 --add a date filter if necessary
group by year(mydate),month(mydate)