分析ビューを使用してテーブルをアップグレード (2023/03/31)

3月 30, 2023

分析ビューを使用してテーブルをアップグレード (2023/03/31)

https://blogs.oracle.com/datawarehousing/post/upgrade-tables-with-analytic-views

投稿者： William Endress | Autonomous Database Product Management

はじめに

クエリの記述または生成は、特にクエリが集計レベルで分析計算を返す必要がある場合に困難になる可能性があります。たとえば、前年同月と比較した当月の売上の変化率など、メジャーを返す単一のクエリを作成するのは困難です。これは 3 パスのクエリであり、最初のパスで集計、パーティション化された外部結合、フィルター拡張、2 番目のパスで分析ウィンドウ関数、最後のパスでいくつかの基本的な計算を行います。

通常、これらのクエリを自分で作成することはありません。それはあまりにも難しいです。代わりに、Oracle Analytic Cloud (OAC) などのビジネスインテリジェンスツールを使用します。OAC には優れたデータ視覚化機能がありますが、難しい作業は OAC サーバーで行われ、クエリを生成します。

開発者は、たとえば Oracle Application Express (APEX) を使用してカスタムアプリケーションを作成するときに、SQL 生成に追われています。レポートまたはグラフを提供する単一の分析クエリを作成することは、複雑になる場合があります。データの選択、さまざまな集計、次元の変更、およびさまざまなパラメーター化された計算をサポートする SQL ジェネレーターを作成するのは、より複雑です。これは、典型的な開発者が試みることのないものです。

なぜそんなに難しいのですか？開発者がすべての作業を行う必要があるため、難しいです。開発者は、SQL ジェネレーターを記述し、それをサポートするメタデータを管理する必要があります。テーブルはデータの格納と取得には優れていますが、テーブルはクエリを記述しません。彼らは？

クエリを記述したテーブルがあった場合はどうなるでしょうか？そのテーブルにセマンティックモデルが付属していたらどうなるでしょうか。そのテーブルが、分析計算を簡単に表現できる特別な関数をサポートしているとしたら？そして、アプリケーションが単純なテンプレートのような SELECT ステートメントでそのテーブルをクエリできるとしたら？開発者は、強力なカスタムアプリケーションをすぐに作成できます。

そのようなテーブルはありますが、テーブルではありません。これは Oracle 分析ビューです。

一部の開発者は、分析ビューが複雑であると恐れているかもしれません。急な学習曲線があること。心配しないでください。分析ビューの作成は、テーブルを結合してデータを集計する SQL クエリを作成するよりもそれほど複雑ではありません。少し学習し、前もって追加の作業を行うことで、簡素化されたクエリと豊富な分析コンテンツをアプリケーションに追加する機能という形で、大きな成果が得られます。

分析ビューについて

Oracle 分析ビューは、開発者がデータ間の関係を定義し、さまざまな集計と計算を計算できるようにする Oracle Databaseの機能です。分析ビューを使用すると、ユーザーは大量のデータを簡単に分析し、複雑な分析クエリを実行できます。

分析ビューは、使い慣れた SQL コンストラクトを使用してクエリされるデータの事前構築済みビューを提供することにより、複雑な分析クエリを構築するプロセスを簡素化します。分析ビューを使用することで、開発者は、複雑な SQL クエリの作成に必要な時間と労力を削減し、一般的に使用される集計の結果を事前に計算してキャッシュすることで、クエリのパフォーマンスを向上させることができます。

分析ビューは、ディメンションモデルを使用して、1 つ以上のディメンションテーブルをファクトテーブルで編成します。分析ビューは、時系列、ランキング、階層シェア (比率) など、さまざまな分析機能と集計機能をサポートしています。

この投稿をLive SQL チュートリアルとして実行

この投稿のすべての例 (およびその他) は、次の 2 つの Oracle Live SQL チュートリアルで実行できます。

分析ビューのクイックスタートパート 1 - 簡単な分析ビューの DDL ステートメント
分析ビューのクイックスタートパート 2 - 開発者向けのシンプルなクエリテンプレート

Live SQL に関する分析ビューのチュートリアルは他にも多数あります。

分析ビューを簡単に作成

