title: Efficient GROUP BY a CASE expression in Amazon Redshift/PostgreSQL
body: <p>In analytics processing there is often a need to collapse "unimportant" groups of data into a single row in the resulting table. One way to do this is to GROUP BY a CASE expression where unimportant groups are coalesced into a single row via the CASE expression returning a single value, e.g., NULL for the groups. This question is about efficient ways to perform this grouping in Amazon Redshift, which is based on ParAccel: close to PosgreSQL 8.0 in terms of functionality. </p>
  
  <p>As an example, consider a GROUP BY on <code>type</code> and <code>url</code> in a table where each row is a single URL visit. The goal is to perform aggregation such that one row is emitted for every (type, url) pair where the URL visit count exceeds a certain threshold and one (type, NULL) row is emitted for <em>all</em> (type, url) pairs where the visit count is under that threshold. The rest of the columns in the result table would have SUM/COUNT aggregates based on this grouping.</p>
  
  <p>For example, the following data</p>
  
  <pre><code>+------+----------------------+-----------------------+
  | type | url                  | &lt; 50+ other columns &gt; |
  +------+----------------------+-----------------------+
  |  A   | http://popular.com   |                       |
  |  A   | http://popular.com   |                       |
  |  A   | &lt; 9997 more times&gt;   |                       |
  |  A   | http://popular.com   |                       |
  |  A   | http://small-one.com |                       |
  |  B   | http://tiny.com      |                       |
  |  B   | http://tiny-too.com  |                       |
  </code></pre>
  
  <p>should produce the following result table with a threshold of 10,000</p>
  
  <pre><code>+------+------------------------------------+--------------------------+
  | type | url                  | visit_count | &lt; SUM/COUNT aggregates &gt; |
  +------+------------------------------------+--------------------------+
  |  A   | http://popular.com   |       10000 |                          |
  |  A   |                      |           1 |                          |
  |  B   |                      |           2 |                          |
  </code></pre>
  
  <p><strong>Summary:</strong></p>
  
  <p>Amazon Redshift has certain subquery correlation limitations one needs to tip-toe around. Gordon Linoff's answer below (the accepted answer) shows how to perform a GROUP BY a CASE expression using double aggregation and replicating the expression in both the result column and the outer GROUP BY clause.</p>
  
  <pre><code>with temp_counts as (SELECT type, url, COUNT(*) as cnt FROM t GROUP BY type, url)
  select type, (case when cnt &gt;= 10000 then url end) as url, sum(cnt) as cnt
  from temp_counts
  group by type, (case when cnt &gt;= 10000 then url end)
  </code></pre>
  
  <p>Further testing indicated that the double aggregation can be "unrolled" into a UNION ALL of independent queries involving each independent CASE expression. In this particular case on a sample data set with approximately 200M rows, this approach consistently performed about 30% faster. That result is schema and data-specific, however.</p>
  
  <pre><code>with temp_counts as (SELECT type, url, COUNT(*) as cnt FROM t GROUP BY type, url)
  select * from temp_counts WHERE cnt &gt;= 10000
  UNION ALL
  SELECT type, NULL as url, SUM(cnt) as cnt from temp_counts 
  WHERE cnt &lt; 10000 
  GROUP BY type
  </code></pre>
  
  <p>This suggests two general patterns for implementing and optimizing arbitrary disjoined grouping &amp; summarization in Amazon Redshift. If performance is important for you, benchmark both.</p>