--- title: 【Sisense Data Modeling】SisenseのSQLでWindow関数は使える？ description: SQLでデータ分析を行う場合、Window関数が使えると便利です。今回はSisenseのデータデザイナーでSQLのWindow関数を使いながらデータモデリングする方法を解説します。 image: https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/07_customsql_edit-1024x440-Oct-07-2020-03-19-56-53-AM.png --- ![INSIGHT LAB_文字のみ_WHT-1](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png) ![INSIGHT LAB_文字のみ](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png?width=400&height=110&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF.png) [![お問い合わせ](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/a4a0266e-14ad-4388-b83f-681a6f35b8a7.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/a4a0266e-14ad-4388-b83f-681a6f35b8a7) - [Sisenseとは](https://service.insight-lab.co.jp/sisense) - [Sisenseナレッジ](https://knowledge.insight-lab.co.jp/sisense) - [セミナー](https://event.insight-lab.co.jp/sisense-seminar) - [トレーニング](https://service.insight-lab.co.jp/data-analytics-dojo/training_course) - [お役立ち資料](https://download.insight-lab.co.jp/sisense/download) ###### 4 分で読むことができます。 # [【Sisense Data Modeling】SisenseのSQLでWindow関数は使える？](https://knowledge.insight-lab.co.jp/sisense/data-modeling/windows) 執筆者 [Turtle](https://knowledge.insight-lab.co.jp/sisense/author/turtle) 更新日時 2020年10月16日 ###### Topics: [Data Modeling](https://knowledge.insight-lab.co.jp/sisense/tag/data-modeling) ![【SQL】SisenseのSQLでWindow関数は使える？](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/07_customsql_edit-1024x440-Oct-07-2020-03-19-56-53-AM.png) 目次 シャローム！Sato-Gです。 今日は今年最後の出勤日、明日から９連休の年末年始の休暇だ。 こんなことは社会人になって初めてのような気がする（が、あったんだと思う）。 大晦日も紅白ゆっくり見ようっと。 さて、**お気に入りの曲No.1は何ですか？** と聞かれた時に、オリコンのシングル部門1位だと答える人はいないよね。 以前、会社の女の子が「超特急」というアイドルグループのCDを30枚くらい飲み会でばらまいていたことがある。 太っ腹！と思ったんだけど、その週だけオリコンで1位になってたw おそらくファンクラブの人たちがオリコン1位にするために、こぞってこういうことをやったんだと思う。 恐るべし！ 僕は「あのグループは新幹線だっけ？」と間違え、「（笑）超・特・急！」って言われた。 ごめん、超特急。 さて、お気に入りの曲No.1は何ですか？ この解釈は色々あるとは思うが、オリコンのシングル部門のように単なる売上数で見たら「お気に入り曲No.1」とは言えないと思う。 ストリーミングで1位？これは近そうで違う。 そこには個人という要素が欠けているからだ。 データ分析の世界では、「個人」という視点をよく持ち込む。 例えば *一定期間において、それぞれの人が一番多く聞いた曲（つまり個人の1位）で、個人の1位が最も多かった曲* って言われれば、なるほどーって思わない？ こんなことを集計する時に使うのがSQLのWindow関数だ。 そして今回は、SisenseのSQLでWindow関数が使えるのか試してみたっていう話。 前置き長すぎ！ ## 1. Windows関数とは 分析の仕事をしているとしょっちゅう使うのが、 ・この顧客は何回購入しているのか ・累積購入額はいくら というような元データから計算しなきゃいけないやつ。 例えば、初回購入した商品が「シャンプー」で、その人がコンディショナーを買うまで何回かかったかとか... こういうケースではBI側で集計するのはとても困難な事態になるので、通常はデータモデリングの時点で前処理しておいたほうがベターだ。 従って、SisenseではSQLで行うことになる。 上記のような場合、SQLの中級者はWindows関数を使って求める。 むしろWindow関数が使えないと面倒なことになる。使わないでやれと言われると、考えるのもうんざりする。 Window関数は、SQL標準化の規格でいうとSQL:2003で規定されたものなので、僕のように初めてSQLを使ったのがSQL92ベースという人には「何それ？」という機能だ。 単にデータ統合するとか、そんな場合には必要ないのかもしれないが、SQLで分析をしたい人はこれを知ってるかどうかで差が出る。 「お気に入り曲No.1」を例にとって説明してみよう。 ・顧客ごとのストリーミング回数を求めてみる （ストリーミングされた回数をlisten\_countとする） **SUM(listen\_count) OVER(PARTITION BY customer\_id)** ・顧客ごとにストリーミングされた回数を元に曲にランクをつける ``` SELECT customer_id, song_id, RANK() OVER(PARTITION BY customer_id ORDER BY SUM(listen_count) DESC) AS song_rank FROM Table GROUP BY customer_id,song_id; ``` ...と、スマートに集計できるのがWindow関数のいいところ。 今回は顧客ごとに購入ごとに、それがその顧客にとって何回目の購入なのかを求めてみる。 ## 2. Sisense内でWindow関数を使ってみる 今回のデータは**[ここ](https://knowledge.insight-lab.co.jp/hubfs/SISENSE/Data/ec_sql_union_2.zip)**のファイルを使った。 **・ec\_sales.db = SQLiteデータ** C:/sqlite/db/ec\_sales.dbに配置する **・ec\_sql\_union.ecube = ErastiCubeデータ** **[【Sisense】ErastiCubeとDashboardファイルの配布とインポート](https://knowledge.insight-lab.co.jp/sisense/administration/ecdata_import)**を基にやってみてね。 データモデルは以下のとおり [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/01_datamodel-1024x677-Oct-07-2020-03-19-43-29-AM.png?width=1024&height=677&name=01_datamodel-1024x677-Oct-07-2020-03-19-43-29-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/01_datamodel-Oct-07-2020-03-19-59-44-AM.png) ここでは、注文ヘッダ、注文明細、配送の３つのテーブルを使用して、カスタムテーブル「明細」を作成し、元のテーブルを非表示設定した。 明細テーブルに購入順を入れるのがゴールだ。 ### 2.1 カスタムテーブル作成SQLで試してみる カスタムテーブル「明細」をクリックし、「編集とプレビュー」を選択する。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/02_customedit-1024x680-Oct-07-2020-03-19-47-07-AM.png?width=1024&height=680&name=02_customedit-1024x680-Oct-07-2020-03-19-47-07-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/02_customedit-Oct-07-2020-03-20-00-19-AM.png) Windows関数の場合は、SQLにこの１行を追加すればいい。 ``` DENSE_RANK() OVER(PARTITION BY 会員番号 ORDER BY 年月日,注文番号) AS 購入順, ``` 簡単に解説すると、以下のようになる。 **・DENSE\_RANK()** ランクをつける。RANK()との違いはこう。 タイムで１位が２人、2位が１人、３位が１人いたら、金メダル2人、銅メダル1、銅メダル0人になるのがRANK()、金メダル2人、銀メダル1人、銅メダル1になるのがDENSE\_RANK()。 **・OVER** Windows関数ではOVER句で集計条件を設定する **・PARTITION BY** どの項目で区切るか、GROUP BYみたいなもんだが、今回の場合は要は全体の行の中から会員番号でくくってパーティション切る（これがWindow）ということ。これで会員番号単位のかたまりができていると思えばいい。 **・ORDER BY** 会員番号単位のパーティション内のレコードの並び替え。今回は年月日と注文番号でソート指定。 これで、顧客ごとに年月日、注文番号順に連番が振られるはず。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/03_custom_sql_edit-1024x622-Oct-07-2020-03-19-57-41-AM.png?width=1024&height=622&name=03_custom_sql_edit-1024x622-Oct-07-2020-03-19-57-41-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/03_custom_sql_edit-Oct-07-2020-03-19-58-14-AM.png) Error! 使えない。 DENSE\_RANK()と入力した時点で、認識してくれなかった。。。 結果 > SisenseのSQLでは、Window関数はサポートされていない ということがわかった。 ### 2.2 インポートクエリのSQLで試してみる SisenseのSQLでWindow関数が使えないことはわかったが、インポートクエリはデータベース側のSQLを使用するから問題ないはずだ。 注文ヘッダは注文番号単位になっているから、１回の購入で１レコード発生している。 よってここでWindow関数を入れてみる。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/04_table_edit-1024x661-Oct-07-2020-03-19-58-84-AM.png?width=1024&height=661&name=04_table_edit-1024x661-Oct-07-2020-03-19-58-84-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/04_table_edit-Oct-07-2020-03-19-54-64-AM.png) 注文ヘッダテーブルをクリックしてサブメニューから「接続設定」→「テーブルの変更」を選択する。 注文ヘッダのクエリを編集する。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/05_importsql_edit-Oct-07-2020-03-19-50-79-AM.png?width=990&height=720&name=05_importsql_edit-Oct-07-2020-03-19-50-79-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/05_importsql_edit-Oct-07-2020-03-19-50-79-AM.png) ``` SELECT *, ROW_NUMBER() OVER(PARTITION BY 会員番号 ORDER BY 注文年月日,注文番号) AS 購入順 FROM `注文ヘッダ` ``` 今回はDENSE\_RANK()ではなく**ROW\_NUMBER()**でいい。なぜなら、注文番号単位でユニークなレコードになっているから。 これで連番が振れるはずなので、プレビューしてみる。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/06_importsql_preview-Oct-07-2020-03-19-44-05-AM.png?width=885&height=654&name=06_importsql_preview-Oct-07-2020-03-19-44-05-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/06_importsql_preview-Oct-07-2020-03-19-44-05-AM.png) できた！インポートクエリはデータベースのSQLなんだから当然といえば当然だけど。 あとはカスタムテーブルの「明細」のSQLを編集する。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/07_customsql_edit-1024x440-Oct-07-2020-03-19-56-53-AM.png?width=1024&height=440&name=07_customsql_edit-1024x440-Oct-07-2020-03-19-56-53-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/07_customsql_edit-Oct-07-2020-03-19-52-54-AM.png) ``` SELECT 年月日, 区分, c.[会員番号], [注文番号], CASE WHEN [区分] = '注文' AND m.[登録年月日]>=ToDatetime('2018/04/01') THEN [購入順] ELSE NULL END AS [購入順], [明細番号], [商品コード], [注文数量], [注文金額], [配送数量], [配送金額], CASE WHEN YearDiff(c.年月日,m.生年月日)<0 THEN 0 ELSE YearDiff(c.年月日,m.生年月日) END AS 購入時年齢, CASE WHEN YearDiff(c.年月日,m.生年月日)<0 THEN 0 ELSE Floor(YearDiff(c.年月日,m.生年月日)/10)*10 END AS 購入時年代 FROM ( SELECT CreateDate( ToInt(Substring(ToString([配送年月日]),1,4)), ToInt(Substring(ToString([配送年月日]),5,2)), ToInt(Substring(ToString([配送年月日]),7,2)) ) AS 年月日, '配送' AS 区分, [会員番号], [注文番号], [購入順], [明細番号], [商品コード], 0 AS 注文数量, 0 AS 注文金額, [数量] AS 配送数量, [明細金額] AS 配送金額 FROM (SELECT * FROM "配送" d LEFT JOIN "注文ヘッダ" o ON d.[注文番号] = o.[注文番号] LEFT JOIN "注文明細" od ON d.[注文番号] = od.[注文番号] AND d.[明細番号] = od.[明細番号]) a UNION ALL SELECT CreateDate( ToInt(Substring(ToString([注文年月日]),1,4)), ToInt(Substring(ToString([注文年月日]),5,2)), ToInt(Substring(ToString([注文年月日]),7,2)) ) AS 年月日, '注文' AS 区分,        [会員番号], o.[注文番号], [購入順], [明細番号], [商品コード], [数量] AS 注文数量, [明細金額] AS 注文金額, 0 AS 配送数量, 0 AS 配送金額 FROM "注文ヘッダ" o LEFT JOIN "注文明細" od ON o.[注文番号] = od.[注文番号] ) c LEFT JOIN "会員" m ON c.[会員番号] = m.[会員番号] ``` 修正箇所は以下のとおり **①配送テーブルからSELECTするカラムに\[購入順\]を追加** **②注文ヘッダからSELECTするカラムに\[購入順\]を追加** **③全体のSELECT（5行目）に購入順を追加** ``` CASE WHEN [区分] = '注文' AND m.[登録年月日]>=ToDatetime('2018/04/01') THEN [購入順] ELSE NULL END AS [購入順], ``` このCASE文は ・購入順は区分が注文の場合のみ ・注文データは2018/04/01以降であるからそれより前に会員登録した顧客は以前注文があった可能性があるため購入順を設定できない という２つの条件で購入順を設定することとし、該当しない場合はNULLとするというもの。 結果、以下のとおり、購入順が設定されている。 [![](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/Imported_Blog_Media/08_results-1024x321-Oct-07-2020-03-19-49-00-AM.png?width=1024&height=321&name=08_results-1024x321-Oct-07-2020-03-19-49-00-AM.png)](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/08_results-Oct-07-2020-03-19-45-00-AM.png) このように、インポートクエリでWindow関数を使用してあげれば、問題なく目的は達せられた。 ## 3. まとめ SQLでデータ分析を行う、またはデータモデル作成時に前処理を行う時にWindow関数は便利だ。 Window関数を使用すると、過去3ヶ月分の累計値を入れるとか、さらに応用すれば移動平均を使ってZチャートを作成することも可能だ。 これで、データ分析の幅は広がるね。 ではまた！ Sisenseを体験してみませんか？ INSIGHT LABではSisense紹介セミナーを定期開催しています。Sisenseの製品紹介や他BI製品との比較だけでなく、デモンストレーションを通してSisenseのシンプルな操作性やプレゼンテーション機能を体感いただけます。 [![詳細はこちら](https://hubspot-no-cache-na2-prod.s3.amazonaws.com/cta/default/5935783/ff8d86bf-1184-4d92-8926-6a31a20de4b0.png)](https://hubspot-cta-redirect-na2-prod.s3.amazonaws.com/cta/redirect/5935783/ff8d86bf-1184-4d92-8926-6a31a20de4b0) - [Tweet](https://twitter.com/share) ![Turtle](https://knowledge.insight-lab.co.jp/hubfs/Sunflower.jpg) #### 執筆者 [Turtle](https://knowledge.insight-lab.co.jp/sisense/author/turtle) 可視化領域を中心とした業務に従事しています。 ###### [前の投稿](https://knowledge.insight-lab.co.jp/sisense/information/mynavi2019winter) [![【セミナーレポート】マイナビニュースフォーラム 2019 Winter for データ活用 ～データが促すビジネスの再定義～](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/DSC_0150-1024x683-Oct-07-2020-03-05-02-39-AM.jpg)](https://knowledge.insight-lab.co.jp/sisense/information/mynavi2019winter) ###### [【セミナーレポート】マイナビニュースフォーラム 2019 Winter for データ活用 ～データが促すビジネスの再定義～](https://knowledge.insight-lab.co.jp/sisense/information/mynavi2019winter) ###### [次の投稿](https://knowledge.insight-lab.co.jp/sisense/formula/pastyear) [![【Formula】会計年度の対応とPastYear関数](https://knowledge.insight-lab.co.jp/hubfs/Imported_Blog_Media/07_label-1024x582-Oct-07-2020-03-16-27-43-AM.png)](https://knowledge.insight-lab.co.jp/sisense/formula/pastyear) ###### [【Sisense Formula】会計年度の対応とPastYear関数](https://knowledge.insight-lab.co.jp/sisense/formula/pastyear) RECOMMEND こちらの記事も人気です。 ###### 4 分で読むことができます。 #### [【Sisense Administaration】無料のSSL証明書"Let's Encrypt"でSSL化してみる](https://knowledge.insight-lab.co.jp/sisense/administration/ssl_lets_encrypt) ###### 2020年 10月 16日 ###### 8 分で読むことができます。 #### [【API連携】Spotify音楽データの分析①【Sisense】](https://knowledge.insight-lab.co.jp/sisense/information/spotify_music_data_1) ###### 2020年 10月 16日 ###### 2 分で読むことができます。 #### [【Sisense Widget】折れ線グラフを階段状に表示する](https://knowledge.insight-lab.co.jp/sisense/widget/step_line_chart) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Widget】Radar Chartを使ってポケモンの種族値を表示してみた](https://knowledge.insight-lab.co.jp/sisense/widget/radarchart) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Formula】CASE関数を使って条件分岐する](https://knowledge.insight-lab.co.jp/sisense/formula/case) ###### 2020年 10月 16日 ###### 3 分で読むことができます。 #### [【Sisense Widget】折れ線グラフにおける欠落値の対処法](https://knowledge.insight-lab.co.jp/sisense/widget/line-graph) ###### 2020年 10月 16日 ##### 運営会社 運営会社 - [会社概要](https://www.insight-lab.co.jp/about/company.html) - [お問い合わせ](https://contact.insight-lab.co.jp/main) ##### メニュー メニュー - Sisenseとは - [Sisenseナレッジ](https://knowledge.insight-lab.co.jp/sisense) - [セミナー](https://event.insight-lab.co.jp/sisense-seminar) - [お役立ち資料](https://download.insight-lab.co.jp/sisense/download) [![INSIGHT LAB_文字のみ_WHT-1](https://knowledge.insight-lab.co.jp/hs-fs/hubfs/INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png?width=200&height=55&name=INSIGHT%20LAB_%E6%96%87%E5%AD%97%E3%81%AE%E3%81%BF_WHT-1.png "INSIGHT LAB_文字のみ_WHT-1")](https://www.insight-lab.co.jp/) - [プライバシーポリシー](https://www.insight-lab.co.jp/privacy.html) Copyright © 2020 INSIGHT LAB, Inc.