如何正确实现按工作日统计各房间最长连续时长（工作日转整数）

Hey there, let's work through fixing your PL/pgSQL functions to meet your requirement: finding the longest continuous usage window for each room on every weekday, with weekdays converted to integers (0 for Sunday, 1 for Monday, etc.).

First, Fix the iweekday Function Error #

The query has no destination for result data error comes from how you tried to assign the weekday integer value in the iweekday function. PL/pgSQL doesn't allow the select iw= CASE ... syntax for variable assignment. Instead, you can either directly assign the CASE result to your variable or skip the variable entirely and return the result directly.

Here's the simplified, corrected version:

create or replace function iweekday(weekday varchar(9)) returns int as $$ 
begin
    return CASE weekday 
        WHEN 'Monday' THEN 1 
        WHEN 'Tuesday' THEN 2 
        WHEN 'Wednesday' THEN 3 
        WHEN 'Thursday' THEN 4 
        WHEN 'Friday' THEN 5 
        WHEN 'Saturday' THEN 6 
        WHEN 'Sunday' THEN 0 
    END;
end; 
$$ language plpgsql;

Next, Fix the fourpointnine Function for Continuous Duration Logic #

Your recursive CTE was headed in the right direction, but it didn't properly group by weekday when merging sessions, and the ranking logic to pick the longest window had gaps. Let's adjust it to correctly:

1. Merge consecutive sessions for the same room and weekday
2. Calculate the duration of each merged session
3. Select the longest session per room and weekday

Here's the corrected function:

create or replace function fourpointnine() returns table (room_id character(7), iw int, start_time timestamp, end_time timestamp, duration_hours numeric) as $$ 
begin
    with recursive cte as (
        -- Base case: start with all individual sessions
        select 
            room_id, 
            weekday, 
            start_time, 
            end_time
        from "learningactivity"
        union all
        -- Recursive case: merge consecutive sessions (same room, same weekday, next session starts when previous ends)
        select 
            cte.room_id, 
            cte.weekday, 
            cte.start_time, 
            l.end_time
        from cte
        join "learningactivity" l 
            on l.room_id = cte.room_id 
            and l.weekday = cte.weekday 
            and l.start_time = cte.end_time
    ),
    -- Calculate full duration for each continuous session chain
    session_durations as (
        select 
            room_id, 
            weekday, 
            start_time, 
            max(end_time) as end_time,
            EXTRACT(EPOCH FROM max(end_time) - start_time)/3600 as duration_hours
        from cte
        group by room_id, weekday, start_time
    ),
    -- Rank sessions by duration (longest first) per room + weekday
    ranked_sessions as (
        select 
            *,
            row_number() over (partition by room_id, weekday order by duration_hours desc) as rn
        from session_durations
    )
    -- Pick the longest session per room and weekday, convert weekday to integer
    select 
        room_id,
        iweekday(weekday) as iw,
        start_time,
        end_time,
        duration_hours
    from ranked_sessions
    where rn = 1;
end; 
$$ language plpgsql;

Key Improvements Breakdown #

• Recursive CTE Fix: We explicitly group by weekday in the join to ensure we only merge sessions on the same day, then use max(end_time) to capture the full end of each continuous session chain.
• Clear Duration Calculation: We compute the session length in hours and use it for ranking, so we're guaranteed to pick the longest window.
• Accurate Ranking: The row_number() is partitioned by both room_id and weekday, so we get the longest session per room per weekday (matching your requirement).
• Transparent Output: We added duration_hours to the return table for clarity, but you can remove it if you don't need to see the exact length.

Test the Function #

Once you've created both corrected functions, run this to get your desired results:

select * from fourpointnine();

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