是否有任何方法可以在presto查询中应用循环

Question

我的用例是使用表顶部的以下presto视图，通过用昨天的值减去今天的值来获得每日计数。如果表中没有数据，则视图应动态考虑第二天的值，然后取缺失日期视图的平均值。

这是预存查询。我在下面的查询中只取了一个字段

CREATE OR REPLACE VIEW hive.facebook.post_metrics_daily AS
SELECT
a.post_id,
a.page,
a.dt,
a.created_time,
(
COALESCE(
(
CAST(a.likes AS integer) 
- IF(
(CAST(b.likes AS integer) IS NULL), 
0, 
CAST(b.likes AS integer)
)
)
, 0
)
) likes
FROM
hive.facebook.post_metrics a                 
LEFT JOIN hive.facebook.post_metrics b 
ON  a.dt = (b.dt + INTERVAL  '+1' DAY)
AND a.post_id = b.post_id 
AND a.brandname = b.brandname
WHERE a.dt = date'2019-09-10'

如果数据是第9天到第12天的数据，而第10天的数据丢失，则视图应采用第11天的数据并采用第9天和第11天之间的平均值来给出第10个视图。如何做到这一点？这个公式可以应用于查询吗？如果可以，如何应用？

(today-yesterday)/n+1，其中n将是丢失的天数。

这是点赞的示例数据。如果缺少点赞，我需要平均点赞，并且应该通过查询动态识别缺少的天数。。

Date   Likes-org.  missing likes daily likes org.   expected likes
2019-10-17  20487   20487         20487                  20487
2019-10-18  25384   25384          4897                  4897
2019-10-19  26817   26817          1433                  1433
2019-10-20  27499   missing likes   682                   257
2019-10-21  27854   missing likes   355                   258
2019-10-22  27987   missing likes   133                   258
2019-10-23  28065   missing likes   78                    258
2019-10-24  28106   28106           41                    258
2019-10-25  28134   28134           28                     28

Answer 1

我想你只想要lag():

SELECT pm.*,
(pm.likes +
LAG(pm.likes) OVER (PARTITION BY pm.post_id, pm.brand_name ORDER BY pm.dt)
) / 2
FROM hive.facebook.post_metrics pm ;

如果要将丢失的天数视为0，则需要进行日期运算。我认为这将是：

SELECT pm.*,
( (pm.likes +
LAG(pm.likes) OVER (PARTITION BY pm.post_id, pm.brand_name ORDER BY pm.dt)
) /
DATE_DIFF(day,
LAG(pm.dt) OVER (PARTITION BY pm.post_id, pm.brand_name ORDER BY pm.dt),
dt
)
)
FROM hive.facebook.post_metrics pm ;

如果您希望在某一天使用此表达式，请使用子查询或上面的表达式，然后在外部查询中进行筛选。