蜂巢-分层组上的多个(平均)计数差异 [英] Hive - multiple (average) count distincts over layered groups

问题描述

给出以下源数据(假设表名称为user_activity):

Given the following source data (say the table name is user_activity):

+---------+-----------+------------+
| user_id | user_type | some_date  |
+---------+-----------+------------+
| 1       | a         | 2018-01-01 |
| 1       | a         | 2018-01-02 |
| 2       | a         | 2018-01-01 |
| 3       | a         | 2018-01-01 |
| 4       | b         | 2018-01-01 |
| 4       | b         | 2018-01-02 |
| 5       | b         | 2018-01-02 |
+---------+-----------+------------+

我想得到以下结果:

+-----------+------------+---------------------+
| user_type | user_count | average_daily_users |
+-----------+------------+---------------------+
| a         | 3          | 2                   |
| b         | 2          | 1.5                 |
+-----------+------------+---------------------+

使用单个查询，但在同一表上没有多个子查询.

using a single query without multiple subqueries on the same table.

使用多个查询，我可以获得:

Using multiple queries, I can get:

• user_count:

select
  user_type,
  count(distinct user_id)
from user_activity
group by user_type

对于average_daily_users:

select
  user_type,
  avg(distinct_users) as average_daily_users
from (
  select
    count(distinct user_id) as distinct_users
  from user_activity
  group by user_type, some_date
)
group by user_type

但是我似乎无法一次性编写出满足我想要的查询.我担心多个子查询对同一个表的性能产生影响(它将不得不对表进行两次扫描...对吗?)我有一个相当大的数据源，并且希望最大程度地减少运行时间.

But I can't seem to write a query that does what I want in one go. I am concerned about the performance impact of multiple subqueries on the same table (it will have to scan through the table twice... right?) I have a rather large data source and would like to minimize running time.

注意:该问题的标题为Hive，因为这是我正在使用的，但是我认为这是一个足够普通的SQL问题，因此我不排除使用其他语言的答案.

注意2:此问题与.

NOTE2: This question shares details with my other question on partition by columns in window functions (for computing the average daily users column).

推荐答案

这应该做您想要的:

select ua.user_type,
       count(distinct ua.user_id) as user_count,
       count(distinct some_date || ':' || ua.user_id) / count(distinct some_date)
from user_activity ua
group by ua.user_type;

这篇关于蜂巢-分层组上的多个(平均)计数差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！