sql - COUNT() OVER 可能在 HIVE 中使用 DISTINCT 和 WINDOWING

Question

我想计算当前行和前 X 行(滑动窗口)之间存在的不同端口号的数量，其中 x 可以是任何整数。

例如，

如果输入是:

ID      PORT   
1        21
2        22  
3        23
4        25 
5        25
6        21

输出应该是:

ID      PORT    COUNT
1        21       1
2        22       2
3        23       3
4        25       4
5        25       4
6        21       4

我在 RapidMiner 上使用 Hive，我尝试了以下方法:

select id, port,
        count (*) over (partition by srcport order by id rows between 5 preceding and current row)

这必须适用于大数据并且 X 是大整数。

如有任何反馈，我们将不胜感激。

Answer 1

我认为没有简单的方法。一种方法使用 lag():

select ( (case when port_5 is not null then 1 else 0 end) +
         (case when port_4 is not null and port_4 not in (port_5) then 1 else 0 end) +
         (case when port_3 is not null and port_3 not in (port_5, port_4) then 1 else 0 end) +
         (case when port_2 is not null and port_2 not in (port_5, port_4, port_3) then 1 else 0 end) +
         (case when port_1 is not null and port_1 not in (port_5, port_4, port_3, port_2) then 1 else 0 end) +
         (case when port is not null and port not in (port_5, port_4, port_3, port_2, port_2) then 1 else 0 end)
       ) as cumulative_distinct_count
from (select t.*,
             lag(port, 5) over (partition by srcport order by id rows) as port_5,
             lag(port, 4) over (partition by srcport order by id rows) as port_4,
             lag(port, 3) over (partition by srcport order by id rows) as port_3,
             lag(port, 2) over (partition by srcport order by id rows) as port_2,
             lag(port, 1) over (partition by srcport order by id rows) as port_1
      from t
     ) t

这是一个复杂的查询，但性能应该没问题。

注意:port 和 srcport 我假设是同一件事，但这借用了您的查询。