基于Ms-Sql：按日期统计总行数及符合条件的最大连续行数

Hey there! Let's work through your problem of getting daily row counts and the longest streak of consecutive valid rows (where numb > 2 AND calibration != 1) per day.

First, I'll assume your table has a structure like this (since you mentioned a diagram, I'll use a typical setup for time-series reading data):

CREATE TABLE device_readings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    record_date DATE NOT NULL,
    numb INT NOT NULL,
    calibration INT NOT NULL,
    reading_timestamp TIMESTAMP NOT NULL -- Critical for ordering rows to determine "consecutive"
);

Step 1: Get Total Rows Per Day #

This is the straightforward part—just group by the date and count all rows:

SELECT record_date, COUNT(*) AS total_rows
FROM device_readings
GROUP BY record_date;

Step 2: Calculate the Longest Consecutive Valid Rows #

The tricky part is identifying consecutive valid rows. Here's a step-by-step approach using window functions:

1. Flag valid rows: Mark each row as 1 if it meets your condition, 0 otherwise.
2. Create consecutive groups: Use a running sum of invalid rows to assign group IDs. Every time we hit an invalid row, the sum increments, so all consecutive valid rows get the same group ID.
3. Count group sizes: For each valid group, count how many rows are in it, then take the maximum per day.

Here's the CTE chain to do this:

WITH flagged_rows AS (
    SELECT
        record_date,
        CASE WHEN numb > 2 AND calibration != 1 THEN 1 ELSE 0 END AS is_valid,
        reading_timestamp
    FROM device_readings
),
consecutive_groups AS (
    SELECT
        record_date,
        is_valid,
        -- Increment group ID every time we hit an invalid row
        SUM(1 - is_valid) OVER (PARTITION BY record_date ORDER BY reading_timestamp) AS group_id
    FROM flagged_rows
),
group_counts AS (
    SELECT
        record_date,
        COUNT(*) AS consecutive_count
    FROM consecutive_groups
    WHERE is_valid = 1
    GROUP BY record_date, group_id
)
SELECT
    record_date,
    COALESCE(MAX(consecutive_count), 0) AS max_consecutive_valid_rows
FROM group_counts
GROUP BY record_date;

Step 3: Combine Both Results #

Finally, join the two datasets to get your desired output with both metrics per day:

WITH total_daily_rows AS (
    SELECT record_date, COUNT(*) AS total_rows
    FROM device_readings
    GROUP BY record_date
),
flagged_rows AS (
    SELECT
        record_date,
        CASE WHEN numb > 2 AND calibration != 1 THEN 1 ELSE 0 END AS is_valid,
        reading_timestamp
    FROM device_readings
),
consecutive_groups AS (
    SELECT
        record_date,
        is_valid,
        SUM(1 - is_valid) OVER (PARTITION BY record_date ORDER BY reading_timestamp) AS group_id
    FROM flagged_rows
),
group_counts AS (
    SELECT
        record_date,
        COUNT(*) AS consecutive_count
    FROM consecutive_groups
    WHERE is_valid = 1
    GROUP BY record_date, group_id
),
max_consecutive AS (
    SELECT
        record_date,
        COALESCE(MAX(consecutive_count), 0) AS max_consecutive_valid_rows
    FROM group_counts
    GROUP BY record_date
)
SELECT
    t.record_date,
    t.total_rows,
    COALESCE(m.max_consecutive_valid_rows, 0) AS max_consecutive_valid_rows
FROM total_daily_rows t
LEFT JOIN max_consecutive m ON t.record_date = m.record_date
ORDER BY t.record_date;

Key Notes #

• Order matters: The reading_timestamp (or an auto-increment id column) is essential to ensure we're counting rows in the order they were recorded—without this, "consecutive" doesn't make sense.
• Handle edge cases: COALESCE(MAX(...), 0) ensures days with no valid rows return 0 instead of NULL.
• Test with sample data: If you have a day where valid rows are split into multiple streaks (e.g., 4 valid, then 1 invalid, then 3 valid), this query will correctly return 4 as the maximum streak.

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