查找每个工作日每个房间id的最长持续时间

5jvtdoz2 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(280)

我想找出每个工作日每个房间的最长管理时间。我想将工作日转换为整数表示形式（0表示星期日，1表示星期一等）。
到目前为止我所拥有的：

create or replace function iweekday(weekday  varchar(9))
returns int as $$
DECLARE iw INT;
begin
select  iw= 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;

create or replace function fourpointnine()
returns table (room_id character(7), iw int, start_time int,end_time int) as $$
DECLARE iw INT;
begin
with recursive cte as(
    select  l.room_id, l.weekday, l.start_time, l.end_time
    from "learningactivity" l
    union all 
    select l.room_id, l.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 cte.end_time=l.start_time
),  cte2 as(
    select *,  row_number() over (partition by cte.room_id  order by EXTRACT(EPOCH FROM cte.end_time - cte.start_time)/3600 desc ) as rn
    from cte
)

select distinct cte2.room_id, iweekday(cte2.weekday), cte2.start_time, cte2.end_time
from cte2
where rn=1
group by cte2.room_id, iweekday(cte2.weekday), cte2.start_time, cte2.end_time;
end;
$$ language plpgsql;

但我有个错误：

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function iweekday(character varying) line 4 at SQL statement

如何做好？

sql postgresql recursive-query plpgsql

来源：https://stackoverflow.com/questions/61892193/find-the-longest-concecutive-duration-of-each-room-id-per-weekday

1条答案

按热度按时间

6tr1vspr1#

立即出现的错误可以用 RETURN QUERY . 请参见：
如何在postgresql中返回函数内部select的结果？
不过，不用担心，pl/pgsql函数一开始就过于复杂了。
通过这种方式，助手函数更简单、更便宜：

CREATE OR REPLACE FUNCTION iweekday(weekday text)
  RETURNS int LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT 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
$func$;

( PARALLEL SAFE 仅适用于postgres 9.6或更高版本。）
最重要的是，它可以内联。相关：
postgresql函数中sql语言与plpgsql语言的区别
您的主要功能可以替换为以下查询：

WITH RECURSIVE cte AS (
   SELECT l.room_id, l.weekday, l.start_time, l.end_time
   FROM   learningactivity l

   UNION ALL 
   SELECT l.room_id, l.weekday, c.start_time, l.end_time
   FROM   cte c
   JOIN   learningactivity l ON l.room_id = c.room_id
                            AND l.weekday = c.weekday
                            AND l.start_time = c.end_time
  )
SELECT DISTINCT ON (1, 2)
       c.room_id, iweekday(c.weekday) AS iw, c.start_time, c.end_time
FROM   cte c
ORDER  BY 1, 2, c.start_time - c.end_time;

如果你坚持一个函数：

CREATE OR REPLACE FUNCTION fourpointnine()
  RETURNS TABLE (room_id text, iw int, start_time int, end_time int)
  LANGUAGE sql AS
$func$
    -- paste query from above
$func$;

db<>在这里摆弄
相关：
选择最长连续序列
旁白：永远不要使用数据类型 character(N) . 请参见：
使用数据类型“text”存储字符串有什么缺点吗？

赞(0）回复(0）举报 2021-08-09

我来回答

查找每个工作日每个房间id的最长持续时间

1条答案

相关问题

热门标签

最新问答