定时运行的R脚本如何仅处理SQL中的新增数据？

Absolutely, you can implement this incremental processing logic by tracking the last processed date directly in your SQL Server environment. Here's a step-by-step adjusted version of your R script, plus the necessary setup to make this work:

1. Setup a Run Log Table (One-Time Step) #

First, create a small table in your SQL database to track the last date your script processed data. Run this SQL query once in your SQL Server:

CREATE TABLE dbo.ScriptRunLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    LastProcessedDt DATETIME NOT NULL,
    RunCompletedDt DATETIME DEFAULT GETDATE()
);
-- Initialize for first run (set to a date earlier than your earliest data)
INSERT INTO dbo.ScriptRunLog (LastProcessedDt) VALUES ('2017-01-01 00:00:00');

2. Modified R Script for Incremental Processing #

This script will:

• Fetch the last processed date from the log table
• Only query new data where Dt is after that date
• Run the regression analysis on the incremental data
• Update the log table with the new max Dt after processing

library("RODBC")
library(tidyverse) # Includes dplyr, purrr, tidyr which you're using

# Establish database connection
dbHandle <- odbcDriverConnect("driver={SQL Server};server=MYSERVER;database=MYBASE;trusted_connection=true")

# Step 1: Get the last processed date from the log table
last_processed_dt <- sqlQuery(dbHandle, "SELECT MAX(LastProcessedDt) AS LastDt FROM dbo.ScriptRunLog")$LastDt[1]
cat("Last processed date:", last_processed_dt, "\n")

# Step 2: Query only NEW data (Dt > last_processed_dt)
# Use parameterized formatting to handle datetime correctly for SQL Server
sql <- sprintf(
  "SELECT Dt, CustomerName, ItemRelation, SaleCount, DocumentNum, DocumentYear, IsPromo 
   FROM dbo.mytable 
   WHERE Dt > '%s'",
  format(last_processed_dt, "%Y-%m-%d %H:%M:%S")
)
df <- sqlQuery(dbHandle, sql)

if(nrow(df) == 0) {
  cat("No new data to process. Exiting script.\n")
  odbcClose(dbHandle)
  quit()
}

# Step 3: Run regression analysis (same core logic as your original code)
my_lm <- function(df) {
  lm(SaleCount~IsPromo, data = df)
}

reg <- df %>% 
  group_by(CustomerName, ItemRelation, DocumentNum, DocumentYear) %>% 
  nest() %>% 
  mutate(fit = map(data, my_lm), tidy = map(fit, tidy)) %>% 
  select(-fit, -data) %>% 
  unnest()

# Step 4: Save regression results to target table (append mode)
sqlSave(dbHandle, as.data.frame(reg), "dbo.mytableforecast", append = TRUE, verbose = TRUE)

# Step 5: Update the run log with the new max Dt from processed data
new_max_dt <- max(df$Dt)
update_sql <- sprintf(
  "INSERT INTO dbo.ScriptRunLog (LastProcessedDt) VALUES ('%s')",
  format(new_max_dt, "%Y-%m-%d %H:%M:%S")
)
sqlQuery(dbHandle, update_sql)
cat("Updated last processed date to:", new_max_dt, "\n")

# Cleanup connection
odbcClose(dbHandle)

Key Notes #

• Datetime Compatibility: Formatting the date with %Y-%m-%d %H:%M:%S ensures it matches SQL Server's datetime type, avoiding conversion errors.
• First Run Handling: The initial log entry uses a date earlier than your earliest dataset, so the first run will process all existing data as expected.
• Empty Data Check: The script skips unnecessary processing if there's no new data, saving resources.
• Audit Trail: The log table gives you a clear history of script runs, making it easy to troubleshoot or verify which dates have been processed.

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