非规范化数据表中跨年份月份销售数据按年汇总实现问询

Got it, let's work through this problem together—denormalized tables (where column names hold year information) can be a bit tricky, but we can build a solid solution for calculating annual sales totals across a specified date range.

First, let's align on a common example of your table structure to make this concrete:

Example table (sales_data):

product	2016_01	2016_02	...	2017_11	2017_12	category
Laptop	12000	15000	...	18000	22000	Electronics
Phone	8000	9500	...	11000	13000	Electronics

Below are two common implementation approaches, depending on whether you're working directly in a database or with local data via Python.

1. SQL Solution (For Database Tables) #

The core idea here is to dynamically identify columns for your target years, then aggregate sums per year.

Step 1: Define Your Date Range #

First, set the start and end years you want to target:

-- Adjust these values to your desired range
SET @start_year = 2016;
SET @end_year = 2017;

Step 2: Build Dynamic Sum Logic #

We'll query the database's schema to find columns matching your target years, then generate a sum statement for each year.

For MySQL:

-- Generate the sum expressions for each target year
SELECT GROUP_CONCAT(
    CONCAT('SUM(`', column_name, '`) AS `', SUBSTRING(column_name, 1, 4), '_total`')
    SEPARATOR ', '
) INTO @sum_expr
FROM information_schema.columns
WHERE table_schema = 'your_database_name' -- Replace with your DB name
  AND table_name = 'sales_data' -- Replace with your table name
  AND column_name REGEXP CONCAT('^', @start_year, '_|^', @end_year, '_');

-- Execute the dynamic query
SET @sql = CONCAT('SELECT ', @sum_expr, ' FROM sales_data;');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

For PostgreSQL:

DO $$
DECLARE
    start_year INT := 2016;
    end_year INT := 2017;
    sum_expr TEXT;
BEGIN
    -- Build sum expressions by extracting year from column names
    SELECT string_agg(
        'SUM("' || column_name || '") AS "' || split_part(column_name, '_', 1) || '_total"',
        ', '
    ) INTO sum_expr
    FROM information_schema.columns
    WHERE table_schema = 'public' -- Default schema; adjust if needed
      AND table_name = 'sales_data'
      AND split_part(column_name, '_', 1)::INT BETWEEN start_year AND end_year;

    -- Run the dynamic query
    EXECUTE 'SELECT ' || sum_expr || ' FROM sales_data;';
END $$;

2. Python Pandas Solution (For Local Data Files) #

If you're working with CSV/Excel files locally, Pandas makes this straightforward—here's how to extend your existing month-fetching code:

import pandas as pd

# Load your data (adjust path/format as needed)
df = pd.read_csv('sales_data.csv')

# Define your target year range
start_year = 2016
end_year = 2017

# Filter columns that belong to your target years
target_cols = [col for col in df.columns if str(start_year) in col or str(end_year) in col]

# Calculate annual totals
annual_sales = {}
for year in range(start_year, end_year + 1):
    # Get all columns for the current year
    year_cols = [col for col in target_cols if str(year) in col]
    # Sum all values across these columns for the year
    annual_sales[f"{year}_total"] = df[year_cols].sum().sum()

# Print or use the results
print("Annual Sales Totals:")
print(pd.Series(annual_sales))

Quick Notes for Edge Cases #

• If your column names use a different format (e.g., 2016Jan instead of 2016_01), adjust the string matching logic to use regex like r'^2016[A-Za-z]+' to capture valid month columns.
• If you need to group totals by another dimension (like product or region), add a GROUP BY clause in SQL, or use df.groupby(['product', 'region'])[year_cols].sum().sum(axis=1) in Pandas.

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