MySQL COUNT, SUM, GROUP BY, CASE WHEN THEN で集計する

概要

ECサイトに新しい決済機能の利用率出したいな
と思ったときのクエリです。

ちょうどいくつかの集計関数がまとまった1クエリとなったので
まとめました。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
DATE_FORMAT(create_date, '%Y-%m-%d') AS 日付
,COUNT(order_id) AS 全件数
,FORMAT(SUM(payment_total),0) AS "全支払い合計(円)"
,COUNT(payment_id = 12 or NULL) AS "新決済機能の購入件数"
,COUNT(customer_id = 0 or NULL) AS "ゲスト購入数"
,FORMAT(SUM(CASE WHEN payment_id = 12 THEN payment_total else 0 END),0) AS "新決済機能の購入支払い合計(円)"
,count(payment_id = 12 or NULL)/count(order_id) * 100 AS "新決済機能の購入比率(%)"
,SUM(CASE WHEN payment_id = 12 THEN payment_total else 0 END)/SUM(payment_total) * 100 AS "新決済機能の購入支払い合計比率(%)"
,COUNT(customer_id = 0 or NULL)/COUNT(order_id) * 100 AS "ゲスト購入比率"
FROM
dtb_order
WHERE 1=1
AND site_id = 1
AND create_date > '2016-09-21 10:00:00'
GROUP BY
DATE_FORMAT(create_date, '%Y%m%d')
;
  • パッケージ = EC-CUBE 2.11.5
  • 新決済ID = 12

結果

CASE文をさらっと書けるようになると少し大人になった気分になります。
心残りは比率部分の重複部分がまとまったらかっこいいかなと。

精進します。

LINE Notify で Zabbix Alert 通知

LINE Notify で Zabbix Alert 通知

概要

Zabbix アラート を LINE Notify を利用して
LINE にメッセージを送るように設定しました。

手順

LINE Notify アクセス

