我有一个表 (view_of_referred_events)，它存储给定页面的访问者数量。

date        country_id  referral    product_id  visitors
2016-04-01  216         pl          113759      1
2016-04-03  216         pl          113759      1
2016-04-06  216         pl          113759      13
2016-04-07  216         pl          113759      10

我想计算该产品的 30 天滚动/移动总和，即使是那些缺失的日子。所以最终结果应该是这样的:

date        country_id  referral    product_id  cumulative_visitors
2016-04-01  216         pl          113759      1
2016-04-02  216         pl          113759      1
2016-04-03  216         pl          113759      2
2016-04-04  216         pl          113759      2
2016-04-05  216         pl          113759      2
2016-04-06  216         pl          113759      15
2016-04-07  216         pl          113759      25

现在，这是一个简单的表示，因为我有数十个不同的 country_id、referral 和 product_id。我无法预先创建包含 {date、country_id、referral 和 product_id 所有可能组合的表格因为考虑到表的大小，这将变得无法处理。如果特定的 {date、country_id、referral 和 product_id<>} 以前不存在。

我在想，如果在 view_of_referred_events 中当天没有访客，是否有一种简单的方法可以告诉 Impala 使用前一行(前一天)的值。

我写了这个查询，其中 list_of_dates 是一个表，其中包含从 4 月 1 日到 4 月 7 日的天数列表。

select
  t.`date`,
  t.country_id,
  t.referral,
  t.product_id,
  sum(visitors) over (partition by t.country_id, t.referral, t.product_id order by t.`date`
                     rows between 30 preceding and current row) as cumulative_sum_visitors
from (
  selec
    d.`date`, 
    re.country_id, 
    re.referral, 
    re.product_id,
    sum(visitors) as visitors
  from list_of_dates d
  left outer join view_of_referred_events re on d.`date` = re.`date`
    and re.referral = "pl"
    and re.product_id = "113759"
    and re.country_id = "216"
  group by d.`date`, re.country_id, re.referral, re.product_id
  ) t
order by t.`date` asc;

这会返回类似于我想要的东西，但不完全是那样。

date        country_id  referral    product_id  cumulative_visitors
2016-04-01  216         pl          113759      1
2016-04-02  NULL        NULL        NULL        NULL
2016-04-03  216         pl          113759      2
2016-04-04  NULL        NULL        NULL        NULL
2016-04-05  NULL        NULL        NULL        NULL
2016-04-06  216         pl          113759      15
2016-04-07  216         pl          113759      25

最佳答案

我添加了另一个子查询以从分区的最后一行获取值。我不确定您使用的是哪个版本的 hive/impala，last_value(column_name, ignore null values true/false) 是语法。

我假设您正在尝试查找 30 天(月)的累计计数，我建议使用月份字段对行进行分组。月份可以来自您的维度表 list_of_dates 或仅来自 substr(date, 1, 7) 并获取超过 ..rows unbounded preceding 的累计访问者数量和当前行。

查询:

select
  `date`,
  country_id,
  referral,
  product_id,
  sum(visitors) over (partition by country_id, referral, product_id order by `date`
                     rows between 30 preceding and current row) as cumulative_sum_visitors 
from (select
  t.`date`,
  -- get the last not null value from the partition window w for country_id, referral & product_id
  last_value(t.country_id, true) over w as country_id,
  last_value(t.referral, true) over w as  referral
  last_value(t.product_id, true) over w as product_id 
  if(visitors = null, 0, visitors) as visitors 
from (
  select
    d.`date`, 
    re.country_id, 
    re.referral, 
    re.product_id,
    sum(visitors) as visitors
  from list_of_dates d
  left outer join view_of_referred_events re on d.`date` = re.`date`
    and re.referral = "pl"
    and re.product_id = "113759"
    and re.country_id = "216"
  group by d.`date`, re.country_id, re.referral, re.product_id
  ) t
window w as (partition by t.country_id, t.referral, t.product_id order by t.`date`
                     rows between unbounded preceding and unbounded following)) t1
order by `date` asc;

关于sql - 当前日期缺失时的 30 天滚动/移动总和，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/36862460/