我目前有两个函数,它们应该返回设备再次开始记录的时间,即前一行之前超过 60 秒的时间。这些功能可能工作正常,但我必须看到它工作,因为它需要永远。有什么捷径可以加快速度吗?
CREATE OR REPLACE FUNCTION findNextTime(startt integer)
RETURNS integer AS
$nextTime$
DECLARE
nextTime integer;
BEGIN
select time into nextTime from m01 where time < startt ORDER BY time DESC LIMIT 1;
return nextTime;
END;
$nextTime$ LANGUAGE plpgsql;
CREATE OR REPlACE FUNCTION findStart()
RETURNS integer AS
$lastTime$
DECLARE
currentTime integer;
lastTime integer;
BEGIN
select time into currentTime from m01 ORDER BY time DESC LIMIT 1;
LOOP
RAISE NOTICE 'Current Time: %', currentTime;
select findNextTime(currentTime) into lastTime;
EXIT WHEN ((currentTime - lastTime) > 60);
currentTime := lastTime;
END LOOP;
return lastTime;
END;
$lastTime$ LANGUAGE plpgsql;
为了澄清,我想基本上找到任何两行之间最后一次中断超过 60 秒的时间。
CREATE TABLE IF NOT EXISTS m01 (
time integer,
value decimal,
id smallint,
driveId smallint
)
示例数据:
在这种情况下,它将返回1520376063因为下一个条目 (1520375766( 相隔 60 秒以上。
| time | value | id | driveid |
|------------|--------------------|------|---------|
| 1520376178 | 516.2 | 5116 | 2 |
| 1520376173 | 507.8 | 5116 | 2 |
| 1520376168 | 499.5 | 5116 | 2 |
| 1520376163 | 491.1 | 5116 | 2 |
| 1520376158 | 482.90000000000003 | 5116 | 2 |
| 1520376153 | 474.5 | 5116 | 2 |
| 1520376148 | 466.20000000000005 | 5116 | 2 |
| 1520376143 | 457.8 | 5116 | 2 |
| 1520376138 | 449.5 | 5116 | 2 |
| 1520376133 | 441.20000000000005 | 5116 | 2 |
| 1520376128 | 432.90000000000003 | 5116 | 2 |
| 1520376123 | 424.6 | 5116 | 2 |
| 1520376118 | 416.20000000000005 | 5116 | 2 |
| 1520376113 | 407.8 | 5116 | 2 |
| 1520376108 | 399.5 | 5116 | 2 |
| 1520376103 | 391.20000000000005 | 5116 | 2 |
| 1520376098 | 382.90000000000003 | 5116 | 2 |
| 1520376093 | 374.5 | 5116 | 2 |
| 1520376088 | 366.20000000000005 | 5116 | 2 |
| 1520376083 | 357.8 | 5116 | 2 |
| 1520376078 | 349.5 | 5116 | 2 |
| 1520376073 | 341.20000000000005 | 5116 | 2 |
| 1520376068 | 332.90000000000003 | 5116 | 2 |
| 1520376063 | 324.5 | 5116 | 2 |
| 1520375766 | 102.5 | 5116 | 2 |
这个简单的查询应该替换你的两个函数。请注意子查询中lead()
的窗口函数:
SELECT *
FROM (
SELECT time, lead(time) OVER (ORDER BY time DESC) AS last_time
FROM m01
WHERE time < _startt
) sub
WHERE time > last_time + 60
ORDER BY time DESC
LIMIT 1;
无论哪种方式,性能的关键部分都是正确的索引。理想情况下(time DESC)
.
假设time
被定义NOT NULL
- 它可能应该是,但问题中的表定义没有这么说。否则,您可能想要ORDER BY time DESC NULLS LAST
- 以及匹配的索引。看:
- PostgreSQL按日期时间asc排序,null first?
我希望这个 plpgsql 函数执行得更快,不过,如果差距通常很早就出现:
CREATE OR REPLACE FUNCTION find_gap_before_time(_startt int)
RETURNS int AS
$func$
DECLARE
_current_time int;
_last_time int;
BEGIN
FOR _last_time IN -- single loop is enough!
SELECT time
FROM m01
WHERE time < _startt
ORDER BY time DESC -- NULLS LAST?
LOOP
IF _current_time > _last_time + 60 THEN -- never true for 1st row
RETURN _current_time;
END IF;
_current_time := _last_time;
END LOOP;
END
$func$ LANGUAGE plpgsql;
叫:
SELECT find_gap_before_time(1520376200);
结果按要求。
旁白:通常通过将列放在最后或第一个value
,将每行节省几个字节,从而最大限度地减少对齐填充。喜欢:
CREATE TABLE m01 (
time integer,
id smallint,
driveId smallint,
value decimal
);
详细说明:
- 在PostgreSQL中计算和节省空间