Как создать триггер SQLite для автоматического расчета столбцов после INSERT?

Зачем вам триггеры? Сгенерированные столбцы (которые вы уже используете в датах) должны выполнять то, что вы хотите, например, вычисления mem*.

В случае, если это не очевидно из названий полей util_lag, mem_lag, мне нужно получить значения из предыдущей строки, что, насколько я знаю, невозможно сделать без ТРИГГЕРА. Наверное, мне следовало назвать их prev_util и prev_mem - извините за это . Если вы не знаете способа получить значение из предыдущей строки, которого нет у других, мне говорят, что необходим ТРИГГЕР.

Что касается ошибки SQL [2]: [SQLITE_ERROR] Ошибка SQL или отсутствующая база данных (рядом с «util_change»: синтаксическая ошибка), я считаю, что в вашем CASE отсутствует END. Перед util_change также отсутствует запятая. Что касается примера ChatGPT, я думаю, он показал вам процедуру, и вы переместили ее в триггер, верно? AFAIK, Sqlite не позволяет DECLARE, за исключением процедур (но я признаю, что прошло некоторое время с тех пор, как я прикоснулся к Sqlite).

Второй пример ChatGPT является дословным. У меня нет навыков конвертировать ПРОЦЕДУРУ в ТРИГГЕР. Единственные изменения, которые я внес в пример ChatGPT v1, — это добавление функции LAG(), поскольку я считал, что ее следует использовать. Я не мог понять, как исходный пример получал значения из предыдущей строки, что и необходимо для расчета «разницы».

Я никогда раньше не слышал термина «начальный ряд». Кажется, я это понимаю, но не уверен. В любом случае я надеюсь избежать создания представления, поскольку, если оно не материализовано, я понимаю, что представление является лишь временным, т. е. находится в памяти. Если/когда машина будет перезагружена, я не хочу, чтобы все эти рассчитанные значения столбцов были потеряны, они должны были быть постоянными. Опять же, мы говорим о сотнях миллионов строк (первоначальные вычисления будут медленными, но расчеты обслуживания по мере добавления новых строк должны быть очень быстрыми).

Я только что заметил, что вы ОГРАНИЧИВАЕТЕ просмотр последней строкой. Поправьте меня, если я ошибаюсь: он создает представление в качестве заполнителя только для вставляемой строки, рассчитывает значения для строки представления, а затем записывает строку представления в реальную таблицу?

Я не знаю почему, но с помощью этого решения я получаю [NULL] для всех вычисляемых столбцов. Мне интересно, есть ли проблемы с DBeaver, инструментом, который я использую. Возможно, в DBeaver есть ошибка... как вы думаете?

Я бы не беспокоился о том, что он останется в памяти или потеряется при перезагрузке. Из документа: «Команда CREATE VIEW присваивает имя предварительно упакованному оператору SELECT» - большинство СУБД делают это именно так. Он занимает нулевое пространство, а выборка из представления в значительной степени преобразуется в выборку из подзапроса, содержащего его определение. Триггер в представлении просто перенаправляет операторы insert, чтобы сохранить входящие строки в базовой таблице — они сохраняются в ней так же, как если бы вы вставили их напрямую.

Определение представления на самом деле не имеет значения: представление используется здесь просто как точка, с помощью которой вы перехватываете и изменяете вставку на лету, что невозможно для прямых вставок в базовую таблицу. Что касается результатов null для вычисляемых столбцов, я предполагаю, что вы вставили строки непосредственно в таблицу, что не активирует триггер в представлении, или вы проверили строки, вставленные в представление, с помощью предложения returning - и в этом случае вы я увижу их состояние до изменения триггером: демо.

Я получил предложение RETURNING * из вашего примера для PostgreSQL здесь: dbfiddle.uk/NQUithQ3. Признаюсь, я не совсем понимаю, что оно делает.

"захватить и изменить вставку на лету" - так я понял ВИД.

«я предполагаю, что вы вставили строки непосредственно в таблицу, что не активирует триггер в представлении»… ааа… я вижу, что ваш TRIGGER находится в представлении, а не в таблице. Я только что еще раз проверил вашу демонстрацию - я совершенно не заметил, что вы вставляете строки в представление, а не в базовую таблицу. Итак, вы вставляете представление между вашими данными и постоянным хранилищем таблиц как фильтр, верно?

Это также было во вступительном заявлении :) Это деталь реализации SQLite, согласно которой вы не можете иначе перехватить операцию — триггер действует как фильтр, но представление — это всего лишь способ получить это instead of trigger. Только представления могут иметь такие

Мне кажется странным, что функцию LAG() нельзя использовать для того, что я пытаюсь сделать. Мне кажется само собой разумеющимся, что так и должно быть. Вместо этого нам приходится заниматься этими «кодонастиками» :D. Полезно знать дополнительное необязательное поле для предложения LIMIT.

Какой из двух ваших ответов, по вашему мнению, будет более эффективным?

