如果我只关心每个组的最小值和最大值，这会相对容易，问题是我的要求是找到各种边界。示例数据集如下:

BoundaryColumn  GroupIdentifier
1                  A
3                  A
4                  A
7                  A
8                  B
9                  B  
11                 B  
13                 A
14                 A
15                 A
16                 A

What I need from the sql is a result set as follows:

min  max  groupid
1    7    A
8    11   B
13   16   A

本质上是找到每个集群的边界。

数据将存储在 oracle11g 或 mysql 中，因此可以为任一平台提供语法。

最佳答案

免责声明:使用前端语言查询部分结果和处理此类内容要容易得多。也就是说……

以下查询适用于 Oracle(支持分析查询)但不适用于 MySQL(不支持)。有一个 SQL Fiddle here .

WITH BoundX AS (
  SELECT * FROM (
    SELECT
     BoundaryColumn,
     GroupIdentifier,
     LAG(GroupIdentifier) OVER (ORDER BY BoundaryColumn) AS GIDLag,
     LEAD(GroupIdentifier) OVER (ORDER BY BoundaryColumn) AS GIDLead
    FROM MyTable
    ORDER BY BoundaryColumn
  )
  WHERE GIDLag IS NULL OR GroupIdentifier <> GIDLag
     OR GIDLead IS NULL OR GroupIdentifier <> GIDLead
)
SELECT MIN, MAX, GROUPID
FROM (
  SELECT
    BoundaryColumn AS MIN,
    LEAD(BoundaryColumn) OVER (ORDER BY BoundaryColumn) AS MAX,
    GroupIdentifier AS GROUPID,
    GIDLag,
    GIDLead
  FROM BoundX
)
WHERE GROUPID = GIDLead

这是逻辑，一步一步。您也许可以对此进行改进，因为我觉得这里有一个子查询太多了...

此查询将之前和之后的 GroupIdentifier 值拉入每一行:

SELECT
 BoundaryColumn,
 GroupIdentifier,
 LAG(GroupIdentifier) OVER (ORDER BY BoundaryColumn) AS GIDLag,
 LEAD(GroupIdentifier) OVER (ORDER BY BoundaryColumn) AS GIDLead
FROM MyTable
ORDER BY BoundaryColumn

结果是这样的:

BoundaryColumn  GroupIdentifier  GIDLag  GIDLead
1                  A                         A
3                  A                A        A
4                  A                A        A
7                  A                A        B
8                  B                A        B
9                  B                B        B
11                 B                B        A
13                 A                B        A
14                 A                A        A
15                 A                A        A
16                 A                A

如果您添加逻辑以删除所有 GIDLag = GIDLead = GroupIdentifier 的行，您将得到边界:

WITH BoundX AS (
  SELECT * FROM (
    SELECT
     BoundaryColumn,
     GroupIdentifier,
     LAG(GroupIdentifier) OVER (ORDER BY BoundaryColumn) AS GIDLag,
     LEAD(GroupIdentifier) OVER (ORDER BY BoundaryColumn) AS GIDLead
    FROM MyTable
    ORDER BY BoundaryColumn
  )
  WHERE GIDLag IS NULL OR GroupIdentifier <> GIDLag
     OR GIDLead IS NULL OR GroupIdentifier <> GIDLead
)
SELECT
  BoundaryColumn AS MIN,
  LEAD(BoundaryColumn) OVER (ORDER BY BoundaryColumn) AS MAX,
  GroupIdentifier AS GROUPID,
  GIDLag,
  GIDLead
FROM BoundX

加上这个结果是:

MIN MAX GROUPID GIDLAG GIDLEAD
--- --- ------- ------ -------
  1   7 A              A
  7   8 A       A      B
  8  11 B       A      B
 11  13 B       B      A
 13  16 A       B      A
 16     A       A

最后，仅包含 GroupID = GIDLead 的那些行。这是此答案顶部的查询。结果是:

MIN MAX GROUPID
--- --- -------
  1   7 A
  8  11 B
 13  16 A

关于mysql - 分组查找每组的最小值、最大值，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19410123/