sql - 汇总 hive 中的每周数据

Question

我想根据以下查询中指定的条件，在过去3个月中每周汇总一次帐户计数。在以num_of_accounts和几周为列的表中获取此数据的最有效方法是什么。

select COUNT(DISTINCT a.account_id) as num_accounts,
WEEKOFYEAR(a.ds) as week
FROM
    (SELECT
    CAST(account_id as BIGINT)
    FROM
    tableA
    WHERE ds='2013-12-28') a
JOIN   
    tableB b
ON a.account_id=b.account_id AND
    b.ds='2013-12-28'
WHERE
b.invoice_date between '2013-12-22' AND '2013-12-28' AND
b.payment_status = 'failed' AND b.payment_status = 'unbilled'

Answer 1

您正在尝试对一大组进行唯一计数。一种可扩展的方法是使用概率数据结构，例如 super 日志或KMV草图集，就像Brickhouse(http://github.com/klout/brickhouse)中提供的那样。在http://brickhouseconfessions.wordpress.com/2013/12/11/using-sketch_set-for-reach-estimation/上有一篇博客文章描述了一种与您一样的情况。这应该给您一个相当接近的估计，而不必完全重新使用您的数据。

如果我对您的理解正确，那么您只想按周汇总，就可以使用Hive UDF WEEKOFYEAR从日期字符串返回一周。只需使用Brickhouse的sketch_set UDAF

SELECT WEEKOFYEAR( ds), estimated_reach( sketch_set( account_id ) ) as num_account_est
  FROM myquery
GROUP BY WEEKOFYEAR( ds);

其中myquery是表示您在上面表达的业务逻辑的 View 。