[https://notify-bot.line.me/ja/]

登録してログイン

サービス登録

この情報が審査されるということは特になかったですが
ある程度精度の高い情報を入力して登録しときました

トークルーム選択しトークン発行

発行したトークンコピー

Zabbix スクリプト設定

Env

  • Zabbix 3.0
  • CentOS Linux release 7.2.1511 (Core)

Install Steps

1
2
3
4
5
[Zabbix-Server]$ cd /usr/lib/zabbix/alertscripts    # AlertScriptsPath
[Zabbix-Server]$ git clone https://github.com/kenzo0107/zabbix3-linenotify
[Zabbix-Server]$ mv zabbix3-slack/line_notify.sh .
[Zabbix-Server]$ rm -r zabbix3-linenotify
[Zabbix-Server]$ chmod 755 line_notify.sh

Media Types 設定

Users > Media 設定

通知テスト

テスト環境などで
Nginx の process が 1 つ以上になったらアラート出すように設定してみた結果

所感

トークルームに参加するにも
LINE アカウントはプライベートアカウントなので
ちょっと知られたくないわ〜という時は
何とも言えない気持ちになる方もいることがわかりました。

ご利用は計画的に。

今後の期待

個人的に
Twilio みたいに LINE Notify で電話通知出来たら嬉しいです。

まずは障害がない世界を ♪

以上です。

error RPC failed; curl 56 SSLRead() return on MacOS Sierra

概要

みんなのGo言語を購入しまして
ghq でGit管理してみよう!
と心動いた方は多いはず

昔から peco で Git Repository 移動コマンドはしてたけど、
ghq を利用したリポジトリ管理は便利ですね。

そんな折、
ghq コマンドで git repository をクローンしようとした際に
掲題のエラーが発生しましたので備忘録。

1
2
3
4
$ ghq get <git repository>
...
error: RPC failed; curl 56 SSLRead() return
...

利用している git が SSL 対応していないようです。

対応

  • git を openssl, curl 付きで再インストール
1
$ brew reinstall git --with-brewed-curl --with-brewed-openssl

再度実行

1
2
3
4
5
$ ghq get <git repository>
remote: Total 74442 (delta 145), reused 0 (delta 0), pack-reused 74160
Receiving objects: 100% (74442/74442), 701.45 MiB | 1.42 MiB/s, done.
Resolving deltas: 100% (50571/50571), done.
Checking out files: 100% (11350/11350), done.

無事できた♪

参照

Curl: (56) SSLRead() return error -9806 - Need Help please slight_smile

負荷監視とその原因調査

負荷監視とその原因調査

概要

新卒向けの説明として簡単な備忘録です。

-Item- -Explain-
%user ユーザー空間での CPU 使用率
%system カーネル空間での CPU 使用率
%iowait I/O 待ち時間の割合
%idle I/O 待ち以外で CPU が何もしていない時間の割合

ある日の Zabbix + Grafana の CPU 関連のグラフから原因を調査する。

① %iowait が飛び抜けて高い

  • %iowait 高
  • %user 低
  • %system 低

原因

スワップが大量に発生している可能性がある。

調査手順

1. SwapIn & SwapOut 確認

1
$ sar -W

2. システム全体のメモリ使用状況

1
$ free

3. メモリ使用率順でソート後メモリを消費しているプロセスを特定する

1
$ top
  • Shift+p: CPU 使用率順にソート
  • Shift+m: メモリ使用率順にソート
実際の原因

定期的に同時刻に発生した為
crontab -l でクーロン設定確認したら
誰も知らないバッチが動いていた汗

② %user が飛び抜けて高い

  • %iowait 低
  • %user 高
  • %system 低

原因

CPU 使用率が高い。

調査手順

1. CPU 使用率の高い順にソートしてプロセス特定

1
$ top
  • Shift+p: CPU 使用率順にソート

ほんの一部分ですが参考になれば何よりです。
以上です。

機械学習ド素人のWebエンジニアが始める機械学習で顔認識

前回

顔検知と顔認識は本質が異なる。

  • 顔検知は顔と判定すること
  • 顔認識は顔が誰か特定の人の顔だと判定すること

今回は後者の顔認識をする仕組みをまとめました。

やろうとしてること

以下5つのSTEPを順を追って実施しています。

  1. 検索エンジンから画像ダウンロード
  2. ダウンロードした画像から顔検知し顔部分のみ抜き取る
  3. 顔部分を抜き取った画像を訓練用と試験用に分ける
  4. 機械学習によりモデル作成
  5. 試験用画像をモデルを利用し誰の顔であるか評価

参考

機械学習では TensorFlow を利用してます。

① 以下 TensorFlow の Hello World 的な例題とコピペすればすぐ動作するコードが記載されてます。

② すぎゃーんさんの記事は非常に参考になりました。

やっていることもシンプルでわかりやすく、且つ、Webエンジニアの発想でサービス化してる所が興味持って望めました。

今後

元々やりたかったことは
Raspberry PI で顔認識して家族だと判定したら「おはよう」と挨拶させる、
なのでその顔認識部分の基礎を今回は学びました。

今後は実際にRaspberry PI とどう今回の仕組みを連結させるかを
やってみようと思います。

とはいえ家族の写真はそう簡単には集まらないので
SMAPで基礎作りをもうちょい頑張ってみよう!

CSVエンコード問題解決

CSVエンコード問題解決

概要

Linux サーバで DB で集計して CSV ファイルをレポートする
なんてことがあるかと思います。

CSV ファイルを Linux サーバで作成し
Windows, Mac にメール添付して送信すると
どちらも CSV ファイルを開くと文字化けしてしまう問題に遭遇しました。

この問題を解決すべく調査しました。

そもそも何で文字化け?

CSV ファイルは Windows, Mac では基本 Excel が起動し開きますが
デフォルト Shift_Jis として開こうとします。

テキストファイルに一旦開いてコピーしてエクセルに貼り付ける対策を紹介しているブログもありましたが
クライアント様がお相手となる場合やファイルサイズが非常に大きい場合は
一手間かける方法は NG です。

調査 1 文字コードを変更してから mutt でメール添付送信

  1. 文字エンコードは nkf : Network Kanji Filter Version 2.0.7 (2006-06-13)
  2. メール送信は mutt 1.4.2.2i
  3. mutt の設定ファイルをいじりましたがうまくいかなかったです。

Shift_JIS

1
2
3
4
5
6
7
8
9
$ echo '大崎,yoshi,浜田,moto,松本' > sjis.csv
$ nkf -g sjis.csv
UTF-8

$ nkf -s --overwrite sjis.csv
$ nkf -g sjis.csv
Shift_JIS

$ echo "Shift_JIS だよ" | mutt -n -s "Shift_JIS CSV 添付" "kenzo.tanaka0107@gmail.com" -a sjis.csv
  • メール受信し添付ファイルをダウンロードし文字コードチェック
1
2
$ nkf -g sjis.csv
UTF-8

あれ? Shift_JIS にエンコードして送ったんだけど UTF-8 になってる

JIS (ISO-2022-JP)

1
2
3
4
5
6
$ echo '大崎,yoshi,浜田,moto,松本' > jis.csv
$ nkf -j --overwrite jis.csv
$ nkf -g jis.csv
ISO-2022-JP

$ echo "JIS だよ" | mutt -n -s "JIS CSV 添付" "kenzo.tanaka0107@gmail.com" -a jis.csv
  • メール受信し添付ファイルをダウンロードし文字コードチェック
1
2
$ nkf -g jis.csv
ISO-2022-JP

ISO-2022-JP で文字コードが変更されず送信されたけど…
やっぱり文字化け…

UTF-8

1
2
3
4
5
6
$ echo '大崎,yoshi,浜田,moto,松本' > utf8.csv
$ nkf -w --overwrite utf8.csv
$ nkf -g utf8.csv
UTF-8

$ echo "UTF-8 だよ" | mutt -n -s "UTF-8 CSV 添付" "kenzo.tanaka0107@gmail.com" -a utf8.csv
  • メール受信し添付ファイルをダウンロードし文字コードチェック
1
2
$ nkf -g utf8.csv
UTF-8

当然文字化け…

UTF-8 BOM 付き

1
2
3
4
5
6
$ echo '大崎,yoshi,浜田,moto,松本' > utf8-bom.csv
$ nkf --overwrite -oc=UTF-8-BOM utf8-bom.csv
$ nkf -g utf8-bom.csv
ISO-2022-JP

$ echo "UTF-8-BOM だよ" | mutt -n -s "UTF-8-BOM CSV 添付" "kenzo.tanaka0107@gmail.com" -a utf8-bom.csv
  • メール受信し添付ファイルをダウンロードし文字コードチェック
1
2
$ nkf -g utf8-bom.csv
ISO-2022-JP

JIS と同様の結果…

EUC

1
2
3
4
5
6
$ echo '大崎,yoshi,浜田,moto,松本' > euc.csv
$ nkf -e --overwrite euc.csv
$ nkf -g euc.csv
EUC-JP

$ echo "EUC だよ" | mutt -n -s "EUC CSV 添付" "kenzo.tanaka0107@gmail.com" -a euc.csv
  • メール受信し添付ファイルをダウンロードし文字コードチェック
1
2
$ nkf -g euc.csv
EUC-JP

ファイルエンコードではうまくいきませんでした。

調査 2 BINARY ファイルにしてみる

もっと具体的にいうと 圧縮ファイルを送ってみる

Shift_JIS で CSV が開かれるので Shift_JIS にエンコードします。

1
2
3
4
5
6
7
$ echo '大崎,yoshi,浜田,moto,松本' > sjis.csv
$ nkf -s --overwrite sjis.csv
$ zip sjis.zip sjis.csv
$ nkf -g sjis.zip
BINARY

$ echo "ZIP だよ" | mutt -n -s "ZIP 添付" "kenzo.tanaka0107@gmail.com" -a sjis.zip
  • メール受信し添付ファイルをダウンロードし文字コードチェック
1
2
3
4
5
6
$ nkf -g sjis.zip
BINARY

$ unzip sjis.zip
$ nkf -g sjis.csv
Shift_JIS

Shift_JIS のままダウンロードできてる!
これは期待できそう!

うまくいった!

総評

  • Windows, Mac で送られてきた CSV ファイルで文字化けせず開くことができました。
  • 圧縮した方が容量を下げて通信が行えるのでよくなりました。

PHP 検証フィルタで Email アドレス検証 を検証する

概要

Email アドレスのフォーマットチェックとして PHP には検証フィルタが用意されています。

こんな使い方しますね。

1
2
3
4
5
if (filter_var($email, FILTER_VALIDATE_EMAIL)) {
echo '(^-^) OK Email アドレスフォーマットとして妥当';
} else {
echo '(>_<) NG';
}

以下 php.net ではこのように記述されている。

http://php.net/manual/ja/filter.filters.validate.php

値が妥当な e-mail アドレスであるかどうかを検証します。
この検証は、e-mail アドレスが RFC 822 に沿った形式であるかどうかを確かめます。 ただし、コメントおよび空白の折り返し (whitespace folding) には対応していません。

検証

結果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
[OK (^-^) EMAIL LIST]
abc@gmail.com
a!bc@gmail.com
a#bc@gmail.com
a$bc@gmail.com
a%bc@gmail.com
a&bc@gmail.com
a`bc@gmail.com
a=bc@gmail.com
a~bc@gmail.com
a~bc@gmail.com
a|bc@gmail.com
a^bc@gmail.com
a*bc@gmail.com
a+bc@gmail.com
a?bc@gmail.com
a`bc@gmail.com
a{bc@gmail.com
a}bc@gmail.com
a}bc@gmail.com
!abc@gmail.com
#abc@gmail.com
$abc@gmail.com
%abc@gmail.com
&abc@gmail.com
=abc@gmail.com
~abc@gmail.com
|abc@gmail.com
^abc@gmail.com
*abc@gmail.com
+abc@gmail.com
?abc@gmail.com
`abc@gmail.com
{abc@gmail.com
}abc@gmail.com
a__bc@gmail.com
abc_@gmail.com
abc@vwx.yz

[NG (>_<) EMAIL LIST]
a"bc@gmail.com
a@bc@gmail.com
a(bc@gmail.com
a)bc@gmail.com
a\bc@gmail.com
a:bc@gmail.com
a;bc@gmail.com
a<bc@gmail.com
a>bc@gmail.com
a>bc@gmail.com
a,bc@gmail.com
a[bc@gmail.com
a]bc@gmail.com
¥abc@gmail.com
"abc@gmail.com
@abc@gmail.com
(abc@gmail.com
)abc@gmail.com
\abc@gmail.com
:abc@gmail.com
;abc@gmail.com
<abc@gmail.com
>abc@gmail.com
,abc@gmail.com
[abc@gmail.com
]abc@gmail.com
a..bc@gmail.com
abc.@gmail.com
abc@@vwx.yz

NGとしたいような Emailアドレス を通してしまいます。

&abc@xyz.ab

これまでの評価

PHP 検証フィルタ FILTER_VALIDATE_EMAIL によるバリデーションは
社内システムで利用するアカウントでのEmailアドレス検証程度であれば利用可能か。

商用サービスとして検証フィルタのみでバリデーションするのは危険かなと思いました。

マイ Email バリデーション

  • 検証フィルタ FILTER_VALIDATE_EMAIL はベーシックに利用
  • 利用できる文字を 半角英数字 . _ - に制限
  • Qiita 記事を参照しDNS 検証チェック入れました。 (ShibuyaKosuke さんありがとうございます!)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
function checkEmailwithDNS($email, $check_dns = false) {
switch (true) {
case !filter_var($email, FILTER_VALIDATE_EMAIL):
case !preg_match("/^([a-zA-Z0-9])+([a-zA-Z0-9\._-])*@([a-zA-Z0-9_-])+([a-zA-Z0-9\._-]+)+$/", $email):
case !preg_match('/@([\w.-]++)\z/', $email, $m):
return false;
case !$check_dns:
case checkdnsrr($m[1], 'MX'):
case checkdnsrr($m[1], 'A'):
case checkdnsrr($m[1], 'AAAA'):
return true;
default:
return false;
}
}

if (checkEmailDNS($email, true)) {
echo '(^-^) OK Email アドレスフォーマットとして妥当';
} else {
echo '(>_<) NG';
}

マイ Email バリデーション検証

結果

ほぼ弾いてくれます〜

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
[OK (^-^) EMAIL LIST]
abc@gmail.com
a__bc@gmail.com
abc_@gmail.com

[NG (>_<) EMAIL LIST]
a!bc@gmail.com
a"bc@gmail.com
a@bc@gmail.com
a#bc@gmail.com
a$bc@gmail.com
a%bc@gmail.com
a&bc@gmail.com
a`bc@gmail.com
a(bc@gmail.com
a)bc@gmail.com
a=bc@gmail.com
a~bc@gmail.com
a~bc@gmail.com
a|bc@gmail.com
a\bc@gmail.com
a^bc@gmail.com
a:bc@gmail.com
a;bc@gmail.com
a*bc@gmail.com
a+bc@gmail.com
a?bc@gmail.com
a<bc@gmail.com
a>bc@gmail.com
a>bc@gmail.com
a,bc@gmail.com
a`bc@gmail.com
a[bc@gmail.com
a]bc@gmail.com
a{bc@gmail.com
a}bc@gmail.com
a}bc@gmail.com
¥abc@gmail.com
!abc@gmail.com
"abc@gmail.com
@abc@gmail.com
#abc@gmail.com
$abc@gmail.com
%abc@gmail.com
&abc@gmail.com
(abc@gmail.com
)abc@gmail.com
=abc@gmail.com
~abc@gmail.com
|abc@gmail.com
\abc@gmail.com
^abc@gmail.com
:abc@gmail.com
;abc@gmail.com
*abc@gmail.com
+abc@gmail.com
?abc@gmail.com
<abc@gmail.com
>abc@gmail.com
,abc@gmail.com
`abc@gmail.com
[abc@gmail.com
]abc@gmail.com
{abc@gmail.com
}abc@gmail.com
a..bc@gmail.com
abc.@gmail.com
abc@@vwx.yz
abc@vwx.yz

