MySQL中全月度相邻ach%跨月对比的批量查询实现方法问询

Hey there! Let's work through this problem together to build that single MySQL query you need for your monthly ach% comparisons and sales growth calculations.

First, let's clarify the core requirements we're tackling:

• Calculate the highest ach% from two consecutive prior months (e.g., July + August max)
• Compare that max value to the next month's ach% (e.g., September's ach%)
• Include monthly sales growth (环比) in the same query
• Do all this in one single query, no multiple steps

Assumptions About Your Data #

I'll assume your existing associate_monthly_ach_p... (let's call it associate_monthly_ach for simplicity) has columns like:

• associate_id: Unique ID for each team member
• report_year: Year of the report (e.g., 2024)
• report_month: Numeric month (1 = Jan, 12 = Dec)
• ach_percent: Achievement percentage (e.g., 92.5 for 92.5%)
• sales: Monthly sales figure

The Single Query Solution #

We'll use MySQL window functions (LAG()) to pull in historical data, then compute our required metrics in a CTE (Common Table Expression) for readability:

WITH monthly_metrics AS (
    SELECT
        associate_id,
        report_year,
        report_month,
        ach_percent,
        sales,
        -- Get ach% from the previous month
        LAG(ach_percent, 1) OVER (
            PARTITION BY associate_id 
            ORDER BY report_year, report_month
        ) AS prev_month_ach,
        -- Get ach% from two months prior
        LAG(ach_percent, 2) OVER (
            PARTITION BY associate_id 
            ORDER BY report_year, report_month
        ) AS prev_prev_month_ach,
        -- Get sales from the previous month for growth calculation
        LAG(sales, 1) OVER (
            PARTITION BY associate_id 
            ORDER BY report_year, report_month
        ) AS prev_month_sales
    FROM associate_monthly_ach -- Replace with your existing view/procedure name
)
SELECT
    associate_id,
    CONCAT(report_year, '-', LPAD(report_month, 2, '0')) AS month_label,
    -- Calculate the highest ach% from the two prior months
    GREATEST(prev_month_ach, prev_prev_month_ach) AS two_month_max_ach,
    ach_percent AS current_month_ach,
    -- Compare current month ach% to the two-month max (adjust this logic to fit your needs!)
    ROUND(
        ((ach_percent - GREATEST(prev_month_ach, prev_prev_month_ach)) / GREATEST(prev_month_ach, prev_prev_month_ach)) * 100,
        2
    ) AS ach_pct_change_from_two_month_max,
    -- Calculate monthly sales growth (环比)
    CASE
        WHEN prev_month_sales IS NOT NULL THEN
            ROUND(((sales - prev_month_sales) / prev_month_sales) * 100, 2)
        ELSE NULL -- No data for prior month, skip calculation
    END AS monthly_sales_growth_pct
FROM monthly_metrics
-- Filter out the first two months (no enough historical data to compute two-month max)
WHERE prev_prev_month_ach IS NOT NULL
ORDER BY associate_id, report_year, report_month;

Breakdown of the Query #

1. CTE (monthly_metrics):

   • Uses LAG() to fetch ach% values from 1 and 2 months prior, grouped by each associate and ordered by date.
   • Pulls in previous month's sales to calculate growth later.

2. Main Query:

   • GREATEST(prev_month_ach, prev_prev_month_ach): Grabs the highest ach% from the two consecutive prior months.
   • ach_pct_change_from_two_month_max: Computes the percentage difference between the current month's ach% and the two-month max (adjust this to use absolute difference or another metric if needed).
   • monthly_sales_growth_pct: Standard monthly sales growth calculation, with a check to avoid division by zero.
   • The WHERE clause excludes the first two months of data for each associate, since we can't compute a two-month max without prior history.

Adjustments for Your Existing Code #

If your associate_monthly_ach_p... is a stored procedure or view, just replace the FROM associate_monthly_ach line with your actual object name. If your table uses a single report_date column instead of separate year/month, swap the ORDER BY report_year, report_month with ORDER BY report_date and extract year/month using YEAR(report_date) and MONTH(report_date) if needed.

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