分析ビューは、必要な構造要素のみを使用して記述メタデータを使用せずに設計することも、複雑な構造と豊富なメタデータを含めることもできます。

シンプルな分析ビューには、分析計算と簡単で再利用可能なクエリンプレートをサポートするために必要なすべての要素が含まれます。この分析ビューを設計することは、テーブルをスタースキーマに結合し、GROUP BY を使用してデータを集計するクエリを作成することと同じくらい難しくありません。

この投稿の例は、Oracle Live SQL の AV スキーマのサンプルデータを使用して分析ビューを作成するために必要な最小限の DDL を示しています。Oracle Autonomous DatabaseのData Studio Analysisアプリケーションを使用して、コーディングなしで同じ分析ビューを作成できます。

伝統的な見方

次のクエリをベースラインとして使用すると役立つ場合があります。

SELECT
    t.year_name
  , p.department_name
  , g.region_name
  , SUM(f.units)
  , SUM(f.sales)
FROM
    av.time_dim      t
  , av.product_dim   p
  , av.geography_dim g
  , av.sales_fact    f
WHERE
        t.month_name = f.month_id
    AND p.category_id = f.category_id
    AND g.state_province_id = f.state_province_id
GROUP BY
    t.year_name
  , p.department_name
  , g.region_name
ORDER BY
    t.year_name
  , p.department_name
  , g.region_name;

このビューは何をしているのですか？それは：

一部の列がメジャーの役割を果たしている列のリストを選択します。YEAR_NAME、DEPARTMENT_NAME、および GEOGRPAHY_NAME 列が属性の役割を果たしているとします。
対策をまとめています。
結合されたテーブルからの選択
行の並べ替え

分析ビュー

単純な分析ビューでは、同じことを行うことができ、さらに次の 3 つのことを非常に簡単に行うことができます。

階層内の集約およびナビゲーションパスを定義
分析ビュー式を使用したメジャーの計算をサポート
クエリの生成を簡素化する一連の階層列を提供

分析ビューは、属性ディメンション、階層ビュー、および分析ビューのオブジェクトのシステムです。それぞれ再利用が可能です。属性次元には、階層で使用されるほとんどのメタデータが含まれます。属性次元から複数の階層を作成できます。分析ビューは階層を参照します。複数の分析ビューが 1 つの階層を参照できます。この再利用性により、多くの作業を節約できます。

詳細については、Live SQL チュートリアルを実行してください。この投稿では、最も重要なトピックについて説明します。

属性ディメンション

属性ディメンションは、ATTRIBUTES 句の列をリストし、レベルのリストを含みます。各レベルには KEY 列があります。レベルには、並べ替えに使用される ORDER BY 列を含めることができます。レベル KEY が適切なラベルでない場合 (たとえば、ID 列に整数が含まれる場合)、別の列が MEMBER NAME として使用されます。DETERMINES 句は、レベルの KEY 値ごとに、決定された属性の値が 1 つしかないことを示しています。DETERMINES 句は、多くの SQL 最適化を有効にします。

テーブルには、CREATE TABLE ステートメントで設定されていない場合にデフォルトとなる多くのプロパティがあるように、属性ディメンションも同様です。すべてのデフォルト値を表示するには、単純な DDL ステートメントを使用して属性次元を作成した後に DDL を取得します。

次のステートメントは、TIME_DIM テーブルで使用される属性ディメンションを作成します。

CREATE OR REPLACE ATTRIBUTE DIMENSION time_attr_dim
USING av.time_dim
ATTRIBUTES
 -- List of columss
 (year_name,
  quarter_name,
  month_name,
  month_end_date)
-- A level of aggregation
LEVEL year
  -- The primary key of the level
  KEY year_name
LEVEL quarter
  KEY quarter_name
  -- For each value of QUARTER_NAME, there is only one value of YEAR_NAME
  DETERMINES (
    year_name)
LEVEL month
  KEY month_name
  -- Sort months by MONTH_END_DATE
  ORDER BY month_end_date
  DETERMINES (
    month_end_date,
    quarter_name);

階層ビュー

階層ビューはシンプルで、親子関係として編成されたレベルのリストです。

CREATE OR REPLACE HIERARCHY time_hier
USING time_attr_dim
 (month CHILD OF
  quarter CHILD OF
  year);

