2018-12-11

MySQL8.0でウィンドウ関数が使えるようになったので試してみる

今更感はありますが、MySQL8.0から待望のWindow関数が実装されました。
SQLを利用してレポートの作成等を行う際には非常に便利なのですが、再度復習しておこうと思います。
なお、Shinjuku.LTのウェブサイトでは一切使われていません。

よく使われるSQLとは文法が結構違うため、まず最初に例を見てみようと思います。
なお、データはMySQLの公式サイトからダウンロードできる、 world database というデータを利用します。

select 
-- 言語毎にパーティショニングして、人工順にソートしてランクを出力
    rank() over(partition by cl.language order by population desc) ranking,
    c.code,
    c.name,
    cl.language,
-- 人工 * 割合。パーティション内で1行ごとに足しわせる
    sum(round(population * percentage / 100)) over(partition by cl.language order by population desc) summary
from
    country c
        inner join
    countrylanguage cl on c.code = cl.countrycode
limit 20;

+---------+------+------------------+-----------+----------+
| ranking | code | name             | language  | summary  |
+---------+------+------------------+-----------+----------+
|       1 | GEO  | Georgia          | Abhyasi   |    84456 |
|       1 | UGA  | Uganda           | Acholi    |   958232 |
|       1 | BEN  | Benin            | Adja      |   676767 |
|       1 | ERI  | Eritrea          | Afar      |   165550 |
|       2 | DJI  | Djibouti         | Afar      |   387574 |
|       1 | ZAF  | South Africa     | Afrikaans |  5773911 |
|       2 | NAM  | Namibia          | Afrikaans |  5937881 |
|       1 | PER  | Peru             | Aimará    |   590226 |
|       2 | CHL  | Chile            | Aimará    |   666281 |
|       3 | BOL  | Bolivia          | Aimará    |   932809 |
|       1 | JPN  | Japan            | Ainu      |        0 |
|       1 | BEN  | Benin            | Aizo      |   530439 |
|       1 | GHA  | Ghana            | Akan      | 10591088 |
|       2 | CIV  | Côte d’Ivoire    | Akan      | 15026888 |
|       1 | ITA  | Italy            | Albaniana |   115360 |
|       2 | YUG  | Yugoslavia       | Albaniana |  1870960 |
|       3 | ALB  | Albania          | Albaniana |  5200735 |
|       4 | MKD  | Macedonia        | Albaniana |  5664231 |
|       1 | AGO  | Angola           | Ambo      |   309072 |
|       1 | ETH  | Ethiopia         | Amhara    | 18769500 |
+---------+------+------------------+-----------+----------+

コードの2行目の部分がWindow関数の部分です。

特徴的な記法なのが、

over(partition by cl.language order by population desc)

かと思います。

over句について

Window関数は、over句の中で partition by ~~ 、 order by ~~ のどちらかもしくは両方を組み合わせて使用します。
上記の例は、languageでパーティションを区切り、パーティション内でpopulationの順にソートする、といった内容になっています。

出力結果を見ると、partition by は group by と似ているような印象をもたれるかもしれませんが、
違いとして、group by は集約して集約した結果を1つのレコードにまとめます。
それに対して、 partiotion by は各行をパーティショニングはしますが、集約はしません。
そのため、入力と出力の行数は同じです。

order by については普通のSQLの order by と同じです。
MySQLにおいては order by は必須ではありませんが、Oracle等では order byがない場合構文エラーとなります。

Window集約関数

上記の例にもありますが、通常の集約関数(avg(), sum() 等)でも over句と組み合わせて使うことができます。
基本的に通常の集約関数と変わりませんが、集約処理は行われません。

よく使われるWindow関数

row_number()

最初に row_number() です。
名前の通り、単純に行番号を振る関数です。
単純に行番号として利用するもよし、ランキングのような使い方もできます！
MySQLでは、従来は行番号を振るのが面倒でしたが、row_number()により改善されました！
また、こちらにもあるように、
従来の方法では結果が保証されるわけではありません。
そのため、MySQL8.0以降ではこちらを積極的に利用したほうが良さそうです。

