投稿者: Yutaro

投稿日:

PrestoのWITH句

２年ほど前、Prestoのクエリを書き始めた時に知って感動して以来、
ずっと使い続けている文法に WITH句 があります。

イメージとしてはSQL中でVIEWのようなものを作れる構文で、
これを使うとクエリのネストを浅くするなど可読性を大幅に向上させることができます。

例えば次の2つのクエリは同じ結果を返してくれます。
(この例だとまだメリットがわかりにくいですね。そもそも一番外側のSELECT句が純粋に無駄)
伝えたいのはSELECT句のネストがなくなり、フラットな書き方ができるということです。
階層がより深かったり、数百行以上の規模のクエリになるとこの効果は劇的に大きく出ます。
各一時テーブルに名前をつけられることも意図を伝える上で非常に有益です。

通常のネスとした書き方。


SELECT
    col1,
    col2_max
FROM (
    SELECT
        col1,
        MAX(col2) AS col2_max
    FROM
        table1
    GROUP BY
        col1
);

WITH句を使った書き方。


WITH
    table2 AS (
        SELECT
            col1,
            MAX(col2) AS col2_max
        FROM
            table1
        GROUP BY
            col1
    )
SELECT
    col1,
    col2_max
FROM
    table2;

うちのチームでは(その人がSQLを十分書ける人であれば)
トレジャーデータのアカウントを渡して基本的な注意事項を説明したら、
すぐに WITH の使い方を教えています。

その時に使える公式なドキュメントとか無いのかな、と思って探していたのですが、
9.32. SELECT の WITH Clause がそれのようです。
(目次でWITHを検索しても出てこないのでこれまで見つけれてなかった。)
非常にあっさりとしか説明されてなくて、職場の既存クエリ読んでもらった方が良さそうな情報量ではあります。

投稿日:

numpyでビンを作成する

以前の記事で、pandas.cutを使ってデータをビンに区切る方法を紹介しました。
参考:pandasで数値データを区間ごとに区切って数える

これはこれで便利なのですが、似たようなことを行う関数がnumpyにも実装されていたのでその紹介です。
個人的にはこちらの方が好きです。
numpy.digitize

引数は次の3つをとります。
x : 元のデータ
bins : 区切り位置のリスト (1次元のリストで単調増加か単調減少のどちらかであることが必須)
right : 統合をどちらの端に含むか。(binsが単調増加か減少かも関係するのでドキュメントの説明を見ていただくのが確実です)

これを使うと、xの各データが、binsで区切られたなんばんめの区画に含まれるのかのリストを返してくれます。
binsは配列で渡すので等間隔でなくても使えます。

動かしてみたのがこちら。


import numpy as np
x = np.random.randint(200, size=10) - 100
print(x)
# [ 20  77  23 -50 -18 -80 -17  45  66  83]
print(np.digitize(x, bins=[-50, -10, 0, 10, 50]))
# [4 5 4 1 1 0 1 4 5 5]

bins に 5つの要素があるので、両端も含めて6つのbin(0〜5)にデータが区切られます。
例えば最初の20は、10<=20<50 なので、4番目の区画ですね。
right を省略し、左側に統合がついているので、
-50<=-50<-10 となり、-50は1番目の区画に入るということも確認できます。

Interval オブジェクトではなく、ただの数列で値を返してくれるのもありがたい。
(Intervalオブジェクトも便利なのかもしれませんがまだ慣れない。)

投稿日:

numpyで重み付き平均

つい最近まで、numpyやpandasには重み付き平均を求める関数は無いと勘違いしていて、
必要な時は自分で実装したのを使っていました。

データと重みが numpy の array で渡される場合だけ対応するのであればこのような関数で計算できます。
(listなどにも対応しようと思うとこれでは動きません)


def weighted_mean(data, weights):
    return np.sum(data * weights) / np.sum(weights)

しかしよくよく調べてみると、いつも使っている numpy.mean のほかにも、
numpy.averageという関数があって、これは引数にweightsを渡せるでは無いですか。
(averageの方が常に優秀というわけではなく、 meanにしか無い引数もあります。)

参考:
numpy.average
numpy.mean

numpy.average を使うと重み付き平均を手軽に計算できます。
せっかくなので適当なデータについて上の関数と結果を見比べましょう。
(ついでに計算直書きしたのも並べて確認しました。)


import numpy as np
data = np.array([1, 3, 7])
weights = np.array([5, 12, 2])
print(weighted_mean(data, weights))
# 2.8947368421052633
print(np.average(data, weights=weights))
# 2.8947368421052633
print((1*5+3*12+7*2)/(5+12+2))
# 2.8947368421052633

