使用While函数时遇天数耗尽问题，求助SQL按日筛选数据至临时表

Hi Thomas,

Looks like you ran into a frustrating bug with your WHILE loop approach for filtering time-series data and importing records into a temp table. The good news is you don’t need loops at all here—SQL’s set-based operations are perfect for this task, and they’ll eliminate those "days exhausted" issues entirely.

Here’s a clean, efficient solution that breaks your problem into two straightforward steps: first identify the dates that meet your criteria, then pull all 24-hour records for those dates into your temp table.

Step-by-Step Solution #

1. Pinpoint Valid Dates

First, we’ll find all dates in the last 30 days where at least one hourly record satisfies SO2_7PCT > 44 and NOX_LB_Validity = 'Valid'. We use DISTINCT to get unique dates since we only need to confirm the date qualifies, not every matching hour.

2. Import All Records for Valid Dates

We then join this list of valid dates back to your original table to grab every hourly record for those dates, and insert them into your temp table in one go.

Example Code (Adjust for Your Database) #

MySQL Version

-- Create temp table (match your original table's schema exactly)
CREATE TEMPORARY TABLE IF NOT EXISTS temp_daily_records (
    timestamp DATETIME,
    SO2_7PCT DECIMAL(10,2),
    NOX_LB_Validity VARCHAR(10),
    -- Add any other columns from your source table here
);

-- Clear temp table if re-running the procedure to avoid duplicates
TRUNCATE TABLE temp_daily_records;

-- Insert all 24-hour records from valid dates
INSERT INTO temp_daily_records
SELECT t.*
FROM your_source_table t
INNER JOIN (
    -- Subquery to get dates with at least one qualifying hour
    SELECT DISTINCT DATE(timestamp) AS target_date
    FROM your_source_table
    WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
      AND SO2_7PCT > 44
      AND NOX_LB_Validity = 'Valid'
) AS valid_dates ON DATE(t.timestamp) = valid_dates.target_date;

SQL Server Version

-- Drop temp table if it exists (prevents schema conflicts on re-runs)
IF OBJECT_ID('tempdb..#temp_daily_records') IS NOT NULL
    DROP TABLE #temp_daily_records;

-- Create temp table with matching schema to your source table
CREATE TABLE #temp_daily_records (
    timestamp DATETIME,
    SO2_7PCT DECIMAL(10,2),
    NOX_LB_Validity VARCHAR(10),
    -- Add other columns as needed
);

-- Insert all qualifying records
INSERT INTO #temp_daily_records
SELECT t.*
FROM your_source_table t
INNER JOIN (
    SELECT DISTINCT CAST(timestamp AS DATE) AS target_date
    FROM your_source_table
    WHERE timestamp >= DATEADD(DAY, -30, GETDATE())
      AND SO2_7PCT > 44
      AND NOX_LB_Validity = 'Valid'
) AS valid_dates ON CAST(t.timestamp AS DATE) = valid_dates.target_date;

Why This Beats a WHILE Loop #

• No Loop Bugs: Set-based operations eliminate the risk of off-by-one errors, incorrect date increment logic, or "days exhausted" glitches that come with manual loops.
• Faster Performance: Databases are built to optimize set operations—this will run way quicker than looping through each date one by one, especially with large datasets.
• Clearer Logic: The code directly mirrors your business rule ("get all records from dates that have at least one qualifying hour") without messy loop boilerplate.

Quick Tips #

• Replace your_source_table with your actual table name, and tweak the temp table schema to match your source table’s columns perfectly.
• If you’re using Oracle, swap out DATE() or CAST() with TRUNC(timestamp) to extract the date part.
• Adding an index on the timestamp column will drastically speed up the WHERE timestamp >= ... filter, especially if your table has years of historical data.

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