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

Question

[Влад Зайцев]

Имеется простая база вида:
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

Answer 1

[nozzy]

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

Comments

[Влад Зайцев]

Нет, что-то не так. Выдает:
epoch, key, value
1467372300 1467372300_isig_level 0
1467372600 1467372600_isig_level 0
1467457860 1467457860_isig_level 0

А в базе:
epoch, key, message, value
1467457860 1467457860_isig_level isig_level 0
1467458160 1467458160_sensor_calibrate_factor sensor_calibrate_factor 0.4626
1467458160 1467458160_isig_level isig_level 0
1467458160 1467458160_high_glucose high_glucose 165
1467458460 1467458460_isig_level isig_level 17

[Freeman]

Влад Зайцев: В подзапросы нужно связку по ключевым словам добавить, а то сейчас по всей таблице ищет. В других СУБД это решается аналитическими функциями, и связка по ключу пишется в partition by.

[Freeman]

По ключевым полям.

[Влад Зайцев]

Vapaamies: ну я вот так делал, когда копировал предыдущий вывод:
select
t2.epoch,
t2.`key`,
t2.`value`
from
(
select
t1.`value`,
t1.`key`,
t1.epoch,
t1.message,
(select `value` from data where epoch < t1.epoch and message = "isig_level" ORDER BY epoch DESC LIMIT 1) prev_value,
(select `value` from data where epoch > t1.epoch and message = "isig_level" ORDER BY epoch LIMIT 1) next_value
from data t1
where t1.value = 0 and t1.message = "isig_level"
order by t1.epoch
) t2
where t2.prev_value = 0 and t2.next_value = 0

[nozzy]

Влад Зайцев
Есть дамп таблицы в sql-формате?

[Влад Зайцев]

nozzy: https://www.dropbox.com/s/d3mi909vk6rv78d/damp.sql?dl=0

[nozzy]

@vvzvlad
Что не так с этим запросом:
select
t2.epoch,
t2.`key`,
t2.`value`
from
(
select
t1.`value`,
t1.`key`,
t1.epoch,
t1.message,
(select `value` from data where epoch < t1.epoch and message = "isig_level" ORDER BY epoch DESC LIMIT 1) prev_value,
(select `value` from data where epoch > t1.epoch and message = "isig_level" ORDER BY epoch LIMIT 1) next_value
from data t1
where t1.value = 0 and t1.message = "isig_level"
order by t1.epoch
) t2
where t2.prev_value = 0 and t2.next_value = 0

[nozzy]

Влад Зайцев:
Может так:
where t2.prev_value <> 0 and t2.next_value <> 0
вместо
where t2.prev_value = 0 and t2.next_value = 0

[Влад Зайцев]

nozzy: да, так работает. спасибо!

[Влад Зайцев]

nozzy: хм, а можно ли как-то ускорить? установкой pragma какой-нибудь? А то на lua оно работает 11 секунд, а через запрос — 42.

[nozzy]

Запрос можно покороче:
select
t1.epoch,
t1.value,
t1.message,
(select `value` from data where epoch < t1.epoch and message = "isig_level" ORDER BY epoch DESC LIMIT 1) prev_value,
(select `value` from data where epoch > t1.epoch and message = "isig_level" ORDER BY epoch LIMIT 1) next_value
from data t1
where t1.value = 0 and t1.message = "isig_level"
and prev_value <> 0 and next_value <> 0
order by t1.epoch

Но главное - CREATE INDEX index1 ON data(epoch);

[nozzy]

Влад Зайцев:
CREATE INDEX index1 ON data(epoch);

[Влад Зайцев]

nozzy: ого, спасибо! Запрос снизил время до 3с, создание индекса до 30мс.