title: hive sql aggregate
body: <p>I have two tables in Hive, <code>t1</code> and <code>t2</code></p>
  
  <pre><code>&gt;describe t1;
  &gt;date_id    string
  
  &gt;describe t2;
  &gt;messageid string,
   createddate string,
   userid int
  
  &gt; select * from t1 limit 3;        
  &gt; 2011-01-01 00:00:00 
    2011-01-02 00:00:00 
    2011-01-03 00:00:00 
  
  &gt; select * from t2 limit 3;
  87211389    2011-01-03 23:57:01 13864753
  87211656    2011-01-03 23:57:59 13864769
  87211746    2011-01-03 23:58:25 13864785
  </code></pre>
  
  <p>What I want is to count previous three-day distinct userid for a given date.<br>
  For example, for date <code>2011-01-03</code>, I want to count distinct userid from <code>2011-01-01</code> to <code>2011-01-03</code>.<br>
  for date <code>2011-01-04</code>, I want to count distinct userid from <code>2011-01-02</code> to <code>2011-01-04</code></p>
  
  <p>I wrote the following query. But it does not return three-day result. It returns distinct userid per day instead.</p>
  
  <pre><code>SELECT to_date(t1.date_id), count(distinct t2.userid) FROM t1 JOIN t2 
  ON (to_date(t2.createddate) = to_date(t1.date_id))  
  WHERE date_sub(to_date(t2.createddate),0) &gt; date_sub(to_date(t1.date_id), 3) 
  AND to_date(t2.createddate) &lt;= to_date(t1.date_id) 
  GROUP by to_date(t1.date_id);
  
  `to_date()` and `date_sub()` are date function in Hive. 
  </code></pre>
  
  <p>That said, the following part does not take effect.</p>
  
  <pre><code>WHERE date_sub(to_date(t2.createddate),0) &gt; date_sub(to_date(t1.date_id), 3) 
  AND to_date(t2.createddate) &lt;= to_date(t1.date_id) 
  </code></pre>
  
  <p>EDIT: One solution can be (but it is super slow):</p>
  
  <pre><code>SELECT to_date(t3.date_id), count(distinct t3.userid) FROM
  (
   SELECT * FROM t1  LEFT OUTER JOIN t2
   WHERE 
   (date_sub(to_date(t2.createddate),0) &gt; date_sub(to_date(t1.date_id), 3)
    AND to_date(t2.createddate) &lt;= to_date(t1.date_id)
   )
  ) t3 
  GROUP by to_date(t3.date_id);
  </code></pre>
  
  <p>UPDATE: Thanks for all answers. They are good.<br>
  But Hive is a bit different from SQL. Unfortunately, they cannot use in HIVE.
  My current solution is to use <code>UNION ALL</code>.</p>
  
  <pre><code> SELECT * FROM t1 JOIN t2 ON (to_date(t1.date_id) = to_date(t2.createddate))
   UNION ALL
   SELECT * FROM t1 JOIN t2 ON (to_date(t1.date_id) = date_add(to_date(t2.createddate), 1)
   UNION ALL 
   SELECT * FROM t1 JOIN t2 ON (to_date(t1.date_id) = date_add(to_date(t2.createddate), 2)
  </code></pre>
  
  <p>Then, I do <code>group by</code> and <code>count</code>. In this way, I can get what I want.<br>
  Although it is not elegant, it is much efficient than <code>cross join</code>.</p>