如何计算日平均频率？

Question

我有这个表my_table：

recorder_id person_id  day 
A1    1    2017-06-03 12:30 
A1    1    2017-06-03 12:45 
B1    1    2017-06-03 12:50 
A1    2    2017-06-03 16:40 
B1    2    2017-06-03 16:45 
B1    2    2017-06-03 18:20 
A1    1    2017-06-04 11:22 

我想知道每个人多少次经过平均每天每个记录。例如，ID为1的人平均每天经过记录器A1 1.5次，而人2平均每天经过该记录器0.5次（因为该人没有2017-06-04的记录）。 B1应用相同的逻辑。

recorder_id person_id daily_average_per_person 
A1   1   1.5 
A1   2   0.5 
B1   1   0.5 
B1   2   1.0 

我该如何得到这个结果？

我尝试此查询，但我不知道该怎么每个独特的人计算，平均每天：

SELECT recorder_id, person_id, 
     to_date(day) as hour, 
     count(*) as hourly_count 

FROM  my_table 

GROUP BY recorder_id, person_id, to_date(day) 

ORDER BY hourly_count; 

Answer 1

如果我理解正确的话，你只需要几天的数据的数量。这成为分母：

SELECT recorder_id, person_id, 
     count(*)/numdays 
FROM t CROSS JOIN 
    (SELECT COUNT(DISTINCT to_date(day)) as numdays 
     FROM t 
    ) tt 
GROUP BY recorder_id, person_id, numdays 
ORDER BY recorder_id, person_id; 

在其他数据库，你可以使用COUNT(DISTINCT)作为窗口函数。我不认为Hive支持这一点。

Answer 2

你真的很亲密。我会使用一个子选择的这一个：

SELECT recorder_id, person_id, avg(day_count) day_avg 
    FROM 
     (SELECT recorder_id, person_id, 
       to_date(day) as record_day, 
       count(*) as day_count 
      FROM my_table 
      GROUP BY recorder_id, person_id, to_date(day)) tmp_tbl 
GROUP BY recorder_id, person_id 
ORDER BY avg(day_count); 

我很抱歉，我不是在一个地方，我可以测试这一点，但它应该让你正确的道路。

祝你好运！