title: Hive - add column with number of distinct values in groups
body: <p>Suppose I have the following data. </p>
  
  <pre><code>number  group   
  1       a       
  1       a       
  3       a       
  4       a       
  4       a       
  5       c       
  6       b       
  6       b       
  6       b       
  7       b       
  8       b       
  9       b       
  10      b       
  14      b       
  15      b       
  </code></pre>
  
  <p>I would like to group the data by <code>group</code> and add a further column which say how many distinct values of <code>number</code> each group has.</p>
  
  <p>My desired output would look as follows:</p>
  
  <pre><code>number  group   dist_number
  1       a       3
  1       a       3
  3       a       3
  4       a       3
  4       a       3
  5       c       1
  6       b       9
  6       b       9
  6       b       9
  7       b       9
  8       b       9
  9       b       9
  10      b       9
  14      b       9
  15      b       9
  </code></pre>
  
  <p>What I have tried is:</p>
  
  <pre><code>&gt; select *, count(distinct number) over(partition by group) from numbers;
  11      11
  </code></pre>
  
  <p>As one sees, this aggregates globally and calculates the number of distinct values independently from the group.</p>
  
  <p>One thing I could do is to use <code>group by</code> as follows:</p>
  
  <pre><code>hive&gt; select *, count(distinct number) from numbers group by group;
  
  a       3
  b       7
  c       1
  </code></pre>
  
  <p>And then join over <code>group</code></p>
  
  <p>But I thought maybe there is a more easy solution to this, e.g., using the <code>over(partition by group)</code> method?</p>