Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Подсчет среднего значения  [new]
ijuve10
Member

Откуда:
Сообщений: 54
Добрый день, народ!
Помогите разобраться в следующем вопросе.
Имеется таблица, содержащая в себе следующие значения. Т.е. данные распределены по ячейкам (CELL) C1, C2, C3, каждая из которых содержит в себе некоторое количество точек (POINT) и у каждой точки на определенный момент (YYYY,MM,DD,HH24) имеется свое значение (SWI1,SWI5...)
+
CELLPOINTYYYYMMDDHH24SWI1SWI5SWI10SWI15SWI20SWI40SWI60SWI100QFLAG1QFLAG5QFLAG10QFLAG15QFLAG20QFLAG40QFLAG60QFLAG100
C1101200711232727272727272727913418.512.59.553.52
C110220071123272727272727272733.527.516.5129532
C11012007122314.515.51616.51717.517.517.51001008570.559.536.52616.5
C11022007122314.515.51616.51717.517.517.5438477665735.525.516.5
C21062007112319.52323.523.5242424249665433225.5149.56
C21072007112319.52323.523.52424242435.55339302413.59.55.5
C21062007122310.51118.52427.53131.531.5504546.5505352.54736
C21072007122310.51118.52427.53131.531.518.53742475051.54636
C31122007112317.5171818.51919.519.519.54073.56048.54023.516.510.5
C31132007112317.517181818.51919191009472.55847.527.519.512
C3112200712231510.516.520.526.52828.52854.553.554.55654.548.538
C31132007122376.51014.518.52526.527.510078676462.5585139.5


Необходимо посчитать средние значения (SWI1,SWI5...) всех точек по ячейкам, относящиеся к одному моменту времени (YYYY,MM,DD,HH24). Т.е. на 01.01.2007 23ч. посчитать среднее значение точек 101 и 102 для ячейки С1 и т.д. Должно получится следующее, отбросив при этом колонку с POINT:

+
CELLYYYYMMDDHH24SWI1SWI5SWI10SWI15SWI20SWI40SWI60SWI100QFLAG1QFLAG5QFLAG10QFLAG15QFLAG20QFLAG40QFLAG60QFLAG100
C120071123272727272727272762.2530.7517.512.259.2553.252
C12007122314.515.51616.51717.517.517.571.5928168.2558.253625.7516.5
C22007112319.52323.523.52424242465.7559413124.7513.759.55.75
C22007122310.51118.52427.53131.531.534.254144.2548.551.55246.536
C32007112317.5171818.2518.7519.2519.2519.257083.7566.2553.2543.7525.51811.25
C32007122345.7510.2515.519.525.7527.25286466.2560.2559.2559.2556.2549.7538.75


После чего необходимо полученные значения для каждой ячейки на определенный момент времени присвоить к каждой точке. Т.е. обновить значения каждой точки в первой таблице полученными значениями из последней таблицы. Т.е. здесь должны быть одинаковые значения для каждой точки, относящиеся к определенной ячейке на определенный момент времени:
+
CELLPOINTYYYYMMDDHH24SWI1SWI5SWI10SWI15SWI20SWI40SWI60SWI100QFLAG1QFLAG5QFLAG10QFLAG15QFLAG20QFLAG40QFLAG60QFLAG100
C110120071123272727272727272762.2530.7517.512.259.2553.252
C110220071123272727272727272762.2530.7517.512.259.2553.252
C11012007122314.515.51616.51717.517.517.571.5928168.2558.253625.7516.5
C11022007122314.515.51616.51717.517.517.571.5928168.2558.253625.7516.5
C21062007112319.52323.523.52424242465.7559413124.7513.759.55.75
C21072007112319.52323.523.52424242465.7559413124.7513.759.55.75
C21062007122310.51118.52427.53131.531.534.254144.2548.551.55246.536
C21072007122310.51118.52427.53131.531.534.254144.2548.551.55246.536
C31122007112317.5171818.2518.7519.2519.2519.257083.7566.2553.2543.7525.51811.25
C31132007112317.5171818.2518.7519.2519.2519.257083.7566.2553.2543.7525.51811.25
C31122007122345.7510.2515.519.525.7527.25286466.2560.2559.2559.2556.2549.7538.75
C31132007122345.7510.2515.519.525.7527.25286466.2560.2559.2559.2556.2549.7538.75


Вопрос состоит в оптимизации процесса. Делал я все поэтапно и с использованием вьюшек. Тестировал все с двумя тысячами строк и результат был выдан за считанные секунды. Но в реале этих строк около 80 млн. Вчера запустил данный процесс, так вот он до сих пор выполняется. Ниже привожу все запросы, которые я использовал для выполнения данной задачи. Подскажите пожалуйста, как можно оптимизировать данный процесс. Можно ли это все как-нибудь одним запросом решить или же стоит не вьюшками работать, а поэтапно созданием таблиц?

+
-- первая таблица
CREATE VIEW VW_PNT_CELLS_T AS
SELECT g.point,
  g.cell,
  s.yyyy,
  s.mm,
  s.dd,
  s.hh24,
  s.swi5,
  s.swi10,
  s.swi15,
  s.swi20,
  s.swi40,
  s.swi60,
  s.swi100,
  s.QFLAG1,
  s.QFLAG5,
  s.QFLAG10,
  s.QFLAG15,
  s.QFLAG20,
  s.QFLAG40,
  s.QFLAG60,
  s.QFLAG100
FROM table1 g,
  table2 s
WHERE g.point = s.point;

