如何在SQL Server中执行多语句并留存中间结果，规避本地内存限制

你可以将所有操作直接在SQL Server端完成，利用临时表留存中间结果，完全避免将数据拉到本地处理，摆脱内存限制。以下是修改后的实现流程：

步骤1：建立数据库连接并写入ID子集临时表 #

这一步和原流程一致，将需要筛选的ID子集写入SQL Server的临时表#id_subset：

con <- DBI::dbConnect(odbc::odbc(),
                      Driver   = "SQL Server",
                      Server   = DEST_SERVER, 
                      Database = DEST_DATABASE,
                      Trusted_Connection = "Yes")

# 生成ID子集并写入SQL Server临时表
id_indices <- 1:30000
id_subset <- data.frame(record_id = all_ids[id_indices])
  
DBI::dbWriteTable(con, '#id_subset', id_subset)

步骤2：直接在SQL Server中完成筛选并生成结果临时表 #

将原流程中本地用dplyr处理的逻辑（取每个ID最新索赔日期且paid='Y'的记录）转换成SQL语句，直接在服务器端执行并生成#paid临时表，无需将中间数据拉到本地：

# 执行SQL完成筛选，直接生成#paid临时表
DBI::dbExecute(con, "
WITH ranked_claims AS (
    SELECT 
        id, 
        claim_date, 
        paid,
        -- 按ID分组，按索赔日期倒序排名，最新日期的记录排名为1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY CONVERT(DATE, claim_date) DESC) AS rn
    FROM [my_schema].[my_table]
    WHERE id IN (SELECT record_id FROM #id_subset)
)
SELECT id, claim_date, paid
INTO #paid
FROM ranked_claims
WHERE rn = 1 AND paid = 'Y'
")

后续操作 #

此时#paid临时表已经在SQL Server中生成，你可以直接在后续的SQL语句中使用它进行多表关联等操作，比如：

# 示例：将#paid与其他表关联
result <- DBI::dbGetQuery(con, "
    SELECT p.*, o.other_column
    FROM #paid p
    JOIN [my_schema].[other_table] o ON p.id = o.id
")

逻辑说明 #

• 使用ROW_NUMBER()窗口函数按ID分组，对每个ID的记录按索赔日期倒序排名，确保最新日期的记录排名为1
• 通过CTE（公共表表达式）ranked_claims先完成排名，再筛选出排名为1且paid='Y'的记录，直接写入#paid临时表
• 全程无需将数据拉到本地，完全利用SQL Server的计算资源，不受本地内存限制

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