同一SELECT语句能否包含多个GROUP BY子句？动态分组存储过程咨询

Got it, let's build this stored procedure step by step. Based on your requirements, we need to dynamically adjust the grouping granularity (day/month/year) depending on the length of the input date range, while joining with your related table to map IDs to their unique dates and return aggregated stats.

1. Core Setup & Assumptions #

First, let's clarify the baseline (I'll use SQL Server syntax here, but the logic translates to other databases with minor tweaks):

• Main table: main_table with main_id (your current table's ID) and related_id (links to the related table)
• Related table: related_table with related_id and record_date (unique date per related_id)
• Input parameters: Start date, end date, and a threshold for when to switch from daily to monthly grouping (e.g., 30 days as a default)
• Stats to return: We'll use COUNT(main_id) as a sample aggregate, but you can swap this for SUM/Avg/etc. based on your actual needs

2. Stored Procedure Implementation #

CREATE PROCEDURE GetDateRangeStats
    @StartDate DATE,
    @EndDate DATE,
    @MaxDailyThreshold INT = 30 -- Default: switch to monthly if range exceeds 30 days
AS
BEGIN
    SET NOCOUNT ON;

    -- Calculate key metrics for grouping logic
    DECLARE @TotalDays INT = DATEDIFF(DAY, @StartDate, @EndDate) + 1;
    DECLARE @TotalMonths INT = 
        (YEAR(@EndDate) - YEAR(@StartDate)) * 12 + 
        (MONTH(@EndDate) - MONTH(@StartDate)) + 1;

    -- Case 1: Date range stays within daily threshold
    IF @TotalDays <= @MaxDailyThreshold
    BEGIN
        SELECT
            rt.record_date AS GroupDate,
            COUNT(mt.main_id) AS RecordCount,
            mt.main_id,
            mt.related_id
        FROM main_table mt
        LEFT JOIN related_table rt ON mt.related_id = rt.related_id
        WHERE rt.record_date BETWEEN @StartDate AND @EndDate
        GROUP BY rt.record_date, mt.main_id, mt.related_id
        ORDER BY rt.record_date;
    END
    -- Case 2: Date range exceeds daily threshold but is within 12 months
    ELSE IF @TotalMonths <= 12
    BEGIN
        SELECT
            YEAR(rt.record_date) AS GroupYear,
            MONTH(rt.record_date) AS GroupMonth,
            DATENAME(MONTH, rt.record_date) + ' ' + CAST(YEAR(rt.record_date) AS VARCHAR) AS GroupName,
            COUNT(mt.main_id) AS RecordCount,
            mt.main_id,
            mt.related_id
        FROM main_table mt
        LEFT JOIN related_table rt ON mt.related_id = rt.related_id
        WHERE rt.record_date BETWEEN @StartDate AND @EndDate
        GROUP BY YEAR(rt.record_date), MONTH(rt.record_date), DATENAME(MONTH, rt.record_date), mt.main_id, mt.related_id
        ORDER BY GroupYear, GroupMonth;
    END
    -- Case 3: Date range spans over 12 months
    ELSE
    BEGIN
        SELECT
            YEAR(rt.record_date) AS GroupYear,
            CAST(YEAR(rt.record_date) AS VARCHAR) AS GroupName,
            COUNT(mt.main_id) AS RecordCount,
            mt.main_id,
            mt.related_id
        FROM main_table mt
        LEFT JOIN related_table rt ON mt.related_id = rt.related_id
        WHERE rt.record_date BETWEEN @StartDate AND @EndDate
        GROUP BY YEAR(rt.record_date), mt.main_id, mt.related_id
        ORDER BY GroupYear;
    END
END

3. Key Adjustments & Notes #

• Raw Data vs. Aggregates: You mentioned "return all related data" - the example includes main_id and related_id in the GROUP BY to retain individual records. If you only need aggregated totals (no per-ID details), remove those fields from both GROUP BY and SELECT clauses.
• Threshold Flexibility: The @MaxDailyThreshold is a parameter, so you can tweak it when calling the proc (e.g., EXEC GetDateRangeStats '2023-01-01', '2023-02-01', 45).
• Date Boundaries: DATEDIFF(DAY, ...) +1 ensures we count both start and end dates as part of the range. Adjust this if you need exclusive boundaries.
• Database Compatibility: For MySQL, replace DATENAME with MONTHNAME; for PostgreSQL, use TO_CHAR(rt.record_date, 'Month') instead of DATENAME.
• NULL Handling: The LEFT JOIN keeps records from main_table even if there's no matching related_id - switch to INNER JOIN if you only want records with valid dates.

4. Usage Examples #

Call the procedure with your desired date range and threshold:

-- Daily stats for a 2-week range
EXEC GetDateRangeStats '2024-01-01', '2024-01-14';

-- Monthly stats for a 6-month range (exceeds 30-day threshold)
EXEC GetDateRangeStats '2023-07-01', '2024-01-01';

-- Yearly stats for a 2-year range
EXEC GetDateRangeStats '2022-01-01', '2024-01-01';

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