SQL分组：选择另一列最小/最大值的值

Question

我希望能按一列分组，获取第二列的最小值和最大值，并且（这是棘手的部分！）获取第三列的值，其中第二列在分组中具有最小值。

例子：

MyTable：

ID     TS     GRP
==================
 1     20      A
 2     20      B
 3     10      A
 4     30      A
 5     10      B
 6     40      A

期望结果（ID应该是TS取得最小值的记录中的值）：

ID    MIN_TS   MAX_TS   GRP
============================
 3      10       40      A
 5      10       20      B

通常，分组查询非常简单：

SELECT <???> AS ID, MIN(TS) AS MIN_TS, MAX(TS) AS MAX_TS, GRP
FROM MyTable
GROUP BY GRP

但是ID部分怎么办？使用分组时不起作用，对吗？为什么呢？有什么最好的解决方法？

- Andy

---

你需要使用JOIN操作来查找那些行。 - jarlh

在出现平局的情况下该怎么办？或者它们不能发生（即，您对TS + GRP有唯一约束条件）？ - Thorsten Kettner

你说得对，这就是为什么它不能与分组一起使用的原因... - Andy

Answer 1

在子查询中进行聚合，然后在另一个子查询中查找每个组的ID：

SELECT
  (SELECT TOP(1) id FROM MyTable WHERE grp = agg.grp ORDER BY ts DESC) AS id,
  min_ts, max_ts, grp
FROM (SELECT min(ts) AS min_ts, max(ts) AS max_ts, grp
      FROM MyTable
      GROUP BY grp) agg

或者使用窗口函数：

SELECT id, min_ts, max_ts, grp
FROM (SELECT 
        id,
        min(ts) OVER (PARTITION BY grp) min_ts,
        max(ts) OVER (PARTITION BY grp) max_ts,
        grp,
        row_number OVER (PARTITION BY grp ORDER BY ts) rn
      FROM MyTable)
WHERE rn = 1;

这个查询使用窗口函数来计算每个组的min_ts和max_ts，然后过滤掉每个组中除第一行以外的所有行（按ts排序）。

- markusk

---

我更喜欢你的第二个解决方案，使用窗口函数。 - Andy

顺便提一下，对于非常大的数据集，排名函数非常慢。 - Heisenberg

Answer 2

有点晚了，但对于未来的读者...

我可以提出另一种解决方案，与现有的解决方案有些不同，基本上是相同的想法，但是它以另一种方式实现（而且可能更快？）。

因此，您可以在子查询中进行所有分组和聚合（使用WITH），然后在该查询和原始表之间使用INNER JOIN来获取所需内容，就像这样...

WITH values AS (
  SELECT
    MIN(ts) as min_ts,
    MAX(ts) AS max_ts,
    grp
  FROM MyTable
  GROUP BY grp
)

SELECT
  tb.id AS id,
  v.min_ts AS min_ts,
  v.max_ts AS max_ts,
  tb.grp AS grp
FROM MyTable tb
INNER JOIN values v ON v.grp = tb.grp AND v.min_ts = tb.ts;

这个比较简单，对我来说也更直观一些。

我已经在Postgres数据库上测试过了，针对以下数据：

 id | ts | grp 
----+----+-----
  7 |  5 | A
  3 | 10 | A
  1 | 20 | A
  5 | 30 | A
  4 | 10 | B
  2 | 20 | B
  6 | 30 | B
  8 | 60 | B

它会给出以下结果

 id | min_ts | max_ts | grp 
----+--------+--------+-----
  7 |      5 |     30 | A
  4 |     10 |     60 | B

- Amr Saber