完全一致してますね。

投稿日:

WordPressでコメントのブラックリストを登録する方法

まだ公開しているコメントは一つもいただけていない本ブログですが、実はスパムのような投稿は日々投稿されています。
キーワードやIPアドレスにもだいぶ法則性が見えてきたのでそろそろ対策することにしました。

(日本語設定している場合)
Wordpressの管理画面の左ペインで、「設定」 => 「ディスカッション」　と選ぶと、
コメントブラックリスト　という設定項目があります。

説明にある通り、ここによく貼られているURLやIPアドレスなどを入れておけばそのコメントは自動的にゴミ箱に入るようです。
このブログの趣旨に沿ったコメントであれば確実に登場しないような単語をいくつかピックアアプして設定しておこうと思います。

コメントの内容、名前、URL、メールアドレス、IP アドレスに以下の単語のうちいずれかでも含んでいる場合、そのコメントはゴミ箱に入ります。各単語や IP アドレスは改行で区切ってください。単語内に含まれる語句にも一致します。例: 「press」は「WordPress」に一致します。

投稿日:

NetworkXで最短経路探索

せっかくgraphvizではなくNetworkXを動かしているので、何か実装されているアゴリズムを試しておこうというのが今回の趣旨です。
とりあえずグラフ中の二点間の最短経路を探す関数を試してみましょう。
(SNSのフォローや友達関係のネットワークで共通の知り合い等を探すのに使えますね)

まず、ランダムにグラフデータを作成します。


import networkx as nx
import matplotlib.pyplot as plt
import numpy as np

G = nx.Graph()

for i in range(30):
    for j in range(i+1, 30):
        # 1/10の確率でnode間を線で結ぶ
        if np.random.randint(10) == 0:
            G.add_edge(i, j)

# 可視化
fig = plt.figure(figsize=(8, 8))
nx.draw_networkx(G)

出来上がったのはこちら。

ここで2点を指定して、最短経路を求めるにはshortest_pathを使います。
ドキュメント:Shortest Paths

パスが存在しない時にshortest_pathを実行するとエラーになるので、
パスの存在を判定する関数も合わせて試してみます。


# 特定の２つのnode間の最短経路を探す
print(nx.shortest_path(G, source=9, target=20))
# [9, 10, 13, 24, 12, 20]

# source か target を省略すると、その点と他の各点の最短経路を求める
print(nx.shortest_path(G, source=5))
# {5: [5], 6: [5, 6], 29: [5, 29], 21: [5, 6, 21]}

# パスが存在するか判定する
print(nx.has_path(G, source=6, target=10))
# False
投稿日:

NetworkXを動かしてみる

ネットワークやグラフ構造の可視化はgraphvizでできるのですが、分析となると、networkX等、別のライブラリを使う必要が出てきます。

このブログでnetworkXを取り上げるのは初なので、とりあえず今回は非常に単純なグラフを書いて可視化してみました。
チュートリアルを見ながらいろいろいじると楽しいのでオススメです。


import networkx as nx
import itertools
import matplotlib.pyplot as plt
# グラフオブジェクトの作成
G = nx.Graph()
# ５つの点を相互に全て結ぶ
for edge in itertools.combinations(range(5), 2):
    G.add_edge(*edge)
# さらに5つの点を追加し、既存の点と結ぶ
for i in range(5):
    G.add_edge(i, i+5)
# 後から追加した5つの点を環状に結ぶ
for i in range(4):
    G.add_edge(i+5, i+6)
G.add_edge(9, 5)
# 可視化
nx.draw_networkx(G)

出力結果がこちら。
点の配置に乱数が使われているので、何度か施行していい感じになったのを保存しました。

投稿日:

MySQLで実行中のクエリを確認する

タイトルの通り、MySQLで実行中のクエリ(正確にはプロセス)を確認するコマンドの紹介です。

プログラムがクエリの実行待ち等で止まってしまった時、
クエリが動いてるかどうか不安になる時などに使っています。

MySQLでは SHOW を使う専用の構文が用意されているので簡単です。


SHOW PROCESSLIST

ドキュメント:13.7.5.30 SHOW PROCESSLIST 構文

投稿日:

Pythonでワードクラウドを作成する

テキスト中の単語の出現頻度を可視化する方法として、ワードクラウド(word cloud)というのがあります。
要は頻出の単語ほどでっかい面積を占拠できるように可視化する方法ですね。

これをPythonで作る時、その名もズバリ wordcloudというライブラリがあり、非常に手軽に使うことができます。