参照

そろそろメールアドレスを正規表現だけでチェックするのは終わりにしませんか?

以上です。

SSL証明書有効期限をチェックして結果をSlackに通知

SSL証明書有効期限をチェックして結果をSlackに通知

概要

1
SSL証明書の有効期限切れでサイトにアクセスができなくなってしまった。

なんてことが発生しない様にする為に実装しました。

Shell スクリプト

  • DOMAIN_LIST で設定した複数ドメインについて有効期限を確認します。

※実際には Jenins で実行しており
 ビルドパラメータでドメイン追加を簡単にしています。

  • 毎月第一月曜日に棚卸ししています。

Zabbix でも監視

Qiita に記事がありました。

Zabbix で SSL 証明書有効期限を監視する

1 週間を切ったら電話通知も設定できますし
対策は何にせよしておくと気持ちが落ち着きます。

以上です。

SlowQuery を検知して Explain で解析し Slack へ通知

SlowQuery を検知して Explain で解析し Slack へ通知

fluentd でエラーログを Slack へ通知 の続きです。

概要

MySQL DB サーバ の SlowQuery 状況を
リアルタイムに Slack で確認できるようにする為に導入しました。

環境

  • CentOS 6.5
  • td-agent 0.12.26

Fluent Plugin インストール

今回必要モジュールをインストールします。

