Go to Qiita Advent Calendar 2024 Top
LoginSignup
527
527

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@w-sato-istin株式会社 情報戦略テクノロジー

そろそろSQLのウィンドウ関数を理解したい - 連載1/3話

Posted at

はじめに

データ分析とデータ品質改善に従事してきた筆者が、SQLを用いた分析の基本である「ウィンドウ関数」の使い方とデータ品質の調査改善を行う手法をまとめてみようと思います。

こちらの記事は、SQLの知識向上と振り返りを主題としているので、ABC分析、バスケット分析、RFM分析などの「データ分析の手法」について説明している記事ではありません。(反響やコメントによって別投稿するかもしれません)

背景

SQLはエンジニアの大多数が利用しており、多くの方はWebサービス開発などでデータの登録画面や検索画面を作る際にSQLを利用したり、またはシステムの運用保守で障害の原因調査のためにSQLを利用して原因を特定すると思います。そのため、テーブル結合・サブクエリ・集計関数といったSQL構文は理解されている人が多いと思いますが、分析関数を理解して使っている人となると、ぐっと減ると思います。

私は以前、社内の管理職やエンジニアを対象にデータマネジメントのセミナー講師を行なっていました。その経験の中で、次のようなことがありました。

私「SQLの利用経験がある人、手を挙げてください」　⇒　9割方、手が挙がった
私「外部結合やサブクエリを理解して利用できる人」　⇒　7割方、手が挙がったまま
私「分析関数を使ってレコードに行番号を付けれる人」　⇒　全員の手が下がる。。

ここで強く言いたいことは、

「分析関数を使える人は極端に少ないため、知っているだけで他の人と差別化を図れ、あなたの強みになる」

ということです。

対象者

この記事は、以下のような方を対象としています。

  • SQLの基本的な構文やテーブル結合といった基礎的な内容は理解しているが、「OLAP関数」や「ウィンドウ関数」という分析関数の用語を聞いてもピンとこない方
  • 分析関数をどういう場面で利用するかイメージできず、詳しく触ってこなかったという方

分析業務に関わる人も、そうでない人も、SQLの理解を深め自分の強みとしたい！と考えている方にオススメの内容となっております。

ウィンドウ関数を使うと、例えばこんなことができる

早速ですが、次のような情報を知りたい時にSQLでささっと書けますか？

知りたいこと：　部門ごとの売上比率(割合)を知りたい
image.png

上記の3レコードから、以下の画像のように「全体の売り上げ」と「売上比率」を求めるというSQLを考えてみましょう。

image.png

シンプルですが、いかがですか？（シンキングタイム 1分）

こちらの記事を読み終わった時には、上記内容を求められるSQLが簡単に書けるようになります。

繰り返しになりますが、分析関数はデータ分析業務に携わる方だけでなく、システム開発や保守を行うエンジニアにも有用な知識です。是非とも理解を深め、皆様の業務に活かしてください。

環境

本記事の内容は、下記RDBMSで動作確認しています。記事中の実行結果画面などの図はPostgreSQLの結果を使います。

  • PostgreSQL 11.5
  • Oracle xe 18.4
  • SQL Server 2017 Express
  • MySQL 8.0.17

※MySQLは2018年4月リリースの8.0からウィンドウ関数が搭載されました。つい最近の出来事なので、8.0より前のバージョンを使用しているシステムがまだ多いので使えるか要確認。

他のレコードの情報を使って値を算出するウィンドウ関数

SQLで分析を行う関数は次のように色々な呼び方があります。

  • ウィンドウ関数
  • OLAP(OnLine Analytical Processing)関数
  • 分析関数

RDBMSによって呼称が違っていたり、関数の種類によって使い分けているため呼び方が複数ありますが、まず最初のうちは全て同じものと考えて良いです。本記事では、分析を行う関数はSQL標準でも定義されている呼称でもある「ウィンドウ関数」と記載します。

ウィンドウ関数とは、通常のSQLでは苦手な「他のレコードの情報」を使って値を求めることができる関数です。大きく分けると以下の二種類存在します。

  • sumやcountといった見慣れた集計関数をウィンドウ関数として利用するもの
  • 分析専用のウィンドウ関数を利用するもの

今回の記事では前者について説明し、後者については次回の記事で説明を記載する予定です。

集計関数をウィンドウ関数として利用するとは？

通常のsumやcountなどの集計関数を利用すると結果が1行に集約されるため、select句に書ける列は、group byで指定した列と集計関数だけという決まりがありますよね。

ウィンドウ関数を使うと、こういった制限を受けることなく、sumやcountの結果を扱えるのです。つまり、次の実行結果イメージのように、全体の売上合計値を各レコードのカラムに追加できるのです！

通常のselect結果
select 部門, 
       売上金額 
from 売上テーブル;

image.png

通常のsum結果
select sum(売上金額) as 全体の売上 
from 売上テーブル;

image.png

ウィンドウ関数のsum結果
select 部門, 
       売上金額, 
       sum(売上金額) over() as 全体の売上 
from 売上テーブル;

image.png

それでは、このようなウィンドウ関数の構文はどのようなものになるのでしょうか？(というか上でしれっと書いておりますが)

