Третий вариант — просто брать максимум группы, где группа — это last_a:
select
v.*
,max(b) over(partition by last_a) test_val
from
(
select
a,b
,last_value(b ignore nulls)over(order by a) right_val
,max(case when b is not null then a end)over(order by a) last_a
from t
) v
Или попроще — второй уровень считать через lag()over():
select
v.*
,lag(b,a-last_a)over(order by a) test_val
from
(
select
a,b
,last_value(b ignore nulls)over(order by a) right_val
,max(case when b is not null then a end)over(order by a) last_a
from t
) v
Он тянет по отдельности по размеру: 1) создание первой таблицы и 2) создание и заполнения второй таблички, но вместе в одну схему это не скомпилить. Можно это сделать как без промежуточной первой таблицы?
В оракле например можно получить то же самое через max()over() только с двумя уровнями вложенности:
-- тестовая табличка
with t(a,b) as (
select 1,10 from dual union all
select 2,20 from dual union all
select 3,null from dual union all
select 4,5 from dual union all
select 5,null from dual union all
select 6,null from dual union all
select 7,1 from dual
)
select
v.*
,max(b)over(order by a range between a-last_a preceding and a-last_a preceding) test_val
from
(
select
a,b
,last_value(b ignore nulls)over(order by a) right_val
,max(case when b is not null then a end)over(order by a) last_a
from t
) v
Вкратце пояснение: сначала получаем последний ключ (а) с NOT NULL значением, затем просто по полученному ключу берем нужное значение.
Еще я нагуглил такое решение: sqlmag.com/t-sql/last-non-null-puzzle
Дайте готовую схему на sqlfiddle.com попробую на MS SQL адаптировать оракловый:
merge
into DEVICE_COUNTER t
using (
select t.rid, t.new_cnt_value1,t.new_cnt_value2
from (
select
ROWID as rid
, dev_counter_duplex1
, dev_counter_duplex2
, last_value( dev_counter_duplex1 ignore nulls )
over( partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value1
, last_value (dev_counter_duplex2 ignore nulls )
over( partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value2
from DEVICE_COUNTER
) t
where t.dev_counter_duplex1 is null
or t.dev_counter_duplex2 is null
) v
on (t.rowid = v.rid and (t.dev_counter_duplex1 is null or t.dev_counter_duplex2 is null))
when matched then
update set dev_counter_duplex1 = new_cnt_value1
,dev_counter_duplex2 = new_cnt_value2
Например, в первом апдейте от топик стартера — идет 3 обращения к одной и той же таблице, причем на каждую строку еще и подзапросом вычисляется max() — т.е. это уже точно как минимум один построчный nested loops перебор. К тому же left join, а те join — то есть несоответствующие данные то даже не отфильтрованы и апдейт значения будет происходит само на себя
Не проще сначала выбрать неверные значения и обновить их рассчитанными верными значениями?
Так это зависит от соотношения — кол-ва которое необходимо изменить/кол-во которое не нужно менять. Если менять нужно хотя бы, скажем, 30%, то merge будет гораздо лучше, т.к. будет всего 2 фулскана и один хэшджойн. Вообще с точки зрения гибкости даже — при необходимости оптимизатор может поменять на nested loops, если окажется что менять надо мало.
Я в MS SQL не разбираюсь, но интересно почему не используется такой же MERGE в MS SQL? Это же крайне быстро было бы аналитикой рассчитать неверные значения и хэшджойном проапдейтить?
Я бы для Oracle еще уменьшил сначала объем для апдейта, отсеяв значения которые обновлять не надо, да и сразу несколько полей можно:
merge
into DEVICE_COUNTER t
using (
select t.rid, t.new_cnt_value1,t.new_cnt_value2
from (
select
ROWID as rid
, dev_counter_duplex1
, dev_counter_duplex2
, last_value(nullif(dev_counter_duplex1,0) ignore nulls )
over (partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value1
, last_value(nullif(dev_counter_duplex1,0) ignore nulls )
over (partition by dev_id order by dev_counter_date, dev_counter_id) as new_cnt_value2
from DEVICE_COUNTER
) t
where t.dev_counter_duplex1 is null or t.dev_counter_duplex1=0
or t.dev_counter_duplex2 is null or t.dev_counter_duplex2=0
) v
on (t.rowid = v.rid)
when matched then
update set dev_counter_duplex1 = new_cnt_value1
,dev_counter_duplex2 = new_cnt_value2
К тому же сделал учитывая, что нужно не только NULLs, но и 0 проапдейтить, а то в решении автора топика 0 вообще не учитываются почему-то, несмотря на озвученное условие в начале.
да, знаю про расширение, но у меня несколько другая позиция — я считаю, что есть достаточное кол-во хороших разработчиков, которые сами прекрасно знают как запрос лучше выполнять. Нужно оставлять разработчикам возможность более низкоуровневого доступа.
Да и я рад что растет, причем хочется чтобы в дальнейшем совместимость с Oracle возрастала. Буквально вчера узнал что в MySQL появились хинты и 2-3 из них даже с такими же именами как в Oracle — хочу такого же и для PostreSQL :)
Интересно, что команда explain plan (результат которой доступен с помощью функции dbms_xplan.display) все равно покажет план, построенный из предположения равномерности, как будто оптимизатор ожидает получения половины таблицы...
Просто в отличие от PostreSQL в Oracle никак нельзя вызвать explain plan с указанием bind variables (к сожалению...)
Спасибо, интересно было почитать про реализацию в PostreSQL.
Насчет Oracle пара поправок:
Поэтому (начиная с версии 11g) Оракл умеет находить и специально обрабатывать запросы, чувствительные к значениям переменных связывания (это называется «adaptive cursor sharing»). При выполнении запроса используется уже имеющийся в кэше план, но отслеживаются реально затраченные ресурсы и сравниваются со статистикой предыдущих выполнений.
оцениваются не ресурсы, а сами входные значения bind variables (см. v$sql_cs_histogram)
Помимо этого у Oracle есть еще механизмы уточнения/исправления плана в случае ошибки вычисления cardinality — dynamic sampling и cardinality feedback.
А с 12-й версии появился новый механизм adaptive plans — который позволяет изменить план прямо во время выполнения, в случае высокой погрешности estimated cardinality от реальной
еще легко такое делается с помощью разницы текущего значения и аналитических lead/lag этого поля и известного алгоритма «start_of_group». Причем это будет значительно легче для «гибких» диапазонов, т.е. например, если захотим считать перерывом 10 дней или 2 недели и тд…
select/*+ leading(test2 test1) use_hash(test1) */ *
from test1 t1,test2 t2
where t1.id*0 = t2.id*0 -- форсируем hash join "все-ко-всему"
and (t1.id = t2.id or t1.id2 = t2.id) -- остальные условия оставляем в фильтре
Раз уж у вас получилось хоть как-то установить и настроить, пожалуйста, сделайте виртуальную машинку с ним и поделитесь. Тогда бы мы смогли тоже потестировать.
Вкратце пояснение: сначала получаем последний ключ (а) с NOT NULL значением, затем просто по полученному ключу берем нужное значение.
Еще я нагуглил такое решение: sqlmag.com/t-sql/last-non-null-puzzle
зы. А аналога model в MS SQL тоже нет?
К тому же сделал учитывая, что нужно не только NULLs, но и 0 проапдейтить, а то в решении автора топика 0 вообще не учитываются почему-то, несмотря на озвученное условие в начале.
Насчет Oracle пара поправок:
оцениваются не ресурсы, а сами входные значения bind variables (см. v$sql_cs_histogram)
Помимо этого у Oracle есть еще механизмы уточнения/исправления плана в случае ошибки вычисления cardinality — dynamic sampling и cardinality feedback.
А с 12-й версии появился новый механизм adaptive plans — который позволяет изменить план прямо во время выполнения, в случае высокой погрешности estimated cardinality от реальной
ps. только для not null полей