1
2
3
# td-agent-gem install fluent-plugin-nata2
# td-agent-gem install fluent-plugin-mysql_explain
# td-agent-gem install fluent-plugin-sql_fingerprint

Percona Tool Kit インストール

fluent-plugin-sql_fingerprint で利用する fingersprint をインストールします。

1
2
# rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum install -y percona-toolkit-2.2.5-2.noarch

fluentd 設定ファイル作成

以下ファイル設定するとします。

  • /etc/td-agent/conf.d/mysql.conf
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<source>
type mysqlslowquery_ex
read_from_head
path /var/lib/mysql/mysql-slow.log
pos_file /var/log/td-agent/mysql-slow.pos
tag mysqld.slow_query.bp
last_dbname_file /tmp/slowquery.log.lastdb
</source>

<filter mysqld.slow_query.**>
type record_transformer
<record>
hostname ${hostname}
</record>
</filter>

<filter mysqld.slow_query.**>
type mysql_explain
host 127.0.0.1
port 3306
database <DB_NAME>
username <DB_USER>
password <DB_PASSWORD>
sql_key sql
added_key explain
</filter>

<filter mysqld.slow_query.**>
type sql_fingerprint
fingerprint_tool_path /usr/bin/pt-fingerprint
</filter>

<match mysqld.slow_query.**>

