SQL Server：特定时间区间数据统计及批量循环需求求助

Fixing the TIME Range Query Syntax Error #

First, let's resolve that SQL syntax issue. The "Incorrect syntax near the keyword 'where'" error almost always means your query structure is misaligned—maybe you misplaced the WHERE clause, or forgot to properly structure your SELECT statement before it.

Here's a correct, tested example to filter rows between two TIME values and sum your target column:

SELECT SUM(your_target_column) AS total_calculated_sum
FROM your_table_name
WHERE time_column BETWEEN '01:00:00' AND '01:05:00';

Double-check these details to avoid mistakes:

• Replace your_table_name with your actual table's name
• time_column should be your TIME-type column (matches the data type you mentioned)
• your_target_column is the column you want to aggregate
• Ensure your time strings follow your database's accepted TIME format (most systems work with 'HH:MM:SS')

If you still hit errors, verify:

• No extra commas or misplaced clauses (like GROUP BY) before the WHERE statement
• You haven't accidentally mixed up clause order (e.g., putting WHERE after ORDER BY)

Scheduling Recurring 5-Minute Stats for 2-3 Hours #

To run this calculation every 5 minutes over a 2-3 hour window, the approach depends on your database system. Here are the most common implementations:

For SQL Server #

Use SQL Server Agent to set up a scheduled job:

1. Open SQL Server Management Studio (SSMS), navigate to SQL Server Agent > Jobs
2. Create a new job, add a step with your sum query (you can also add logic to save results to a stats table if needed)
3. Configure the schedule:
   • Set frequency to Daily
   • Under "Daily frequency", choose "Occurs every 5 minutes"
   • Define your start and end times (e.g., start at 01:00:00, end at 04:00:00 for a 3-hour window)
4. Save the job—it will run automatically during your specified time frame

For MySQL #

Use the built-in Event Scheduler:
First, enable the scheduler if it's disabled:

SET GLOBAL event_scheduler = ON;

Then create the recurring event:

CREATE EVENT recurring_5min_stats
ON SCHEDULE EVERY 5 MINUTE
STARTS '2024-05-20 01:00:00' -- Replace with your desired start datetime
ENDS '2024-05-20 04:00:00' -- 3 hours after start time
DO
BEGIN
  -- Example: Save results to a stats log table
  INSERT INTO stats_log (run_timestamp, total_sum)
  SELECT NOW(), SUM(your_target_column)
  FROM your_table_name
  WHERE time_column BETWEEN '01:00:00' AND '01:05:00'; -- Use dynamic logic below for variable windows
END;

If you want each run to calculate the sum for the previous 5 minutes (instead of a fixed window), use dynamic time calculations:

WHERE time_column BETWEEN DATE_FORMAT(NOW() - INTERVAL 5 MINUTE, '%H:%i:%s') AND DATE_FORMAT(NOW(), '%H:%i:%s')

For PostgreSQL #

Use the pg_cron extension (you'll need to install it first):

-- Enable the extension if not already active
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- Schedule the 5-minute stats job
SELECT cron.schedule(
  '5-minute-stats-job',
  '*/5 * * * *', -- Runs every 5 minutes
  $$
    INSERT INTO stats_log (run_time, total_sum)
    SELECT NOW(), SUM(your_target_column)
    FROM your_table_name
    WHERE time_column BETWEEN '01:00:00' AND '01:05:00'; -- Or use dynamic time logic
  $$
);

-- To stop the job after 3 hours, schedule an unschedule command
SELECT cron.schedule(
  'stop-stats-job',
  '0 4 * * *', -- Adjust to your end time (e.g., 04:00 if starting at 01:00)
  $$SELECT cron.unschedule('5-minute-stats-job');$$
);

For dynamic time ranges in PostgreSQL:

WHERE time_column BETWEEN NOW() - INTERVAL '5 minutes' AND NOW()

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