通过Prisma按Pair分组筛选最低价格数据的技术问题

数据库存有7000条包含pair字段的数据，其中仅含1000个唯一pair值，每个pair对应7条重复数据。需求是从7000条数据中筛选出1000条记录，每个pair仅保留价格最低的那条。当前使用的Prisma查询同时按价格和createdAt取最大值，不符合预期。

数据库示例数据 #

[
  {
    "pair": "btcusdt",
    "price": 20,
    "createdAt": "2020-01-01"
  },
  {
    "pair": "btcusdt",
    "price": 2,
    "createdAt": "2020-01-03"
  },
  {
    "pair": "btcusdt",
    "price": 1,
    "createdAt": "2020-01-02"
  },
  {
    "pair": "ethusdt",
    "price": 40,
    "createdAt": "2020-01-03"
  },
  {
    "pair": "ethusdt",
    "price": 2,
    "createdAt": "2020-01-21"
  },
  {
    "pair": "ethusdt",
    "price": 9,
    "createdAt": "2020-01-12"
  },
  {
    "pair": "bnbusdt",
    "price": 1,
    "createdAt": "2020-01-01"
  },
  {
    "pair": "bnbusdt",
    "price": 0.6,
    "createdAt": "2020-01-22"
  },
  {
    "pair": "bnbusdt",
    "price": 0.01,
    "createdAt": "2020-01-03"
  }
]

预期返回结果 #

[
  {
    "pair": "btcusdt",
    "price": 1,
    "createdAt": "2020-01-02"
  },
  {
    "pair": "ethusdt",
    "price": 2,
    "createdAt": "2020-01-21"
  },
  {
    "pair": "bnbusdt",
    "price": 0.01,
    "createdAt": "2020-01-03"
  }
]

当前使用的Prisma代码 #

const maxPrices = await prismaClient[exchange].groupBy({
  by: ["pair"],
  _max: {
    price: true,
    createdAt: true,
  },
  where: {
    createdAt: {
      gt: new Date(Date.now() - 1000 * 60 * dumpPeriod),
    },
  },
});

解决方案 #

1. SQL 查询语句 #

使用窗口函数ROW_NUMBER()按pair分组，按price升序排序，取每个分组的第一条记录（即价格最低的那条）：

SELECT pair, price, createdAt
FROM (
  SELECT 
    pair, 
    price, 
    createdAt,
    ROW_NUMBER() OVER (PARTITION BY pair ORDER BY price ASC) AS rn
  FROM your_table_name
  WHERE createdAt > DATE_SUB(NOW(), INTERVAL ? MINUTE) -- 对应原查询的时间条件
) AS ranked
WHERE rn = 1;

2. Prisma 实现方式 #

由于Prisma的groupBy无法直接返回聚合字段外的其他字段（如createdAt），推荐使用原生SQL查询（$queryRaw）来实现需求：

const dumpPeriodMinutes = dumpPeriod; // 假设dumpPeriod是分钟数
const minPriceRecords = await prismaClient.$queryRaw`
  SELECT pair, price, createdAt
  FROM (
    SELECT 
      pair, 
      price, 
      createdAt,
      ROW_NUMBER() OVER (PARTITION BY pair ORDER BY price ASC) AS rn
    FROM ${Prisma.raw(exchange)}
    WHERE createdAt > DATE_SUB(NOW(), INTERVAL ${dumpPeriodMinutes} MINUTE)
  ) AS ranked
  WHERE rn = 1;
`;

如果需要兼容不同数据库（如SQLite不支持DATE_SUB），可调整时间条件为JavaScript处理后的日期：

const cutoffDate = new Date(Date.now() - 1000 * 60 * dumpPeriod);
const minPriceRecords = await prismaClient.$queryRaw`
  SELECT pair, price, createdAt
  FROM (
    SELECT 
      pair, 
      price, 
      createdAt,
      ROW_NUMBER() OVER (PARTITION BY pair ORDER BY price ASC) AS rn
    FROM ${Prisma.raw(exchange)}
    WHERE createdAt > ${cutoffDate}
  ) AS ranked
  WHERE rn = 1;
`;

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