在GROUP BY中使用TOP之类的东西

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

我需要为每个组添加passenger最长的名称（length(passenger)）orig-我该如何处理？

预期产量

+------+-------------+-----------+---------------+-------------------+
| 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。

• 在每个GROUP BY组中选择第一行？

但是我看不到将它（或任何其他简单的方法）与您的原始查询合并在一个方法中的任何方法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在join子句中方便地仅输出的一个实例orig，因此您可以SELECT *在外部简单地使用SELECT。

如果passenger可以为NULL，则添加NULLS LAST以下内容很重要：

• PostgreSQL按datetime asc排序，是否为null？

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

表现？

通常，单次扫描比较好，尤其是顺序扫描。

上面的查询使用 两次 扫描（可能是索引/仅索引扫描）。但是第二次扫描相对便宜，除非表太大（无法容纳）。卢卡斯建议的替换查询仅具有 单个
SELECT
加入：

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

这个想法是聪明，但我最后一次测试，array_agg有ORDER BY没有如此上佳表现。（每个组的开销ORDER BY很大，并且数组处理也很昂贵。）

像Postgres
Wiki此处所指示的那样
，使用定制的聚合函数可以使同一方法更便宜。或者，使用PGXN上可用C编写的版本，速度更快。消除了数组处理的额外成本，但我们仍然需要每组。
可能* 只有少数几个组 更快 。然后，您将添加： first()
ORDER BY *

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

戈登和卢卡斯也提到了窗口功能first_value()。窗口函数 在 聚合函数 之后
应用。要以同样的方式使用它SELECT，我们首先需要以passenger 某种方式 进行聚合-捕获22。Gordon用子查询解决了这个问题-
另一个候选者，使用标准Postgres可以获得良好的性能。

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

• 优化GROUP BY查询以检索每个用户的最新记录

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