単純！

追加の属性ディメンションと階層ビュー

製品と地理の属性ディメンションと階層は、同じパターンに従います。

CREATE OR REPLACE ATTRIBUTE DIMENSION product_attr_dim
USING av.product_dim
ATTRIBUTES
 (department_name,
  category_name,
  category_id)
LEVEL department
  KEY department_name
LEVEL category
  KEY category_id
  MEMBER NAME category_name
  DETERMINES (
    department_name);
    
CREATE OR REPLACE HIERARCHY product_hier
USING product_attr_dim
 (category CHILD OF
  department);
  
CREATE OR REPLACE ATTRIBUTE DIMENSION geography_attr_dim
USING av.geography_dim
ATTRIBUTES
 (region_name
  , country_name
  , state_province_name
  , state_province_id)
LEVEL region
  KEY region_name
LEVEL country
  KEY country_name
  DETERMINES (
    region_name)
LEVEL state_province
  KEY state_province_id
  MEMBER NAME state_province_name
  DETERMINES (
    country_name);
    
CREATE OR REPLACE HIERARCHY geography_hier
USING geography_attr_dim
 (state_province CHILD OF
  country CHILD OF
  region);

分析ビュー

分析ビューは、SALES_FACT テーブルから FACT メジャーを識別し、REFERENCES 句を使用してファクトテーブルを階層に結合します。FACT メジャーには集計演算子が含まれます。SALES_PRIOR_PERIOD は、計算メジャーの例です。

CREATE OR REPLACE ANALYTIC VIEW sales_av
USING av.sales_fact
DIMENSION BY
  (time_attr_dim
    KEY month_id REFERENCES month_name
    HIERARCHIES (
      time_hier DEFAULT),
   product_attr_dim
    KEY category_id REFERENCES category_id
    HIERARCHIES (
      product_hier DEFAULT),
   geography_attr_dim
    KEY state_province_id 
    REFERENCES state_province_id
    HIERARCHIES (
      geography_hier DEFAULT)
   )
MEASURES
 (sales FACT sales,
  units FACT units,
  sales_prior_period AS (LAG(SALES) OVER (HIERARCHY time_hier OFFSET 1))
  );

それだけです。読み進めて、この分析ビューで何ができるかを確認してください。

SQL 入門

最初の SQL クエリとして、完全なマルチパス分析クエリを作成する人はいません。最初のクエリはおそらく単純です (たとえば、SELECT * FROM SALES_ORDERS)。経験を積むと、クエリはより複雑になり、おそらく GROUP BY と結合が追加されます。経験豊富な開発者は、外部結合と分析ウィンドウ関数を使用して複雑な分析クエリを作成する場合があります。

分析ビューで同じパターンに従います。単純なモデルから始めて、モデルの構造に焦点を当てます。必要に応じて、より高度な機能を利用して、追加の経験を積んでください。

シンプルな分析ビューは非常に強力です。単純なクエリテンプレートを使用してクエリを実行し、計算されたメジャーを含めることができます。これにより、初日からクエリの複雑さの曲線が平坦化されます。

レポート要件の関数としての SQL の複雑さ

例: 単純集計

この最初の例は単純です。このクエリは、データを年、地域、および部門レベルに集計します。

テーブルクエリ

次のクエリは、テーブルから直接選択します。クエリは、予測可能な SELECT ... FROM .. WHERE ... GROUP BY ... ORDER BY パターンに従います。

SELECT
    t.year_name
  , p.department_name
  , g.region_name
  , SUM(sales)
FROM
    av.time_dim      t
  , av.product_dim   p
  , av.geography_dim g
  , av.sales_fact    f
WHERE
        t.month_name = f.month_id
    AND p.category_id = f.category_id
    AND g.state_province_id = f.state_province_id
GROUP BY
    t.year_name
  , p.department_name
  , g.region_name
ORDER BY
    t.year_name
  , p.department_name
  , g.region_name;

分析ビュークエリ

分析ビューから選択するクエリは、テーブルのクエリに似ています。主な違いは、結合と GROUP BY が HIERARCHIES 句に置き換えられることです。

属性の使用

