[MySQL][マーケティング] *n 層の分類をする SQL
性別、生年月日(8桁数値)のカラムを持つテーブルのデータをマーケティングでよくみる ([MF][1-3]|C|T) 層に分類したところ、えらい残念な SQL ができたのでメモ。
Fn層の分類についてはこの辺 ( F1層 とは - コトバンク ) を参照のこと。
テーブルはこんな感じでした。
1 2 3 4 | CREATE TABLE `user` (
`gender` TINYINT(1) NOT NULL, -- 0が男性, 1が女性
`birthday` CHAR(8) NOT NULL,
);
|
で、分類する SQL は以下。もうちょいマシにならないものか。今回、子供を正確に分類する必要がなかったため [MF]0 層として計上しています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | SELECT b.class, COUNT(b.class) AS count
FROM (
SELECT CONCAT(
CASE gender WHEN 0 THEN 'M' ELSE 'F' END,
CASE
WHEN a.age < 20 THEN '0'
WHEN a.age < 25 THEN '1'
WHEN a.age < 35 THEN '2'
ELSE '3' END
) AS class
FROM (
SELECT
(YEAR(CURDATE()) - YEAR(date_format(birthday,'%Y-%m-%d')))
- (RIGHT(CURDATE(),5) < RIGHT(date_format(birthday,'%Y-%m-%d'),5)) AS age
FROM user
) a
) b
GROUP BY b.class
|
こんな感じの結果が返ってきます。
+-------+-------+
| class | count |
+-------+-------+
| F0 | 100 |
| F1 | 200 |
| F2 | 300 |
| F3 | 400 |
| M0 | 100 |
| M1 | 200 |
| M2 | 300 |
| M3 | 400 |
+-------+-------+
いちおう分類表も書いておきます(表組みの練習もかねて)。
| 表記 | 性別 | 年齢層 |
|---|---|---|
| M1 | 男性 | 20 - 34 歳 |
| M2 | 男性 | 35 - 49 歳 |
| M3 | 男性 | 50 歳以上 |
| F1 | 女性 | 20 - 34 歳 |
| F2 | 女性 | 35 - 49 歳 |
| F3 | 女性 | 50 歳以上 |
| C | 男女 | 4 - 12 歳 |
| T | 男女 | 13 - 19 歳 |
DB マイグレーションツール Flyway で fail したときのメモ
最近プロジェクトに DB マイグレーションツールであるところの、 flyway を導入しました。
SQL ベースで DB の状態を管理していくので、特定の O/R マッパーに依存することもなく、プロジェクトが走り出してからさくっと導入するぶんには特に問題も起こりませんでした。
そんななか migrate が fail したときに少々困ったのでメモ。
fail するとどうなるか
fail するとこんな感じでエラーメッセージが表示されて処理が停止します。
1 2 3 4 5 6 7 | $ ./flyway.sh migrate
Flyway (Command-line Tool) v.2.2.1
Current version of schema `HOGEDB`: 3.1
Migrating schema `HOGEDB` to version 3.2
ERROR: com.googlecode.flyway.core.api.FlywayException: Migration of schema `HOGEDB` to version 3.5 failed! Please restore backups and roll back database and code!
以下エラー文
|
schema_version テーブルを見てみると、 version が 0 になっているレコードが挿入されていました。この状態ではいくら migrate コマンドを実行してもマイグレーションは行なわれません。
repair してあげよう
以下のように repair コマンドを実行することで、 success が 0 なレコードが取り除かれ、 migrate コマンドを実行できるようになります。もちろん migrate コマンドを実行する前に DB や SQL に起こっている問題は解消しておきましょう。
1 2 3 4 5 | $ ./flyway.sh repair
Flyway (Command-line Tool) v.2.2.1
Metadata table `HOGEDB`.`schema_version` successfully repaired (execution time 00:00.001s).
Manual cleanup of the remaining effects the failed migration may still be required.
|
migrate できた。
1 2 3 4 5 6 | $ ./flyway.sh migrate
Flyway (Command-line Tool) v.2.2.1
Current version of schema `HOGEDB`: 3.4
Migrating schema `HOGEDB` to version 3.5
Successfully applied 1 migration to schema `HOGEDB` (execution time 00:00.102s).
|
[Windows]バッチファイルにて処理対象ファイル内でコメントアウトを扱う
仕事で必要になったのでメモします。 利用したいコマンドが、大量データを扱うにもかかわらずファイル内のコメントをサポートしていなかったら、やっぱり寂しいですよね。そんな時に。
手順としては単純です。
- FINDSTRで正規表現を用いて任意のパターンを含む行を無視してリダイレクト
- リダイレクトして作った中間ファイルを任意のコマンドに入力
以上です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | @echo off
if "%‾1" == "" goto usage
SET VALUE=%1
rem "^#"とマッチする行を無視して中間ファイルを作成
type %1 | findstr /v "^#" > tmp.dat
hoge.exe tmp.dat
pause
goto :eof
:usage
echo Usage: fuga.bat piyo
|
おまけに引数チェックも。
for文でどうやらコメントアウトが扱えるらしいと聞いたのは、もちろんfor文も書き込んで、ぐるぐると処理が回りきったあとのことでした。
Facebook の Events を iCal に同期する
FacebookのEventsページにあるExport Eventsから、iCalやGoogle CalenderからEventsを照会できるURLを入手することができます。このURLを利用して、iPhoneのカレンダーにEventsを同期することが可能です。べんり。
- Export Eventsダイアログに表示されたURLをiPhoneに送信
- 「設定→メール/連絡先/カレンダー→アカウントを追加→その他→照会するカレンダーを追加」で先程のURLを入力。
以上の手順でiPhoneのカレンダーより照会可能です。
migemo を emacs 24.3.1 にインストールしたときのメモ, Mac OS X
migemo とは
migemo とは、ローマ字を入力することで日本語文章をインクリメンタルサーチするためのソフトウェアです。emacs 拡張を利用すると、いつもの C-s がそのまま日本語に対応します。
今回はC言語で実装された cmigemo をインストールします。
cmigemo のインストール
github から cmigemo を入手してインストールします。オプションはデフォルトのままにしておきました。デフォルトでは /usr/local/ 以下にインストールされます。
$ git clone https://github.com/koron/cmigemo.git cmigemo
$ cd cmigemo
$ ./configure
$ make osx
$ make osx-dict
$ sudo make osx-install
cmigemo を試してみる
下記のコマンドを実行するとプロンプトが表示され、入力待ちの状態になります。
$ cmigemo -d /usr/local/share/migemo/utf-8/migemo-dict
migemo_open("/usr/local/share/migemo/utf-8/migemo-dict")=0x7fb552c03990
clock()=0.120343
QUERY:
QUERY 適当なローマ字を入力してみると、正規表現が生成されることがわかります。正規表現を入力の度に生成し、それを検索クエリとすることでインクリメンタルサーチを可能にするわけですね。
QUERY: paison
PATTERN: (ﾊﾟｲｿ[ﾝﾉﾈﾇﾆﾅ]|パイソ[ンノネヌニナ]|ぱいそ[んのねぬにな]|ｐａｉｓｏｎ|paison)
QUERY: toukyo
PATTERN: (ﾄｳｷｮ|トウキョ|等距離|登極|当(局|協会)|東[教京]|とうきょ|ｔｏｕｋｙｏ|toukyo)
QUERY:
migemo.el のインストール
emacs 拡張の migemo.el は package.el を使うと簡単にインストールできます。 *scratch* バッファで (package-install ‘migemo) を評価でもしてやると入ります。
migemo.el の設定
init.el にこんな感じの設定を追記。 C-s してミニバッファに [MIGEMO] I-search と表示されれば日本語インクリメンタルサーチが可能になっているはずです。
(require 'migemo)
(setq migemo-command "/usr/local/bin/cmigemo")
(setq migemo-options '("-q" "--emacs"))
(setq migemo-dictionary "/usr/local/share/migemo/utf-8/migemo-dict")
(setq migemo-user-dictionary nil)
(setq migemo-coding-system 'utf-8-unix)
(setq migemo-regex-dictionary nil)
(load-library "migemo")
(migemo-init)
ChatWork + Fluidで未読件数の表示をする
Fluid でユーザスクリプトが使えることを知ってちょろっと探してみると上の記事が見つかったので。
Fluidって？
WebアプリのURLを食べさせるとMacOSのhoge.appを吐き出してくれるものです。ユーザスクリプトはPurchaseしないと利用できない模様。
元の記事と何が違うの？
未読件数の表示に加えて、Docのアイコンが跳ねます。changelogを見ると`window.fluid.requestUserAttention`ってAPIがあったのでこれや！と思って1行追加しただけです。 Fluid Developer 見ても書いてなかったぞ。。。
Google Playストアに接続できなくなったときの対処法
Xperia S LT26iでいきなりGoogle Playストアが使えなくなって困ったので対処法をメモ。
事象
充電が完全に切れた状態で放置していたXperia S LT26i（OSのバージョンは 2.3.7）を久々に起動し、Google Playストアを立ち上げたところ、真っ白な画面に「接続できません」の文字が。「再接続」ボタンを押しても一向に正常に動作する気配もなし。特に原因を示唆する文言もないためかなり困る。
対処法
内蔵時計がリセットされていたのが原因でした。設定から日付・時刻をしっかり合わせてからGoogle Playストアを立ち上げるとそこには元気なマーケットプレイスの姿が。
Tinkerer で Google Analytics を有効化する
折角公開したので、 Google Analytics をのせてみました。
まぁ、Tinkerer の ドキュメント を読めば書いてあることです。
トラッキングコードを保存して
_static/google_analytics.js にトラッキングコードをコピペ
$ cd {Tinkerer のルートディレクトリ}
$ mkdir _static
$ touch _static/google_analytics.js
テンプレートを作り
$ touch _templates/page.html
テンプレートからトラッキングコードを読み込んで
_templates/page.html を編集
{% extends "!page.html" %}
{% set script_files = script_files + ["_static/google_analytics.js"] %}
ビルドしておわり
$ tinker -b
Tinkerer でブログをはじめてみました
Sphinx を使ってブログが書ける Tinkerer を発見したのではじめてみました。 ホスティングは GitHub を利用しています。
GitHub でブログを公開するまでにやったこと
virtualenv で仮想環境を作る
Sphinx の拡張機能をいろいろ試したかったので仮想環境を用意しました。Github Pages に公開したかったので Git リポジトリも同時に作成。
$ cd {your_git_directory}
$ git init hoge
$ cd hoge
$ mkvirtualenv hoge
あと、 $ workon hoge とかやってすぐに書きはじめられるといいなーと思ったので $VIRTUAL_ENV/bin/.postactivate を以下のように編集。これで workon したときに Git リポジトリのあるディレクトリに移動してくれます。
#!/opt/local/bin/zsh
# This hook is run after this virtualenv is activated.
proj_name=$(echo $VIRTUAL_ENV|awk -F'/' '{print $NF}')
cd {your_git_directory}/hoge
Tinkerer のインストールから記事を書くまで
Tinkerer インストール
$ pip install tinkerer
ブログを作る
$ tinker -s
カレントディレクトリに Sphinx のプロジェクトが展開されます。まずは conf.py をいじってブログタイトルなどを設定しましょう。 TODO: から始まるセクションを編集します。
記事を書く
以下のコマンドで rst ファイルが生成されるので編集しましょう。 blog ディレクトリ内に rst ファイルが配置されます。
$ tinker -p title
ちなみに、以下のコマンドで単一のページを作成することも可能です。こちらは pages ディレクトリ内に rst ファイルが配置されます。
$ tinker --page title
記事が書けたら、 $ tinker -b とするとビルドが実行されます。 index.html が生成されるのでブラウザで開いて出来ばえを確認できます。
GitHub Pages で公開
Hosting on GitHub とか GitHub Help を参考に設定。注意点としては、リポジトリのルートに .nojekyll ファイルを作っておくことと、ブログのデータをコミットするブランチを gh-pages としておくことが必要です。
思ったこと
Sphinx で書けるので、当然 Blockdiag なんかも使えます。 うまいことテーマをいじって wiki とブログを組み合わせたような使い方ができないか考え中。