带多统计与分组的MySQL存储过程开发——慈善假期礼品申领APP数据库

Hey there, let's build out some practical stored procedures for your charity app based on the table structure you shared. First, let's recap and flesh out the tables to make examples concrete (since your Families table was cut off):

CREATE TABLE IF NOT EXISTS ValidDates( 
    chosenYear YEAR PRIMARY KEY, 
    startDate DATE NOT NULL, 
    endDate DATE NOT NULL, 
    maxReservationsPerDay INTEGER default 50, 
    CONSTRAINT chkDates CHECK(startDate < endDate) 
) ENGINE=INNODB;

-- Completed Families table (common fields for this use case)
CREATE TABLE IF NOT EXISTS Families( 
    fID INTEGER PRIMARY KEY AUTO_INCREMENT,
    familyName VARCHAR(100) NOT NULL,
    contactEmail VARCHAR(100) UNIQUE NOT NULL,
    numberOfMembers INTEGER NOT NULL,
    registrationDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB;

-- Reservations table (critical for linking families to sign-up dates)
CREATE TABLE IF NOT EXISTS Reservations(
    resID INTEGER PRIMARY KEY AUTO_INCREMENT,
    fID INTEGER NOT NULL,
    signUpDate DATE NOT NULL,
    FOREIGN KEY (fID) REFERENCES Families(fID),
    FOREIGN KEY (signUpDate) REFERENCES ValidDates(chosenYear)
    -- Adjust this FK if your schema ties reservations directly to date ranges instead of year
) ENGINE=INNODB;

Now let's create stored procedures for common stats and grouping needs tailored to your app:

1. Daily Reservation Stats vs. Capacity #

This procedure returns daily sign-up counts for a given year, paired with max allowed reservations, so you can track remaining slots easily.

DELIMITER //
CREATE PROCEDURE GetDailyReservationStats(IN targetYear YEAR)
BEGIN
    SELECT 
        v.startDate,
        v.endDate,
        v.maxReservationsPerDay,
        COALESCE(COUNT(r.resID), 0) AS dailyReservations,
        (v.maxReservationsPerDay - COALESCE(COUNT(r.resID), 0)) AS remainingSlots
    FROM ValidDates v
    LEFT JOIN Reservations r 
        ON r.signUpDate BETWEEN v.startDate AND v.endDate
        AND YEAR(r.signUpDate) = targetYear
    WHERE v.chosenYear = targetYear
    GROUP BY v.startDate, v.endDate, v.maxReservationsPerDay
    ORDER BY r.signUpDate;
END //
DELIMITER ;

-- Call it like this:
CALL GetDailyReservationStats(2024);

2. Family Sign-Up Grouping by Household Size #

This procedure groups families by member count to help plan gift quantities and resource allocation.

DELIMITER //
CREATE PROCEDURE GetFamilySizeGroupStats(IN targetYear YEAR)
BEGIN
    SELECT 
        CASE 
            WHEN f.numberOfMembers <= 2 THEN 'Small (1-2 members)'
            WHEN f.numberOfMembers BETWEEN 3 AND 5 THEN 'Medium (3-5 members)'
            ELSE 'Large (6+ members)'
        END AS familySizeGroup,
        COUNT(f.fID) AS familyCount,
        SUM(f.numberOfMembers) AS totalPeople
    FROM Families f
    JOIN Reservations r ON f.fID = r.fID
    JOIN ValidDates v ON YEAR(r.signUpDate) = v.chosenYear
    WHERE v.chosenYear = targetYear
    GROUP BY familySizeGroup
    ORDER BY familyCount DESC;
END //
DELIMITER ;

-- Call it like this:
CALL GetFamilySizeGroupStats(2024);

3. Monthly Reservation Trend for the Holiday Season #

This procedure breaks down sign-ups by month to identify peak registration periods.

DELIMITER //
CREATE PROCEDURE GetMonthlyReservationTrend(IN targetYear YEAR)
BEGIN
    SELECT 
        MONTHNAME(r.signUpDate) AS month,
        COUNT(r.resID) AS monthlyReservations
    FROM Reservations r
    JOIN ValidDates v 
        ON r.signUpDate BETWEEN v.startDate AND v.endDate
        AND v.chosenYear = targetYear
    GROUP BY MONTH(r.signUpDate), MONTHNAME(r.signUpDate)
    ORDER BY MONTH(r.signUpDate);
END //
DELIMITER ;

-- Call it like this:
CALL GetMonthlyReservationTrend(2024);

Quick Notes #

• I added the Reservations table because it's essential for linking families to their sign-up dates—tweak the FK logic if your actual schema works differently.
• COALESCE ensures days with zero reservations still show up in results, so you don't miss any dates in your valid range.
• Feel free to adjust the grouping logic (like the CASE statements) to match your charity's specific needs.

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