type copy

<store>
type slack
webhook_url <Slack Webhook URL>
channel <Slack Channel>
username xxx DB1 [MySQL] Slow Query
icon_emoji :ghost:
color danger
message "*[User]* %s\r\n *[Host]* %s\r\n *[Query Time]* %s\r\n *[Lock Time]* %s\r\n *[Rows sent]* %s\r\n *[Rows Examined]* %s\r\n *[SQL]* %s \r\n *[Explain]* %s \r\n"
message_keys user,host,query_time,lock_time,rows_sent,rows_examined,fingerprint,explain
flush_interval 1m
</store>

</match>

※slowquery のパス、DB のアクセスアカウントなどは各環境により変更してください。

td-agent 再起動

1
# service td-agent restart

確認

SlowQuery を発行し、Slack に通知されるか確認します。

  • 3 秒 sleep させ、my.cnf に設定されている long-query-time の閾値の秒数を超えるようにしています。
1
mysql > SELECT count(*), sleep(3) FROM <table>;
  • Slack 通知確認

Slack に通知されました!

show more をクリックすると Explain が通知されているのがわかる。

総評

リアルタイム通知は
特に新規開発時に効果的でした。

また
ElasticSearch へ蓄積し時間軸で分析するのは
サイトのイベントとの相関性が見え面白いです。

