Postgres 9.6：如何按tlid分组取单数组表的最新10条数据？

表结构变更说明 #

原表结构：

CREATE TABLE traces_v0
 ( canvas_id UUID NOT NULL
 , tlid BIGINT NOT NULL
 , trace_id UUID NOT NULL
 , timestamp TIMESTAMP WITH TIME ZONE NOT NULL
 , PRIMARY KEY (canvas_id, tlid, trace_id)
 );

新表结构：

CREATE TABLE traces_v0
 ( canvas_id UUID NOT NULL
 , root_tlid BIGINT NOT NULL
 , trace_id UUID NOT NULL
 , callgraph_tlids BIGINT[] NOT NULL
 , timestamp TIMESTAMP WITH TIME ZONE NOT NULL
 , PRIMARY KEY (canvas_id, root_tlid, trace_id)
 );

变更逻辑：原表每行对应一组(tlid, trace_id)，新表每行对应一个trace_id及关联的callgraph_tlids数组（一个trace可能关联多个tlid）。

原查询逻辑 #

原表中可按每个tlid获取最新10条(tlid, trace_id)数据：

SELECT tlid, trace_id
  FROM (
    SELECT
      tlid, trace_id,
      ROW_NUMBER() OVER (PARTITION BY tlid ORDER BY timestamp DESC) as row_num
    FROM traces_v0
    WHERE tlid = ANY(@tlids::bigint[])
      AND canvas_id = @canvasID
  ) t
  WHERE row_num <= 10

（注：@tlids是Postgres驱动参数写法，等价于$1）

新表查询困境 #

迁移到新表后，现有查询无法按tlid分组限制最多10条数据：

SELECT callgraph_tlids, trace_id
FROM traces_v0
WHERE @tlids && callgraph_tlids  -- '&&' 为数组重叠运算符
  AND canvas_id = @canvasID
ORDER BY timestamp DESC

该查询只能筛选出关联目标tlid的trace，但无法对每个tlid单独限制最多10条最新记录。

解决方案（Postgres 9.6） #

需要先将callgraph_tlids数组拆分为单个tlid行，再用窗口函数按tlid分区并筛选最新10条：

SELECT unnest_tlid, callgraph_tlids, trace_id
FROM (
  SELECT
    unnest(callgraph_tlids) AS unnest_tlid,
    callgraph_tlids,
    trace_id,
    timestamp,
    ROW_NUMBER() OVER (PARTITION BY unnest(callgraph_tlids) ORDER BY timestamp DESC) AS row_num
  FROM traces_v0
  WHERE @tlids && callgraph_tlids
    AND canvas_id = @canvasID
) t
WHERE row_num <= 10

逻辑说明： #

1. 拆分数组：使用unnest(callgraph_tlids)将每个trace的关联tlid数组拆分为多行，每行对应一个trace和一个关联tlid。
2. 分区打行号：通过ROW_NUMBER() OVER (PARTITION BY unnest(callgraph_tlids) ORDER BY timestamp DESC)，对每个拆出来的tlid单独分区，按时间戳倒序给记录打行号。
3. 筛选最新10条：过滤行号<=10的记录，得到每个tlid对应的最新10条关联trace数据。

如果需要去重（比如同一个trace可能被多个tlid关联，且在不同tlid的结果中重复出现），可根据需求添加DISTINCT ON(trace_id)或其他去重逻辑。

内容的提问来源于stack exchange，提问作者Paul Biggar