hadoop - 在配置单元中的移动窗口函数上执行 collect_set 时只保留不同的行

Question

假设我有一个包含 3 行的配置单元表:merchant_id、week_id、acc_id。我的目标是每周收集前 4 周内的唯一客户，我正在使用移动窗口来执行此操作。

我的代码:

创建测试表:

CREATE TABLE table_test_test (merchant_id INT, week_id INT, acc_id INT);

INSERT INTO TABLE table_test_test VALUES  
(1,0,8),  
(1,0,9),  
(1,0,10),  
(1,2,1),  
(1,2,2),  
(1,2,4),  
(1,4,1),  
(1,4,3),  
(1,4,4), 
(1,5,1),  
(1,5,3),  
(1,5,5), 
(1,6,1),  
(1,6,5),  
(1,6,6)

然后收集:

select 
merchant_id, 
week_id, 
collect_set(acc_id) over (partition by merchant_id ORDER BY week_id RANGE BETWEEN 4 preceding AND 0 preceding) as uniq_accs_prev_4_weeks
from 
table_test_test

结果表是:

    merchant_id week_id uniq_accs_prev_4_weeks
1   1   0   []
2   1   0   []
3   1   0   []
4   1   2   [9,8,10]
5   1   2   [9,8,10]
6   1   2   [9,8,10]
7   1   4   [9,8,10,1,2,4]
8   1   4   [9,8,10,1,2,4]
9   1   4   [9,8,10,1,2,4]
10  1   5   [1,2,4,3]
11  1   5   [1,2,4,3]
12  1   5   [1,2,4,3]
13  1   6   [1,2,4,3,5]
14  1   6   [1,2,4,3,5]
15  1   6   [1,2,4,3,5]

如您所见，表格中有多余的行。这只是一个例子，在我的实际情况下，这个表很大，冗余导致内存问题。

我尝试过使用 distinct 和 group by，但这些都不起作用。

有什么好的方法吗？非常感谢。

Answer 1

不同的效果很好:

select distinct merchant_id, week_id, uniq_accs_prev_4_weeks 
  from
(
select 
merchant_id, 
week_id, 
collect_set(acc_id) over (partition by merchant_id ORDER BY week_id RANGE BETWEEN 4 preceding AND current row) as uniq_accs_prev_4_weeks
from 
table_test_test
)s;

结果:

OK
1       0       [9,8,10]
1       2       [9,8,10,1,2,4]
1       4       [9,8,10,1,2,4,3]
1       5       [1,2,4,3,5]
1       6       [1,2,4,3,5,6]
Time taken: 98.088 seconds, Fetched: 5 row(s)

我的 Hive 不接受 0 preceding，我替换为 current row。好像this bug或 this bug ，我的Hive版本是1.2。在上部子查询中添加 distinct 后，您的应该可以正常工作。