Oracle中按月份分组表数据的SQL查询需求及表数据说明

Hey there, let's break down how to group your CAPACITY_MON.TABLESPACE_GROWTH_HIST data by month. Based on the sample data and query you provided, here are practical, tailored solutions:

Basic Monthly Grouping (By Instance + Tablespace) #

If you want to see aggregated metrics (like total used space) for each tablespace, per instance, per month, use this query:

SELECT
    INSTANCE,
    TABLESPACE_NAME,
    TRUNC(REPORT_DA, 'MM') AS REPORT_MONTH, -- Truncates date to the first day of the month
    SUM(TOTAL_USED) AS MONTHLY_TOTAL_USED,
    AVG(TOTAL_USED) AS MONTHLY_AVG_USED,
    MAX(TOTAL_USED) AS MONTHLY_PEAK_USED,
    MIN(TOTAL_FREE) AS MONTHLY_MIN_FREE
FROM CAPACITY_MON.TABLESPACE_GROWTH_HIST
WHERE INSTANCE = 'MONDAY'
GROUP BY INSTANCE, TABLESPACE_NAME, TRUNC(REPORT_DA, 'MM')
ORDER BY REPORT_MONTH DESC, TABLESPACE_NAME;

Key Details: #

• TRUNC(REPORT_DA, 'MM'): This function cuts the REPORT_DA date down to the first day of its month (e.g., 27-MAR-18 becomes 01-MAR-18), ensuring all records from the same month are grouped together.
• Aggregation functions: Swap SUM, AVG, etc., with whatever metrics matter to you (total free space, max free block size, etc.).
• GROUP BY: Must include all non-aggregated columns (instance, tablespace, truncated month) to avoid Oracle errors.

Overall Monthly Summary (Across All Tablespaces) #

If you just want a high-level monthly overview for the instance, remove the tablespace from the grouping:

SELECT
    TRUNC(REPORT_DA, 'MM') AS REPORT_MONTH,
    SUM(TOTAL_USED) AS OVERALL_MONTHLY_USED,
    SUM(TOTAL_SIZE) AS OVERALL_MONTHLY_TOTAL_SIZE,
    SUM(TOTAL_FREE) AS OVERALL_MONTHLY_FREE
FROM CAPACITY_MON.TABLESPACE_GROWTH_HIST
WHERE INSTANCE = 'MONDAY'
GROUP BY TRUNC(REPORT_DA, 'MM')
ORDER BY REPORT_MONTH DESC;

Friendly Month Formatting (e.g., "March 2018") #

If you want readable month names instead of raw dates, use TO_CHAR to format the truncated date. For better performance, do the grouping first then format:

SELECT
    INSTANCE,
    TABLESPACE_NAME,
    TO_CHAR(REPORT_MONTH, 'Month YYYY') AS FORMATTED_MONTH,
    MONTHLY_TOTAL_USED
FROM (
    -- Inner query handles grouping efficiently
    SELECT
        INSTANCE,
        TABLESPACE_NAME,
        TRUNC(REPORT_DA, 'MM') AS REPORT_MONTH,
        SUM(TOTAL_USED) AS MONTHLY_TOTAL_USED
    FROM CAPACITY_MON.TABLESPACE_GROWTH_HIST
    WHERE INSTANCE = 'MONDAY'
    GROUP BY INSTANCE, TABLESPACE_NAME, TRUNC(REPORT_DA, 'MM')
)
ORDER BY REPORT_MONTH DESC, TABLESPACE_NAME;

Handling String Date Columns (If REPORT_DA is Text) #

If REPORT_DA is stored as a string instead of a DATE type, first convert it to a date with TO_DATE before truncating:

SELECT
    INSTANCE,
    TABLESPACE_NAME,
    TRUNC(TO_DATE(REPORT_DA, 'DD-MON-RR'), 'MM') AS REPORT_MONTH,
    SUM(TOTAL_USED) AS MONTHLY_TOTAL_USED
FROM CAPACITY_MON.TABLESPACE_GROWTH_HIST
WHERE INSTANCE = 'MONDAY'
GROUP BY INSTANCE, TABLESPACE_NAME, TRUNC(TO_DATE(REPORT_DA, 'DD-MON-RR'), 'MM')
ORDER BY REPORT_MONTH DESC;

The format mask 'DD-MON-RR' matches your sample date format (27-MAR-18). Adjust it if your actual string dates use a different format.

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