リポジトリ: amueller/word_cloud

インストールはpipでできます。


$ pip install wordcloud

20newsgroups のデータを使ってやってみましょう。
あまりにもごちゃごちゃすると意味がわからないので、カテゴリを一個に絞ってやってみます。(今回は sci.electronics にしました)
細かいですが、STOPWORDS があらかじめ用意されているのもありがたいですね。


from wordcloud import WordCloud
from wordcloud import STOPWORDS
from sklearn.datasets import fetch_20newsgroups
import matplotlib.pyplot as plt
remove = ('headers', 'footers', 'quotes')
categorys = [
        "sci.electronics",
    ]
twenty_news = fetch_20newsgroups(
                                subset='train',
                                remove=remove,
                                categories=categorys
                            )
raw_data = twenty_news.data
wordcloud = WordCloud(
                            stopwords=STOPWORDS, background_color="white"
                        ).generate(" ".join(raw_data))

fig = plt.figure(figsize=(12, 8))
ax = fig.add_subplot(1, 1, 1)
ax.imshow(wordcloud, interpolation='bilinear')
ax.axis("off")
plt.show()

結果がこちらです。

投稿日:

itertools でリストの部分集合をリストアップする

Pythonの標準ライブラリである、itertools を使うと、リストや集合(set)の部分集合を手軽にリストアップすることができます。
ドキュメントはこちら。
itertools — 効率的なループ実行のためのイテレータ生成関数

欲しい部分集合の性質(重複の可否や、ソートの有無)に応じて、次の3種つの関数が用意されています。

itertools.permutations(iterable, r=None)
長さrのタプル列、重複なしのあらゆる並び

itertools.combinations(iterable, r)
長さrのタプル列、ソートされた順で重複なし

itertools.combinations_with_replacement(iterable, r)
長さrのタプル列、ソートされた順で重複あり

試しに要素が5個のリストを用意して、 r=3 でソートされた順番で重複無しの
部分集合をリストアップしてみましょう。
$_5\mathrm{C}_3=\frac{5\cdot4\cdot3}{3\cdot2\cdot1}=10$なので、10組みの結果が得られるはずです。


import itertools
data = list("ABCDE")

for subset in itertools.combinations(data, 3):
    print(subset)

# 以下出力
('A', 'B', 'C')
('A', 'B', 'D')
('A', 'B', 'E')
('A', 'C', 'D')
('A', 'C', 'E')
('A', 'D', 'E')
('B', 'C', 'D')
('B', 'C', 'E')
('B', 'D', 'E')
('C', 'D', 'E')

想定通りの組み合わせが出ました。
また、結果はsetやlistではなく、タプルで返されることも確認できます。

投稿日:

MySQLのテーブルをDB内でコピーする2つの方法

先日、諸事情あってMySQLのテーブルをコピーしておきたいことがありました。

SELECT以外の操作は滅多にやらないので、少し調べて出てきたのがこちらの方法です。
1つ目のクエリで元のテーブルと同じ構造のテーブルを作り、
2つ目のクエリでデータを移しています。


CREATE TABLE new_table_name LIKE table_name;
INSERT INTO new_table_name SELECT * FROM table_name;

今回必要だった要件は、これで満たせたのですが、一個のクエリでコピーする方法があったはず、
というおぼろげな記憶があったのでもう少し探して見つけたのがこちら。


CREATE TABLE new_table_name SELECT * FROM table_name;

これなら1行で中のテーブルをコピーできます。

なんで一行で済む2番目の方法よりも、クエリを二つ使う1番目の方法の方がよく使われているのか気になったので、後日実験してみました。

結果わかったのは、データはどちらの方法でも同じようにコピーされるのですが、
2番目の方法では、テーブルの設定の一部がコピーされないことです。

試したのがこちら。
まず、元のテーブル。


mysql> desc sample1;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255) | YES  |     | NULL              |                             |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+

1番目の方法でコピーしたテーブル。こちらは元のテーブルと同じです。


mysql> desc sample2;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name       | varchar(255) | YES  |     | NULL              |                             |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+

2番目の方法でコピーしたテーブル。
auto_incrementやDefaultの値の一部、主キー属性などがコピーされていません。


mysql> desc sample2;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   |     | 0                 |                             |
| name       | varchar(255) | YES  |     | NULL              |                             |
| created_at | datetime     | NO   |     | CURRENT_TIMESTAMP |                             |
| updated_at | datetime     | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+-----------------------------+

値はどちらの方法でもコピーされるのですが、
見落としがちな部分に差異があるので注意が必要ですね。