MODEL句の使用

oracle db sql


MODEL句というのは、Oracle10gから使えるSELECT文の機能です。認知度が極端に低いです。

簡単な例 - テーブルの値を書き換えるだけ
with dt as(
SELECT '1' ID, 'JP' Country, '1000' Costs, '1' Timeline FROM DUAL UNION ALL
SELECT '2' ID, 'JP' Country, '200' Costs, '2' Timeline FROM DUAL UNION ALL
SELECT '3' ID, 'JP' Country, '4000' Costs, '3' Timeline FROM DUAL UNION ALL
SELECT '4' ID, 'UK' Country, '10' Costs, '1' Timeline FROM DUAL UNION ALL
SELECT '5' ID, 'US' Country, '30' Costs, '1' Timeline FROM DUAL UNION ALL
SELECT '6' ID, 'US' Country, '11' Costs, '2' Timeline FROM DUAL UNION ALL
SELECT '7' ID, 'US' Country, '1111' Costs, '3' Timeline FROM DUAL
)

select
calculated_difference
from
dt
model
  DIMENSION BY (id)
  MEASURES (Costs calculated_difference)
  rules
  (
    calculated_difference[any] = 100
  )