「いつも見慣れているSQLの構文とは全然違くて、結局あまり使うことは無いんだろうな」と思っている皆様、ご安心ください！

書き方はselect句の中で、通常の集計関数の後ろに over()を追加するだけ！

sum(売上金額) over()

いかがでしょう、ウィンドウ関数がものすごく身近に感じられたのではないでしょうか？

もちろんこれはウィンドウ関数の構文の中で一番シンプルなものですが、over()を追加するだけでこういう動きになるということは結構大きな発見ですよね。

それでは、冒頭の売上比率を求める問題をウィンドウ関数で書いてみましょう。
(売上比率は 個別の売上/全体の売上 です)

売上比率を求めるウィンドウ関数
select 部門
      ,売上金額
      ,sum(売上金額) over() as "全体の売上"
      ,売上金額 / sum(売上金額) over() as "売上比率(割合)"
from 売上テーブル

image.png

補足コメント

売上比率を求める際、列に別名として "全体の売上" を使いたいですがSQLの処理順の関係でまだ使えません。実行順の詳細や使うためのテクニックは次記事で執筆予定。

ウィンドウ関数を知らない状態だと、全体の売上を取得しそれと結合して、、というアプローチを考えたり、スカラーサブクエリを使っていけるかな、、などというアプローチになるかと思います。

もちろんこのようなアプローチでも結果を取得することは出来ますが、力技で書いたSQLは他の人が見たときに「なぜこういうことをしているのか」というのが理解しづらい状況になりがちです。いわば、関数が用意されているのにそれを使わずロジックを1から記述しているという状況です。

ウィンドウ関数の動きを知らなかったこれまでと、動きを知ったこれからとで、出来ることの幅が広がるのは良いことですよね。

よく使うウィンドウ関数の一覧

この一覧は今回検証のために利用した4つのRDBMSに共通して搭載されています。

関数名 説明
AVG 全体の平均、グループごとの平均、累積平均を求める
COUNT 全体の件数、グループごとの件数、累積件数を求める
MAX 全体の最大、グループごとの最大を求める
MIN 全体の最小、グループごとの最小を求める
SUM 全体の合計、グループごとの合計、累積合計を求める
ROW_NUMBER ソートして順位付けする(同じ値の場合も重複なし)1,2,3,4,5
RANK ソートして順位付けする(同じ値の場合に重複あり、値は重複分飛ぶ)1,2,2,4,5
DENSE_RANK ソートして順位付けする(同じ値の場合に重複あり、値は飛ばない)1,2,2,3,4
NTILE レコードを等分割した値を付与する
FIRST_VALUE 最初の行の値を利用できる
LAST_VALUE 最後の行の値を利用できる
LAG 前の行の値を利用できる
LEAD 次の行の値を利用できる

この中で最もイメージしやすい、集計関数のウィンドウ化のSQLと実行結果をまとめて書きます。

count over

select 部門, 
       売上金額, 
       count(売上金額) over() as "全体の件数" 
from 売上テーブル;

image.png

max over

select 部門, 
       売上金額, 
       max(売上金額) over() as "全体の最大値" 
from 売上テーブル;

image.png

min over

select 部門, 
       売上金額, 
       min(売上金額) over() as "全体の最小値" 
from 売上テーブル;

image.png

sum over

select 部門, 
       売上金額, 
       sum(売上金額) over() as "全体の合計値" 
from 売上テーブル;

image.png

avg over

select 部門, 
       売上金額, 
       avg(売上金額) over() as "全体の平均値" 
from 売上テーブル;

image.png

最後に

まずはウィンドウ関数の中で最も基本的な構文の説明と動作をまとめてみました。

次回は、ウィンドウ関数のメインの概念である、ウィンドウ・パーティションといった用語の説明と分析用の関数として初めて登場してきたrank()やlag()といった関数について、それぞれどういったことが出来るか、どのように記述するかといったことをまとめてみたいと思います。

今回はウィンドウ関数の基本機能の紹介だったため、「これくらいのことならjoinとサブクエリでも簡単に出来るよ」と思われた方もいるかもしれません。ですが、ウィンドウ関数で出来ることはまだまだ沢山あります！

次回以降ご紹介したいと思いますが参考までにこういったことも出来るというものを載せておきます。(ウィンドウ関数を使わないでSQLをパズル的に考えるのも個人的には好きなので、こうすれば出来るといったことを考えてみるのも楽しいですよね)

参考１）部門ごとの売上合計を求める

select 部門, 
       売上金額, 
       sum(売上金額) over(partition by 部門) as 部門ごとの売上合計
from 売上テーブル;

image.png

参考２）部門ごとの売上合計の推移を求める

select 部門, 
       売上金額, 
       売上日, 
       sum(売上金額) over(partition by 部門 order by 売上日) as 部門ごとの売上合計の推移
from 売上テーブル;

image.png

参考３）売上ランキングを求める

select rank() over(order by 売上金額 desc) as 売上ランキング, 
       部門, 
       売上金額, 
       売上日 
from 売上テーブル 
order by 売上ランキング;

image.png

それではまた次回にて。

527
527
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
527
527

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?