select row_number() over (order by GNP desc) row_num, name, GNP from country limit 20;
+---------+--------------------+------------+
| row_num | name               | GNP        |
+---------+--------------------+------------+
|       1 | United States      | 8510700.00 |
|       2 | Japan              | 3787042.00 |
|       3 | Germany            | 2133367.00 |
|       4 | France             | 1424285.00 |
|       5 | United Kingdom     | 1378330.00 |
|       6 | Italy              | 1161755.00 |
|       7 | China              |  982268.00 |
|       8 | Brazil             |  776739.00 |
|       9 | Canada             |  598862.00 |
|      10 | Spain              |  553233.00 |
|      11 | India              |  447114.00 |
|      12 | Mexico             |  414972.00 |
|      13 | Netherlands        |  371362.00 |
|      14 | Australia          |  351182.00 |
|      15 | Argentina          |  340238.00 |
|      16 | South Korea        |  320749.00 |
|      17 | Russian Federation |  276608.00 |
|      18 | Switzerland        |  264478.00 |
|      19 | Taiwan             |  256254.00 |
|      20 | Belgium            |  249704.00 |
+---------+--------------------+------------+

rank(), dense_rank()

次に rank() です。
前述の row_number() と似ていますが、違いは同一の行には同じ行セットの場合は同一の順位を割り振ります。
また、rank() と似ている関数で dense_rank() という関数があります。
この2つの違いは、例えば2位の行セットが2つあった場合、前者は 1, 2, 2, 4 となりますが、
校舎は 1, 2, 2, 3 といったように出力されます。
関数の名前のとおり、ランキングとして使えると思います。
使用する際には row_number() も含め適切なものを比較検討すると良さそうです。

-- rank()
select rank() over(order by countryCode) ranking, countryCode, language from countrylanguage limit 20;
+---------+-------------+------------------+
| ranking | countryCode | language         |
+---------+-------------+------------------+
|       1 | ABW         | Dutch            |
|       1 | ABW         | English          |
|       1 | ABW         | Papiamento       |
|       1 | ABW         | Spanish          |
|       5 | AFG         | Balochi          |
|       5 | AFG         | Dari             |
|       5 | AFG         | Pashto           |
|       5 | AFG         | Turkmenian       |
|       5 | AFG         | Uzbek            |
|      10 | AGO         | Ambo             |
|      10 | AGO         | Chokwe           |
|      10 | AGO         | Kongo            |
|      10 | AGO         | Luchazi          |
|      10 | AGO         | Luimbe-nganguela |
|      10 | AGO         | Luvale           |
|      10 | AGO         | Mbundu           |
|      10 | AGO         | Nyaneka-nkhumbi  |
|      10 | AGO         | Ovimbundu        |
|      19 | AIA         | English          |
|      20 | ALB         | Albaniana        |
+---------+-------------+------------------+

-- dense_rank()
select dense_rank() over(order by countryCode) ranking, countryCode, language from countrylanguage limit 20;
+---------+-------------+------------------+
| ranking | countryCode | language         |
+---------+-------------+------------------+
|       1 | ABW         | Dutch            |
|       1 | ABW         | English          |
|       1 | ABW         | Papiamento       |
|       1 | ABW         | Spanish          |
|       2 | AFG         | Balochi          |
|       2 | AFG         | Dari             |
|       2 | AFG         | Pashto           |
|       2 | AFG         | Turkmenian       |
|       2 | AFG         | Uzbek            |
|       3 | AGO         | Ambo             |
|       3 | AGO         | Chokwe           |
|       3 | AGO         | Kongo            |
|       3 | AGO         | Luchazi          |
|       3 | AGO         | Luimbe-nganguela |
|       3 | AGO         | Luvale           |
|       3 | AGO         | Mbundu           |
|       3 | AGO         | Nyaneka-nkhumbi  |
|       3 | AGO         | Ovimbundu        |
|       4 | AIA         | English          |
|       5 | ALB         | Albaniana        |
+---------+-------------+------------------+

lag(), lead()

次に lag() と lead() を紹介します。
lag() は列名を引数に取り、1つ前の行の列の値を返します。
lead() は反対に、1つ後のの行の列の値を返します。
前後の値を比較したい場合に良さそうです。

select
    name,
     GNP,
     lag(GNP) over(order by GNP desc) as `lag`,
     lead(GNP) over(order by GNP desc) as `lead`,
     -- 前の行のGNPとの差分
     lag(GNP) over(order by GNP desc) - GNP lag_diff,
     -- 次の行のGNPとの差分
     GNP - lead(GNP) over(order by GNP desc) lead_diff
 from country 
 limit 20;

