计算当前日期前后2天销售额总和的SQL实现问询

Let's break down what's wrong with your current query, then walk through two working solutions to get the sales sums you need.

The Issue with Your Original Query #

Your current approach uses CASE statements within a grouped query, but here's the critical flaw: once you GROUP BY end_Date, every row in the group has the exact same end_Date. So when you write case when end_Date between DATE_SUB(end_Date, INTERVAL 2 day) and end_Date, you're essentially checking if the date equals itself—this will only sum sales for the current day, not the 2-day window you want. You can't cross date boundaries when you've already grouped by individual dates.

What You Actually Need #

You want two key aggregates per date:

• total_2_days_prior_sales: Sum of sales from 2 days before the current date up to and including the current date
• total_2_days_after_sales: Sum of sales from the current date up to and including 2 days after

Solution 1: Window Functions (MySQL 8.0+) #

Window functions are the cleanest way to handle sliding date ranges if your MySQL version supports them (8.0 and above). First, we'll aggregate daily sales to avoid redundant calculations, then use window ranges to get the sums:

WITH daily_sales AS (
    -- First, calculate total sales per individual date
    SELECT end_Date, SUM(sales) AS daily_total
    FROM test
    GROUP BY end_Date
)
SELECT 
    end_Date,
    daily_total AS CurrentSales,
    -- Sum from 2 days prior to current date
    SUM(daily_total) OVER (
        ORDER BY end_Date
        RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
    ) AS total_2_days_prior_sales,
    -- Sum from current date to 2 days after
    SUM(daily_total) OVER (
        ORDER BY end_Date
        RANGE BETWEEN CURRENT ROW AND INTERVAL 2 DAY FOLLOWING
    ) AS total_2_days_after_sales
FROM daily_sales
ORDER BY end_Date;

How This Works: #

• The daily_sales CTE (Common Table Expression) first collapses multiple rows per date into a single row with the total daily sales, simplifying the window calculations.
• The OVER() clause defines a sliding window for each date:
  • RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW includes all dates from 2 days before the current row's date up to the current date.
  • RANGE BETWEEN CURRENT ROW AND INTERVAL 2 DAY FOLLOWING includes all dates from the current row's date up to 2 days after.

Solution 2: Self-Joins (Compatible with Older MySQL Versions) #

If you're using a MySQL version that doesn't support window functions, you can use self-joins to pull in the relevant date ranges:

SELECT 
    t1.end_Date,
    SUM(t1.sales) AS CurrentSales,
    SUM(t2.sales) AS total_2_days_prior_sales,
    SUM(t3.sales) AS total_2_days_after_sales
FROM test t1
-- Join to fetch sales from 2 days prior to current date
LEFT JOIN test t2 
    ON t2.end_Date BETWEEN DATE_SUB(t1.end_Date, INTERVAL 2 DAY) AND t1.end_Date
-- Join to fetch sales from current date to 2 days after
LEFT JOIN test t3 
    ON t3.end_Date BETWEEN t1.end_Date AND DATE_ADD(t1.end_Date, INTERVAL 2 DAY)
GROUP BY t1.end_Date
ORDER BY t1.end_Date;

How This Works: #

• We join the test table to itself twice:
  • t2 brings in all records where the date falls within the 2-day window before and including the current date (t1.end_Date).
  • t3 brings in all records where the date falls within the current date and the next 2 days.
• Grouping by t1.end_Date lets us aggregate the sales sums for each date's target windows.

Example Result Check #

For 2022-01-03:

• total_2_days_prior_sales should be 20 (2022-01-01) + 20 (2022-01-02) + 10 (2022-01-03) = 50
• total_2_days_after_sales should be 10 (2022-01-03) + 10 (2022-01-04) + 10 (2022-01-05) = 30

Both solutions will return this correct value.

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