在Amazon /PostgreSQL中按案例表达式进行有效分组

Question

在分析处理中，通常需要将“不重要”的数据组折叠为结果表中的一行。这样做的一种方法是通过返回单个值的CASE表达式将不重要的组合并成单个行，例如，组为NULL。这个问题是关于在Amazon中执行这个分组的有效方法，它基于ParAccel:在功能上接近PosgreSQL 8.0。

例如，考虑表中的type和url上的GROUP，其中每一行都是单个URL访问。目标是执行聚合，以便为URL访问计数超过某个阈值的每一对(type，url)对发出一行，对于访问次数低于该阈值的所有(type，url)对发出一个(类型，空)行。结果表中的其余列将基于此分组具有SUM/COUNT聚合。

例如，以下数据

+------+----------------------+-----------------------+
| type | url                  | < 50+ other columns > |
+------+----------------------+-----------------------+
|  A   | http://popular.com   |                       |
|  A   | http://popular.com   |                       |
|  A   | < 9997 more times>   |                       |
|  A   | http://popular.com   |                       |
|  A   | http://small-one.com |                       |
|  B   | http://tiny.com      |                       |
|  B   | http://tiny-too.com  |                       |

应生成阈值为10,000的下列结果表

+------+------------------------------------+--------------------------+
| type | url                  | visit_count | < SUM/COUNT aggregates > |
+------+------------------------------------+--------------------------+
|  A   | http://popular.com   |       10000 |                          |
|  A   |                      |           1 |                          |
|  B   |                      |           2 |                          |

摘要：

Amazon有一定的子查询相关性限制，需要小心处理。Gordon下面的答案(可接受的答案)展示了如何使用双重聚合并在结果列和外部GROUP BY子句中复制表达式，通过案例表达式来执行组。

with temp_counts as (SELECT type, url, COUNT(*) as cnt FROM t GROUP BY type, url)
select type, (case when cnt >= 10000 then url end) as url, sum(cnt) as cnt
from temp_counts
group by type, (case when cnt >= 10000 then url end)

进一步的测试表明，双重聚合可以被“展开”成一个UNION --所有涉及每个独立案例表达式的独立查询。在这种情况下，在大约有200M行的样本数据集上，这种方法的执行速度要快30%左右。然而，这个结果是模式和数据特定的。

with temp_counts as (SELECT type, url, COUNT(*) as cnt FROM t GROUP BY type, url)
select * from temp_counts WHERE cnt >= 10000
UNION ALL
SELECT type, NULL as url, SUM(cnt) as cnt from temp_counts 
WHERE cnt < 10000 
GROUP BY type

这为实现和优化任意分离分组提供了两种通用模式& Amazon中的摘要。如果性能对您很重要，那么对两者都进行基准测试。

Answer 1

您可以通过两个聚合来完成这一任务：

select type, (case when cnt > XXX then url end) as url, sum(cnt) as visit_cnt
from (select type, url, count(*) as cnt
      from t
      group by type, url
     ) t
group by type, (case when cnt > XXX then url end)
order by type, sum(cnt) desc;

Answer 2

1. 首先，您在type, url上分组。
2. 然后在type, case when visit_count < 10000 then NULL else url上进行第二次分组。

我已经使用了SQL Server语法，我希望它也适用于Postgres。