特定时间内聚合查询：筛选英国初始评2分且评分涨幅≥3的酒店

Got it, let's break down how to solve this problem. We need to identify UK-based hotels that meet two key criteria:

• Their initial rating (first recorded score) is exactly 2
• Their rating has increased by at least 3 points from that initial value at some point in the dataset

Step-by-Step Approach #

1. First, capture each hotel's initial rating and corresponding earliest date in the dataset.
2. Calculate the highest rating each hotel achieved after their initial record.
3. Compute the difference between the highest rating and the initial rating.
4. Filter for hotels in the UK, with an initial rating of 2, and a rating improvement of 3 or more.

SQL Query Implementation #

Here's a query that handles this logic with time-based aggregation (works across most modern databases like PostgreSQL, MySQL 8+, etc.):

WITH hotel_initial_rating AS (
    SELECT 
        Hotel,
        Rating AS initial_rating,
        Region
    FROM (
        SELECT 
            Hotel,
            Rating,
            Region,
            ROW_NUMBER() OVER (PARTITION BY Hotel ORDER BY Month ASC) AS rn
        FROM hotel_ratings
    ) AS ranked_ratings
    WHERE rn = 1  -- Grab the first (initial) rating entry for each hotel
),
hotel_max_rating AS (
    SELECT 
        Hotel,
        MAX(Rating) AS max_rating
    FROM hotel_ratings
    GROUP BY Hotel
)
SELECT 
    hir.Hotel
FROM hotel_initial_rating hir
JOIN hotel_max_rating hmr ON hir.Hotel = hmr.Hotel
WHERE 
    hir.Region = 'UK'
    AND hir.initial_rating = 2
    AND (hmr.max_rating - hir.initial_rating) >= 3;

Explanation #

• CTE 1 (hotel_initial_rating): Uses a window function ROW_NUMBER() to rank each hotel's records by date, then selects the first entry to get the initial rating and region.
• CTE 2 (hotel_max_rating): Aggregates each hotel's records to find their highest ever rating across all time.
• Final Join & Filter: Combines the two CTEs, then applies our target filters to narrow down to only qualifying UK hotels.

Sample Result #

Running this query against your provided sample data will return:

Hotel
-----
B

Which aligns with your expected outcome—Hotel B started with a 2 in February 2019, reached a 5 in August 2019 (a 3-point improvement), and is based in the UK.

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