这是一个标准的Top-1-Per-Group问题。使用ROW_NUMBER获取每个组中的top值，然后jsonb_agg将其聚合到JSON数组中。

SELECT jsonb_agg(m) 
FROM (
    SELECT
      m.id,
      m.period_type,
      m.period,
      m.commission
    FROM (
        SELECT 
          m.*,
          ROW_NUMBER() OVER (PARTITION BY id ORDER BY period_type DESC, period DESC) AS rn
        FROM mytable m
    ) m
    WHERE rn = 1
) m;

字符串
db<>fiddle

下一个查询可以写得更容易，但我似乎总是有问题时，多个最大值弹出在1个查询（如获得佣金的最大周期的最大周期类型）：

SELECT
    m3.id,
    m3.period_type,
    m3.period,
    m4.commission
  FROM (
    SELECT 
      m.id,
      m.period_type,
      max(m.period) as period
    FROM mytable m
    INNER JOIN (
      SELECT DISTINCT
        id,
        max(period_type) over (partition by id ) period_type
      FROM mytable
      ) x on x.id=m.id and x.period_type=m.period_type
    GROUP BY m.id, m.period_type
  ) m3
  INNER JOIN mytable m4 on m4.id=m3.id 
                       and m4.period_type=m3.period_type 
                       and m4.period=m3.period

字符串
它将返回：
| ID|期间类型|期间|委员会|
| --|--|--|--|
| 123456 |每月| 3 | 11 |
| 123457 |每月| 9 | 16 |
此查询可用作子查询以获取JSON：

SELECT json_agg(m5) 
from (
  SELECT
    m3.id,
    m3.period_type,
    m3.period,
    m4.commission
  FROM (
    SELECT 
      m.id,
      m.period_type,
      max(m.period) as period
    FROM mytable m
    INNER JOIN (
      SELECT DISTINCT
        id,
        max(period_type) over (partition by id ) period_type
      FROM mytable
      ) x on x.id=m.id and x.period_type=m.period_type
    GROUP BY m.id, m.period_type
  ) m3
  INNER JOIN mytable m4 on m4.id=m3.id 
                       and m4.period_type=m3.period_type 
                       and m4.period=m3.period
) m5

型
产出：

[{"id":123456,"period_type":"Monthly","period":3,"commission":11}, 
 {"id":123457,"period_type":"Monthly","period":9,"commission":16}]

型
请参阅：DBFIDDLE