MySQL技术问询：如何统计当前周内每日的呼叫总量

Hey there! You've already got solid procedures for daily and weekly total calls, so expanding that to daily breakdowns for the current week is straightforward. Let's walk through two solutions depending on your needs:

Solution 1: Basic Daily Breakdown (Only Days with Calls) #

This procedure will return each day in the current week that has call records for the specified account, along with the total calls for that day. It matches the YEARWEEK logic you used in your existing GETTotalCallsThisWeek procedure to keep consistency (using mode 1, where Monday is the first day of the week).

DROP PROCEDURE IF EXISTS GETDailyCallsThisWeek;
DELIMITER //
CREATE PROCEDURE GETDailyCallsThisWeek(IN account_NO INT(11))
BEGIN
    -- Group calls by date within the current week, filtered by account
    SELECT 
        DATE(Start_Call_Time) AS call_date,
        COUNT(*) AS total_calls
    FROM logfile
    WHERE 
        YEARWEEK(Start_Call_Time, 1) = YEARWEEK(CURDATE(), 1)
        AND AccountNO = account_NO
    GROUP BY DATE(Start_Call_Time)
    ORDER BY call_date ASC;
END //
DELIMITER ;

-- Example usage:
-- CALL GETDailyCallsThisWeek(1);

Solution 2: Complete Weekly Breakdown (All Days, Even with 0 Calls) #

If you want to see every day of the current week—including days where the account had no calls (showing 0 instead of omitting the day)—we can generate a list of all dates in the current week and left join it with your call logs. This is great for reporting where you need a full weekly view.

DROP PROCEDURE IF EXISTS GETDailyCallsThisWeekWithAllDays;
DELIMITER //
CREATE PROCEDURE GETDailyCallsThisWeekWithAllDays(IN account_NO INT(11))
BEGIN
    -- Generate all dates in the current week (Monday to Sunday)
    WITH week_dates AS (
        SELECT 
            DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) + INTERVAL n DAY AS week_day
        FROM (
            SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL 
            SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
        ) AS numbers
    )
    -- Left join with call logs to get total calls per day (0 if no calls)
    SELECT 
        w.week_day AS call_date,
        COALESCE(l.total_calls, 0) AS total_calls
    FROM week_dates w
    LEFT JOIN (
        SELECT 
            DATE(Start_Call_Time) AS call_date,
            COUNT(*) AS total_calls
        FROM logfile
        WHERE 
            YEARWEEK(Start_Call_Time, 1) = YEARWEEK(CURDATE(), 1)
            AND AccountNO = account_NO
        GROUP BY DATE(Start_Call_Time)
    ) l ON w.week_day = l.call_date
    ORDER BY w.week_day ASC;
END //
DELIMITER ;

-- Example usage:
-- CALL GETDailyCallsThisWeekWithAllDays(1);

Key Notes: #

• Consistent Week Logic: Both procedures use YEARWEEK(..., 1) to match your existing code, ensuring the week starts on Monday. If you need Sunday as the first day, change the 1 to 0.
• MySQL Version Compatibility: The second procedure uses a CTE (WITH clause), which works in MySQL 8.0+. If you're on an older version (like 5.7), replace the CTE with a manual UNION ALL to list the week's dates:

  -- Alternative date list for MySQL 5.7 and below
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AS week_day UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE())-1 DAY) UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE())-2 DAY) UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE())-3 DAY) UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE())-4 DAY) UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE())-5 DAY) UNION ALL
  SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE())-6 DAY)
  

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