蜂箱内累积计数明显

Question

下面是表daily_user中的一些示例数据。每行代表一个特定日子的活跃用户，收入是基于该用户当天产生的钱。表中最早的日期是1/1。

date user_id group revenue  
1/1  1       a     1  
1/1  2       b     0  
1/1  3       a     0  
1/2  2       b     10  
1/2  3       a     0  
1/3  3       a     1  

我想要的输出(基本上，每一行告诉我从1/1到每个观察日期，每个组有多少用户曾经付费。例如，最后一行表示从1/1到1/3，对于组b，我们总共有1个用户向我们付费):

end_date    group     # users who ever paid  
1/1          a             1  
1/1          b             0   
1/2          a             1  
1/2          b             1  
1/3          a             2  
1/3          b             1  

似乎有一些udf可以做累积求和，但我不确定是否有任何累积不同的计数函数，我可以在这里利用。是否有任何方式构造一个hive查询来实现这一点?

Answer 1

我认为解决方案是实际上'collect_set'的用户(收集唯一的值)，并采取数组的大小，为少数用户(即。这将适合内存)

SELECT size( collect_set( user_id ) ) as uniques
       end_date, group
FROM daily_user
GROUP BY end_date, group;

对于大量的唯一，您将需要一个概率数据结构，如草图集或hyperloglog，可以从Brickhouse库(http://github.com/klout/brickhouse)获得UDF。这将为您提供一个接近的估计，但不是唯一

的确切数量。

SELECT estimated_reach( sketch_set( user_id )) as uniques_est,
       end_date, group
FROM daily_user
GROUP BY end_date, group;

你也可以合并这些，这样就可以合并前几天预计算的集合/草图。例如:

SELECT size(combine_unique( unique_set ) ) as uniques,
      group
FROM daily_uniques
WHERE end_date > date_add( today, -30 )
GROUP BY group;

或

SELECT estimated_reach( union_sketch( unique_sketch) ) as uniques,
       group
FROM daily_uniques
WHERE end_date > date_add( today, -30 )
GROUP BY group;

Answer 2

如果收益为0，函数if(revenue=0,1,0)的值为1，否则为0。将这个函数相加，你会得到收入为0的总人数:

select
  date as end_date,
  group,
  sum(if(revenue=0,1,0)) as number_of_users_who_never_paid
from
  daily_user
group by
  date,
  group

Answer 3

不需要编写自定义UDF的最简单方法是执行某种笛卡尔连接:

select
  date as end_date,
  group,
  sum(if(mon.user_id is not null AND mon.date <= du.date,1,0)) as cumulative_spenders
from
  daily_user du
LEFT OUTER JOIN
  (
   select
     distinct
     user_id,
     date,
     group
   from
     daily_user 
   where
     revenue > 0
   ) mon
ON
  (du.user_id=mon.user_id and du.group=mon.group)
group by
  date,
  group    

这将在原始表中为每个条目生成一行，然后从那里进行聚合。