+--------------------+------------+------------+------------+------------+------------+
| name               | GNP        | lag        | lead       | lag_diff   | lead_diff  |
+--------------------+------------+------------+------------+------------+------------+
| United States      | 8510700.00 |       NULL | 3787042.00 |       NULL | 4723658.00 |
| Japan              | 3787042.00 | 8510700.00 | 2133367.00 | 4723658.00 | 1653675.00 |
| Germany            | 2133367.00 | 3787042.00 | 1424285.00 | 1653675.00 |  709082.00 |
| France             | 1424285.00 | 2133367.00 | 1378330.00 |  709082.00 |   45955.00 |
| United Kingdom     | 1378330.00 | 1424285.00 | 1161755.00 |   45955.00 |  216575.00 |
| Italy              | 1161755.00 | 1378330.00 |  982268.00 |  216575.00 |  179487.00 |
| China              |  982268.00 | 1161755.00 |  776739.00 |  179487.00 |  205529.00 |
| Brazil             |  776739.00 |  982268.00 |  598862.00 |  205529.00 |  177877.00 |
| Canada             |  598862.00 |  776739.00 |  553233.00 |  177877.00 |   45629.00 |
| Spain              |  553233.00 |  598862.00 |  447114.00 |   45629.00 |  106119.00 |
| India              |  447114.00 |  553233.00 |  414972.00 |  106119.00 |   32142.00 |
| Mexico             |  414972.00 |  447114.00 |  371362.00 |   32142.00 |   43610.00 |
| Netherlands        |  371362.00 |  414972.00 |  351182.00 |   43610.00 |   20180.00 |
| Australia          |  351182.00 |  371362.00 |  340238.00 |   20180.00 |   10944.00 |
| Argentina          |  340238.00 |  351182.00 |  320749.00 |   10944.00 |   19489.00 |
| South Korea        |  320749.00 |  340238.00 |  276608.00 |   19489.00 |   44141.00 |
| Russian Federation |  276608.00 |  320749.00 |  264478.00 |   44141.00 |   12130.00 |
| Switzerland        |  264478.00 |  276608.00 |  256254.00 |   12130.00 |    8224.00 |
| Taiwan             |  256254.00 |  264478.00 |  249704.00 |    8224.00 |    6550.00 |
| Belgium            |  249704.00 |  256254.00 |  226492.00 |    6550.00 |   23212.00 |
+--------------------+------------+------------+------------+------------+------------+

個人的には row_number() がかなり嬉しいですが、今回紹介したもの以外にも便利そうな関数があるので、
また試してみたいと思います。

2016-05-07

人を動かすを読んだ感想

今回読んだのはこの本。 www.amazon.co.jp

人との付き合い方について書かれている本。この本に書いてあることは、当たり前のことだけど、実践するとなるとなかなか難しいことが書かれているなという感じた。あと、全部を100%実践してしまうと気持ち悪い人間になってしまうんじゃないかな。なので、普段の自分の人との接し方を思い返して、取り入れた方が良いところだけ意識するようにするのがいいんじゃないかと思う。とりあえず印象に残っているところを

盗人にも五分の理を認める

他人の欠点を指摘しても、相手はすぐさま防御態勢を敷いて、正当化しようとしたり、反抗したりする。

これは自分でも経験があるところで、たとえ自分が正しいことを言っていたとしても、それによって良い方向には転ばなかった。

およそ人を扱う場合には、相手を論理の動物だと思ってはならない。相手は感情の動物であり、しかも偏見に満ち、自尊心と虚栄心によって行動するということをよく心得ておかねばならない。

これをわかっていないと、余計な批判をしてしまうことがあると思う。

人を非難する代わりに、相手を理解するように努めようではないか。どういうわけで、相手がそんなことをしでかすに至ったか、よく考えてみようではないか。そのほうがよほど得策でもあり、また、面白くもある。そうすれば、同情、寛容、好意も、自ずと生まれ出てくる。

批判する前にまず理解しようとすることがとても大切だと思った。

心からほめる

自分は人をほめることがとても苦手。しかし、

