sql - 使用 TOP 和 GROUP BY

Question

表 table1 如下所示

+--------+-------+-------+------------+-------+
| flight |  orig |  dest |  passenger |  bags |
+--------+-------+-------+------------+-------+
|   1111 |  sfo  |  chi  |  david     |     3 |
|   1112 |  sfo  |  dal  |  david     |     7 |
|   1112 |  sfo  |  dal  |  kim       |     10|
|   1113 |  lax  |  san  |  ameera    |     5 |
|   1114 |  lax  |  lfr  |  tim       |     6 |
|   1114 |  lax  |  lfr  |  jake      |     8 |
+--------+-------+-------+------------+-------+

我正在通过 orig 聚合表格，如下所示

select 
  orig
  , count(*) flight_cnt
  , count(distinct passenger) as pass_cnt
  , percentile_cont(0.5) within group ( order by bags ASC) as bag_cnt_med
from table1
group by orig

我需要为每个 orig 组添加名称最长的 passenger ( length(passenger) ) - 我该怎么做是吗？

预期输出

+------+-------------+-----------+---------------+-------------------+
| orig |  flight_cnt |  pass_cnt |  bags_cnt_med | pass_max_len_name |
+------+-------------+-----------+---------------+-------------------+
| sfo  |           3 |         2 |             7 |  david            |
| lax  |           3 |         3 |             6 | ameera            |
+------+-------------+-----------+---------------+-------------------+

Answer 1

您可以使用 DISTINCT ON 方便地检索每组姓名最长的乘客。

• Select first row in each GROUP BY group?

但我看不出有什么办法可以将它(或任何其他简单的方法)与您的原始查询结合在一个 SELECT 中。我建议加入两个单独的子查询:

SELECT *
FROM  (  -- your original query
   SELECT orig
        , count(*) AS flight_cnt
        , count(distinct passenger) AS pass_cnt
        , percentile_cont(0.5) WITHIN GROUP (ORDER BY bags) AS bag_cnt_med
   FROM   table1
   GROUP  BY orig
   ) org_query
JOIN  (  -- my addition
   SELECT DISTINCT ON (orig) orig, passenger AS pass_max_len_name
   FROM   table1
   ORDER  BY orig, length(passenger) DESC NULLS LAST
   ) pas USING (orig);

连接子句中的

USING 方便地只输出一个 orig 实例，因此您可以简单地在外部 中使用 SELECT *选择。

如果passenger可以为NULL，添加NULLS LAST很重要:

• PostgreSQL sort by datetime asc, null first?

从同一组中具有相同最大长度的多个乘客姓名中，您得到一个任意选择 - 除非您向 ORDER BY 添加更多表达式作为决胜局。上面链接的答案中有详细解释。

性能？

通常，单次扫描效果更好，尤其是顺序扫描。

上述查询使用两次 扫描(可能是索引/仅索引扫描)。但是第二次扫描相对便宜，除非表太大而无法放入缓存(主要是)。 Lukas suggested an alternative query with only a single SELECT添加:

, (ARRAY_AGG (passenger ORDER BY LENGTH (passenger) DESC))[1]  -- I'd add NULLS LAST

想法很聪明，但是last time I tested , array_agg 和 ORDER BY 表现不佳。 (每组 ORDER BY 的开销很大，数组处理也很昂贵。)

使用自定义聚合函数 first() 相同的方法可以更便宜 like instructed in the Postgres Wiki here .或者，更快，但是，使用 a version written in C, available on PGXN .消除了数组处理的额外成本，但我们仍然需要每组 ORDER BY。 可能更快只有少数群体。然后你会添加:

 , first(passenger ORDER BY length(passenger) DESC NULLS LAST)

Gordon和 Lukas还要提到窗口函数 first_value() .窗口函数在聚合函数之后应用。要在同一个 SELECT 中使用它，我们需要先聚合 passenger somehow - catch 22。Gordon 用子查询解决了这个问题 - 另一个候选人与标准 Postgres 的良好性能。

first() 在没有子查询的情况下做同样的事情，应该更简单，更快一些。但对于大多数情况下，每组只有几行，它仍然不会比单独的 DISTINCT ON 快。对于每组很多行，递归 CTE 技术通常更快。如果您有一个单独的表来保存所有相关的、唯一的 orig 值，那么还有更快的技术。详情:

• Optimize GROUP BY query to retrieve latest record per user

最佳解决方案取决于多种因素。布丁的证明在于吃。要优化性能，您必须使用您的设置进行测试。上面的查询应该是最快的。