MySQL中使用CASE语句对跨天事件按办公时段分类的问题

Great question! Your original query works well for events that fit neatly within a single period on the same day, but it misses two key scenarios: same-day events that cross period boundaries (like 16:30-17:30) and cross-day events. Let's fix that with a more robust approach that covers all cases while preserving your core categorization logic.

First, let's clarify the business rules we'll use (you can adjust these based on your needs):

• Any event that overlaps with standard office hours (08:00-17:00) on any day gets categorized as During office hours (this handles cross-period same-day and cross-day events that touch work hours).
• For events that don't overlap with office hours:
  • If the event starts in after-hours (17:00-23:59) (even if it crosses into next-day before-hours), categorize as After office hours (aligning with your initial thought).
  • Otherwise, it's entirely in before-hours (00:00-07:59), so categorize as Before office hours.
• Keep the No Usage category for when Speed=0.

Here's the revised MySQL query:

CASE
    -- Handle no usage scenario first
    WHEN b01.Speed = 0 THEN 'No Usage'
    -- Handle events below speed threshold (matches original behavior, returns NULL)
    WHEN b01.Speed * 3.6 < 5 THEN NULL
    ELSE
        -- Categorize valid events (speed meets threshold)
        CASE
            -- Check if event overlaps with office hours on any day
            WHEN (
                -- Same-day event: overlaps with 08:00-17:00
                (b01.SamplingStart < b01.SamplingEnd AND 
                 NOT (TIME(b01.SamplingEnd) < '08:00:00' OR TIME(b01.SamplingStart) > '17:00:00'))
                OR
                -- Cross-day event: either starts before 17:00 (overlaps same-day office hours) 
                -- or ends after 08:00 (overlaps next-day office hours)
                (b01.SamplingStart > b01.SamplingEnd AND 
                 (TIME(b01.SamplingStart) <= '17:00:00' OR TIME(b01.SamplingEnd) >= '08:00:00'))
            ) THEN 'During office hours'
            ELSE
                -- No overlap with office hours: determine before/after
                CASE
                    -- Event starts in after-hours (same-day or cross-day)
                    WHEN (
                        (b01.SamplingStart < b01.SamplingEnd AND TIME(b01.SamplingStart) >= '17:00:00')
                        OR
                        (b01.SamplingStart > b01.SamplingEnd AND TIME(b01.SamplingStart) >= '17:00:00')
                    ) THEN 'After office hours'
                    -- Entirely in before-hours (same-day only, since cross-day would have overlapped with office hours otherwise)
                    ELSE 'Before office hours'
                END CASE
        END CASE
END AS VUsage

Let's test this against edge cases: #

1. Cross-day event (2020-08-17 23:00:00 → 2020-08-18 08:05:00): Overlaps with next-day office hours (08:05 is after 08:00) → categorized as During office hours.
2. Cross-day non-office event (2020-08-17 22:00:00 → 2020-08-18 07:00:00): Starts in after-hours → After office hours.
3. Same-day cross-period (16:30 →17:30): Overlaps with office hours → During office hours.
4. Same-day before hours (05:00→06:00): Before office hours.
5. Same-day after hours (19:00→20:00): After office hours.

If you want to prioritize majority duration for cross-day non-office events: #

If you'd rather categorize cross-day events (like 23:00→06:00) based on which period takes up more time, we can calculate the duration in before vs after hours. Here's how that would look (note: this uses more complex date arithmetic):

CASE
    WHEN b01.Speed = 0 THEN 'No Usage'
    WHEN b01.Speed * 3.6 < 5 THEN NULL
    ELSE
        CASE
            WHEN (
                (b01.SamplingStart < b01.SamplingEnd AND NOT (TIME(b01.SamplingEnd) < '08:00:00' OR TIME(b01.SamplingStart) > '17:00:00'))
                OR
                (b01.SamplingStart > b01.SamplingEnd AND (TIME(b01.SamplingStart) <= '17:00:00' OR TIME(b01.SamplingEnd) >= '08:00:00'))
            ) THEN 'During office hours'
            ELSE
                -- Calculate duration in before/after hours and pick the majority
                CASE
                    WHEN 
                        -- Total duration minus during hours (which is zero here) equals before+after
                        TIMESTAMPDIFF(MINUTE, b01.SamplingStart, IF(b01.SamplingEnd > b01.SamplingStart, b01.SamplingEnd, DATE_ADD(b01.SamplingEnd, INTERVAL 1 DAY)))
                        -
                        -- Duration in before hours
                        TIMESTAMPDIFF(MINUTE, 
                            GREATEST(b01.SamplingStart, DATE_ADD(DATE(b01.SamplingStart), INTERVAL 0 HOUR)),
                            LEAST(IF(b01.SamplingEnd > b01.SamplingStart, b01.SamplingEnd, DATE_ADD(b01.SamplingEnd, INTERVAL 1 DAY)), DATE_ADD(DATE(b01.SamplingStart), INTERVAL 8 HOUR))
                        )
                        -
                        -- Duration in before hours of next day (if cross-day)
                        IF(b01.SamplingStart > b01.SamplingEnd, TIMESTAMPDIFF(MINUTE, DATE_ADD(DATE(b01.SamplingEnd), INTERVAL 0 HOUR), b01.SamplingEnd), 0)
                        >
                        -- Duration in after hours
                        TIMESTAMPDIFF(MINUTE, 
                            GREATEST(b01.SamplingStart, DATE_ADD(DATE(b01.SamplingStart), INTERVAL 17 HOUR)),
                            LEAST(IF(b01.SamplingEnd > b01.SamplingStart, b01.SamplingEnd, DATE_ADD(b01.SamplingEnd, INTERVAL 1 DAY)), DATE_ADD(DATE(b01.SamplingStart), INTERVAL 24 HOUR))
                        )
                        +
                        -- Duration in after hours of previous day (if cross-day)
                        IF(b01.SamplingStart > b01.SamplingEnd, TIMESTAMPDIFF(MINUTE, b01.SamplingStart, DATE_ADD(DATE(b01.SamplingStart), INTERVAL 24 HOUR)), 0)
                    THEN 'After office hours'
                    ELSE 'Before office hours'
                END CASE
        END CASE
END AS VUsage

This second version calculates the total time spent in before vs after hours and assigns the category with the longer duration. Adjust the rules based on what makes the most sense for your use case!

内容的提问来源于stack exchange，提问作者jin cheng teo