-- вторая таблица
CREATE VIEW vw_pnt_avg_to_cells_t as 
select g.cell, s.yyyy, s.mm, s.dd, s.hh24,
avg(case when s.swi1 is not nan then swi1 end) swi1,
avg(case when s.swi5 is not nan then swi5 end) swi5,
avg(case when s.swi10 is not nan then swi10 end) swi10,
avg(case when s.swi15 is not nan then swi15 end) swi15,
avg(case when s.swi20 is not nan then swi20 end) swi20,
avg(case when s.swi40 is not nan then swi40 end) swi40,
avg(case when s.swi60 is not nan then swi60 end) swi60,
avg(case when s.swi100 is not nan then swi100 end) swi100,
avg(case when s.QFLAG1 is not nan then QFLAG1 end) QFLAG1,
avg(case when s.QFLAG5 is not nan then QFLAG5 end) QFLAG5,
avg(case when s.QFLAG10 is not nan then QFLAG10 end) QFLAG10,
avg(case when s.QFLAG15 is not nan then QFLAG15 end) QFLAG15,
avg(case when s.QFLAG20 is not nan then QFLAG20 end) QFLAG20,
avg(case when s.QFLAG40 is not nan then QFLAG40 end) QFLAG40,
avg(case when s.QFLAG60 is not nan then QFLAG60 end) QFLAG60,
avg(case when s.QFLAG100 is not nan then QFLAG100 end) QFLAG100
from table1 g, table2 s
where g.point = s.point
group by  g.cell, s.yyyy, s.mm, s.dd, s.hh24;

-- конечный результат
SELECT o.point,
  o.cell,
  o.yyyy,
  o.mm,
  o.dd,
  o.hh24,
  (SELECT s.swi1
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi1,
  
  (SELECT s.swi5
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
   and rownum =1
  ) swi5,
  
  (SELECT s.swi10
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi10,
  
  (SELECT s.swi15
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi15,
  
  (SELECT s.swi20
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi20,
  
  (SELECT s.swi40
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi40,
  
  (SELECT s.swi60
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi60,
  
  (SELECT s.swi100
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi100,
  
    (SELECT s.QFLAG1
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG1,
  
  (SELECT s.QFLAG5
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG5,
  
  (SELECT s.QFLAG10
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG10,
  
  (SELECT s.QFLAG15
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG15,
  
  (SELECT s.QFLAG20
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG20,
  
  (SELECT s.QFLAG40
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG40,
  
  (SELECT s.QFLAG60
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG60,
  
  (SELECT s.QFLAG100
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG100
  
FROM vw_pnt_cells_t o
22 янв 14, 13:29    [15451933]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет среднего значения  [new]
Кристобаль Хозевич
Member

Откуда: тутошний
Сообщений: 250
ijuve10,

Несколько (не все) вопросы:

1. Может быть, нормализовать результирующую таблицу?
2. Не вижу запроса update/merge. Или у вас выборка идёт сутки?
3. avg(case when s.swi1 is not nan then swi1 end) => avg(nanvl(s.swi1,null)) ?
4. Всё, что после "-- конечный результат" - это что? В смысле для чего вы это делаете?
5. (80000000 / 2000) * (считанные секунды) = сколько суток?
22 янв 14, 14:42    [15452333]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет среднего значения  [new]
-2-
Member

Откуда:
Сообщений: 15330
Кристобаль Хозевич
5. (80000000 / 2000) * (считанные секунды) = сколько суток?
с учетом десятка скалярных подзапросов зависимость по крайней мере квадратичная.
22 янв 14, 14:49    [15452381]     Ответить | Цитировать Сообщить модератору
 Re: Подсчет среднего значения  [new]
rockclimber
Member

Откуда: у меня в голове опилки?
Сообщений: 11085
ijuve10
-- конечный результат

+
SELECT o.point,
  o.cell,
  o.yyyy,
  o.mm,
  o.dd,
  o.hh24,
  (SELECT s.swi1
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi1,
  
  (SELECT s.swi5
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
   and rownum =1
  ) swi5,
  
  (SELECT s.swi10
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi10,
  
  (SELECT s.swi15
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi15,
  
  (SELECT s.swi20
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi20,
  
  (SELECT s.swi40
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi40,
  
  (SELECT s.swi60
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi60,
  
  (SELECT s.swi100
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) swi100,
  
    (SELECT s.QFLAG1
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG1,
  
  (SELECT s.QFLAG5
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG5,
  
  (SELECT s.QFLAG10
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG10,
  
  (SELECT s.QFLAG15
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG15,
  
  (SELECT s.QFLAG20
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG20,
  
  (SELECT s.QFLAG40
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG40,
  
  (SELECT s.QFLAG60
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG60,
  
  (SELECT s.QFLAG100
  FROM vw_pnt_avg_to_cells_t s
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
  ) QFLAG100
  
FROM vw_pnt_cells_t o
То ли я чего-то глобально не понимаю в этой жизни этом запросе, то ли вам надо этот "конечный результат" заменить на
SELECT o.point,
  o.cell,
  o.yyyy,
  o.mm,
  o.dd,
  o.hh24,
  s.*
  FROM vw_pnt_avg_to_cells_t s,
       vw_pnt_cells_t o
  WHERE s.yyyy =o.yyyy
  AND s.mm     = o.mm
  AND s.dd     = o.dd
  AND s.hh24   = o.hh24
  and rownum =1
Даже если не ускорит, хотя бы можно будет прочитать и понять.
22 янв 14, 15:21    [15452576]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить