How to form a nested query in Sqlite?

Question

SQL

There is a simple type of base:
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
It can sometimes be found erroneous zero values: an
epoch name of value
1465858440 isig_level 11
1465858140 isig_level 0
1465857840 isig_level 11.8
At the same time, there may be normal zero values:
epoch name value
1465858440 isig_level 0
1465858140 isig_level 0
1465857840 isig_level 0
Sign of erroneous - values ​​before and after it are not equal to zero.
Now I'm looking for the wrong ones like this:

function sql_delete_outliers()
  local sqlite3 = require 'lsqlite3'
  local db = sqlite3.open(config.DB_FILE)
  local sql =  
  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 = ..epoch..
    for a,b,c,value in db:urows(sql_select_2) do 
      value_down = value
    end
    sql_select_2 = ..epoch..
    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 = ..new_value....key..
      db:exec(sql_update)
      processed_counter = processed_counter + 1
    end
  end
  db:exec("COMMIT TRANSACTION;")
  db:close()
  return all_counter, processed_counter
end

Those. first I select all zeros, and then for each issue I make a request for the next and previous row, and if they are not equal to zero, I update the current zero with the average value.
The base is growing, and such a selection begins to take a significant amount of time.
Is it possible to select such characteristic values ​​with a single SQL query? I am using Sqlite 3.8

Answer 1

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