SQL中CustomerID表每日两次加载后COUNT结果对比及无新增记录告警需求

Hey, here's a practical, straightforward way to set up the monitoring you need for your CustomerID table—focused solely on checking if the 22:00 load adds new records, and alerting if it doesn't:

1. Create an Audit Table to Track Daily Counts #

First, set up a small audit table to store the morning (10 AM) and evening (22 PM) record counts. This keeps a historical log and makes comparing the two values simple:

CREATE TABLE CustomerID_Audit (
    AuditDate DATE PRIMARY KEY,
    MorningCount INT NOT NULL,
    EveningCount INT NULL
);

2. Schedule a 10 AM Job to Capture the Baseline Count #

Use your database's built-in scheduler (like SQL Server Agent, MySQL Event Scheduler) or an external tool (cron, Task Scheduler) to run a daily job at 10:00 AM. This job saves the record count for the day:

For SQL Server: #

MERGE INTO CustomerID_Audit AS Target
USING (SELECT CAST(GETDATE() AS DATE) AS AuditDate, COUNT(*) AS MorningCount FROM CustomerID) AS Source
ON Target.AuditDate = Source.AuditDate
WHEN MATCHED THEN 
    UPDATE SET Target.MorningCount = Source.MorningCount
WHEN NOT MATCHED THEN 
    INSERT (AuditDate, MorningCount) VALUES (Source.AuditDate, Source.MorningCount);

For MySQL: #

INSERT INTO CustomerID_Audit (AuditDate, MorningCount)
SELECT CURDATE(), COUNT(*) FROM CustomerID
ON DUPLICATE KEY UPDATE MorningCount = VALUES(MorningCount);

3. Schedule a 22 PM Job to Compare Counts and Alert on Anomalies #

At 22:00 PM, run another job that grabs the latest count, updates the audit table, and checks if the number of records has grown. If not, send an alert to the responsible person:

Example SQL Server Stored Procedure (easily adaptable to other DBs): #

CREATE PROCEDURE CheckCustomerIDGrowth
AS
BEGIN
    DECLARE @Today DATE = CAST(GETDATE() AS DATE);
    DECLARE @MorningCount INT, @EveningCount INT;

    -- Fetch the saved morning count
    SELECT @MorningCount = MorningCount FROM CustomerID_Audit WHERE AuditDate = @Today;

    -- Get the current evening count
    SET @EveningCount = (SELECT COUNT(*) FROM CustomerID);

    -- Update the audit table with the evening number
    UPDATE CustomerID_Audit 
    SET EveningCount = @EveningCount 
    WHERE AuditDate = @Today;

    -- Check for the "no growth" anomaly
    IF @EveningCount <= @MorningCount
    BEGIN
        -- Optional: Log the anomaly for future reference
        INSERT INTO Audit_Alerts (AlertTimestamp, AlertMessage)
        VALUES (GETDATE(), 'CustomerID表异常: 22点加载后未新增记录，记录数未增长');

        -- Send a notification to the responsible team member
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'YourCompanyMailProfile',
            @recipients = 'your-team-lead@company.com',
            @subject = '⚠️ CustomerID表监控异常',
            @body = '22点加载后未产生新增记录，不符合预期的持续增长趋势，请及时核查。';
    END
END

Quick Tips #

• Scheduler Reliability: Make sure your scheduled jobs are set to run even if the database restarts—most built-in schedulers handle this automatically, but double-check.
• Alerting Flexibility: If email isn't your team's go-to, you can swap the email step with a script to send a Slack message, trigger an alert in your incident tool, or even a simple text notification.
• Historical Insight: The audit table isn't just for daily checks—it lets you go back and see growth trends over weeks or months, which can help spot larger issues early.

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