MySQL技术问询：跨时段求和与按特殊时段分组统计行程收益

Hey there, let's tackle your two MySQL aggregation problems one by one—both involve custom time windows instead of standard calendar days, which is totally common for rideshare services like Uber or taxi fleets. All solutions are single SQL statements, perfect for direct PHP execution via PDO/mysqli.

1. Total Amount Between 5pm and 5am Next Day #

Assuming your table has a datetime field (e.g., trip_start_time) and a monetary field (e.g., trip_amount), the key is to group trips into their respective "5pm-to-5am" windows.

How it works: #

We shift each trip's timestamp back by 17 hours (since 5pm is 17:00 in 24-hour time). This ensures every trip between X day 5pm and X+1 day 5am will map to the same calendar date (X). We then group by this adjusted date and sum the amounts.

SQL Query: #

SELECT
  DATE(DATE_SUB(trip_start_time, INTERVAL 17 HOUR)) AS window_start_date,
  SUM(trip_amount) AS total_amount
FROM
  rideshare_trips
-- Optional: Filter for a specific date range if needed
WHERE
  trip_start_time BETWEEN '2024-01-01 17:00:00' AND '2024-01-31 05:00:00'
GROUP BY
  window_start_date
ORDER BY
  window_start_date;

PHP Note: #

When calling this from PHP, you can safely execute it as a prepared statement (add parameter placeholders for the date range if you need dynamic filters) and fetch results directly—no extra server-side processing needed.

2. Most Profitable Windows (12pm to 12pm Next Day) #

For this, we need to group trips into X day 12pm to X+1 day 12pm windows, then find the top-performing windows. We'll cover two scenarios: getting just the top window, or all windows tied for the highest profit.

Core Logic: #

Shift each timestamp back by 12 hours. This maps every trip between X day 12pm and X+1 day 12pm to the calendar date X. We then aggregate and sort, or use a window function to rank results.

Scenario 1: Get Top N Profitable Windows #

Use this if you want the top 5, top 10, etc.:

SELECT
  DATE(DATE_SUB(trip_start_time, INTERVAL 12 HOUR)) AS window_start_date,
  SUM(trip_amount) AS total_profit
FROM
  rideshare_trips
GROUP BY
  window_start_date
ORDER BY
  total_profit DESC
LIMIT 10; -- Adjust limit as needed

Scenario 2: Get All Windows Tied for Highest Profit #

Use a RANK() window function to capture all windows with the maximum profit (avoids missing ties):

WITH window_profits AS (
  SELECT
    DATE(DATE_SUB(trip_start_time, INTERVAL 12 HOUR)) AS window_start_date,
    SUM(trip_amount) AS total_profit
  FROM
    rideshare_trips
  GROUP BY
    window_start_date
)
SELECT
  window_start_date,
  total_profit
FROM (
  SELECT
    *,
    RANK() OVER (ORDER BY total_profit DESC) AS profit_rank
  FROM
    window_profits
) ranked_profits
WHERE
  profit_rank = 1;

PHP Note: #

Both queries are single-statement, so you can execute them directly in PHP. The CTE (Common Table Expression) in Scenario 2 works in MySQL 8.0+—if you're on an older version, you can rewrite it with a subquery instead:

SELECT
  window_start_date,
  total_profit
FROM (
  SELECT
    DATE(DATE_SUB(trip_start_time, INTERVAL 12 HOUR)) AS window_start_date,
    SUM(trip_amount) AS total_profit,
    RANK() OVER (ORDER BY SUM(trip_amount) DESC) AS profit_rank
  FROM
    rideshare_trips
  GROUP BY
    window_start_date
) ranked_profits
WHERE
  profit_rank = 1;

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