Oracle中如何仅对连续数据分组，统计笼具费率及周期？

Got it, let's break this down—you’re totally right that a basic GROUP BY rate, cage_count with MIN(date)/MAX(date) won’t work here. When the cage count bounces back to the same number later, those are separate continuous periods, and we need to treat them as distinct rows in your billing output.

This is a classic "gap-and-islands" problem in SQL, and the CTE approach you’re working with is the right direction. Here’s a polished, robust version of the query tailored to your use case, plus a breakdown of how it works:

Step-by-Step Query #

Assuming your source table (let’s call it cage_daily_records) has columns like record_date (the date of the count), rate (the daily rate for the cages), and cage_count (number of cages active that day):

WITH continuous_periods AS (
    SELECT
        record_date,
        rate,
        cage_count,
        -- Calculate a unique ID for each "island" of continuous identical rate + cage_count
        ROW_NUMBER() OVER (ORDER BY record_date) -
        ROW_NUMBER() OVER (PARTITION BY rate, cage_count ORDER BY record_date) AS island_identifier
    FROM cage_daily_records
)
SELECT
    rate,
    cage_count,
    MIN(record_date) AS period_start,
    MAX(record_date) AS period_end
FROM continuous_periods
GROUP BY rate, cage_count, island_identifier
ORDER BY period_start;

How This Works #

• The island_identifier is the magic here:
  • The first ROW_NUMBER() assigns a sequential number to every row ordered by date.
  • The second ROW_NUMBER() resets the sequence every time rate or cage_count changes.
  • The difference between these two numbers stays the same for all rows in a continuous period (same rate + cage count with no gaps/changes), creating a unique ID for each "island" of consistent data.
• Grouping by rate, cage_count, and island_identifier ensures we only group rows that are actually consecutive, so MIN(record_date) and MAX(record_date) give you the true start and end of each billing period.

Customization Note #

If you need to track this per individual cage (since you mentioned different cages might have different rates), just add cage_id to the PARTITION BY clause in the CTE:

ROW_NUMBER() OVER (PARTITION BY cage_id, rate, cage_count ORDER BY record_date)

This will ensure you get continuous periods per cage, rate, and count combination—perfect for generating accurate daily billing.

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