title: only keep distinct rows when doing collect_set over a moving windowing function in hive
body: <p>Lets say I have a hive table that has 3 rows: merchant_id, week_id, acc_id. My goal is to collect the unique customers in the previous 4 weeks for each week and I am using a moving window to do this.</p>
  
  <p>My codes:</p>
  
  <p>create a test table:</p>
  
  <pre><code>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)
  
  </code></pre>
  
  <p>Then do the collect:</p>
  
  <pre><code>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
  
  </code></pre>
  
  <p>The result table is :</p>
  
  <pre><code>    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]
  </code></pre>
  
  <p>As you can see, there are redundant rows in the table. This is just an example, in my actual case this table is huge and the redundancy causes memory problem.</p>
  
  <p>I have tried using distinct and group by but neither of these works.</p>
  
  <p>Is there a good way to do it? Thanks a lot.</p>