Teradata中特殊类型累计求和的查询实现问询

Got it, let's solve this running total problem that automatically adjusts as new monthly claims data comes in—no hardcoded dates required.

First, let's clarify the core requirement: we need to calculate cumulative sums starting from January 2020, where each row shows the total from Jan 2020 up to that month. And when new monthly data is added, the query should pick it up without any edits.

Step 1: Aggregate Monthly Claims #

First, we'll group our raw claims data by month to get the total for each individual month. This simplifies the running total calculation later.

Step 2: Calculate Running Total with Window Functions #

Using a window function, we'll compute the cumulative sum ordered by month. This ensures each row includes the sum from Jan 2020 up to the current month.

Standard SQL Query (Works for PostgreSQL, BigQuery, etc.) #

WITH monthly_claims AS (
    SELECT
        DATE_TRUNC('month', claim_date) AS claim_month,
        SUM(claim_amount) AS monthly_total
    FROM claims
    WHERE claim_date >= '2020-01-01' -- Filter to start from Jan 2020
    GROUP BY DATE_TRUNC('month', claim_date)
)
SELECT
    TO_CHAR(claim_month, 'YYYY-MM') AS reporting_period,
    SUM(monthly_total) OVER (
        ORDER BY claim_month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_from_jan2020
FROM monthly_claims
ORDER BY claim_month;

Adjustments for Other Databases #

If you're using a different SQL dialect, tweak the date functions:

• MySQL: Replace DATE_TRUNC('month', claim_date) with DATE_FORMAT(claim_date, '%Y-%m-01'), and TO_CHAR with DATE_FORMAT(claim_month, '%Y-%m').
• SQL Server: Replace DATE_TRUNC('month', claim_date) with DATEFROMPARTS(YEAR(claim_date), MONTH(claim_date), 1), and TO_CHAR with FORMAT(claim_month, 'yyyy-MM').

Why This Works Automatically #

• No Hardcoded Dates: The WHERE clause only filters for dates on or after Jan 2020—any new months added to the claims table will be picked up by the DATE_TRUNC grouping.
• Dynamic Cumulative Sum: The window function SUM(...) OVER (...) automatically includes new months in the running total as they're added to the monthly_claims CTE.

Alternative: Columnar Output (If You Need All Totals in One Row) #

If you specifically need each cumulative total as a separate column (e.g., jan_2020_total, jan_feb_2020_total, etc.), this requires dynamic SQL since the number of columns will grow with new months. However, this is more complex and database-specific. For most use cases, the row-based output above is preferable because it's simpler and naturally adapts to new data.

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