如何通过关联查询计算工作日销售额较上一工作日的变动值

Got it, let's break this down. First, let's confirm your source data and expected output to align on what we're building:

Original Sales Data #

Expected Output #

Solution SQL #

The trick here is using the LAG() window function—it's perfect for this scenario because it automatically grabs the most recent prior row in your ordered dataset. Since weekends don't have entries in your table, LAG() skips right over them and pulls the last valid working day's sales, no extra weekend-handling logic needed.

Here's the query (swap your_sales_table with your actual table name):

SELECT
    date,
    Item,
    net_sale,
    net_sale - LAG(net_sale) OVER (PARTITION BY Item ORDER BY date) AS change
FROM
    your_sales_table
ORDER BY
    date;

How It Works #

• PARTITION BY Item: Makes sure we only compare sales for the same product (super useful if you add more items to your table later).
• ORDER BY date: Tells LAG() to look at rows in chronological order, so it always picks the immediate prior working day (even when weekends are missing, like between 2023/01/06 and 2023/01/09).
• net_sale - LAG(net_sale): Calculates the difference between the current day's sales and the previous working day's sales. The first row returns NULL because there's no prior data, which matches your expected output exactly.

This works across all modern SQL databases (MySQL 8+, PostgreSQL, SQL Server, etc.) without any extra complexity.

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