TECH BLOG

MENU

オンプレクラウド

Dr.Sum

Dr.Sum

 

BIツールでおなじみのドリルダウン。

大分類ごとの売上 → 大分類・中分類ごとの売上 → 大分類・中分類・小分類ごとの売上 のようにどんどんと細かい情報にしてデータ分析をする方法です。

この機能を使いたいのにデータが階層ごとになっていないからうまくできない、ということはございませんか?

この記事ではカラム内の文字列の中で特定の文字列で分割して、階層項目をつくるという内容です。

便利な技術なのでぜひご覧になってください。

 

階層テーブルでドリルダウンをする

 

このドリルダウンはホームページのURLを階層にして表示させています。pagePath1 → pagePath2 → pagePath3 と詳細にドリルダウンしていますね。

このドリルダウンを実現するためにはpagePath1、pagePath2、pagePath3という項目が必要です。

 

 

ひとつの項目にフルパスが入っている場合が多い

しかしこのように階層構造となっているテーブルはなかなかなく、下図のように1つの項目にフルパスで入っていることが多いです。

 

Page URLはスラッシュ(/)で区切られています。こちらをpagePath1、pagePath2・・・のように分解していきます。

 

それではデータを加工していきましょう。

今回はSQLをつかってデータを加工します。

 

SQLではなくDS Scriptで実装したい方はこちらの記事を参照ください。

 

元となるテーブル

 

今回のサンプルとなるテーブルはこちらです。[アクセスログ]というテーブルにしています。

 

こちらは[アクセスログb]というテーブルです。階層数とpagePath1~pagePath10までの項目があります。

こちらのテーブルにデータを入れます。つまり[アクセスログ]のデータを参照・加工して[アクセスログb]に挿入します。

 

SQLをつかってデータ加工をする

今回使用するSQLはこちらです。

INSERT INTO アクセスログb
SELECT
    PageURL,
    LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) 階層数,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 1 THEN '/' || SPLIT_PART(PageURL, '/', 2)
    ELSE ''
    END pagePath1,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 2 THEN '/' || SPLIT_PART(PageURL, '/', 3)
    ELSE ''
    END pagePath2,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 3 THEN '/' || SPLIT_PART(PageURL, '/', 4)
    ELSE ''
    END pagePath3,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 4 THEN '/' || SPLIT_PART(PageURL, '/', 5)
    ELSE ''
    END pagePath4,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 5 THEN '/' || SPLIT_PART(PageURL, '/', 6)
    ELSE ''
    END pagePath5,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 6 THEN '/' || SPLIT_PART(PageURL, '/', 7)
    ELSE ''
    END pagePath6,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 7 THEN '/' || SPLIT_PART(PageURL, '/', 8)
    ELSE ''
    END pagePath7,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 8 THEN '/' || SPLIT_PART(PageURL, '/', 9)
    ELSE ''
    END pagePath8,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 9 THEN '/' || SPLIT_PART(PageURL, '/', 10)
    ELSE ''
    END pagePath9,
    CASE WHEN LENGTH(PageURL) - LENGTH(REPLACE(PageURL,'/','')) >= 10 THEN '/' || SPLIT_PART(PageURL, '/', 11)
    ELSE ''
    END pagePath10,
    pageView
FROM アクセスログ

上記のSQLを流すと、[アクセスログb]に以下の様にデータが入ります。

 

ちゃんと階層構造になりましたね。

この状態になればドリルダウンをすることが可能です。

 

BIツールのパフォーマンスを最大限に活かすには、元データを使いやすい形に加工することも重要です。

SQLを理解して、データ加工の技術を身に着けていきましょう。

 

 

 

この記事にリアクションしてみませんか?

  • 分かりやすい (2)
  • 問題が解決した (0)
あなたのお役に立ててうれしいです!
プロフィールへ

Toru Sasahara

Dr.Sumのソースコード1行目から開発に携わり、四半世紀にわたってDr.Sumと共に歩んできました。 2022年からはDr.Sumの開発責任者とプロダクトエンゲージメント担当を兼務し、ユーザーが愛着を持ち、長く使い続けられる製品の開発に情熱を注いでいます。

Related article

Related article関連記事

Pick up

Pick upおすすめ記事

特定の文字列で分割して階層構造をつくりたい!~DS Scriptで分割する~1

  • #データ加工
  • #DS Script

2023.07.17

Dr.Sum

DS Scriptで必要なテーブルだけ再構築(リビルド)をしよう

  • #DS Script

2023.07.06

Dr.Sum

「データポイント数が、しきい値をオーバーしました」というエラーの解決方法【データベースで処理をしよう】

  • #データ加工

2023.06.13

MotionBoard

Ranking

Rankingランキング

1

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

  • #データ取り込み
  • #データ加工

2021.01.19

Dr.Sum

2

【Excelの取り込み】データベースにExcelデータをインポートしよ…

  • #データ取り込み

2020.08.14

Dr.Sum

3

【実は動く】フリーのSQL実行ツールでDr.SumのSQLを書こう

  • #外部接続
  • #SQL

2023.11.13

Dr.Sum

Info

Information

ウイングアーク1stからのお知らせ