この形式のクエリは、テーブルの元の列である属性を選択します。分析ビューは任意の集計レベルでデータを返すことができるため、WHERE 句では LEVEL_NAME フィルターを使用します。

SELECT
    year_name
  , department_name
  , region_name
  , sales
FROM
    sales_av HIERARCHIES (
      time_hier
    , product_hier
    , geography_hier
    )
WHERE
    time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'DEPARTMENT'
    AND geography_hier.level_name = 'REGION'
ORDER BY
    year_name
  , department_name
  , region_name;

属性からの選択はなじみがあるかもしれませんが、このクエリは、テーブルから選択するクエリと複雑な問題を共有しています。集計のレベルが変化すると、列名を変更する必要があります。たとえば、クエリが四半期、ブランド、および国レベルで選択する場合、多くの列名が変更されます。SQL ジェネレーターはそれを処理する必要があります。

階層列の使用

分析ビューから SELECT するクエリでは、階層列を使用できます。これらの列は、すべての階層および分析ビューの一部です。階層列は、集計のすべてのレベルでデータを返すため、さまざまなレベルの集計を表すさまざまな列をナビゲートする必要がなくなります。SQL ジェネレーターは、これらの列にハードコーディングできます。

MEMBER_NAME 列は通常、階層メンバーのわかりやすい名前を返します (KEY 値とは異なる場合があります)。HIER_ORDER 列はソート順を返します。両方の列がすべての階層に存在するため、列名は階層名で修飾されます。

SELECT
    time_hier.member_name AS time
  , product_hier.member_name AS product
  , geography_hier.member_name AS geography
  , sales
FROM sales_av
    HIERARCHIES (
        time_hier
        , product_hier
        , geography_hier
        )
WHERE
    time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'DEPARTMENT'
    AND geography_hier.level_name = 'REGION'
ORDER BY
    time_hier.hier_order
    , product_hier.hier_order
    , geography_hier.hier_order;

集計レベルの変更

四半期、ブランド、および国のデータから選択するようにこのクエリを変更するには、レベルフィルターを変更します。他の変更は必要ありません。

SELECT
    time_hier.member_name AS time
  , product_hier.member_name AS product
  , geography_hier.member_name AS geography
  , sales
FROM sales_av
    HIERARCHIES (
        time_hier
        , product_hier
        , geography_hier
        )
WHERE
    time_hier.level_name = 'QUARTER'
    AND product_hier.level_name = 'BRAND'
    AND geography_hier.level_name = 'COUNTRY
ORDER BY
    time_hier.hier_order
  , product_hier.hier_order
  , geography_hier.hier_order;

期間期間クエリ

次に、クエリに計算を追加しましょう。

テーブルクエリ

テーブルクエリには 2 つのパスが必要です。1 つ目は売上データを集計し、2 つ目は前の期間を計算します。分割された外部結合は、前の期間のデータが欠落している場合を処理します。ファクトテーブルに行がない場合、クエリは前の期間に対して NULL を返す必要があります。

WITH sum_sales AS (
    -- First pass, aggregate data.
    SELECT
        t.year_name
      , p.department_name
      , g.region_name
      , SUM(sales) AS sales
    FROM
        av.time_dim      t
      , av.product_dim   p
      , av.geography_dim g
      , av.sales_fact    f
    WHERE
            t.month_name = f.month_id
        AND p.category_id = f.category_id
        AND g.state_province_id = f.state_province_id
    GROUP BY
        t.year_name
      , p.department_name
      , g.region_name
), 
  -- Get time periods.
  year_dim AS (
    SELECT DISTINCT
        year_name
    FROM
        av.time_dim
)
-- Second pass, calculate sale prior period.
SELECT
    b.year_name
  , a.department_name
  , a.region_name
  , a.sales
  , LAG(a.sales)
      OVER(PARTITION BY a.department_name, a.region_name
           ORDER BY
               b.year_name ASC
    ) AS sales_prior_period
FROM
    sum_sales a
    PARTITION BY ( a.department_name
                 , a.region_name ) RIGHT OUTER JOIN (
        SELECT DISTINCT
            b.year_name
        FROM
            av.time_dim b
    )         b ON ( a.year_name = b.year_name )