人間は、誰でも周囲のものに認められたいと願っている。自分の真価を認めて欲しいのだ。小さいながらも、自分の世界では自分が重要な存在だと感じていたいのだ。見え透いたお世辞は聞きたくないが、心からの賞賛には飢えているのだ。

これを読んで、自分も確かにそういうところがあるし、自分がそうなら他人も同じだろうと思った。自分がして欲しいなら他人にもそれをすることができるようになろうと思った。これを実践するために、相手の長所を見つけること、小さなことでもほめるの２つを実践しようと思った。

上に書いた２つ以外にも人付き合いをするうえで大切なことが書いてあった。全てを意識するのはいきなりは難しいと思うので、とりあえずは、

相手を批判する前に理解しようと努める
心からほめる

この２つを実践していくようにしようと思う。

この本はいつ読んでも発見がありそうな本なので、時間をおいてから読み返すのがいいかもしれない。

2013-12-03

堅牢なプログラムを書くために意識すべきこと

変数は宣言と同時に初期化する（できるだけでＯＫ）

初期化がどこでされているかわからない変数はバグの原因になりやすい。例えば、分岐処理の条件によって実行されない初期化があったりするとそれがバグの原因になり得る。（その場合Javaではコンパイルエラーになる。）可読性の観点からも。

変数を使いまわさない

変数の使い回しをするとデバッグが困難になることがある。再代入は極力避けるようにする。finalや不変オブジェクトを活用する。

変数のスコープを小さくする

変数のスコープが大きくなると追跡が難しくなる。フィールド変数をローカル変数に出来ないか、またブロックスコープを確認する。

オブジェクトの寿命を意識する

Javaではガベージコレクションによって、オブジェクトへの参照がなくなるとオブジェクトは消滅する。変数からオブジェクトへの参照が外れる条件は以下のとき。

ローカル変数およびパラメータ変数のスコープが外れた時
オブジェクトがしたとき、インスタンスフィールド変数の参照が外れる
クラスが消滅したとき、クラスフィールド変数からの参照が外れる
変数に別のオブジェクト参照・またはnullを再代入した時。
メソッドチェーンを使用した際に、式の評価が終わったあと

また、ファクトリ－メソッド、シングルトンパターンの利用を検討する。

不変オブジェクトを活用する

バグの多くはオブジェクトの状態が不正に変化することで起きる。不変なオブジェクト（Stringクラス等）ならメソッドのパラメータとして渡した時に勝手に変更されることがないので、余計な心配をしなくても良くなる。

説明が足りないところが結構あるので、後で追記しよう・・・

2013-12-02

Javaにおけるコレクションフレームワークの利用指針

Javaの復習のために調査したものをメモ。

コレクションとは

モノの集まりを表現するものであり、かつモノの集まりに対する操作を表現するモノ

Javaでは基本的でかつ汎用性の高いデータ構造・アルゴリズムを、コレクションフレームワークとして提供している。

List

順序どおりに並んだ集まり。多くの場合、インデックスを使用してアクセスする。

具象クラス

ArrayList
LinkedList

利用指針

ArrayList

インデックスを指定しての読み出し・書き換え速度が速い
先頭から全てを順になめていく処理が速い
要素の挿入・削除が遅いことがある。（先頭に近いほど遅い）

LinkedList

要素の挿入・削除が速い
インデックスを指定しての読み出し・書き換えが遅い
条件に合致した要素の検索（contains,indexOf,lastIndexOf）が早くない

要素の読み込み・書き換えが中心の場合、ArrayListを使用する。要素の挿入や削除が中心の場合、LinkedListを使用する。

Map

キーと値のペアをの集まり。キーを使用して検索する。

具象クラス

HashMap
LinkedHashMap
TreeMap

利用指針

HashMap

順序が不要な時。追加や検索が高速なため
LinkedHashMap
追加した順序やアクセスした順序が欲しい場合。（内部的にハッシュ表とLinkedListの両方を生成している）
TreeMap

２分探索木構造（詳しくはググること）のMap。任意の比較規則に従った順序が欲しい場合。数値の大小順、辞書順等。あいまい検索をしたい場合にはNavigatavleMapインターフェースを使用。

Set

重複のない要素の集まり。

具象クラス

HashSet
LinkedHashSet
TreeSet

