MySQL生成扫描时间序列聚合数据的最优方案咨询

Hey Daniel, great question—let’s break down how to generate those time-bucketed aggregates (min, max, sum, avg) for your scan data, without needing to jump to a data warehouse or overcomplicate things with overly nested queries. Since you mentioned you’re not super familiar with stored procedures, I’ll walk through both ad-hoc query approaches and a reusable stored procedure solution that fits your needs.

1. Ad-Hoc Queries with Time Bucket Calculation #

The core idea here is to "bucket" your scan timestamps into your desired intervals (15min, 30min, 1hr, etc.) using MySQL’s date functions, then aggregate your metrics over those buckets. This works well for one-off analyses or when you need to tweak the logic quickly.

Example: 15-Minute Interval Aggregation #

First, let’s assume your scan records are in a table called scans, with a scan_timestamp DATETIME column, and you need to join with guests, reservations, events, etc., to get the full context. Here’s a query that buckets scans into 15-minute windows and calculates aggregates:

-- 15-minute time buckets
SELECT
  -- Create a start time for each 15-minute bucket
  FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(s.scan_timestamp) / (15 * 60)) * (15 * 60)) AS interval_start,
  -- Aggregation metrics (adjust these to your actual scan metrics, e.g., scan_count, duration)
  MIN(s.scan_value) AS min_scan,
  MAX(s.scan_value) AS max_scan,
  SUM(s.scan_value) AS total_scans,
  AVG(s.scan_value) AS avg_scan,
  -- Optional: Group by related entities if needed
  e.event_name,
  p.promo_code
FROM scans s
JOIN guests g ON s.guest_id = g.id
JOIN reservations r ON g.reservation_id = r.id
LEFT JOIN events e ON r.event_id = e.id
LEFT JOIN promotions p ON r.promo_id = p.id
-- Filter your time range here (critical for performance)
WHERE s.scan_timestamp BETWEEN '2024-01-01 00:00:00' AND '2024-02-01 00:00:00'
GROUP BY interval_start, e.event_name, p.promo_code
ORDER BY interval_start ASC;

Adjusting for Other Intervals #

To switch to different time buckets, just change the denominator in the UNIX_TIMESTAMP calculation:

• 30 minutes: 30 * 60
• 1 hour: 60 * 60
• 4 hours: 4 * 60 * 60
• 1 day: 24 * 60 * 60
• 1 week: Use DATE_FORMAT(s.scan_timestamp, '%Y-%u') to get calendar-aligned week buckets (adjust to %Y-%v if you need weeks starting on Sunday)
• 1 month: Use DATE_FORMAT(s.scan_timestamp, '%Y-%m-01') to get the first day of the month as the bucket start

2. Reusable Stored Procedure for All Intervals #

If you find yourself running these queries often, a stored procedure lets you pass in the interval type and time range as parameters, avoiding repetitive code. Here’s a simple, flexible one:

DELIMITER //
CREATE PROCEDURE GetScanAggregates(
  IN interval_type VARCHAR(20), -- '15min', '30min', '1hr', '4hr', 'day', 'week', 'month'
  IN start_date DATETIME,
  IN end_date DATETIME
)
BEGIN
  DECLARE bucket_sql VARCHAR(1000);
  
  -- Determine the time bucket logic based on input
  CASE interval_type
    WHEN '15min' THEN
      SET bucket_sql = 'FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(s.scan_timestamp)/(15*60))*(15*60)) AS interval_start';
    WHEN '30min' THEN
      SET bucket_sql = 'FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(s.scan_timestamp)/(30*60))*(30*60)) AS interval_start';
    WHEN '1hr' THEN
      SET bucket_sql = 'FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(s.scan_timestamp)/(60*60))*(60*60)) AS interval_start';
    WHEN '4hr' THEN
      SET bucket_sql = 'FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(s.scan_timestamp)/(4*60*60))*(4*60*60)) AS interval_start';
    WHEN 'day' THEN
      SET bucket_sql = 'DATE(s.scan_timestamp) AS interval_start';
    WHEN 'week' THEN
      SET bucket_sql = 'DATE_FORMAT(s.scan_timestamp, ''%Y-%u'') AS interval_start';
    WHEN 'month' THEN
      SET bucket_sql = 'DATE_FORMAT(s.scan_timestamp, ''%Y-%m-01'') AS interval_start';
    ELSE
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid interval type. Use: 15min, 30min, 1hr, 4hr, day, week, month';
  END CASE;
  
  -- Build and execute the dynamic query
  SET @query = CONCAT(
    'SELECT ', bucket_sql, ',
      MIN(s.scan_value) AS min_scan,
      MAX(s.scan_value) AS max_scan,
      SUM(s.scan_value) AS total_scans,
      AVG(s.scan_value) AS avg_scan,
      e.event_name,
      p.promo_code
    FROM scans s
    JOIN guests g ON s.guest_id = g.id
    JOIN reservations r ON g.reservation_id = r.id
    LEFT JOIN events e ON r.event_id = e.id
    LEFT JOIN promotions p ON r.promo_id = p.id
    WHERE s.scan_timestamp BETWEEN ''', start_date, ''' AND ''', end_date, '''
    GROUP BY interval_start, e.event_name, p.promo_code
    ORDER BY interval_start ASC'
  );
  
  PREPARE stmt FROM @query;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

How to Use the Stored Procedure #

Call it with your desired interval and date range:

-- Get 1-hour aggregates for January 2024
CALL GetScanAggregates('1hr', '2024-01-01 00:00:00', '2024-02-01 00:00:00');

3. Performance Tips #

Since you’re dealing with time-series data, these tweaks will keep your queries fast:

• Index the scan timestamp: Add an index on scans.scan_timestamp to speed up filtering and grouping:

  CREATE INDEX idx_scans_timestamp ON scans(scan_timestamp);
  

• Filter early: Always include a WHERE clause for the time range to avoid scanning the entire table.
• Avoid over-joining: Only join tables you actually need for your aggregates (e.g., skip events if you don’t need to group by event name).
• Consider partitioning: If your scans table is huge, partition it by scan_timestamp (e.g., monthly partitions) to reduce the data scanned per query.

Final Notes #

You’re right that moving to a data warehouse isn’t necessary yet—these approaches work well for moderate-sized datasets. The ad-hoc queries are great for experimentation, while the stored procedure is perfect for repeated reports. If you start hitting performance limits down the line, you could look into materialized views (MySQL has them in 8.0.19+) or incremental aggregation, but for now, these should cover your needs.

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