sql - Maven 版本排序

Question

我有一张 Maven 项目表。每个项目都有一些参数和一个版本号。

当我从表中选择时，我应该只获得最高版本，但由于 maven 版本的外观，这很棘手。这是我到目前为止的查询:

select id, group_id as group, artifact_id as artifact
from (
   select 
     p.group_id,
     p.artifact_id,
     p.id,
     rank() over (partition by p.group_id, p.artifact_id order by p.version desc)
   from projects p
   ) as ranked
   where ranked.rank = 1

这不会给出最高版本，因为版本不遵循字母数字顺序。
The version format is described here .

要点是版本可以是1.2.3-SNAPSHOT，其中1(主要)，2(次要)，3(递增)是数字，应该这样排序，SNAPSHOT(限定符)是一个字符串。如果版本不遵循这种格式，则应将其视为字符串。

这在 PostgreSQL 中可行吗？

Answer 1

解析字符串。喜欢:

SELECT version
     , substring(version, '^(\d+)')::int AS major
     , substring(version, '^\d+\.(\d+)')::int AS minor
     , substring(version, '^\d+\.\d+\.(\d+)')::int AS incremental
     , substring(version, '-(.+)$') AS qualifier
FROM  (
   VALUES
     ('1.2.3-SNAPSHOT')
   , ('2-FOO')
   , ('2-BAR')
   , ('2.1-BAR')
   , ('13.5.6-SNAPSHOT')
   , ('13.11.11-SNAPSHOT')
   ) x(version)
ORDER  BY major NULLS LAST
        , minor NULLS FIRST
        , incremental NULLS FIRST
        , qualifier NULLS FIRST;

db<> fiddle here

• substring(version, '^(\d+)') ... 使用 substring() with regular expression patterns解析。
  ^ ... 字符串的开始
  () ...捕获括号
  \d ... 类数字简写
• substring(version, '^(\d+)')::int ... 转换为整数以按数字排序
• major NULLS LAST ...没有编号的版本放在最后(我的假设)。
• minor NULLS FIRST ... 2 在 2.1
之前
• NULLS LAST 是 ORDER BY 中的默认 ins ASC(升序)排序顺序并且可以省略。

您可以直接在 ORDER BY 中使用这些表达式(无需将它们添加到 SELECT 列表中)。只是为了演示。

高级解决方案

对于更复杂的规则，您可能需要使用 regexp_matches() - 或者，最好是 regexp_match()在 Postgres 10 或更高版本中:

SELECT *
     , part[1] AS p1, part[2] AS p2, part[3] AS p3, part[4] AS p4
     , part[5] AS p5, part[6] AS p6, part[7] AS p7
FROM  (
   SELECT test_id, version
        , regexp_match(version
                     , '^(?:(\d+)(\w*)\.?(\d*)(\w*)\.?(\d*)(\w*))?(?:\-*(\w+))?') AS part
   FROM  (
      VALUES
        (1, '1.2.3-SNAPSHOT')
      , (2, '2-FOO')
      , (3, '2')
      , (4, '2-BAR')
      , (5, '2.1-BAR')
      , (6, '13.5.6-SNAPSHOT')
      , (7, '13.11.11-SNAPSHOT')
      , (8, '13.11a.11-SNAPSHOT')
      , (9, '13.11b.11')
      , (10, 'Test')
      , (11, 'TEST2')
      , (12, '1a')
      , (13, '1a.1a.1a-foo')
      , (14, '1a.1a.1a-')
      , (15, '1a.1a.1b-foo')
      , (16, 'sp9d8hgf')
      , (17, '2a-BAR')
      , (18, '2.1a-BAR')
      , (19, '2.1ab-BAR')
      , (20, 'incorrect1.2-foo')
      ) x(test_id, version)
   ) sub
ORDER  BY NULLIF(part[1], '')::int NULLS FIRST
        , NULLIF(part[2], '')      NULLS FIRST
        , NULLIF(part[3], '')::int NULLS FIRST
        , NULLIF(part[4], '')      NULLS FIRST
        , NULLIF(part[5], '')::int NULLS FIRST
        , NULLIF(part[6], '')      NULLS FIRST
        , part[7]                  NULLS FIRST;

db<> fiddle here

这涉及 all additional rules from your comment .

regexp_matches() 是一个强大的工具，但您需要对正则表达式有基本的了解。如有疑问，请测试。
特别注意:

• 不添加 g 开关。 More here.
• NULLIF(part[1],'')::int ... 不匹配项在数组中列为空字符串。在转换为 integer
之前需要转换为 NULL

注意 capturing () and non-capturing parentheses (:?) 之间的区别.前者还报告匹配，而后者则没有。