Mapの具象クラスと名前が似ているのは、Mapの実装クラスをそのままSetでも利用しているため。 Mapのキーが重複してはいけないという点と意味が同じであることを利用。

利用指針もMapの利用指針を参考にすること。

Iterator

集合と繰り返しは強い関係があるため、セットで覚えること。

拡張for構文

//拡張for構文
for(Integer n : list) {
sum += n;
}

下記の理由がない場合はこちらを使用すること。（可読性や、後述する理由から)

コレクションの要素を逆順になめるとき
ループを途中で抜けたあと、再度同じ位置からループを再開したいとき（あまりないけど・・・）
コレクションをなめる最中で、要素の追加や削除を行うとき

Itaratorを使用したループ

//Iteratorを使用したループ
for(Iterator it = list.iterator(); it.hasNext();) {
Integer n = it.next();
sum += n;
}

最も柔軟な記述方法。上記の理由で拡張for文が使えない場合、こちらの方法で記述可能。（疲れたので具体例は後日・・・）

インデックスを使用したforループ

//インデックスでforループをまわすパターン(あまり推奨しない）
for(int i = 0; i < list.size(); i++) {
Integer n = list.get(i);
sum += n;
}

あまりおすすめしない。理由は、インデックスを利用するforループは、コレクション一般に利用できるコードではなく、リストにのみ適用可能な処理であるため。

とりあえず最低限はこんなところかなと。個人的にはTreeMap及びNavigatableMap、Iteratorのところは知らなかった部分もあったので、収穫ありでした。

2013-11-19

SpringMVC3.2　環境構築

最近まで違う言語で開発していたが、またJavaに触れることになりそうなのでJavaを思い出すついでにSpringの復習をしようと思ったので、環境構築手順をメモ。

参考：SpringMVC3.2.1を利用してのWebアプリケーション #01 - m-namikiの日記

事前に

JDKのインストール（ＪＲＥじゃだめ）

http://www.oracle.com/technetwork/java/javase/downloads/index.html

ＳＴＳ（Spring Tool Suite、Eclipseの)

http://spring.io/tools/sts

Maven

http://maven.apache.org/download.cgi

ＪＤＫ、Mavenは環境変数の設定をしておくこと。

プロジェクトの作成

spring-mvc-quickstart-archetypeのインストール

kolorobot/spring-mvc-quickstart-archetype · GitHub

git clone またはzipをダウンロード。

取り込んだディレクトリに移動して以下のコマンドを実行。

mvn install

ひな形の作成

任意のディレクトリで以下のコマンドを実行。

mvn archetype:generate -DarchetypeGroupId=com.github.spring-mvc-archetypes -DarchetypeArtifactId=spring-mvc-quickstart -DarchetypeVersion=1.0.0-SNAPSHOT -DgroupId=jp.co.shantery -DartifactId=springmvc-tutorial -Dversion=1.0.0

※通常はこのあと「mvn eclipse:eclipse」を使用しますが、STSで標準でインストールされているm2eプラグインと併用できないため、今回は使用しない。

参考：xawa雑記帳: Eclipse にインポート時に Unsupported IClasspathEntry が出る

STSへインポート

STSのエクスプローラ上で右クリック -> import -> Maven -> Existing Maven Projects

ビルドエラーが出た際にはプロジェクトを選択してMaven -> Update Project または Alt + F5キーでMaven リポジトリを更新する。

プロジェクトの実行

今回はSTS標準のVMWare vFablic tc Serverから実行。

Run as -> Run On Server -> VMWare vFablic tc Serverを選択。

とりあえず動いたのでここまで。

over句について

Window集約関数

よく使われるWindow関数

row_number()

rank(), dense_rank()

lag(), lead()

変数は宣言と同時に初期化する（できるだけでＯＫ）

変数を使いまわさない

変数のスコープを小さくする

オブジェクトの寿命を意識する

不変オブジェクトを活用する

コレクションとは

List

具象クラス

利用指針

LinkedList

Map

具象クラス

利用指針

HashMap

LinkedHashMap

TreeMap

Set

具象クラス

拡張for構文

Itaratorを使用したループ

インデックスを使用したforループ

事前に

プロジェクトの作成

spring-mvc-quickstart-archetypeのインストール

ひな形の作成

STSへインポート

プロジェクトの実行