その環境と状況により発生するスロークエリが見えてきます。

以上です。

PHP エンジニアであれば必ずやるべき 1 ライナー

PHP エンジニアであれば必ずやるべき 1 ライナー

みんなが幸せになれるhiraku さんの究極の 1 ライナーです。

1
$ composer config -g repositories.packagist composer http://packagist.jp

composer による インストールが劇的に早くなります。

遅い理由は 特に packagist.org が フランスにある からとのこと

問題発生

早速上記 1 ライナーを実行!!

すると…

1
2
You are running composer with xdebug enabled. This has a major impact on runtime performance. See https://getcomposer.org/xdebug
Do not run Composer as root/super user! See https://getcomposer.org/root for details

xdeug が enabled になっているぞと怒られている。。

xdebug 設定箇所を探す

1
2
3
4
5
6
7
$ php -i | grep xdebug

/etc/php.d/xdebug.ini,
xdebug
xdebug support => enabled
...
...

/etc/php.d/xdebug.ini で 設定していた。

※環境によっては php.ini で設定している等あるので注意

xdebug を disabled に設定変更

自分の PHP 実行環境では xdebug を利用する必要性がなかった為、
/etc/php.d/xdebug.ini 退避

1
mv /etc/php.d/xdebug.ini /etc/php.d/xdebug.ini.org

再度実行

あれ… また出てきた… 今度は、

1
Do not run Composer as root/super user! See https://getcomposer.org/root for details

root ユーザで実行するなと怒られている。。

root ユーザ以外の通常ユーザへ変更

1
# su - <user>

再度実行

成功した!

1
2
$ composer config -g repos.packagist composer https://packagist.jp
$

設定確認

packagist url が https://packagist.jp になっていることを確認

1
2
3
4
5
6
7
8
9
10
11
$ cat .composer/config.json

{
"config": {},
"repositories": {
"packagist": {
"type": "composer",
"url": "https://packagist.jp"
}
}
}

良き PHP ライフを!

参照