2017-08-07

Nmap を使ってシステムに不要なポートが開いていないか調べる

今回はセキュリティの世界では有名な Nmap というツールを使ってみる。このツールは、ポートスキャンや簡易な脆弱性スキャナーとして利用される。例えば不要なポートが外部からアクセスできるようになっていないかを調べることは重要なポイントだ。 Nmap は高機能なので、とても全ては紹介できないから今回は基本的なポートスキャンについて書いてみる。

注意: ポートスキャンは攻撃と見なされるので外部のサーバには実行しないこと

今回使った環境は次の通り。

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.3 LTS
Release:    16.04
Codename:   xenial
$ uname -r
4.4.0-89-generic

インストール

Ubuntu であればリポジトリからインストールできる。

$ sudo apt-get -y install nmap

TCP ポートをスキャンする

試しに何もオプションを付けずに自身のループバックアドレスに対して実行してみよう。これだけで主要な TCP のポートが開いているかどうかを一気に調べることができる。

$ sudo nmap localhost

Starting Nmap 7.01 ( https://nmap.org ) at 2017-08-07 12:32 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000045s latency).
Not shown: 999 closed ports
PORT   STATE SERVICE
22/tcp open  ssh

Nmap done: 1 IP address (1 host up) scanned in 0.04 seconds

上記のスキャンで SSH 用の TCP:22 ポートが開いていることが分かった。

もちろんローカルホストで何処のポートが開いているか調べるだけなら ss なり netstat コマンドを使うこともできる。

$ ss -tl
State      Recv-Q Send-Q Local Address:Port                 Peer Address:Port                
LISTEN     0      128        *:ssh                      *:*                    
LISTEN     0      128       :::ssh                     :::*

とはいえ、ここで Listen しているポートがインターネットなど外部からアクセスできるかどうかは、また別問題。そうした意味では、自前のシステムに外部からスキャンをかけてチェックするのには意味がある。

別のポートも開けてみる

試しに別のポートも開けて実行結果が変わることを確認してみよう。

例として Apache httpd をインストールしてみる。

$ sudo apt-get -y install apache2

ポートスキャンを実行する。

$ sudo nmap localhost

Starting Nmap 7.01 ( https://nmap.org ) at 2017-08-07 12:33 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000043s latency).
Not shown: 998 closed ports
PORT   STATE SERVICE
22/tcp open  ssh
80/tcp open  http

Nmap done: 1 IP address (1 host up) scanned in 0.04 seconds

先ほどの結果に加えて TCP:80 (HTTP) も開いたことが分かる。

スキャンのテクニックについて

Nmap には、テクニックと呼ばれるスキャンのやり方がいくつも用意されている。スキャンをするとき、どういった通信が発生しているのかを確認してみることにしよう。ちなみに、自前でテクニックを書いて追加することも可能らしい。

通信をキャプチャするために tcpdump をインストールする。

$ sudo apt-get -y install tcpdump

TCP SYN scan

管理者権限をつけて実行したときのデフォルトは TCP SYN scan と呼ばれるテクニックになっている。これは -sS オプションを付けたときと同じ。

$ sudo nmap -sS localhost

Starting Nmap 7.01 ( https://nmap.org ) at 2017-08-07 12:38 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.0000040s latency).
Not shown: 998 closed ports
PORT   STATE SERVICE
22/tcp open  ssh
80/tcp open  http

Nmap done: 1 IP address (1 host up) scanned in 1.63 seconds

通信内容を確認すると TCP の SYN フラグのついたパケットを送信している。それに対して相手が SYN/ACK を返すとポートを Open と見なしているのだろう。ただし、実際にスリーウェイハンドシェイクは完了させず RST フラグのついたパケットを送って通信を切断している。 RST は Linux Kernel が送っているものかな？

$ sudo tcpdump -i lo "port 80"
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on lo, link-type EN10MB (Ethernet), capture size 262144 bytes
12:38:39.661017 IP localhost.36923 > localhost.http: Flags [S], seq 4178950553, win 1024, options [mss 1460], length 0
12:38:39.661036 IP localhost.http > localhost.36923: Flags [S.], seq 3106868738, ack 4178950554, win 43690, options [mss 65495], length 0
12:38:39.661042 IP localhost.36923 > localhost.http: Flags [R], seq 4178950554, win 0, length 0

TCP のセッションを実際には成立させないので、おそらく秘匿性にも優れていると思われる。

TCP connect scan

管理者権限をつけずに実行したり -sT オプションを指定したときに使われるのが TCP connect scan というテクニックになる。

$ sudo nmap -sT localhost

Starting Nmap 7.01 ( https://nmap.org ) at 2017-08-07 12:39 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000041s latency).
Not shown: 998 closed ports
PORT   STATE SERVICE
22/tcp open  ssh
80/tcp open  http

Nmap done: 1 IP address (1 host up) scanned in 0.04 seconds

先ほどと同じように通信内容をキャプチャしてみよう。すると、今度は SYN > SYN/ACK > ACK というスリーウェイハンドシェイクが完了していることが分かる。つまり TCP のセッションを実際に成立させているということだ。おそらく、これはソケットのインターフェースを使って実現しているんだろう。

$ sudo tcpdump -i lo "port 80"
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on lo, link-type EN10MB (Ethernet), capture size 262144 bytes
12:39:23.661866 IP localhost.55316 > localhost.http: Flags [S], seq 55539266, win 43690, options [mss 65495,sackOK,TS val 189029 ecr 0,nop,wscale 7], length 0
12:39:23.661872 IP localhost.http > localhost.55316: Flags [S.], seq 3737402388, ack 55539267, win 43690, options [mss 65495,sackOK,TS val 189029 ecr 189029,nop,wscale 7], length 0
12:39:23.661878 IP localhost.55316 > localhost.http: Flags [.], ack 1, win 342, options [nop,nop,TS val 189029 ecr 189029], length 0

TCP のセッションを実際に成立させるので、おそらく TCP SYN scan に比べると秘匿性は劣ることだろう。

その他、ポートスキャンのテクニックについては以下のページにまとまっている。もちろんテクニックを選べば UDP のポートもスキャンもできる。

Port Scanning Techniques

Version Detection

これはテクニックとは異なるけど -sV オプションをつけると、オープンしているポートで動作しているアプリケーションについての情報も調べることができる。

$ sudo nmap -sV localhost

Starting Nmap 7.01 ( https://nmap.org ) at 2017-08-07 12:40 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.0000070s latency).
Not shown: 998 closed ports
PORT   STATE SERVICE VERSION
22/tcp open  ssh     OpenSSH 7.2p2 Ubuntu 4ubuntu2.2 (Ubuntu Linux; protocol 2.0)
80/tcp open  http    Apache httpd 2.4.18 ((Ubuntu))
Service Info: OS: Linux; CPE: cpe:/o:linux:linux_kernel

Service detection performed. Please report any incorrect results at https://nmap.org/submit/ .
Nmap done: 1 IP address (1 host up) scanned in 8.40 seconds

OS のバージョンとか、さらに詳しい情報まで得たいときは -A オプションが使える。

$ sudo nmap -A localhost

Starting Nmap 7.01 ( https://nmap.org ) at 2017-08-07 12:45 UTC
Nmap scan report for localhost (127.0.0.1)
Host is up (0.0000090s latency).
Not shown: 998 closed ports
PORT   STATE SERVICE VERSION
22/tcp open  ssh     OpenSSH 7.2p2 Ubuntu 4ubuntu2.2 (Ubuntu Linux; protocol 2.0)
| ssh-hostkey:
|   2048 e6:bb:3f:fc:95:3f:27:ab:68:45:c6:c1:90:fa:c2:11 (RSA)
|_  256 f7:cc:ba:13:46:ef:c9:e2:74:94:2f:bf:59:ef:19:c6 (ECDSA)
80/tcp open  http    Apache httpd 2.4.18 ((Ubuntu))
|_http-server-header: Apache/2.4.18 (Ubuntu)
|_http-title: Apache2 Ubuntu Default Page: It works
Device type: general purpose
Running: Linux 3.X
OS CPE: cpe:/o:linux:linux_kernel:3
OS details: Linux 3.12 - 3.19, Linux 3.8 - 3.19
Network Distance: 0 hops
Service Info: OS: Linux; CPE: cpe:/o:linux:linux_kernel

OS and Service detection performed. Please report any incorrect results at https://nmap.org/submit/ .
Nmap done: 1 IP address (1 host up) scanned in 10.89 seconds

まとめ

今回はセキュリティの世界で有名な Nmap というツールを使ってポートスキャンをする方法について書いた。 Nmap でポートスキャンすることで、システムの不要なポートが外部に公開されていないかを確認できる。繰り返しになるけどポートスキャンといえど攻撃と見なされるので外部のサーバには実行しないように。

コンピュータネットワークセキュリティ

作者: 八木毅,村山純一,秋山満昭
出版社/メーカー: コロナ社
発売日: 2015/03/17
メディア: 単行本
この商品を含むブログを見る

スマートPythonプログラミング: Pythonのより良い書き方を学ぶ

作者: もみじあめ
発売日: 2016/03/12
メディア: Kindle版
この商品を含むブログ (1件) を見る

2017-08-06

sqlmap を使って SQL インジェクションの脆弱性を検証する

Security SQL SQLite macOS Python

sqlmap は SQL インジェクションに特化したオープンソースのペネトレーションテストツール。これを使うと Web アプリケーションの特定のパラメータに SQL インジェクションの脆弱性があるか否かを確認しやすい。

注意: 外部のサーバに使うことは攻撃となるので絶対にしないように。

今回使った環境は次の通り。

$ sw_vers    
ProductName:    Mac OS X
ProductVersion: 10.12.6
BuildVersion:   16G29
$ sqlmap --version
1.1.8#stable
$ python --version
Python 2.7.10

インストール

sqlmap は Homebrew でインストールできる。

$ brew install sqlmap

これで sqlmap コマンドが使えるようになる。

$ sqlmap --version
1.1.8#stable

ちなみに sqlmap は Python で書かれているけど Python 3 では動かない。

$ python --version
Python 3.6.2
$ sqlmap   
[CRITICAL] incompatible Python version detected ('3.6.2'). For successfully running sqlmap you'll have to use version 2.6.x or 2.7.x (visit 'http://www.python.org/download/')

作者の方針的に Python 3 対応をする予定はないようだ。

Port code to Python 3 · Issue #93 · sqlmapproject/sqlmap · GitHub

ターゲット

今回はツールの検証のために意図的に SQL インジェクションの脆弱性を仕込んだ Web アプリケーションを用意してみた。これは Python の Flask で書いてある。データベースには組み込みで使える SQLite3 を使った。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3

from flask import Flask
from flask import request
from flask import abort
from flask import jsonify

app = Flask(__name__)


@app.route('/users')
def get():
    # クエリパラメータから 'name' の値を取得する
    name = request.args.get('name')
    # ひとまずクエリパラメータは必須ということで
    if name is None:
        abort(400)

    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    try:
        # XXX: クエリの組み立てにプレースホルダーを使っておらず SQL インジェクションが発生しうる
        query = 'SELECT * FROM users where name like \'{name}\''.format(name=name)  # noqa
        cursor.execute(query)

        # 結果を取得して表示する
        result = cursor.fetchall()
    finally:
        # 後片付け
        cursor.close()
        conn.close()

    # レスポンスの組み立て
    response_body = {
        'users': [
            {
                'name': name,
                'age': age,
            }
            for name, age in result
        ]
    }
    response = jsonify(response_body)
    response.status_code = 200
    response.headers['Content-Type'] = 'application/json'
    return response


def initialize_db():
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    try:
        # 既存の内容があれば消す
        cursor.execute('DROP TABLE IF EXISTS users')
        # テーブルのスキーマを作る
        cursor.execute('CREATE TABLE users (name TEXT, age INTEGER)')
        # ダミーデータを追加する
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Alice', 20))
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Bob', 25))
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Carol', 30))
        conn.commit()
    finally:
        # 後片付け
        cursor.close()
        conn.close()


def main():
    # データベースを初期化する
    initialize_db()
    # アプリケーションを起動する
    app.run()


if __name__ == '__main__':
    main()

上記のアプリケーションは SQL の実行にプレースホルダを使っていないため SQL インジェクションの脆弱性がある。

使い方

動かすには Flask が必要なのでインストールする。

$ pip install flask

あとは先ほどのファイルを適当な名前で保存して実行するだけ。

$ python queryparam.py 
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

上手くいけば次のように URL のクエリパラメータで既存のユーザを検索できるアプリケーションが動く。ただ、このパラメータ name には SQL インジェクションの脆弱性がある。

$ curl 'http://localhost:5000/users?name=Alice'
{
  "users": [
    {
      "age": 20,
      "name": "Alice"
    }
  ]
}

sqlmap でペネトレーションテストする

それでは sqlmap を使って先ほどのアプリケーションをペネトレーションテストしてみよう。

クエリパラメータであれば -u オプションを使って URL をそのまま指定するだけでテストできる。 --dump オプションは脆弱性があったときにデータベースの内容をダンプすることを意味する。

$ sqlmap -u 'http://localhost:5000/users?name=Alice' --dump

いくつか質問に答えていって、脆弱性が実際に見つかると次のような出力が得られる。

$ sqlmap -u 'http://localhost:5000/users?name=Alice' --dump
...(snip)...
GET parameter 'name' is vulnerable. Do you want to keep testing the others (if any)? [y/N]

さらに進めると users テーブルの内容が表示される。

$ sqlmap -u 'http://localhost:5000/users?name=Alice' --dump
...(snip)...
GET parameter 'name' is vulnerable. Do you want to keep testing the others (if any)? [y/N]
...(snip)...
Database: SQLite_masterdb
Table: users
[3 entries]
+-----+-------+
| age | name  |
+-----+-------+
| 20  | Alice |
| 25  | Bob   |
| 30  | Carol |
+-----+-------+

仮にこれが実際の攻撃ならデータベースの内容を盗み出されていたことになる。

フォームタグ内のパラメータをテストする

さっきはクエリパラメータだったけど、次はフォームタグで送信される内容を想定してみよう。これはようするに Content-Type が application/x-www-form-urlencoded されるもの。

サンプルコードは次の通り。先ほどと同じようにユーザを検索するというもの。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3

from flask import Flask
from flask import request
from flask import abort
from flask import jsonify

app = Flask(__name__)


@app.route('/users', methods=['POST'])
def post():
    # フォームパラメータから 'name' の値を取得する
    name = request.form.get('name')
    # ひとまずクエリパラメータは必須ということで
    if name is None:
        abort(400)

    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    try:
        # XXX: クエリの組み立てにプレースホルダーを使っておらず SQL インジェクションが発生しうる
        query = 'SELECT * FROM users where name like \'{name}\''.format(name=name)  # noqa
        cursor.execute(query)

        # 結果を取得して表示する
        result = cursor.fetchall()
    finally:
        # 後片付け
        cursor.close()
        conn.close()

    # レスポンスの組み立て
    response_body = {
        'users': [
            {
                'name': name,
                'age': age,
            }
            for name, age in result
        ]
    }
    response = jsonify(response_body)
    response.status_code = 200
    response.headers['Content-Type'] = 'application/json'
    return response


def initialize_db():
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    try:
        # 既存の内容があれば消す
        cursor.execute('DROP TABLE IF EXISTS users')
        # テーブルのスキーマを作る
        cursor.execute('CREATE TABLE users (name TEXT, age INTEGER)')
        # ダミーデータを追加する
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Alice', 20))
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Bob', 25))
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Carol', 30))
        conn.commit()
    finally:
        # 後片付け
        cursor.close()
        conn.close()


def main():
    # データベースを初期化する
    initialize_db()
    # アプリケーションを起動する
    app.run()


if __name__ == '__main__':
    main()

先ほどと同じように適当な名前をつけて実行する。

$ python contentbody.py 
 * Running on http://127.0.0.1:5000/ (Press CTRL+C to quit)

curl コマンドからは次のようにして呼び出せる。

$ curl -X POST -d 'name=Alice' http://localhost:5000/users
{
  "users": [
    {
      "age": 20,
      "name": "Alice"
    }
  ]
}

sqlmap コマンドで上記のパラメータをテストするときは --data オプションを使う。

$ sqlmap -u 'http://localhost:5000/users' --data 'name=Alice' --dump

実行すると次のようにデータベースの内容がダンプして得られる。

$ sqlmap -u 'http://localhost:5000/users' --data 'name=Alice' --dump
...(snip)...
Database: SQLite_masterdb
Table: users
[3 entries]
+-----+-------+
| age | name  |
+-----+-------+
| 20  | Alice |
| 25  | Bob   |
| 30  | Carol |
+-----+-------+

JSON を使った WebAPI をテストする

次は Content-Type が application/json な WebAPI をテストするときについて。

サンプルコードは次の通り。 WebAPI なのに POST でユーザを検索するっていう、ちょっと不自然なものになっちゃってる。これは INSERT とか UPDATE を使った SQL インジェクションは難しくて sqlmap がそれに対応していないのが理由。

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import sqlite3

from flask import Flask
from flask import request
from flask import abort
from flask import jsonify

app = Flask(__name__)


@app.route('/users', methods=['POST'])
def post():
    # クエリパラメータから 'name' の値を取得する
    name = request.json.get('name')
    # ひとまずクエリパラメータは必須ということで
    if name is None:
        abort(400)

    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    try:
        # XXX: クエリの組み立てにプレースホルダーを使っておらず SQL インジェクションが発生しうる
        query = 'SELECT * FROM users where name like \'{name}\''.format(name=name)  # noqa
        cursor.execute(query)

        # 結果を取得して表示する
        result = cursor.fetchall()
    finally:
        # 後片付け
        cursor.close()
        conn.close()

    # レスポンスの組み立て
    response_body = {
        'users': [
            {
                'name': name,
                'age': age,
            }
            for name, age in result
        ]
    }
    response = jsonify(response_body)
    response.status_code = 200
    response.headers['Content-Type'] = 'application/json'
    return response


def initialize_db():
    conn = sqlite3.connect('users.db')
    cursor = conn.cursor()

    try:
        # 既存の内容があれば消す
        cursor.execute('DROP TABLE IF EXISTS users')
        # テーブルのスキーマを作る
        cursor.execute('CREATE TABLE users (name TEXT, age INTEGER)')
        # ダミーデータを追加する
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Alice', 20))
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Bob', 25))
        cursor.execute('INSERT INTO users VALUES (?, ?)', ('Carol', 30))
        conn.commit()
    finally:
        # 後片付け
        cursor.close()
        conn.close()


def main():
    # データベースを初期化する
    initialize_db()
    # アプリケーションを起動する
    app.run()


if __name__ == '__main__':
    main()

上記のアプリケーションは curl コマンドからだと次のように呼び出せる。

$ curl -X POST -H "Content-Type: application/json" -d '{"name": "Alice"}' http://localhost:5000/users
{
  "users": [
    {
      "age": 20,
      "name": "Alice"
    }
  ]
}

上記を sqlmap でテストするときは、まず Content-Body に JSON を含んだリクエスト内容をファイルとして用意しておく。

$ cat << 'EOF' > json-request.txt
POST /users HTTP/1.1
Host: localhost:5000
Content-Type: application/json

{"name": "Alice"}
EOF

sqlmap コマンドでは、上記のファイルを -r オプションで指定する。

$ sqlmap -r json-request.txt --dump

すると JSON 使ってる？って sqlmap が確認してくるので Y する。

$ sqlmap -r json-request.txt --dump

JSON data found in POST data. Do you want to process it? [Y/n/q]

あとはこれまでと同じようにやっていくとデータベースの内容が出力された。

...(snip)...
Database: SQLite_masterdb
Table: users
[3 entries]
+-----+-------+
| age | name  |
+-----+-------+
| 20  | Alice |
| 25  | Bob   |
| 30  | Carol |
+-----+-------+

まとめ

今回は sqlmap を使った SQL インジェクションのペネトレーションテストをするやり方について書いた。正直なところ SQL インジェクションくらい初歩的な脆弱性ならコードを確認した方が早いのではとも思ってしまう。しかし、脆弱性の有無を調べるにはコードを確認するような静的解析以外にも、今回やったような動的解析もまた一つのやり方といえるだろう。繰り返しになるけど sqlmap を外部のサーバに使うのは攻撃になるので絶対にやらないこと。

コンピュータネットワークセキュリティ

作者: 八木毅,村山純一,秋山満昭
出版社/メーカー: コロナ社
発売日: 2015/03/17
メディア: 単行本
この商品を含むブログを見る

スマートPythonプログラミング: Pythonのより良い書き方を学ぶ

作者: もみじあめ
発売日: 2016/03/12
メディア: Kindle版
この商品を含むブログ (1件) を見る

2017-06-30

SQL: CASE 句を使って縦持ちのデータを横持ちに変換する

macOS MariaDB SQL

使った環境は次の通り。

$ sw_vers
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

サンプルのテーブルとレコードを用意する

まずはサンプルとして次のようなテーブルを用意する。これは色々なアイテムの購入履歴を表している。購入された時刻とアイテムのコード、そして数量が記録される。

> CREATE TABLE orders(
    ->   datetime DATETIME,
    ->   code VARCHAR(255) NOT NULL,
    ->   amount INTEGER NOT NULL
    -> );
Query OK, 0 rows affected (0.02 sec)

上記のテーブルにいくつかレコードを追加しておく。二分間におにぎりとパンとパスタがいくつか購入されている。

> INSERT INTO
    ->   orders
    -> VALUES
    ->   ('2017-06-30 20:00:00', 'Onigiri', 1),
    ->   ('2017-06-30 20:00:00', 'Pan', 2),
    ->   ('2017-06-30 20:00:00', 'Pasta', 1),
    ->   ('2017-06-30 20:00:10', 'Onigiri', 3),
    ->   ('2017-06-30 20:00:20', 'Pan', 2),
    ->   ('2017-06-30 20:00:30', 'Pasta', 1),
    ->   ('2017-06-30 20:01:00', 'Onigiri', 2),
    ->   ('2017-06-30 20:01:00', 'Pan', 2),
    ->   ('2017-06-30 20:01:00', 'Pasta', 2);
Query OK, 9 rows affected (0.00 sec)
Records: 9  Duplicates: 0  Warnings: 0

格納されたデータはこんな感じ。

> SELECT * FROM orders;
+---------------------+---------+--------+
| datetime            | code    | amount |
+---------------------+---------+--------+
| 2017-06-30 20:00:00 | Onigiri |      1 |
| 2017-06-30 20:00:00 | Pan     |      2 |
| 2017-06-30 20:00:00 | Pasta   |      1 |
| 2017-06-30 20:00:10 | Onigiri |      3 |
| 2017-06-30 20:00:20 | Pan     |      2 |
| 2017-06-30 20:00:30 | Pasta   |      1 |
| 2017-06-30 20:01:00 | Onigiri |      2 |
| 2017-06-30 20:01:00 | Pan     |      2 |
| 2017-06-30 20:01:00 | Pasta   |      2 |
+---------------------+---------+--------+
9 rows in set (0.00 sec)

特定の条件に一致するアイテムを確認する

このとき、特定のコードを持ったアイテムがどれだけ購入されたかを確認してみよう。一般的には WHERE 句を使ってこうするはず。

> SELECT
    ->   *
    -> FROM orders
    -> WHERE code like 'Onigiri';
+---------------------+---------+--------+
| datetime            | code    | amount |
+---------------------+---------+--------+
| 2017-06-30 20:00:00 | Onigiri |      1 |
| 2017-06-30 20:00:10 | Onigiri |      3 |
| 2017-06-30 20:01:00 | Onigiri |      2 |
+---------------------+---------+--------+
3 rows in set (0.00 sec)

ただ、上記だと同時に一つのアイテムしか確認できない。おにぎりとパンとパスタの売れ方を同時に時系列で見たい、という場合はどうすれば良いだろうか？

CASE 句で縦持ちのデータを横持ちに変換する

そういったときは CASE 句を使って新しいカラムを追加する。一般的には条件分岐に用いる CASE 句だけど、次のようにすると特定の条件に一致する内容で新しいカラムを作れる。これによって縦持ちのデータを横持ちにできる。

> SELECT
    ->   datetime,
    ->   CASE WHEN code like 'Onigiri' THEN amount ELSE 0 END AS 'Onigiri',
    ->   CASE WHEN code like 'Pan' THEN amount ELSE 0 END AS 'Pan',
    ->   CASE WHEN code like 'Pasta' THEN amount ELSE 0 END AS 'Pasta'
    -> FROM orders;
+---------------------+---------+-----+-------+
| datetime            | Onigiri | Pan | Pasta |
+---------------------+---------+-----+-------+
| 2017-06-30 20:00:00 |       1 |   0 |     0 |
| 2017-06-30 20:00:00 |       0 |   2 |     0 |
| 2017-06-30 20:00:00 |       0 |   0 |     1 |
| 2017-06-30 20:00:10 |       3 |   0 |     0 |
| 2017-06-30 20:00:20 |       0 |   2 |     0 |
| 2017-06-30 20:00:30 |       0 |   0 |     1 |
| 2017-06-30 20:01:00 |       2 |   0 |     0 |
| 2017-06-30 20:01:00 |       0 |   2 |     0 |
| 2017-06-30 20:01:00 |       0 |   0 |     2 |
+---------------------+---------+-----+-------+
9 rows in set (0.00 sec)

こうすれば、おにぎりとパンとパスタの売れ行きを同時に見られて嬉しい。

ちなみに条件に一致しないときのデフォルト値として ELSE 0 を指定しているのは地味に重要なポイント。これを入れておかないと、どれにも一致しないときに NULL が返ってきてしまう。

> SELECT
    ->   datetime,
    ->   CASE WHEN code like 'Onigiri' THEN amount END AS 'Onigiri',
    ->   CASE WHEN code like 'Pan' THEN amount END AS 'Pan',
    ->   CASE WHEN code like 'Pasta' THEN amount END AS 'Pasta'
    -> FROM orders;
+---------------------+---------+------+-------+
| datetime            | Onigiri | Pan  | Pasta |
+---------------------+---------+------+-------+
| 2017-06-30 20:00:00 |       1 | NULL |  NULL |
| 2017-06-30 20:00:00 |    NULL |    2 |  NULL |
| 2017-06-30 20:00:00 |    NULL | NULL |     1 |
| 2017-06-30 20:00:10 |       3 | NULL |  NULL |
| 2017-06-30 20:00:20 |    NULL |    2 |  NULL |
| 2017-06-30 20:00:30 |    NULL | NULL |     1 |
| 2017-06-30 20:01:00 |       2 | NULL |  NULL |
| 2017-06-30 20:01:00 |    NULL |    2 |  NULL |
| 2017-06-30 20:01:00 |    NULL | NULL |     2 |
+---------------------+---------+------+-------+
9 rows in set (0.00 sec)

こうなると NULL の伝搬を考えなきゃいけなくなるので、ここからの集計処理が大変になる。

購入時刻でまとめる

ちなみにさっきの集計だと同じ時刻のものがそれぞれのアイテムごとに複数行で表示されてしまっていた。こんなときは購入時刻で GROUP BY した上で、それぞれの CASE 句を SUM() 関数で合計すれば良い。

> SELECT
    ->   datetime,
    ->   SUM(CASE WHEN code like 'Onigiri' THEN amount ELSE 0 END) AS 'Onigiri',
    ->   SUM(CASE WHEN code like 'Pan' THEN amount ELSE 0 END) AS 'Pan',
    ->   SUM(CASE WHEN code like 'Pasta' THEN amount ELSE 0 END) AS 'Pasta'
    -> FROM orders
    -> GROUP BY datetime;
+---------------------+---------+------+-------+
| datetime            | Onigiri | Pan  | Pasta |
+---------------------+---------+------+-------+
| 2017-06-30 20:00:00 | 1       | 2    | 1     |
| 2017-06-30 20:00:10 | 3       | 0    | 0     |
| 2017-06-30 20:00:20 | 0       | 2    | 0     |
| 2017-06-30 20:00:30 | 0       | 0    | 1     |
| 2017-06-30 20:01:00 | 2       | 2    | 2     |
+---------------------+---------+------+-------+
5 rows in set (0.00 sec)

同じ時刻が一つのレコードで確認できるようになって、さらに見やすくなった。

一定の時刻の範囲で集計する

ただ、先ほどの表示を見ると、まだ問題が残っていることに気づくはず。全く同じ時刻に購入されたものは一つのレコードで表現されているけど、秒数が異なるものは違うレコードになってしまっている。

そんなときは集計したい時刻の範囲を切り出して、それを GROUP BY でまとめる。次のクエリでは時刻を一分単位までしか見ないようにして集計している。これなら秒数が異なっても同じレコードとしてまとめることができる。

> SELECT
    ->   DATE_FORMAT(datetime, '%Y-%m-%d %H:%i') AS datetime_minute,
    ->   SUM(CASE WHEN code like 'Onigiri' THEN amount ELSE 0 END) AS 'Onigiri',
    ->   SUM(CASE WHEN code like 'Pan' THEN amount ELSE 0 END) AS 'Pan',
    ->   SUM(CASE WHEN code like 'Pasta' THEN amount ELSE 0 END) AS 'Pasta'
    -> FROM orders
    -> GROUP BY datetime_minute;
+------------------+---------+------+-------+
| datetime_minute  | Onigiri | Pan  | Pasta |
+------------------+---------+------+-------+
| 2017-06-30 20:00 | 4       | 4    | 2     |
| 2017-06-30 20:01 | 2       | 2    | 2     |
+------------------+---------+------+-------+
2 rows in set (0.00 sec)

ちなみに、時刻の扱いは RDBMS ごとにかなり異なるので、次の DATE_FORMAT() 関数を使った SQL は別の実装では動かないと思う。

まとめ

縦に色んなデータが入ったテーブルを横に並べるには CASE 句を使うと良い。

ビッグデータ分析・活用のためのSQLレシピ

作者: 加嵜長門,田宮直人,丸山弘詩
出版社/メーカー: マイナビ出版
発売日: 2017/03/27
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

2017-06-29

SQL: COALESCE() 関数で NULL を別の値に置き換える

macOS MariaDB SQL

RDB のスキーマ設計において、なるべくなら Nullable なカラムは無い方が良いけど、そうもいかないときがある。そんなときは COALESCE() 関数を使うと NULL になっているフィールドを別の値に置換できる。これは、特定の値を NULL に置換できる NULLIF() 関数と対になるものかな。

blog.amedama.jp

動作確認に使った環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

下準備

下準備として、例えば次のようなテーブルを作っておく。何らかの商品の購入履歴のテーブルで、元々の金額といくらディスカウントがあったかを記録する。

> CREATE TABLE purchases (
    ->   id INTEGER,
    ->   amount INTEGER,
    ->   discount INTEGER,
    ->   PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

サンプルとなるレコードをいくつか追加しておく。ここでディスカウントの金額が NULL になっているものがあるところに注意する。ディスカウントがなかったときは 0 ではなく NULL が入るということ。

> INSERT INTO
    ->   purchases
    -> VALUES
    ->   (1, 1000, NULL),
    ->   (2, 2000, 500),
    ->   (3, 3000, 1000);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

格納されるデータはこんな感じ。

> SELECT * FROM purchases;
+----+--------+----------+
| id | amount | discount |
+----+--------+----------+
|  1 |   1000 |     NULL |
|  2 |   2000 |      500 |
|  3 |   3000 |     1000 |
+----+--------+----------+
3 rows in set (0.00 sec)

NULL を置換せずに計算した場合

何も考えずにディスカウントが適用された金額を計算してみよう。これは、ただ単に金額からディスカウントを引くだけ。

> SELECT
    ->   id,
    ->   amount - discount AS discounted_amount
    -> FROM purchases;
+----+-------------------+
| id | discounted_amount |
+----+-------------------+
|  1 |              NULL |
|  2 |              1500 |
|  3 |              2000 |
+----+-------------------+
3 rows in set (0.00 sec)

するとディスカウントの値が NULL だったところの内容が NULL になってしまった。これは NULL の伝搬と呼ばれるもので、計算内容に NULL が混入していると結果が全て NULL になってしまうというもの。

COALESCE() 関数で NULL を置換する

この NULL の伝搬を防ぐには NULL になっている値を何らかの値に置換してやれば良い。今回の例であれば NULL を 0 に置き換えてしまえば大丈夫なはず。そんなとき使えるのが今回紹介する COALESCE() 関数で、これは第一引数が NULL だったときに第二引数の値に置換できる。

先ほどのクエリに COALESCE() 関数を使ってみよう。

> SELECT
    ->   id,
    ->   amount - COALESCE(discount, 0) AS discounted_amount
    -> FROM purchases;
+----+-------------------+
| id | discounted_amount |
+----+-------------------+
|  1 |              1000 |
|  2 |              1500 |
|  3 |              2000 |
+----+-------------------+
3 rows in set (0.00 sec)

今度は結果が NULL にならず、ちゃんとした値になった。

まとめ

スキーマ定義にはなるべく Nullable なカラムはない方が良い
- とはいえ、そうもいかないときがある
計算内容に NULL が含まれると、それが伝搬してしまう
それを防ぐには COALESCE() 関数を使うと良い

ビッグデータ分析・活用のためのSQLレシピ

作者:加嵜長門,田宮直人
発売日: 2017/03/27
メディア: 単行本（ソフトカバー）

Linuxで動かしながら学ぶTCP/IPネットワーク入門

作者:もみじあめ
発売日: 2020/02/29
メディア: Kindle版

2017-06-29

SQL: NULLIF() 関数を使ってゼロ除算を防ぐ

Postgresql MariaDB Apache Hive SQL

RDBMS によってはゼロ除算をするとエラーになってしまうものがある。

例えば PostgreSQL とかがそう。

$ psql --version
psql (PostgreSQL) 9.6.3

次のようにゼロ除算をするとエラーになって結果が得られない。これは SQL を使って演算するときに問題となることがある。

# SELECT 1 / 0;
ERROR:  division by zero

そんなときは NULLIF() 関数を使って意図的に値を NULL にしてしまうことが考えられる。数値と NULL の計算であれば NULL の伝搬によって結果もまた NULL となるのでエラーにはならない。

次のクエリでは NULLIF() 関数の引数が両方とも 0 になっているけど、本来なら最初の変数が何らかのカラムの名前になるはず。結果が空っぽに見えるのは PostgreSQL はデフォルトで NULL を空白として表示するからみたい。

# SELECT 1 / NULLIF(0, 0);
 ?column?
----------

(1 row)

これはようするに第一引数の中身が第二引数と一致したら、それを NULL に置き換えるということ。

# SELECT NULLIF(0, 0);
 nullif
--------

(1 row)

# SELECT NULLIF(1, 0);
 nullif
--------
      1
(1 row)

ゼロ除算がエラーにならない RDBMS もある

ゼロ除算してもエラーにならない実装もあるようだ。例えば MariaDB とか。

$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

次のようにゼロ除算を発生させると結果が自動的に NULL になる。

> SELECT 1 / 0;
+-------+
| 1 / 0 |
+-------+
| NULL  |
+-------+
1 row in set, 1 warning (0.00 sec)

あとは厳密には RDBMS じゃないけど SQL の処理系という意味では Hive とか。

$ hive --version
Hive 1.2.2
Subversion git://vgumashta.local/Users/vgumashta/Documents/workspace/hive-git -r 395368fc6478c7e2a1e84a5a2a8aac45e4399a9e
Compiled by vgumashta on Sun Apr 2 13:12:26 PDT 2017
From source with checksum bd47834e727562aab36c8282f8161030

こちらもゼロ除算を発生させると結果が NULL になった。

> SELECT 1 / 0;
OK
NULL
Time taken: 0.094 seconds, Fetched: 1 row(s)

まとめ

ゼロ除算するとエラーになる RDBMS がある
そのときは NULLIF() 関数を使って結果を NULL にするとエラーを防げる
そもそもゼロ除算がエラーにならず NULL になる処理系もある

ビッグデータ分析・活用のためのSQLレシピ

作者: 加嵜長門,田宮直人,丸山弘詩
出版社/メーカー: マイナビ出版
発売日: 2017/03/27
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

ちなみに反対に NULL を特定の値に置換するのには COALESCE() 関数を使う。

blog.amedama.jp

2017-06-28

SQL: UNION を使ってテーブルを縦に連結する

MariaDB macOS SQL

今回は SQL の UNION を使ってみる。

試した環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

典型的な使い方

例えばスキーマ設計において、パフォーマンスの向上などを目的として同じカラムを持ったテーブルが複数あったりすることがある。特定の月のデータを入れるテーブルとか、あるいは毎月新しいテーブルを作っていくとか、そんな感じ。

次のサンプルでは同じカラムを持ったテーブルが日付で分かれている。

> CREATE TABLE purchases_20170627 (
    ->   purchase_id INTEGER,
    ->   user_id VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.04 sec)

> CREATE TABLE purchases_20170628 (
    ->   purchase_id INTEGER,
    ->   user_id VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.02 sec)

上記のテーブルに、それぞれサンプルのレコードを追加しておこう。

> INSERT INTO
    ->   purchases_20170627
    -> VALUES
    ->   (1, 'Alice'),
    ->   (2, 'Bob'),
    ->   (3, 'Carol');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

> INSERT INTO
    ->   purchases_20170628
    -> VALUES
    ->   (4, 'Alice'),
    ->   (5, 'Bob');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

さて、上記のようなテーブルを一つのクエリで処理したいとする。そんなときこそ今回扱う UNION の出番といえる。 UNION では複数の SELECT 文を繋げて扱うことができる。もちろん同じカラムという前提はあるけど。

次のクエリでは先ほどの二つのテーブルの内容をまとめて取得している。

> SELECT
    ->   '20170627' AS date,
    ->   purchase_id,
    ->   user_id
    -> FROM purchases_20170627
    -> UNION ALL
    -> SELECT
    ->   '20170628' AS date,
    ->   purchase_id,
    ->   user_id
    -> FROM purchases_20170628;
+----------+-------------+---------+
| date     | purchase_id | user_id |
+----------+-------------+---------+
| 20170627 |           1 | Alice   |
| 20170627 |           2 | Bob     |
| 20170627 |           3 | Carol   |
| 20170628 |           4 | Alice   |
| 20170628 |           5 | Bob     |
+----------+-------------+---------+
5 rows in set (0.00 sec)

同じカラムを持った内容をどんどん縦に繋げていくイメージ。

擬似的なテーブルを作るのに使う

続いては UNION を使って擬似的なテーブルを作るやり方について。例えば SELECT 文を UNION で繋いでいけば CREATE TABLE ... しなくても擬似的なテーブルが用意できる。

次のクエリでは WITH と共に使うことで擬似的に作ったテーブルの内容を表示している。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION ALL
    ->   SELECT 2 AS device_type, 'L2SW' AS device_name
    ->   UNION ALL
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   *
    -> FROM device_types;
+-------------+-------------+
| device_type | device_name |
+-------------+-------------+
|           1 | LB          |
|           2 | L2SW        |
|           3 | L3SW        |
+-------------+-------------+
3 rows in set (0.00 sec)

ちなみに、これまでは UNION ALL を使っていたけど、重複する内容を省きたいときは UNION DISTINC を使う。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION DISTINCT
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION DISTINCT
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   *
    -> FROM device_types;
+-------------+-------------+
| device_type | device_name |
+-------------+-------------+
|           1 | LB          |
|           3 | L3SW        |
+-------------+-------------+
2 rows in set (0.00 sec)

同じ内容を持ったレコードが削除されている。

UNION で作った擬似的なテーブルも CREATE TABLE ... で作ったテーブルと同じように扱うことができる。例えば、次のように擬似的なテーブルと関連するようなテーブルを作っておく。

> CREATE TABLE devices(
    ->   id INTEGER,
    ->   type INTEGER
    -> );
Query OK, 0 rows affected (0.02 sec)

> INSERT INTO
    ->   devices
    -> VALUES
    ->   (1, 1),
    ->   (2, 1),
    ->   (3, 2),
    ->   (4, 3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

そして両者を JOIN してみよう。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION ALL
    ->   SELECT 2 AS device_type, 'L2SW' AS device_name
    ->   UNION ALL
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   devices.id,
    ->   device_types.device_name AS device_type
    -> FROM devices
    -> JOIN device_types
    -> ON devices.type = device_types.device_type;
+------+-------------+
| id   | device_type |
+------+-------------+
|    1 | LB          |
|    2 | LB          |
|    3 | L2SW        |
|    4 | L3SW        |
+------+-------------+
4 rows in set (0.01 sec)

ちゃんと上手く JOIN できた。

まあ上記くらいの内容なら擬似的なテーブルを作るよりも CASE を使った方が楽ちんかな。

> SELECT
    ->   devices.id,
    ->   CASE
    ->     WHEN devices.type = 1 THEN 'LB'
    ->     WHEN devices.type = 2 THEN 'L2SW'
    ->     WHEN devices.type = 3 THEN 'L3SW'
    ->   END AS device_type
    -> FROM devices;
+------+-------------+
| id   | device_type |
+------+-------------+
|    1 | LB          |
|    2 | LB          |
|    3 | L2SW        |
|    4 | L3SW        |
+------+-------------+
4 rows in set (0.00 sec)

再帰クエリの中で使う

UNION のもう一つの重要な使い方として再帰クエリ (WITH RECURSIVE) の中での用法がある。これは再帰的にクエリを実行して得られた内容を結合するのに UNION を使うということ。再帰クエリについては以下に詳しく書いた。

blog.amedama.jp

ちなみに MySQL 5.7 には再帰クエリが実装されていないけど MariaDB 10.2 なら使える。

まとめ

今回は UNION を使う場面について見てみた。

ビッグデータ分析・活用のためのSQLレシピ

作者: 加嵜長門,田宮直人,丸山弘詩
出版社/メーカー: マイナビ出版
発売日: 2017/03/27
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

2017-06-27

SQL:2003 のウィンドウ関数を MariaDB 10.2 で試す

macOS MariaDB SQL

今回は SQL:2003 の規格で追加されたウィンドウ関数を使ってみる。この機能を使うとカラムをグループ化して集約関数を使うのが楽になる。

使った環境は次の通り。

$ sw_vers                    
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1

MariaDB では 10.2.0 からウィンドウ関数が使えるようになっている。

Window Functions - MariaDB Knowledge Base

インストールは Homebrew を使えばさくっといける。

$ brew install mariadb
$ brew services start mariadb

ちなみに MySQL 5.7 ではウィンドウ関数がまだ実装されていない。

サンプル用のデータを用意する

まずは MariaDB のシェルに入る。

$ mysql -u root

サンプル用のデータを入れるためのデータベースを作成する。

> DROP DATABASE IF EXISTS sample;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
> CREATE DATABASE sample;
Query OK, 1 row affected (0.00 sec)
> USE sample
Database changed

サンプル用のテーブルを作る。今回はボードゲームのレーティングを示すテーブルっぽいものにした。テーブル名はプレイヤーの方が良かったかな。

> DROP TABLE IF EXISTS users;
Query OK, 0 rows affected, 1 warning (0.00 sec)
> CREATE TABLE users (
    ->   name VARCHAR(255),
    ->   category VARCHAR(255),
    ->   rate INTEGER
    -> );
Query OK, 0 rows affected (0.02 sec)

なんか適当にレコードを追加しておく。ゲームのカテゴリごとにプレイヤーとレーティングが格納されている。

> INSERT INTO
    ->   users
    -> VALUES
    ->   ('Alice', 'Shogi', 2000),
    ->   ('Bob', 'Igo', 1800),
    ->   ('Carol', 'Shogi', 1800),
    ->   ('Daniel', 'Igo', 1600);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

なんかこんな感じのデータができた。

> SELECT
    ->   *
    -> FROM users;
+--------+----------+------+
| name   | category | rate |
+--------+----------+------+
| Alice  | Shogi    | 2000 |
| Bob    | Igo      | 1800 |
| Carol  | Shogi    | 1800 |
| Daniel | Igo      | 1600 |
+--------+----------+------+
4 rows in set (0.00 sec)

GROUP BY と共に集約関数を使う

まずはオーソドックスな GROUP BY と一緒に集約関数を使うやり方を見てみる。

カテゴリごとのレーティングを AVG() 関数を使って計算してみよう。

> SELECT
    ->   category,
    ->   AVG(rate) AS avg
    -> FROM users
    -> GROUP BY category;
+----------+-----------+
| category | avg       |
+----------+-----------+
| Igo      | 1700.0000 |
| Shogi    | 1900.0000 |
+----------+-----------+
2 rows in set (0.00 sec)

まあ、見慣れた感じだ。

ちなみに GROUP BY を使うと基本的には SELECT で表示できるカラムが限定される。具体的には GROUP BY で指定したものか、あるいは集約関数を適用したものしか使うことができない。次の例では GROUP BY で指定しているわけでも集約関数を適用したわけでもない name カラムを SELECT に指定している。これはエラーにはなっていないものの、平均しているのに特定のレコードの内容 (name) が表示されていて、あまり意味をなしていない。

> SELECT
    ->   name,
    ->   category,
    ->   AVG(rate) AS avg
    -> FROM users
    -> GROUP BY category;
+-------+----------+-----------+
| name  | category | avg       |
+-------+----------+-----------+
| Bob   | Igo      | 1700.0000 |
| Alice | Shogi    | 1900.0000 |
+-------+----------+-----------+
2 rows in set (0.00 sec)

ウィンドウ関数を使う

それでは続いてウィンドウ関数を使うパターンを紹介する。ウィンドウ関数では GROUP BY の代わりに集約関数の後に OVER() をつける。

次の例では先ほどと同じようにカテゴリごとのレーティングの平均を計算している。異なるのは GROUP BY の代わりにウィンドウ関数を使っているところ。 OVER() には PARTITION BY でグループ化するカラムを指定する。

> SELECT
    ->   name,
    ->   category,
    ->   AVG(rate) OVER(PARTITION BY category) AS avg
    -> FROM users;
+--------+----------+-----------+
| name   | category | avg       |
+--------+----------+-----------+
| Alice  | Shogi    | 1900.0000 |
| Bob    | Igo      | 1700.0000 |
| Carol  | Shogi    | 1900.0000 |
| Daniel | Igo      | 1700.0000 |
+--------+----------+-----------+
4 rows in set (0.01 sec)

ウィンドウ関数を使う場合は全てのレコードに対して集約関数の結果が表示されている。また、もちろん AVG() だけでなく SUM() や COUNT() といった集約関数でも同じように使える。

RANK/ROW_NUMBER/DENSE_RANK

また、ウィンドウ関数では新たに使える集約関数が増えている。例えば RANK() を使うと特定のカラムの内容に応じて順位をつけたりできる。次の例では rate の内容に応じてソートした上で、それに順位をつけている。

> SELECT
    ->   name,
    ->   rate,
    ->   RANK() OVER(ORDER BY rate DESC) AS rank
    -> FROM users;
+--------+------+------+
| name   | rate | rank |
+--------+------+------+
| Alice  | 2000 |    1 |
| Bob    | 1800 |    2 |
| Carol  | 1800 |    2 |
| Daniel | 1600 |    4 |
+--------+------+------+
4 rows in set (0.00 sec)

ちなみに、同じ値のときでも異なる番号を振りたいときは ROW_NUMBER() を使う。

> SELECT
    ->   name,
    ->   rate,
    ->   ROW_NUMBER() OVER(ORDER BY rate DESC) AS rank
    -> FROM users;
+--------+------+------+
| name   | rate | rank |
+--------+------+------+
| Alice  | 2000 |    1 |
| Bob    | 1800 |    2 |
| Carol  | 1800 |    3 |
| Daniel | 1600 |    4 |
+--------+------+------+
4 rows in set (0.00 sec)

同じ順位があったとき、その分の順位を飛ばさないようにするには DENSE_RANK() を使う。

> SELECT
    ->   name,
    ->   rate,
    ->   DENSE_RANK() OVER(ORDER BY rate DESC) AS rank
    -> FROM users;
+--------+------+------+
| name   | rate | rank |
+--------+------+------+
| Alice  | 2000 |    1 |
| Bob    | 1800 |    2 |
| Carol  | 1800 |    2 |
| Daniel | 1600 |    3 |
+--------+------+------+
4 rows in set (0.01 sec)

PARTITION BY と ORDER BY を組み合わせて使う

先ほどの例だとカテゴリの違いを無視して順位付けをしたので、ちょっと不自然だったかもしれない。グループ化した上で順位をつけたいときは、次のように PARTITION BY と ORDER BY を組み合わせて使う。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   RANK() OVER(PARTITION BY category ORDER BY rate DESC) AS rank
    -> FROM users;
+--------+----------+------+------+
| name   | category | rate | rank |
+--------+----------+------+------+
| Alice  | Shogi    | 2000 |    1 |
| Bob    | Igo      | 1800 |    1 |
| Carol  | Shogi    | 1800 |    2 |
| Daniel | Igo      | 1600 |    2 |
+--------+----------+------+------+
4 rows in set (0.00 sec)

LAG/LEAD

それ以外にも LAG() や LEAD() を使うと現在のレコードの前後のレコードが取得できる。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   -- 一つ前のレコードを得る
    ->   LAG(name) OVER(ORDER BY rate DESC) AS prev1,
    ->   -- 二つ前のレコードを得る
    ->   LAG(name, 2) OVER(ORDER BY rate DESC) AS prev2,
    ->   -- 一つ先のレコードを得る
    ->   LEAD(name) OVER(ORDER BY rate DESC) AS next1,
    ->   -- 二つ先のレコードを得る
    ->   LEAD(name, 2) OVER(ORDER BY rate DESC) AS next2
    -> FROM users;
+--------+----------+------+-------+-------+--------+--------+
| name   | category | rate | prev1 | prev2 | next1  | next2  |
+--------+----------+------+-------+-------+--------+--------+
| Alice  | Shogi    | 2000 | NULL  | NULL  | Bob    | Carol  |
| Bob    | Igo      | 1800 | Alice | NULL  | Carol  | Daniel |
| Carol  | Shogi    | 1800 | Bob   | Alice | Daniel | NULL   |
| Daniel | Igo      | 1600 | Carol | Bob   | NULL   | NULL   |
+--------+----------+------+-------+-------+--------+--------+
4 rows in set (0.00 sec)

ROWS … で集計する範囲を指定する

また、集約関数の対象となる範囲を ROWS ... で指定できる。

例えば、次のようにすると現在のレコードの前後一つずつを SUM() で合計できる。 PRECEDING の前に指定するのが前いくつ分を対象にするか。そして FOLLOWING の前に指定するのが後ろいくつ分を対象にするかになる。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   -- 前後一つずつのレートを合計する
    ->   SUM(rate) OVER(
    ->     ORDER BY rate DESC
    ->     ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ->   ) AS sum_neighbor
    -> FROM users;
+--------+----------+------+--------------+
| name   | category | rate | sum_neighbor |
+--------+----------+------+--------------+
| Alice  | Shogi    | 2000 | 3800         |
| Bob    | Igo      | 1800 | 5600         |
| Carol  | Shogi    | 1800 | 5200         |
| Daniel | Igo      | 1600 | 3400         |
+--------+----------+------+--------------+
4 rows in set (0.00 sec)

変数となっているところを UNBOUNDED にすると無制限になる。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   SUM(rate) OVER(
    ->     ORDER BY rate DESC
    ->     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ->   ) AS sum_neighbor
    -> FROM users;
+--------+----------+------+--------------+
| name   | category | rate | sum_neighbor |
+--------+----------+------+--------------+
| Alice  | Shogi    | 2000 | 7200         |
| Bob    | Igo      | 1800 | 7200         |
| Carol  | Shogi    | 1800 | 7200         |
| Daniel | Igo      | 1600 | 7200         |
+--------+----------+------+--------------+
4 rows in set (0.00 sec)

上手く使えば、こんな感じで累積の値を簡単に集計できる。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   SUM(rate) OVER(
    ->     ORDER BY rate DESC
    ->     ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING
    ->   ) AS cumulative_sum
    -> FROM users;
+--------+----------+------+----------------+
| name   | category | rate | cumulative_sum |
+--------+----------+------+----------------+
| Alice  | Shogi    | 2000 | 2000           |
| Bob    | Igo      | 1800 | 3800           |
| Carol  | Shogi    | 1800 | 5600           |
| Daniel | Igo      | 1600 | 7200           |
+--------+----------+------+----------------+
4 rows in set (0.00 sec)

この ROWS ... の指定をしないと挙動が変わってしまう集約関数もあるようなので気をつけたい。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   SUM(rate) OVER(
    ->     ORDER BY rate DESC
    ->   ) AS sum_neighbor
    -> FROM users;
+--------+----------+------+--------------+
| name   | category | rate | sum_neighbor |
+--------+----------+------+--------------+
| Alice  | Shogi    | 2000 | 2000         |
| Bob    | Igo      | 1800 | 5600         |
| Carol  | Shogi    | 1800 | 5600         |
| Daniel | Igo      | 1600 | 7200         |
+--------+----------+------+--------------+
4 rows in set (0.00 sec)

FIRST_VALUE/LAST_VALUE

例えば最初に登場する要素と最後に登場する要素を取得する FIRST_VALUE() と LAST_VALUE() も ROWS ... の指定がいるようだ。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   -- レートでソートして一番最初にくる内容を表示する
    ->   FIRST_VALUE(name) OVER(
    ->     ORDER BY rate DESC
    ->     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ->   ) AS first,
    ->   -- レートでソートして一番最後にくる内容を表示する
    ->   LAST_VALUE(name) OVER(
    ->     ORDER BY rate DESC
    ->     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ->   ) AS last
    -> FROM users;
+--------+----------+------+-------+--------+
| name   | category | rate | first | last   |
+--------+----------+------+-------+--------+
| Alice  | Shogi    | 2000 | Alice | Daniel |
| Bob    | Igo      | 1800 | Alice | Daniel |
| Carol  | Shogi    | 1800 | Alice | Daniel |
| Daniel | Igo      | 1600 | Alice | Daniel |
+--------+----------+------+-------+--------+
4 rows in set (0.00 sec)

ROWS ... をコメントアウトすると、次のように意図した挙動にならない。

> SELECT
    ->   name,
    ->   category,
    ->   rate,
    ->   -- ROWS ... を入れないと上手く動作しない場合がある
    ->   FIRST_VALUE(name) OVER(
    ->     ORDER BY rate DESC
    ->     -- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ->   ) AS first,
    ->   -- ROWS ... を入れないと上手く動作しない場合がある
    ->   LAST_VALUE(name) OVER(
    ->     ORDER BY rate DESC
    ->     -- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ->   ) AS last
    -> FROM users;
+--------+----------+------+-------+--------+
| name   | category | rate | first | last   |
+--------+----------+------+-------+--------+
| Alice  | Shogi    | 2000 | Alice | Alice  |
| Bob    | Igo      | 1800 | Alice | Carol  |
| Carol  | Shogi    | 1800 | Alice | Carol  |
| Daniel | Igo      | 1600 | Alice | Daniel |
+--------+----------+------+-------+--------+
4 rows in set (0.00 sec)

まとめ

SQL:2003 の規格で追加されたウィンドウ関数を使うとグループ化して集約関数を適用するのが楽にできる。ただし、集約関数の適用範囲を決める ROWS ... の挙動には注意が必要となる。

ビッグデータ分析・活用のためのSQLレシピ

作者: 加嵜長門,田宮直人,丸山弘詩
出版社/メーカー: マイナビ出版
発売日: 2017/03/27
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

インストール

TCP ポートをスキャンする

別のポートも開けてみる

スキャンのテクニックについて

TCP SYN scan

TCP connect scan

Version Detection

まとめ

インストール

ターゲット

使い方

sqlmap でペネトレーションテストする

フォームタグ内のパラメータをテストする

JSON を使った WebAPI をテストする

まとめ

サンプルのテーブルとレコードを用意する

特定の条件に一致するアイテムを確認する

CASE 句で縦持ちのデータを横持ちに変換する

購入時刻でまとめる

一定の時刻の範囲で集計する

まとめ

もくじ

下準備

NULL を置換せずに計算した場合

COALESCE() 関数で NULL を置換する

まとめ

ゼロ除算がエラーにならない RDBMS もある

まとめ

典型的な使い方

擬似的なテーブルを作るのに使う

再帰クエリの中で使う

まとめ

サンプル用のデータを用意する

GROUP BY と共に集約関数を使う

ウィンドウ関数を使う

RANK/ROW_NUMBER/DENSE_RANK

PARTITION BY と ORDER BY を組み合わせて使う

LAG/LEAD

ROWS … で集計する範囲を指定する

FIRST_VALUE/LAST_VALUE

まとめ