基于15个字段关联150万行销售数据：实现2018与2019销售额并列展示及内存溢出解决方案

Hey there, let's work through this problem—dealing with 1.5M-row datasets can definitely tax local memory when you try full joins, so we need smarter approaches to get your side-by-side YoY sales view without crashing things. Here are a few practical, tested methods:

1. Aggregate First, Join Second (Most Recommended) #

The root cause of your memory overload is that a full join between two 1.5M-row tables creates an enormous intermediate dataset. Instead, reduce the data size before joining by aggregating on your matching dimensions first.

Step-by-Step: #

• For both 2018 and 2019 data, create a Month_Day field (extract the day/month portion from your Date column, e.g., convert 01.01.18 to 01.01). This aligns dates across years for YoY comparison.
• Aggregate sales for each unique combination of Field1 → Field15 + Month_Day. This collapses duplicate dimension groups into single rows, drastically cutting down the dataset size.
• Join the two aggregated datasets using the shared dimensions (Field1 → Field15 + Month_Day) to get your side-by-side sales columns.

SQL Example:

-- Aggregate 2018 sales
WITH sales_2018_agg AS (
    SELECT 
        Date AS Date_18,
        SUBSTRING(Date, 1, 5) AS Month_Day, -- Adjust based on your date format
        Field1, Field2, ..., Field15,
        SUM(Sales) AS Sales_2018
    FROM sales_2018
    GROUP BY Date, SUBSTRING(Date, 1, 5), Field1, Field2, ..., Field15
),
-- Aggregate 2019 sales
sales_2019_agg AS (
    SELECT 
        Date AS Date_19,
        SUBSTRING(Date, 1, 5) AS Month_Day,
        Field1, Field2, ..., Field15,
        SUM(Sales) AS Sales_2019
    FROM sales_2019
    GROUP BY Date, SUBSTRING(Date, 1, 5), Field1, Field2, ..., Field15
)
-- Join aggregated datasets
SELECT 
    a.Date_18,
    b.Date_19,
    a.Field1, a.Field2, ..., a.Field15,
    a.Sales_2018,
    b.Sales_2019
FROM sales_2018_agg a
INNER JOIN sales_2019_agg b
    ON a.Month_Day = b.Month_Day
    AND a.Field1 = b.Field1
    AND a.Field2 = b.Field2
    -- Repeat for all 15 fields
    AND a.Field15 = b.Field15

Excel/Power Query Example:

1. Import each sales table into Power Query.
2. Add a custom column to extract Month_Day (e.g., Text.Middle([Date], 0, 5) if your date is formatted as dd.mm.yy).
3. Use the Group By feature to group by Field1 → Field15 + Month_Day, aggregating Sales as a sum.
4. Merge the two aggregated queries using the shared dimensions as matching keys.

2. Use a Database Engine Instead of Local Tools #

Local Excel/Power Pivot isn't built to handle 1.5M-row joins efficiently. Import your data into a lightweight database like SQLite (file-based, no server needed) or MySQL, then run the aggregation/join query above. Databases are optimized for handling large datasets and will manage memory far better than desktop tools.

3. Optimize Power Pivot for Large Datasets #

If you must use Power Pivot, apply these tweaks to avoid memory exhaustion:

• Set dimension fields to "Category" data type: In Power Query, change Field1 → Field15 to the Category type—this compresses repeated values and reduces memory usage.
• Load aggregated data only: Instead of loading the full 1.5M rows, aggregate the data in Power Query first (as described in Method 1) before loading it into the data model.
• Use Relationships Instead of Joins: In the Power Pivot model, create a relationship between the two tables using Field1 → Field15 + Month_Day. Then build a pivot table with 2018 and 2019 sales as values—Power Pivot will handle the matching efficiently without creating a massive joined table.

4. Chunk Processing (Last Resort) #

If none of the above work, split your data into smaller chunks based on a high-cardinality field (e.g., Field1 or Location). Process each chunk separately (join the 2018 and 2019 subsets for that chunk), then combine all results into a single table. This keeps each individual join operation within your PC's memory limits.

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