title: Sum over a date range per group in Hive
body: <p>I have a table with certain dates and product types</p>
  
  <pre><code>Product  Process_Date Prod_Count
  A         2015-08-01    2
  A         2015-09-03    2
  A         2015-05-06    3
  B         2014-01-01    4
  B         2014-12-31    5
  </code></pre>
  
  <p>I am trying to compute for each row, the count of product entries in the last 6 months from the process date (including the process_date). So the output should look like</p>
  
  <pre><code>Product  Process_Date  Count
  A         2015-08-01    5
  A         2015-09-03    7
  A         2015-05-06    3
  B         2014-01-01    4
  B         2014-12-31    5
  </code></pre>
  
  <p>Currently I am doing a self-join</p>
  
  <pre><code>SELECT
  q.Product, q.Process_Date, SUM(Prod_Count) AS num_180days
  FROM tableT p
  INNER JOIN (
    SELECT DISTINCT Product, Process_Date
    FROM tableT
  ) q
  ON p.Product = q.Product
  WHERE Process_Date BETWEEN DATE_SUB(q.Process_Date, 180) AND q.Process_Date
  GROUP BY q.Product, q.Process_Date; 
  </code></pre>
  
  <p>This query takes really long since the table is pretty big. I was wondering if  there is an alternative using windowed functions to do this.</p>
  
  <p>Thanks!</p>