Я получаю смешанные результаты с этим ответом. Если я вставлю одну строку в один оператор, похоже, что она не получит значений предыдущей строки. Если я вставлю блок строк в один оператор, некоторые строки будут правильно получать значения из предыдущей строки, а другие — нет. Иногда группа строк получает «предыдущие» значения из одной предыдущей строки, поэтому все они одинаковы, что является неверным результатом.

29.03.2021 19:49:55.419,2021,3,29,19,49,55,419,2021-03-29 19:49:55.419,100,0.6,0.8,177,,,,,\n 09.06.2021 14 :48:16.628,2021,6,9,14,48,16,628,2021-06-09 14:48:16.628,100,0.67,0.61,143,0.67,0.61,0.0,0.0,\n 22.07.2021 07 :51:11.373,2021,7,22,7,51,11,373,2021-07-22 07:51:11.373,100,0.01,0.1,135,0.01,0.1,0.0,0.0,\n 06.03.2021 23 :10:49.057,2021,3,6,23,10,49,57,2021-03-06 23:10:49.057,100,0.5,0.9,97,0.01,0.1,0.49,0.8,Вверх\n 28.07 .2021 23:12:40.555,2021,7,28,23,12,40,555,2021-07-28 23:12:40.555,100,0.84,0.03,95,0.6,0.8,0.24,-0.77,Вверх

Может показаться, что lag() просто означает предыдущую строку, но учтите, что это оконная функция. Это предыдущая строка в окне, которая сужает подмножество того, что выбрано. Вероятно, вы могли бы его применить, но это не будет иметь особого смысла — это полезно, когда у вас есть набор строк и вы хотите каким-то образом соотнести каждую из них со своими предшественниками. Между тем, «SQLite поддерживает только триггеры FOR EACH ROW, а не триггеры FOR EACH STATEMENT», поэтому вы всегда имеете дело только с одной строкой, и вам нужно только определить предшественника этой единственной строки.

В предыдущей теме я упоминал, что lag(), вероятно, может творить чудеса с триггером на уровне оператора - для вставки из нескольких строк вы просто получите одну «загрузочную строку», самую последнюю в таблице, которая хронологически предшествует первой в для входящего пакета вы бы отдали его для первой строки, чтобы отключить _diff, а затем использовали lag() для остальной части пакета.

Что касается «аномалии», мне нужно посмотреть, что именно вы делаете. Оба моих ответа связаны с демо-версией, и она отлично работает независимо от того, вставляете ли вы несколько одновременно или делаете это один за другим. Вот пример, где я разделяю партию и вставляю ее одну за другой, затем проверяю после каждой вставки, а позже вставляю все остальные сразу: демо. Если вы можете воспроизвести поведение, с которым столкнулись, но сделайте это на db<>fiddle, нажмите run и поделитесь ссылкой на это, я посмотрю. Вы запускаете вставки из приложения, используя пул? Из отдельных вкладок/сессий в DBeaver?

Я просто запускаю весь SQL на одной вкладке в DBeaver. Никакого пула, это все есть на этом ноуте (на котором я тоже здесь пишу). Я перепроверил каждую строку своего кода и обнаружил некоторые различия между вашим и моим, поэтому сообщу о своих результатах. Теперь я думаю, что это я делаю неправильно, а не DBeaver или ваш код.

Я только что заметил, что некоторые из ваших временных меток имеют другой формат: это может быть причиной. SQLite принимает только ISO-8601 ГГГГ-ММ-ДД ЧЧ:ММ:СС. Если вы используете что-нибудь еще, он тихо предположит, что это просто текст, поэтому вся логика, определяющая, что считать последней строкой, полностью изменится.

Да, я вижу, что в SQLite нет внутреннего типа datetime. Вот именно это я и запустил в DBeaver, и это сработало! dbfiddle.uk/ljgXecSu. Как ни странно, <db.fiddle> выдает ошибку. Я добавил в таблицу больше вычисляемых столбцов, чтобы извлечь поля даты и времени из ТЕКСТА и создать правильное сортируемое поле gen_isodate. Исходный формат даты и времени — DD.MM.YYYY HH:mm:ss.sss, а не YYYY-MM-DD HH:mm:ss.sss. Я изменил исходный формат, чтобы упростить свой вопрос, поскольку мое внимание было сосредоточено на расчетах разницы строк.

Я также предполагал (возможно, ошибочно, хотя я так не думаю), что SQLite будет сортировать столбец «искусственный» datetime TEXT именно так — TEXT, лексикографически.

Вот ваша исправленная рабочий пример: dbfiddle.uk/TGLeFxkD ей не нравились двойные кавычки вместо одинарных кавычек и не нравилось несколько команд в одном блоке.

Мне только что пришло в голову: если вы используете оператор SELECT в TRIGGER, можете ли вы использовать там функцию LAG(), и стоит ли оно того?

Да, можно, и нет, оно того не стоит, если только SQLite не добавит триггеры на уровне операторов или не изменится логика в вашем приложении.