PostgreSQL中实现带重复时间序列的自定义排序方法问询

要实现你描述的「先按时间批次循环取各时间段记录」的排序逻辑，我们可以利用PostgreSQL的窗口函数给每条记录分配「批次号」，再基于批次号和时间段排序。以下是具体实现：

核心思路 #

你的需求本质是：

• 把所有记录按**一天中的小时段（比如1PM对应13点、2PM对应14点）**分组
• 给每个组内的记录按顺序分配一个「批次号」（同一时间段的第一条记录批次为1，第二条为2，以此类推）
• 排序时先按批次号升序，同一批次内再按时间段（小时）升序，最后按时间和ID保证顺序稳定

PostgreSQL SQL实现 #

假设你的表名为your_table，可以用以下SQL语句：

SELECT id, "time"
FROM your_table
ORDER BY
  -- 按小时分组，给每组内的记录分配批次号
  row_number() OVER (PARTITION BY extract(hour FROM "time") ORDER BY "time", id),
  -- 同一批次内按小时顺序排列（1PM→2PM→3PM→4PM）
  extract(hour FROM "time"),
  -- 确保同小时同批次的记录顺序稳定
  "time",
  id;

代码解释 #

• PARTITION BY extract(hour FROM "time")：将所有记录按小时段分组，比如所有1PM的记录归为一组，2PM的归为另一组
• row_number() OVER (...)：给每个组内的记录按time（时间相同则按id）排序，分配一个递增的批次号
• 外层ORDER BY先按批次号，这样同一批次的各时间段记录会排在一起；再按小时保证时间段顺序；最后按time和id避免排序歧义

Rails框架实现 #

在Rails中，你可以直接在查询中嵌入SQL窗口函数，推荐用Arel.sql遵循最佳实践（避免潜在的SQL注入风险）：

# 假设你的模型名为YourModel
YourModel.select(:id, :time).order(
  Arel.sql('row_number() OVER (PARTITION BY extract(hour FROM "time") ORDER BY "time", id)'),
  Arel.sql('extract(hour FROM "time")'),
  :time,
  :id
)

如果你更习惯直接写字符串，也可以这样（这里无动态参数，同样安全）：

YourModel.select(:id, :time).order(
  'row_number() OVER (PARTITION BY extract(hour FROM "time") ORDER BY "time", id) ASC',
  'extract(hour FROM "time") ASC',
  'time ASC',
  'id ASC'
)

验证效果 #

用你提供的示例数据测试，这个查询会输出你期望的排序结果：

• 第1批次：各时间段的第一条记录（ID1、2、4、13）
• 第2批次：各时间段的第二条记录（ID3、5、10、16）
• 第3批次：各时间段的第三条记录（ID7、6、8）
• 以此类推，直到所有记录按循环顺序排列

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