SQL Server中按条件统计行数：首条在线记录后的计数需求

Alright, let's break down how to compute the two values you need based on your transaction data. First, let's recap the sample data and requirements clearly:

Sample Data #

What We Need to Compute #

For each field1 (we'll assume there could be multiple fields, even though your sample only has F1):

• Metric 1: Number of Online records that happen after the first Online record (in your sample, this is 2: the entries on 2018-04-05 and 2018-04-10)
• Metric 2: Total number of all records that happen after the first Online record (in your sample, this is 7: every record from 2018-04-05 to 2018-04-11)

Solution 1: Using a CTE (Works for Most SQL Dialects) #

This approach first finds the earliest Online transaction date for each field, then uses that to filter and count the relevant records:

WITH first_online_dates AS (
    SELECT 
        field1,
        MIN(trxndate) AS first_online_date
    FROM your_table
    WHERE onlineoffline = 'Online'
    GROUP BY field1
)
SELECT
    t.field1,
    -- Count only Online records after the first Online date
    COUNT(CASE WHEN onlineoffline = 'Online' THEN 1 END) AS post_first_online_count,
    -- Count all records after the first Online date
    COUNT(*) AS total_post_first_online_count
FROM your_table t
JOIN first_online_dates fo ON t.field1 = fo.field1
WHERE t.trxndate > fo.first_online_date
GROUP BY t.field1;

How This Works #

1. The first_online_dates CTE grabs the very first Online transaction date for each field1 using MIN(trxndate).
2. We join this back to the original table, filtering only records where the transaction date is later than that first Online date.
3. The conditional COUNT(CASE...) picks out only the Online records in that filtered set for Metric 1.
4. COUNT(*) gives us the total number of records in the filtered set for Metric 2.

Solution 2: Using Window Functions (More Efficient for Large Datasets) #

If you're using a SQL dialect that supports window functions (like Oracle, PostgreSQL, SQL Server), this version avoids a join and computes everything in a single pass:

SELECT
    field1,
    SUM(CASE WHEN onlineoffline = 'Online' AND trxndate > first_online_date THEN 1 ELSE 0 END) AS post_first_online_count,
    SUM(CASE WHEN trxndate > first_online_date THEN 1 ELSE 0 END) AS total_post_first_online_count
FROM (
    SELECT
        field1,
        onlineoffline,
        trxndate,
        -- Get the first Online date for each field1
        MIN(CASE WHEN onlineoffline = 'Online' THEN trxndate END) OVER (PARTITION BY field1) AS first_online_date
    FROM your_table
) subquery
GROUP BY field1;

How This Works #

1. The inner subquery uses MIN(CASE...) OVER (PARTITION BY field1) to calculate the earliest Online date for each field1 directly, attaching that value to every row in the table.
2. We then use conditional SUM() functions to count only the rows that meet our criteria (after the first Online date, and either Online or all records).

Sample Output #

Running either query on your sample data will return:

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