避免使用Union获取每位乘客出行TOP5地点的SQL方案

Hey there! Let's tackle this problem: we need to get the top 5 locations each passenger has traveled to, and we can't use UNION for counting. Here's a solid approach that avoids union entirely:

First, let's break down what we need: we have to count every location a passenger has been to—whether it's a starting point or destination—without using UNION to combine those two sets of locations. Instead, we can use a lateral join (or cross apply in SQL Server) to "unpivot" the FromLocationId and ToLocationId into separate rows, then count and rank them.

Step-by-Step Breakdown: #

• Unpivot Locations: Turn each trip's start and end location into individual rows, so every location visit (start or end) gets its own entry
• Count Visits: Group by passenger and location to tally up how many times each passenger visited each spot
• Rank & Filter: Use a window function to rank locations by visit count per passenger, then pick the top 5

SQL Code (SQL Server Example): #

WITH PassengerLocations AS (
    SELECT 
        t.PassengerId,
        loc.LocationId,
        loc.Address,
        loc.City,
        loc.State,
        loc.Zip
    FROM Trip t
    CROSS APPLY (
        -- Split start and end locations into separate rows instead of using UNION
        VALUES 
            (t.FromLocationId),
            (t.ToLocationId)
    ) AS pl(LocationId)
    JOIN Location loc ON pl.LocationId = loc.LocationId
),
LocationCounts AS (
    SELECT 
        PassengerId,
        LocationId,
        Address,
        City,
        State,
        Zip,
        COUNT(*) AS VisitCount,
        -- Rank locations by visit count for each passenger
        ROW_NUMBER() OVER (PARTITION BY PassengerId ORDER BY COUNT(*) DESC) AS RankNum
    FROM PassengerLocations
    GROUP BY PassengerId, LocationId, Address, City, State, Zip
)
SELECT 
    PassengerId,
    LocationId,
    Address,
    City,
    State,
    Zip,
    VisitCount,
    RankNum
FROM LocationCounts
WHERE RankNum <= 5
ORDER BY PassengerId, RankNum;

For MySQL 8.0+/PostgreSQL: #

Just swap CROSS APPLY with LATERAL JOIN:

WITH PassengerLocations AS (
    SELECT 
        t.PassengerId,
        loc.LocationId,
        loc.Address,
        loc.City,
        loc.State,
        loc.Zip
    FROM Trip t
    JOIN LATERAL (
        VALUES 
            (t.FromLocationId),
            (t.ToLocationId)
    ) AS pl(LocationId) ON TRUE
    JOIN Location loc ON pl.LocationId = loc.LocationId
),
LocationCounts AS (
    SELECT 
        PassengerId,
        LocationId,
        Address,
        City,
        State,
        Zip,
        COUNT(*) AS VisitCount,
        ROW_NUMBER() OVER (PARTITION BY PassengerId ORDER BY COUNT(*) DESC) AS RankNum
    FROM PassengerLocations
    GROUP BY PassengerId, LocationId, Address, City, State, Zip
)
SELECT 
    PassengerId,
    LocationId,
    Address,
    City,
    State,
    Zip,
    VisitCount,
    RankNum
FROM LocationCounts
WHERE RankNum <= 5
ORDER BY PassengerId, RankNum;

Notes: #

• If you need to handle ties (e.g., two locations with the same visit count), replace ROW_NUMBER() with RANK() or DENSE_RANK():
  • RANK() will leave gaps in ranking for ties (e.g., 1,2,2,4)
  • DENSE_RANK() won't leave gaps (e.g.,1,2,2,3)
• This approach completely avoids UNION by using a lateral join to split the two location columns into rows, making it easy to count all visits in one go.

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