Найти последний разрыв между временными метками Unix
В настоящее время у меня есть две функции, которые должны возвращать время, когда устройство снова начало регистрироваться, время, когда предыдущая строка до него была более 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 |
1 ответ
Этот простой запрос должен заменить ваши две функции. Обратите внимание на функцию окна 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
- и соответствующий индекс. Увидеть:
Я ожидаю, что эта функция 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
);
Детальное объяснение: