多时间区间报表SQL方案选型：子查询VS窗口函数（PostgreSQL 8.0+AWS表）

Hey there! Let's dive into your question about building a report with week-to-date (WTD), month-to-date (MTD), and year-to-date (YTD) metrics in PostgreSQL 8.0, comparing subqueries vs. window functions. I know you're leaning toward subqueries, so let's break down both options clearly.

Performance & Simplicity Comparison #

1. Subquery Approach #

First, let's recap how this would look—you'd write a separate subquery for each time period to calculate cumulative totals, either via correlated subqueries or joining aggregated subqueries. Here's an example of the correlated subquery style:

SELECT
    t.date,
    SUM(t.value) AS daily_value,
    -- WTD total
    (SELECT SUM(value) 
     FROM your_table 
     WHERE date_trunc('week', date) = date_trunc('week', t.date) 
       AND date <= t.date) AS wtd_total,
    -- MTD total
    (SELECT SUM(value) 
     FROM your_table 
     WHERE date_trunc('month', date) = date_trunc('month', t.date) 
       AND date <= t.date) AS mtd_total,
    -- YTD total
    (SELECT SUM(value) 
     FROM your_table 
     WHERE date_trunc('year', date) = date_trunc('year', t.date) 
       AND date <= t.date) AS ytd_total
FROM your_table t
GROUP BY t.date
ORDER BY t.date;

Pros: It's straightforward and easy to follow if you're more comfortable with basic SQL constructs. No need to wrap your head around window function syntax.
Cons: This approach hits your table three extra times (once per correlated subquery) on top of the main query scan. For large AWS-hosted tables, that means more IO, slower execution, and potentially higher costs (since many AWS data services charge based on data scanned). It's also repetitive—you're writing almost identical logic three times, which makes maintenance a pain if you ever need to adjust the aggregation.

2. Window Function Approach #

You mentioned using CASE with partition over, but for cumulative totals like WTD/MTD/YTD, you don't even need CASE—just use SUM() OVER () with partitioning by the time period. Here's how that looks:

SELECT
    date,
    SUM(value) AS daily_value,
    -- WTD cumulative total
    SUM(SUM(value)) OVER (
        PARTITION BY date_trunc('week', date)
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS wtd_total,
    -- MTD cumulative total
    SUM(SUM(value)) OVER (
        PARTITION BY date_trunc('month', date)
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS mtd_total,
    -- YTD cumulative total
    SUM(SUM(value)) OVER (
        PARTITION BY date_trunc('year', date)
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS ytd_total
FROM your_table
GROUP BY date
ORDER BY date;

(Note: PostgreSQL 8.0 fully supports this window function syntax—including PARTITION BY, ORDER BY, and the ROWS clause. The ROWS BETWEEN part is optional here because ORDER BY in a window function defaults to cumulative from the start of the partition, but writing it explicitly makes the logic clearer.)

Pros: This approach scans your table only once. All the cumulative calculations happen in-memory after the initial scan, which is way faster for large datasets. It's also far more concise—no repeated subquery logic, so if you need to tweak the aggregation (like changing SUM to AVG), you only have to adjust it in a few spots instead of three separate subqueries.
Cons: If you're new to window functions, the syntax might take a minute to get used to, but once you grasp how partitioning works, it's intuitive.

Final Verdict #

• Performance: Window functions win hands down, especially with large tables. The single scan vs. multiple scans makes a huge difference in execution time and AWS costs.
• Simplicity: Once you're comfortable with window functions, this approach is more concise and easier to maintain. The subquery approach is simpler for beginners, but the repetition becomes a liability long-term.

I get why you might lean toward subqueries—they feel familiar! But if you're working with non-trivial data volumes, the window function approach is the better choice for both speed and clean code.

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