ORDER BY
    year_name
  , department_name
  , region_name;

分析ビュークエリ

分析ビュークエリは、1 つの変更と 1 つの追加 (計算されたメジャー) を受け取ります。

計算されたメジャーはクエリの実行中に分析ビューの定義を変更するため、このクエリでは FROM 句の ANALYTIC VIEW USING 形式を使用します。ADD MEASURES 句を使用して計算メジャーを追加します。それ以外の場合は、最初の分析ビュークエリと同じクエリテンプレートです。

この形式のクエリは、すべてのクエリに使用できます。ADD MEASURES には少なくとも 1 つのメジャーが必要です。ROW_COUNT メジャーは、クエリテンプレートに含めると便利なメジャーです。このメジャーは、行ごとに 1 の定数を返し、SUM を使用して集計します。クエリの行数を提供します。メジャーを SELECT リストに含める必要はありません。

SELECT
    time_hier.member_name AS time
  , product_hier.member_name AS product
  , geography_hier.member_name AS geography
  , sales
  , sales_pp
FROM ANALYTIC VIEW (
    USING sales_av HIERARCHIES (
      time_hier
    , product_hier
    , geography_hier
    )
    ADD MEASURES (
        row_count FACT (1) AGGREGATE BY SUM,
        sales_pp AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1))
    )
  )
WHERE
    time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'DEPARTMENT'
    AND geography_hier.level_name = 'REGION'
ORDER BY
    time_hier.hier_order
  , product_hier.hier_order
  , geography_hier.hier_order;

変化と変化率の測定

次の例では、さらに 2 つのメジャー、Sales Change Early Period と Sales Percent Change Early Period を追加します。

テーブルクエリ

3 番目のパスを追加して、前の期間のメジャーからの売上の変化と売上の変化率をテーブルクエリに追加します。

WITH sum_sales AS (
    -- First pass, aggregate data.
    SELECT
        t.year_name
      , p.department_name
      , g.region_name
      , SUM(sales) AS sales
    FROM
        av.time_dim      t
      , av.product_dim   p
      , av.geography_dim g
      , av.sales_fact    f
    WHERE
            t.month_name = f.month_id
        AND p.category_id = f.category_id
        AND g.state_province_id = f.state_province_id
    GROUP BY
        t.year_name
      , p.department_name
      , g.region_name
),
  -- Get time periods.
  year_dim AS (
    SELECT DISTINCT
        year_name
    FROM
        av.time_dim
),
  -- Second pass, calculate sales prior period.
  sales_prior_period AS (
    SELECT
        b.year_name
      , a.department_name
      , a.region_name
      , a.sales
      , LAG(a.sales)
          OVER(PARTITION BY a.department_name, a.region_name
               ORDER BY
                   b.year_name ASC
        ) AS sales_prior_period
    FROM
        sum_sales a
        PARTITION BY ( a.department_name
                     , a.region_name ) RIGHT OUTER JOIN (
            SELECT DISTINCT
                b.year_name
            FROM
                av.time_dim b
        )         b ON ( a.year_name = b.year_name )
)
-- Third pass, calculate the change and percent change prior period.
SELECT
    year_name
  , department_name
  , region_name
  , sales
  , sales_prior_period
  , sales - sales_prior_period                          AS sales_change_prior_period
  , ( sales - sales_prior_period ) / sales_prior_period AS sales_pct_change_prior_period
FROM
    sales_prior_period
ORDER BY
    year_name
  , department_name
  , region_name;

分析ビュークエリ

同じメジャーを分析ビュークエリに追加するには、メジャーを ADD MEASURES および選択リストに追加し、同じテンプレートを再利用します。それだけです。

SELECT
    time_hier.member_name AS time
  , product_hier.member_name AS product
  , geography_hier.member_name AS geography
  , sales
  , sales_pp
  , sales_change_prior_period
  , sales_pct_change_period_period
FROM ANALYTIC VIEW (
    USING sales_av HIERARCHIES (
      time_hier
    , product_hier
    , geography_hier
    )
    ADD MEASURES (
        row_count FACT (1) AGGREGATE BY SUM,
        sales_pp AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1)),
        sales_change_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1)),
        sales_pct_change_period_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1))
    )
  )
WHERE
    time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'DEPARTMENT'
    AND geography_hier.level_name = 'REGION'
ORDER BY
  time_hier.hier_order
  , product_hier.hier_order
  , geography_hier.hier_order;

フィルター展開

次の最後のクエリは複雑さを追加します: YEAR_NAME は CY2015 にフィルターされます。

テーブルクエリ

クエリが単に CY2105 にフィルター処理された場合、クエリは前の期間のメジャーに対して間違ったデータを返します。

WITH sum_sales AS (
    SELECT
        t.year_name
      , p.department_name
      , g.region_name
      , SUM(sales) AS sales
    FROM
        av.time_dim      t
      , av.product_dim   p
      , av.geography_dim g
      , av.sales_fact    f
    WHERE
            t.month_name = f.month_id
        AND p.category_id = f.category_id
        AND g.state_province_id = f.state_province_id
    GROUP BY
        t.year_name
      , p.department_name
      , g.region_name
), year_dim AS (
    SELECT DISTINCT
        year_name
    FROM
        av.time_dim
    WHERE
        -- Filter to CY2015
        year_name = 'CY2015'
), sales_prior_period AS (
    SELECT
        b.year_name
      , a.department_name
      , a.region_name
      , a.sales
      , LAG(a.sales)
          OVER(PARTITION BY a.department_name, a.region_name
               ORDER BY
                   b.year_name ASC
        ) AS sales_prior_period
    FROM
        sum_sales a
        PARTITION BY ( a.department_name
                     , a.region_name ) RIGHT OUTER JOIN (
            SELECT DISTINCT
                b.year_name
            FROM
                year_dim b
        )         b ON ( a.year_name = b.year_name )
)
SELECT
    year_name
  , department_name
  , region_name
  , sales
  , sales_prior_period
  , sales - sales_prior_period                          AS sales_change_prior_period
  , ( sales - sales_prior_period ) / sales_prior_period AS sales_pct_change_prior_period
FROM
    sales_prior_period
ORDER BY
    year_name
  , department_name
  , region_name;

CY2014 のデータは存在しますが、前の期間の計算では NULL が返されました。

YEAR_NAME	DEPARTMENT_NAME	REGION_NAME	SALES	SALES_PRIOR_PERIOD	SALES_CHANGE_PRIOR_PERIOD	SALES_PCT_CHANGE_PRIOR_PERIOD
CY2015	Cameras and Camcorders	Africa	53988440.36	-	-	-
CY2015	Cameras and Camcorders	Asia	237910120.18	-	-	-
CY2015	Cameras and Camcorders	Europe	36759029.27	-	-	-

YEAR_DIM クエリのフィルターを拡張して前の年を含め、外側のクエリを YEAR_NAME = 'CY2015' にフィルターすることでこれを修正します。これらのフィルターは、任意の数のメソッドを使用して追加できます (たとえば、クエリを生成する前に前年を取得するか、新しいサブクエリを追加して前年を取得するなど)。方法に関係なく、フィルターを追加する必要があります。

WITH sum_sales AS (
    SELECT
        t.year_name
      , p.department_name
      , g.region_name
      , SUM(sales) AS sales
    FROM
        av.time_dim      t
      , av.product_dim   p
      , av.geography_dim g
      , av.sales_fact    f
    WHERE
            t.month_name = f.month_id
        AND p.category_id = f.category_id
        AND g.state_province_id = f.state_province_id
    GROUP BY
        t.year_name
      , p.department_name
      , g.region_name
), year_dim AS (
    SELECT DISTINCT
        year_name
    FROM
        av.time_dim
    WHERE
        -- Filter to CY2015 and the prior year.
        year_name = 'CY2015'
        OR year_name = 'CY2014'
), sales_prior_period AS (
    SELECT
        b.year_name
      , a.department_name
      , a.region_name
      , a.sales
      , LAG(a.sales)
          OVER(PARTITION BY a.department_name, a.region_name
               ORDER BY
                   b.year_name ASC
        ) AS sales_prior_period
    FROM
        sum_sales a
        PARTITION BY ( a.department_name
                     , a.region_name ) RIGHT OUTER JOIN (
            SELECT DISTINCT
                b.year_name
            FROM
                year_dim b
        )         b ON ( a.year_name = b.year_name )
)
SELECT
    year_name
  , department_name
  , region_name
  , sales
  , sales_prior_period
  , sales - sales_prior_period                          AS sales_change_prior_period
  , ( sales - sales_prior_period ) / sales_prior_period AS sales_pct_change_prior_period
FROM
    sales_prior_period
WHERE
    -- Filter the rows returned to CY2015.
    year_name = 'CY2015'
ORDER BY
    year_name
  , department_name
  , region_name;

これで、前の期間の計算の正しい値が返されます。

YEAR_NAME	DEPARTMENT_NAME	REGION_NAME	SALES	SALES_PRIOR_PERIOD	SALES_CHANGE_PRIOR_PERIOD	SALES_PCT_CHANGE_PRIOR_PERIOD
CY2015	Cameras and Camcorders	Africa	53988440.36	51367665.95	2620774.41	.0510199239449772975328266788808612395207
CY2015	Cameras and Camcorders	Asia	237910120.18	228207652.38	9702467.8	.0425159616639144710524978408701686325108
CY2015	Cameras and Camcorders	Europe	36759029.27	34927715.07	1831314.2	.052431548881161896170953847625967821433

分析ビュークエリ

分析ビュークエリには、CY2015 へのフィルターが 1 つだけ追加されます。分析ビューは、前の期間を含むようにクエリを自動的に拡張し、正しいデータを返します。

SELECT
    year_name
  , department_name
  , region_name
  , sales
  , sales_pp
  , sales_change_prior_period
  , sales_pct_change_period_period
FROM ANALYTIC VIEW (
    USING sales_av HIERARCHIES (
      time_hier
    , product_hier
    , geography_hier
    )
    ADD MEASURES (
        sales_pp AS (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1)),
        sales_change_prior_period AS (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1)),
        sales_pct_change_period_period AS (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1))
    )
  )
WHERE
    time_hier.level_name = 'YEAR'
    AND product_hier.level_name = 'DEPARTMENT'
    AND geography_hier.level_name = 'REGION'
    AND time_hier.year_name = 'CY2015'
ORDER BY
    year_name
  , department_name
  , region_name;

このクエリは正しい結果を返します。

YEAR_NAME	DEPARTMENT_NAME	REGION_NAME	SALES	SALES_PP	SALES_CHANGE_PRIOR_PERIOD	SALES_PCT_CHANGE_PERIOD_PERIOD
CY2015	Cameras and Camcorders	Africa	53988440.36	51367665.95	2620774.41	.0510199239449772975328266788808612395207
CY2015	Cameras and Camcorders	Asia	237910120.18	228207652.38	9702467.8	.0425159616639144710524978408701686325108
CY2015	Cameras and Camcorders	Europe	36759029.27	34927715.07	1831314.2	.052431548881161896170953847625967821433

まとめ

Live SQL は、開発者が分析ビューについて学ぶのに最適な場所です。この投稿の冒頭で述べたように、次の 2 つのチュートリアルは出発点として適しています。

分析ビューのクイックスタートパート 1 - 簡単な分析ビューの DDL ステートメント
分析ビューのクイックスタートパート 2 - 開発者向けのシンプルなクエリテンプレート

Bud's Collateral Libraryでは、さまざまな情報を見つけることができます。

コーディングせずに分析ビューをすばやく作成する方法を学びたい場合は、 Oracle Live Labs の Data Studio を使用して分析ビューを開始することをお勧めします。

このブログを検索

Oracle Cloud Infrastructure Blog(翻訳)

分析ビューを使用してテーブルをアップグレード (2023/03/31)

分析ビューを使用してテーブルをアップグレード (2023/03/31)

コメント

コメントを投稿

このブログの人気の投稿

Oracle RACによるメンテナンスのためのドレインとアプリケーション・コンティニュイティの仕組み (2023/11/01)

Oracle Cloud Infrastructure Secure Desktopsを発表: デスクトップ仮想化のためのOracleのクラウドネイティブ・サービス (2023/06/28)

Oracle Cloudのデータベースをオブジェクト・ストレージにバックアップする3つの方法 (2021/12/13)