Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как правильно построить индекс. Получение последнего значения по дате / 7 сообщений из 7, страница 1 из 1
08.06.2020, 18:30
    #39967159
anvano
anvano
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно построить индекс. Получение последнего значения по дате
Есть табличка с показаниями датчиков (самих датчиков около 20 тыс )

counter_id - идентификатор датчика
val - значение
val_date - дата значения

Значений в табличке сотни миллионов уже

В табличку постоянно валятся новые показания, табличка секционирована по месяцу даты и по данному полю индекс (локальный).
Ранее надо было всегда вытаскивать таблицу значений за определённый календарный день и такая схема прекрасно работала.

А сейчас вдруг появилась необходимость, если для для какого-то датчика в запрошенном дне НЕ было значения, то отражать последнее по дате значение. Вот что-то я на "последнее по дате" заткнулся немного при таком размере таблицы.

Если бы всегда нужны были данные на текущий день - можно было бы где-то хранить дату последнего показания для каждого счётчика, а тут получается, что могут запросить данные за любой день и если для какого-то счётчика дата попадает в "дырку" надо именно для этого счётчика искать когда же было последнее показание.

Т.е. было просто:
Код: plsql
1.
SELECT  *  FROM  counter_val  WHERE  val_date  >=  to_date('01.01.2020')  and val_date < to_date('02.01.2020')


Код: plaintext
1.
2.
3.
4.
SELECT STATEMENT                    
  PARTITION RANGE SINGLE            
   TABLE ACCESS BY LOCAL INDEX ROWID
    INDEX RANGE SCAN

А теперь мне для начала для каждого счётчика нужно получить последнюю дату (меньшую или равную указанной), в которой есть значение
Код: plsql
1.
2.
SELECT  counter_id , max(val_date)  FROM  counter_val  WHERE  val_date  <  to_date('02.01.2020') 
GROUP BY counter_id


Код: plaintext
1.
2.
3.
4.
SELECT STATEMENT           
  HASH GROUP BY            
   PARTITION RANGE ITERATOR
    TABLE ACCESS FULL


Фуллскан полюбому? на трёхстах миллионах значений уже за две минуты переваливает :(

Никакой путной структуры для хранения таких данных "с дырками" нет, чтобы можно было на любую дату быстро вытащить последнее значение?
Пытаться насильно заполнять "дырки" последним значением ?

Обидно то, что "дырки" небольшие и я заведомо знаю, что если в каком-то дне нет показания, то за последние три-четыре дня точно оно есть.... но базе этого не объяснить.

--------------------------------------------------------------
Запомните, товарищи офицеры, чтобы ничего не делать, надо уметь делать все.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2020, 19:05
    #39967165
exciter
exciter
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно построить индекс. Получение последнего значения по дате
anvano,

LAG() IGNORE NULLS?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2020, 19:22
    #39967167
exciter
exciter
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно построить индекс. Получение последнего значения по дате
Если не напутал с синтаксисом,
Код: plsql
1.
SELECT  cv.*, nvl(cv.val,LAG(cv.val) IGNORE NULLS OVER (PARTITION BY cv.counter_id ORDER BY cv.val_date))  FROM  counter_val  cv WHERE  cv.val_date  <= :date
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2020, 19:52
    #39967169
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно построить индекс. Получение последнего значения по дате
anvano

А сейчас вдруг появилась необходимость, если для для какого-то датчика в запрошенном дне НЕ было значения, то отражать последнее по дате значение.

Как определяете факт отсутствия значения для какого-либо датчика?
Есть справочник датчиков или где?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2020, 19:52
    #39967170
exciter
exciter
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно построить индекс. Получение последнего значения по дате
Если же в таблице вовсе нет строки на заданную дату, а не поле val_date не заполнено, то может быть, так:

Код: plsql
1.
2.
3.
4.
5.
select counter_id, 
max(val) keep(dense_rank first order by val_date desc) from 
counter_test
where val_date<=:dt
group by counter_id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2020, 20:04
    #39967176
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно построить индекс. Получение последнего значения по дате
anvano

Никакой путной структуры для хранения таких данных "с дырками" нет, чтобы можно было на любую дату быстро вытащить последнее значение?

Есть. SCD2, например, с partition key по дате завершения версии.

anvano

Пытаться насильно заполнять "дырки" последним значением ?

Подход имеет право на жизнь, но требует внимательного рассмотрения:
- Кто и когда будет формировать отсутствующие значения?
- Достаточно ли часто выполняется указанный запрос по актуальному периоду, чтобы экономия на его ресурсоемкости покрыла ресурсы, необходимые для заполнения пустот?
- Можно ли разнести подготовку данных и их использование по времени?

anvano

Обидно то, что "дырки" небольшие и я заведомо знаю, что если в каком-то дне нет показания, то за последние три-четыре дня точно оно есть.... но базе этого не объяснить.


Было бы желание, а объяснить можно.
Разными способами.
Например, если справочника приборов нет:
Код: plsql
1.
2.
3.
4.
5.
6.
SELECT counter_id
     , max(val) keep (dense_rank last order by val_date) val
     , max(val_date) val_date
  FROM  counter_val  
 WHERE  val_date  >=  to_date('01.01.2020') -5 -- поясняем базе, что искать надо с запасом в 5 дней
   and val_date < to_date('02.01.2020')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.06.2020, 20:06
    #39967178
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как правильно построить индекс. Получение последнего значения по дате
...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Как правильно построить индекс. Получение последнего значения по дате / 7 сообщений из 7, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 532ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1881176]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 160ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 2ms
others: 286ms
total:  532ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]