Как сформировать вложенный запрос в Sqlite?

Имеется простая база вида:
1465858740 isig_level 10.6 5.4
1465858440 isig_level 11 5.7
1465858140 isig_level 11.4 5.9
1465857840 isig_level 11.8 6.1

В ней иногда попадаются ошибочные нулевые значения:
epoch name value
1465858440 isig_level 11
1465858140 isig_level 0
1465857840 isig_level 11.8

В то же время, там могут быть и нормальные нулевые значения:
epoch name value
1465858440 isig_level 0
1465858140 isig_level 0
1465857840 isig_level 0

Признак ошибочных — до него и после него значения не равны нулю.

Сейчас я ищу ошибочные так:
function sql_delete_outliers()
	local sqlite3 = require 'lsqlite3'
	local db = sqlite3.open(config.DB_FILE)
	local sql = [[SELECT * FROM "data" WHERE "message"="isig_level" and value="0" ORDER BY "epoch";]] 
	db:exec("BEGIN TRANSACTION;")
	local all_counter, processed_counter = 0, 0
	for epoch,key,message,value in db:urows(sql) do 
		all_counter = all_counter + 1
		local value_up, value_down, sql_select_2
		sql_select_2 = [[SELECT * FROM "data" WHERE "message"="isig_level" AND "epoch" < ]]..epoch..[[ ORDER BY "epoch" DESC LIMIT 1;]]
		for a,b,c,value in db:urows(sql_select_2) do 
			value_down = value
		end
		sql_select_2 = [[SELECT * FROM "data" WHERE "message"="isig_level" AND "epoch" > ]]..epoch..[[ ORDER BY "epoch"  LIMIT 1;]]
		for a,b,c,value in db:urows(sql_select_2) do 
			value_up = value
		end

		if (value_down ~= 0 and value_up ~= 0 and value_down ~= nil and value_up ~= nil) then
			local new_value = system_func.math_round((value_down+value_up)/2, 1)
			local sql_update = [[UPDATE "data" SET "value" = ]]..new_value..[[ WHERE "key" = "]]..key..[[";]]
			db:exec(sql_update)
			processed_counter = processed_counter + 1
		end
	end
	db:exec("COMMIT TRANSACTION;")
	db:close()
	return all_counter, processed_counter
end

Т.е. сначала выбираю все нулевые, а потом для каждой выдачи делаю запрос на следующую и предыдущую строку, и если они не равны нулю, обновляю текущую нулевую средним значением.
База растет, и такая выборка начинает занимать ощутимое время.
Можно ли выбрать такие характерные значения одним запросом SQL? Использую Sqlite 3.8
  • Вопрос задан
  • 1327 просмотров
Решения вопроса 1
@nozzy
Silex, Symfony, Laravel, SQL
select
t2.epoch,
t2.`value`
from
(
 select 
 t1.`value`,
 t1.epoch,
 (select `value` from data where epoch < t1.epoch ORDER BY epoch DESC LIMIT 1) prev_value,
 (select `value` from data where epoch > t1.epoch ORDER BY epoch LIMIT 1) next_value
 from data t1
 where t1.value = 0
 order by t1.epoch
) t2
where t2.prev_value = 0 and t2.next_value = 0
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы