みなさんは[一時表]をご存知でしょうか?　Dr.SumのSQLは一部のクエリーで内部的に一時表を作成します。一時表にデータを出力しておくことでそのデータを流用しさらに処理を行う、これをDr.Sumでは内部で自動的に実装してくれます。

大変便利な機能なのですが、一時表をつかうSQLを組み合わせてしまうと、それだけレスポンスに影響が出てしまいます。

そこで今回はどのSQLで一時表が作成されるかを紹介します。

運用しているSQLの中で一時表が多用されてしまっている処理がないかチェックしてみましょう。

集計、集約

Sum関数やGroup byを使用したときに、一時表が作成されます。ただし同じような表現でもDISTINCTのときは一時表は作成されません。

主にDatalizer、MotionBoardのクロス集計で内部的に実行するSQLです。集約するとデータ件数が減るため、一時表を作成する時間は軽微です。

SELECT SUM(売上額) FROM 受注ビュー;
SELECT 地域,都道府県 FROM 受注ビュー GROUP BY 地域,都道府県;

※ DISTINCTは一時表を作成しない
SELECT DISTINCT 地域,都道府県 FROM 受注ビュー;

SQLログを確認すると、一時表が出力されているか確認できます。以下のような出力例となります。

CREATE_TEMP_TABLE:_AGG_20230929165639_0_1628

FROM句のサブクエリー

FROM句でテーブルを指定せず、サブクエリーを呼んだ場合も一時表が作成されます。

SELECT * FROM (SELECT * FROM M商品 WHERE 大分類コード='01') 商品マスタ;

SQLログでは以下のように出力されます。

CREATE_TEMP_TABLE:_w商品マスタ_20230929165203_1_11896

WITH句

クエリーの先頭にWITH句を記述することで、共通テーブル式を利用できます。

下記は共通テーブル式「サブ」を生成し、その共通テーブル式から2021年のデータを月の昇順で抽出します。

WITH サブ(年, 月, 総数量,総売上) AS (SELECT 年, 月, SUM(数量) , SUM(売上額) FROM 受注ビュー GROUP BY 年,月)
SELECT * FROM サブ WHERE 年 = 2008 ORDER BY 月;

SQLログでは以下のように出力されます。

CREATE_TEMP_TABLE:_wサブ_20230929165951_1_7316

テーブル関数

テーブル関数は、FROM句にのみ記述できる関数です、主にデータの自動生成などに用います。

SELECT * FROM GENERATE_DATE('2020/06/01', '2020/06/05');

SQLログでは以下のように出力されます。

CREATE_TEMP_TABLE:_gen20230929170119_1_21044_

PIVOT・UNPIVOT

データを縦持ち⇔横持ちに変換する関数です。

SELECT * FROM 半期別売上データ PIVOT(SUM(売上額) AS 総売上, COUNT(*)AS 件数 FOR 半期 IN('上期' AS 上期, '下期' AS 下期)) AS クロス表;
SELECT * FROM 月別平均気温 UNPIVOT(平均気温 FOR 地域 IN (札幌, 東京, 大阪)) AS 平均気温テーブル WHERE 年月 IN (201901,201902,201903,201904);

SQLログでは以下のように出力されます。

CREATE_TEMP_TABLE:_PV_20230929170233_1_8352

CREATE_TEMP_TABLE:_UPV_20230929170331_1_11008

またPIVOT・UNPIVOTの使い方はこちらの記事が参考になります。

Excelのデータを加工したい。～変換(横持ち⇔縦持ち)について～

https://navi.wingarc.com/product/drsum/9326

エクセルなどでデータを管理している場合に、例えば店舗ごとの売り上げが年度が増えるごとに横に伸びている場合や、アンケート項目が増えるごとに横に伸びることがよくあります。このようなデータ構造を横持ちと言います。今回はPIVOT関数とUNPIVOT関数を使用して横持ちのデータを縦持ちのデータに変換していきます。なお、Dr.Sumではマウス操作のみで簡単に横持ち⇔縦持ちを変換する機能を持っています。詳細は下記の記事をご覧ください。 データを縦持ちにするメリットデータベース設計をする際の「横持ち」と「縦持ち」について...

WingArc TECH BLOG

2021.01.19

 

特定のビュー

ビュー自体では一時表を作成しませんが、下記の特定の構文を使用したSQLの参照がビューである場合、一時表を作成します。

• ORDER BY
• DISTINCT
• TOP
• CONSTRAINT ON TOP
• 集合演算子
• ウインドウ関数を指定したビュー

CREATE VIEW 売上TOP100 AS SELECT TOP 100 * FROM 受注ビュー ORDER BY 売上額 DESC;
SELECT * FROM 売上TOP100;

SQLログでは以下のように出力されます。

CREATE_TEMP_TABLE:_売上TOP100_20230929171105_8_17504

集合演算

テーブル同士を縦に結合するUNIONでも一時表が作成されます。

SELECT * FROM T売上実績_2022
UNION ALL
SELECT * FROM T売上実績_2023;

SQLログでは以下のように出力されます。

CREATE_TEMP_TABLE:_U20230929170536_4_19728

右外部結合・完全外部結合

BIの中では内部結合や←外部結合をよく使用しますが、右外部や完全外部結合をするときは、一時表が作成されます。

SELECT * FROM 受注データ RIGHT OUTER JOIN 顧客マスタ ON 受注データ.顧客コード = 顧客マスタ.顧客コード;

SQLログでは以下のように出力されます。

CREATE_TEMP_TABLE:ITOJ_DELTA_14170450_2824

さいごに

いかがでしょうか、俯瞰して全体を眺めてみると、クエリーの中でも内部的に複数の処理をしていそうな処理に一時表が使われていることがわかりますね。

どの処理が一時表をつかっているかを判定し、使うケースを切り分けることで、効率の良い運用ができるようになりますね。