优化重复窗口子句

llew8vvj 于 2021-06-29 发布在 Hive

关注(0)|答案(2)|浏览(328)

我有以下hql

SELECT count(*) OVER (PARTITION BY identity.hwid, passwordused.domain ORDER BY event.time ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) pocet,
       min(event.time) OVER (PARTITION BY identity.hwid, passwordused.domain ORDER BY event.time ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) minTime,
       max(event.time) OVER (PARTITION BY identity.hwid, passwordused.domain ORDER BY event.time ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) maxTime 
FROM t21_pam6

如何将3个相同的窗口子句定义为一个？
文件(https://cwiki.apache.org/confluence/display/hive/languagemanual+windowingandanalytics )显示此示例

SELECT a, SUM(b) OVER w
FROM T;
WINDOW w AS (PARTITION BY c ORDER BY d ROWS UNBOUNDED PRECEDING)

但我觉得没用。不可能将窗口w定义为。。。不是hql命令。

Hive hql window clause

来源：https://stackoverflow.com/questions/37789422/hive-hql-optimizing-repetitive-window-clause

2条答案

按热度按时间

ie3xauqp1#

这种类型的优化是编译器需要做的事情。我不认为有一种方法可以通过编程来确保这一点。
也就是说，最短时间的计算是完全没有必要的。因为 order by ，应该是当前行中的时间。同样，如果你能处理 null 值，则表达式可以简化为：

SELECT count(*) OVER (PARTITION BY identity.hwid, passwordused.domain ORDER BY event.time ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) pocet,
       event.time as minTime,
       lead(event.time, 2) OVER (PARTITION BY identity.hwid, passwordused.domain ORDER BY event.time) as maxTime 
FROM t21_pam6;

请注意 maxtime 计算略有不同，因为它将返回 NULL 最后两个匹配条件的值。

赞(0）回复(0）举报 2021-06-29

dl5txlt92#

正如@sergey khudyakov所回应的，文档中有一个bug。此变体工作正常：

SELECT count(*) OVER w,
          min(event.time) OVER w,
          max(event.time) OVER w 
   FROM ar3.t21_pam6
   WINDOW w AS (PARTITION BY identity.hwid, passwordused.domain ORDER BY      event.time ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING)

赞(0）回复(0）举报 2021-06-29

我来回答

优化重复窗口子句

2条答案

相关问题

热门标签

最新问答