基于Node.js实现BigQuery手动分页及总行数获取的问题

方案1：单查询同时获取总行数与分页数据 #

通过CTE（公共表表达式）在一次查询中同时返回符合条件的总行数和当前页数据，避免两次查询的冗余：

let query = `
WITH filtered_data AS (
  SELECT * FROM \`${projectId}.${docDataset}.${docTable}\` t
  WHERE customerId = @myCustomerId
)
SELECT 
  (SELECT COUNT(*) FROM filtered_data) AS totalRows,
  ARRAY_AGG(t ORDER BY documentId ASC LIMIT 5) AS pageData
FROM filtered_data t
LIMIT 1`;

const options = {
  query: query,
  location: location,
  params: { myCustomerId: customerId }
};

const [job] = await client.createQueryJob(options);
const [rows] = await job.getQueryResults();

const { totalRows, pageData } = rows[0];
debugLog(`Total rows: ${totalRows}, Page results: ${pageData.length}`);
console.log(pageData);

后续分页只需调整ARRAY_AGG中的OFFSET和LIMIT，比如第二页可改为ARRAY_AGG(t ORDER BY documentId ASC OFFSET 5 LIMIT 5)。

方案2：复用查询Job+通过元数据获取总行数 #

不限制查询的LIMIT，借助getQueryResults的分页参数控制每页数据，同时通过开启统计信息获取总行数：

// 初始化查询（不带LIMIT）
let query = `SELECT * FROM \`${projectId}.${docDataset}.${docTable}\` t
  WHERE customerId = @myCustomerId
  ORDER BY documentId ASC`;

const options = {
  query: query,
  location: location,
  params: { myCustomerId: customerId }
};

const [job] = await client.createQueryJob(options);
// 获取第一页数据及统计信息
const [rows, metadata] = await job.getQueryResults({
  maxResults: 5,
  startIndex: 0,
  includeStatistics: true,
  useLegacySql: false
});

// 从元数据中提取总行数
const totalRows = metadata.statistics.query.totalRows;
debugLog(`Total rows: ${totalRows}, Page results: ${rows.length}`);
console.log(rows);

// 第二页查询
const [secondPageRows] = await job.getQueryResults({
  maxResults: 5,
  startIndex: 5
});
console.log(secondPageRows);

此方案可复用已创建的查询Job，后续分页仅需调整startIndex即可，无需重复执行查询逻辑。

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