如何合并同一张ticket表的多条SQL查询语句？

Hey there! Let's figure out how to merge those repeated SQL queries into one efficient statement. Since all your queries are hitting the ticket table to calculate average ticket prices with different filter sets, conditional aggregation is perfect here—it lets you compute all those averages in a single scan of the table, which is way more efficient than running multiple separate queries.

Here's How It Works #

Each of your original queries can be converted into a CASE WHEN block inside an AVG() function. This way, we only scan the ticket table once, and compute all required averages in one go, grouped by TicketDate just like your original queries.

Example Merged Query

Let's use your sample query plus a hypothetical second query to demonstrate. Adjust the additional CASE WHEN blocks to match your actual remaining queries:

SELECT 
  TicketDate,
  -- Average price matching your first query's filters
  AVG(CASE 
        WHEN TicketPrice BETWEEN 552 AND 1302 
          AND AirlineID = 1 
          AND TicketDate BETWEEN '2016-01-01' AND '2016-12-31' 
        THEN TicketPrice 
      END) AS avg_airline1_2016_price_552_1302,
  -- Add more AVG(CASE ...) blocks here for each of your other queries
  AVG(CASE 
        WHEN TicketPrice BETWEEN 200 AND 600 
          AND AirlineID = 2 
          AND TicketDate BETWEEN '2016-01-01' AND '2016-12-31' 
        THEN TicketPrice 
      END) AS avg_airline2_2016_price_200_600
FROM ticket
-- Optional: If all queries share common filters (like the 2016 date range), put them here to pre-filter data
WHERE TicketDate BETWEEN '2016-01-01' AND '2016-12-31'
GROUP BY TicketDate;

Key Tips #

• Mirror Exact Filters: Ensure each CASE WHEN block includes all conditions from your original query (price range, AirlineID, date range, etc.). The AVG() function ignores NULL values (returned when the CASE condition isn't met), so the result will match running that query separately.
• Optimize with Shared Filters: If all your queries have overlapping filters (like the 2016 date range), moving those to the outer WHERE clause cuts down on the data the database needs to process upfront.
• Use Descriptive Aliases: Name each average column clearly so you can easily identify which result corresponds to which original query.

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