编写SQL语句实现跨年度列间数据迁移（百万级数据表）

Got it, let's tackle this problem for your million-row dataset. The goal is to take the PY volumes value from the next year's row (matching Location and Product) and populate it into the CY Volumes column of the previous year's row—like moving the 2018 Georgia Gasoline PY volumes into the 2017 row's CY Volumes.

Step 1: Understand the Core Logic #

We need to join the table to itself (a self-join) to link each row with its corresponding next-year row, then update the CY Volumes column using the matched PY volumes value.

Step 2: Efficient SQL Update (for MySQL) #

For large datasets, avoid slow subqueries—use a direct JOIN with your update. This is way faster for million-row tables:

-- First, create a composite index to speed up the self-join (critical for performance!)
CREATE INDEX idx_loc_prod_date ON sales_data (Location, Product, Date);

-- Run the update in a transaction to safely roll back if needed
START TRANSACTION;

UPDATE sales_data t1
JOIN sales_data t2
  ON t1.Location = t2.Location
  AND t1.Product = t2.Product
  AND t2.Date = DATE_ADD(t1.Date, INTERVAL 1 YEAR) -- Matches exactly one year later
SET t1.`CY Volumes` = t2.`PY volumes`;

-- Verify the update before committing (run this separately first if you want)
-- SELECT t1.Date, t1.Location, t1.Product, t1.`CY Volumes`, t2.`PY volumes` FROM sales_data t1 JOIN sales_data t2 ON t1.Location = t2.Location AND t1.Product = t2.Product AND t2.Date = DATE_ADD(t1.Date, INTERVAL 1 YEAR) LIMIT 100;

COMMIT;
-- If something looks off, run ROLLBACK instead of COMMIT

Step 3: Adjust for Other Databases #

If you're using PostgreSQL instead of MySQL, the update syntax uses FROM instead of a JOIN:

-- Create the same composite index
CREATE INDEX idx_loc_prod_date ON sales_data (Location, Product, "Date");

-- Update statement
BEGIN;

UPDATE sales_data t1
SET "CY Volumes" = t2."PY volumes"
FROM sales_data t2
WHERE t1.Location = t2.Location
  AND t1.Product = t2.Product
  AND t2."Date" = t1."Date" + INTERVAL '1 year';

COMMIT;

Key Notes for Large Datasets #

• Indexing is non-negotiable: The composite index on (Location, Product, Date) ensures the self-join doesn't scan the entire table millions of times—this will drastically reduce execution time.
• Test first: Always run a SELECT version of the join (like the commented-out query) to confirm you're matching the correct rows before updating.
• Use transactions: Wrapping the update in a transaction lets you roll back if you spot mistakes, which is crucial for production data.
• Handle missing matches: If some rows don't have a corresponding next-year entry, their CY Volumes will stay as their original value. If you want to set these to NULL instead, use a LEFT JOIN and add SET t1.CY Volumes = COALESCE(t2.PY volumes, NULL).

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