以前、こんな記事を書いた。
先日、Windows 10 バージョン 1607 を適用したところ、スタートメニューが変わって、リモートデスクトップでも簡単にシャットダウンできるようになった。
うっかりクリックしないよう注意が必要か・・・。
CentOS 7 の psql で tab 補完が大文字にならない件
今更だけど、人に聞かれたついでにメモ。
psql で tab を押してSQLキーワードを補完できるが、バージョンによって大文字だったり小文字だったり違いがある。
CentOS 6 / psql (PostgreSQL) 8.4.20
db=> sel ↓ tab db=> SELECT →SQLキーワードが大文字で補完される
CentOS 7 / psql (PostgreSQL) 9.2.15
db=> sel ↓ tab db=> select →SQLキーワードが大文字ではなく、小文字で補完される
PostgreSQL 8.4/9.2 で何が変わったかを、ドキュメントで確認。
8.4
https://www.postgresql.jp/document/8.4/html/app-psql.html
→COMP_KEYWORD_CASE の項目が無い
9.2
https://www.postgresql.jp/document/9.2/html/app-psql.html
→COMP_KEYWORD_CASE の項目がある
SQLキーワードを補完する時に大文字小文字のどちらを使用するかを決定します。
lowerまたはupperが設定された場合、補完された単語はそれぞれ小文字または大文字になります。
preserve-lowerまたはpreserve-upper（デフォルト）が設定された場合、 補完された単語は入力済みの文字の大文字小文字を引き継ぎますが、何も入力されていない場合はそれぞれ小文字または大文字に補完されます。
つまり、途中まで大文字で入力されていれば、大文字で補完される。
CentOS 7 / psql (PostgreSQL) 9.2.15
db=> SEL ↓ tab db=> SELECT
以前のバージョンと同じ動作（途中まで小文字でも、大文字で補完する）にしたければ、psql のパラメータで変更できる。
db=> \set COMP_KEYWORD_CASE upper
これをデフォルトにしたい場合は、~/.psqlrc へ記載する。
SourceTree for Windows 1.8.3 → 1.9.5 へ更新したら
git操作アイコンの配置が変わりました。
1.8.3
1.9.5
プッシュとフェッチの位置が逆に（´・ω・｀）
1.8 の時にあったコミットメッセージ欄で日本語を入力できない（フォーム内で右クリックすればOK）問題は解消されている。
ORDER BY で CASE文を使って特定の行を上位に表示する
CentOS 6.x の PostgreSQL 8.4 と SQLite3 で動作確認。
何らかのデータをソートして表示する際、特定の行だけは上位に表示したい事がある。
例えば、国マスタをコード順でソートした時に、日本を一番上に表示し、他はコードでソートして表示したい、等々。
単純にコードでソートした場合の例
db=> SELECT * FROM country ORDER BY iso; iso | name --------+-------------------------------------------- AD | アンドラ AE | アラブ首長国連邦 AF | アフガニスタン AG | アンティグア・バーブーダ AI | アンギラ AL | アルバニア AM | アルメニア AN | オランダ領アンティル AO | アンゴラ AQ | 南極
ORDER BY にCASE文を使った場合の例
db=> SELECT * FROM country ORDER BY CASE iso WHEN 'JP' THEN 1 ELSE 2 END, iso; iso | name --------+-------------------------------------------- JP | 日本 AL | アルバニア DZ | アルジェリア AS | サモア AD | アンドラ AO | アンゴラ AI | アンギラ AQ | 南極 AG | アンティグア・バーブーダ AR | アルゼンチン AM | アルメニア AW | アルバ
注意点は、下記のように、ORDER BY の条件でCASE文だけにすると、ELSE部分のソートが意図した通りにならない事がある。
CASE文だけでなく、本来ソートしたかった条件も必要。
失敗な例
db=> SELECT * FROM country ORDER BY CASE iso WHEN 'JP' THEN 1 ELSE 2 END; iso name ----- --------------------------------- JP 日本 AF アフガニスタン AL アルバニア DZ アルジェリア AS サモア AD アンドラ AO アンゴラ AI アンギラ AQ 南極
同様に、都道府県の場合の例（東京都を一番上に表示、他はコードでソート）
db=> SELECT * FROM pref ORDER BY CASE cd WHEN 13 THEN 1 ELSE 2 END, cd; cd | name ----+---------- 13 | 東京都 1 | 北海道 2 | 青森県 3 | 岩手県 4 | 宮城県 5 | 秋田県 6 | 山形県 7 | 福島県 8 | 茨城県 9 | 栃木県
国のデータは以下を利用。
国名と国コードの対応表
SQLiteでIPソート
複雑なわりには、使いどころが微妙（´・ω・｀）
PostgreSQL では inet 型にCASTすると簡単なものの、SQlite には inet型は無い。
0詰めしてソートしようとしても、PostgreSQL の「split_part」や「lpad」に相当する組み込み関数が無い。
どうにかできないか考えたところ、「SUBSTR」と「INSTR」を使えば何とかできそう。
但し、SQLite の「INSTR」は、バージョン 3.7.15 以降で利用可能。
なお、CentOS 7.2.1511 の標準リポジトリから yum でインストールされている SQLite は下記の通り。
$ sqlite3 --version 3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668
CentOS 6.8ではバージョンが低いので未対応。
$ sqlite3 --version
3.6.20
では、SQLite の「SUBSTR」と「INSTR」を使って、テキストとして保存されたIPアドレスを0詰めしてソートしてみる。
準備として、結果を見やすく設定。
sqlite> .header on sqlite> .mode column sqlite> .width 15
最初に、普通にソートした場合。
sqlite> SELECT ip FROM network ORDER BY ip;
ip
----------------
10.1.10.10
10.20.10.10
10.3.10.10
10.3.10.5
192.168.1.1
192.168.10.1
192.168.2.1
192.168.20.1
192.168.3.1
上記の通りになるので、各オクテットを0詰めして3桁にしてソートする事を考える。
0詰めする前に、まずはドットで区切る方法・・・
- 第一オクテットは、最初のドットまでを抽出する
- 第二オクテットだけを抽出するには、第一オクテットとドットを切り取った後、次のドットまでを抽出する
- 第三オクテットと第四オクテットも同様。
上記の考え方で、ドットで区切った第一～第四オクテットをカラムとして取得するSQL。
SELECT SUBSTR( ip, 0, INSTR(ip, '.') ) AS o1, SUBSTR( SUBSTR(ip, INSTR(ip, '.')+1), 0, INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.') ) AS o2, SUBSTR( SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), 0, INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.') ) AS o3, SUBSTR( SUBSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1), 0 ) AS o4 FROM network; o1 o2 o3 o4 ---------------- ---------- ---------- ---------- 192 168 1 1 192 168 10 1 192 168 2 1 192 168 20 1 192 168 3 1 10 1 10 10 10 20 10 10 10 3 10 10 10 3 10 5
各オクテットを0詰めするには、（LDAPが無いので）'00'という文字列を各オクテットに連結し、SUBSTR で下位3桁とドットを抽出する。
SELECT SUBSTR('00'|| SUBSTR( ip, 0, INSTR(ip, '.')+1 ), -4, 4 ) || SUBSTR('00'|| SUBSTR( SUBSTR(ip, INSTR(ip, '.')+1), 0, INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1 ), -4, 4 ) || SUBSTR('00'|| SUBSTR( SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), 0, INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1 ), -4, 4 ) || SUBSTR('00'|| SUBSTR( SUBSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), INSTR(SUBSTR(SUBSTR(ip, INSTR(ip, '.')+1), INSTR(SUBSTR(ip, INSTR(ip, '.')+1), '.')+1), '.')+1), 0+1 ), -3, 3 ) AS ip FROM network ORDER BY ip; ip ---------------- 010.001.010.010 010.003.010.005 010.003.010.010 010.020.010.010 192.168.001.001 192.168.002.001 192.168.003.001 192.168.010.001 192.168.020.001
SQL が長いし、可読性が悪い（´・ω・｀）
ユーザ定義関数も使わず、もっと簡単にソートできないものか・・・。
PostgreSQLでIPアドレスをソートする
PostgreSQL にて、IPアドレスをtext型に保存している場合、そのままソートしても意図したようにソートされない。
SELECT ip FROM nic ORDER BY ip;
ip
--------------
192.168.1.1
192.168.10.1
192.168.2.1
192.168.20.1
192.168.3.1
inet 型にCAST するだけ。
SELECT ip::inet FROM nic ORDER BY ip;
ip
--------------
192.168.1.1
192.168.2.1
192.168.3.1
192.168.10.1
192.168.20.1
参考：ネットワークアドレス型
http://www.postgresql.jp/document/8.4/html/datatype-net-types.html
という簡単な事なんだけど、以前に0詰めする方法書いてた。
shobon.hatenablog.com
何故0詰めしたのか・・・CSVに出力した後Excelでソートする時を考えての事だったか・・・
・・・覚えてない（´・ω・｀）
UserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する
PostgreSQLにて、DBに保存されたUserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する。
- ロボットの事は考慮していない。
- ChromeのUserAgentには、「Safari」という文字列が含まれるので、Safariより先に判定する。
- 「MSIE」は「IE 10」まで、「IE 11」は「MSIE」が削除されて「Trident」と「rv:11.0」と表示される。
- 互換表示の場合、IE10：MSIE 7.0 と Trident/6.0 のように出てくるので、Tridentだけ判定して、「rv」があれば「rv」までを抽出。
- Tridentとrevの間に、「Touch」「MALNJS」「MAFSJS」などが入っているものがあるが、そのまま抽出。
- Mac OS Xのバージョンの表示は、FireFoxの場合「10.11」、その他の場合「10_11」と表記が異なる。
- 期限切れの「Windows NT 5.1」=「Windows XP」が含まれているが、テストなので気にしない。
例えば、logテーブルのuser_agentというカラムにUserAgent情報が保存されている場合に、OSとブラウザの種類・バージョンをグループ化して件数を調べる。
db_name=> SELECT COUNT(*), CASE WHEN user_agent LIKE '%Macintosh%' THEN SUBSTRING(user_agent from E'\(Mac OS[^)|;]*\)') WHEN user_agent LIKE '%iPad%' THEN SUBSTRING(user_agent from E'\(iPad[^)]*\)') WHEN user_agent LIKE '%iPhone%' THEN SUBSTRING(user_agent from E'\(iPhone[^)]*\)') WHEN user_agent LIKE '%Linux%' THEN SUBSTRING(user_agent from E'\(X11[^)]*\)') WHEN user_agent LIKE '%Windows NT%' THEN SUBSTRING(user_agent from E'\(Windows NT [0-9.]+\)[)|;].*') ELSE user_agent END AS os, CASE WHEN user_agent LIKE '%Edge%' THEN SUBSTRING(user_agent from E'\(Edge/[0-9.]+\)') WHEN user_agent LIKE '%Sleipnir%' THEN SUBSTRING(user_agent from E'\(Sleipnir/[0-9.]+\)') WHEN user_agent LIKE '%OPR%' THEN SUBSTRING(user_agent from E'\(OPR/[0-9.]+\)') WHEN user_agent LIKE '%Vivaldi%' THEN SUBSTRING(user_agent from E'\(Vivaldi/[0-9.]+\)') WHEN user_agent LIKE '%Firefox%' THEN SUBSTRING(user_agent from E'\(Firefox/[0-9.]+\)') WHEN user_agent LIKE '%Chrome%' THEN SUBSTRING(user_agent from E'\(Chrome/[0-9.]+\)') WHEN user_agent LIKE '%Trident%' THEN SUBSTRING(user_agent from E'\(Trident/[0-9.]+;[^)]*\)') WHEN user_agent LIKE '%MSIE%' THEN SUBSTRING(user_agent from E'\(MSIE [0-9.]+\)') WHEN user_agent LIKE '%Safari%' THEN SUBSTRING(user_agent from E'\(Version/[0-9.]+ .*Safari/[0-9.]+\)') ELSE user_agent END AS browser FROM log GROUP BY os, browser ORDER BY os, browser;
次のような結果になる。
count | os | browser
-------+-------------------------------------+-------------------------------------
2 | Mac OS X 10.11 | Firefox/46.0
1 | Mac OS X 10_11_5 | Chrome/50.0.2661.102
10 | Mac OS X 10_11_5 | Version/9.1.1 Safari/601.6.17
9 | Windows NT 10.0 | Chrome/50.0.2661.102
19 | Windows NT 10.0 | Edge/13.10586
7 | Windows NT 10.0 | Firefox/46.0
1 | Windows NT 10.0 | Sleipnir/6.1.11
9 | Windows NT 10.0 | Trident/7.0; rv:11.0
1 | Windows NT 5.1 | Chrome/49.0.2623.112
1 | Windows NT 5.1 | Firefox/46.0
2 | Windows NT 6.0 | Chrome/49.0.2623.112
4 | Windows NT 6.1 | Chrome/52.0.2743.10
17 | Windows NT 6.1 | Firefox/46.0
1 | Windows NT 6.1 | OPR/37.0.2178.43
1 | Windows NT 6.1 | Trident/7.0; MAFSJS; rv:11.0
22 | Windows NT 6.1 | Trident/7.0; rv:11.0
1 | Windows NT 6.1 | Vivaldi/1.1.453.59
2 | Windows NT 6.3 | Chrome/51.0.2704.63
5 | Windows NT 6.3 | Firefox/46.0
1 | Windows NT 6.3 | OPR/37.0.2178.43
1 | Windows NT 6.3 | Trident/7.0; MALNJS; rv:11.0
3 | Windows NT 6.3 | Trident/7.0; Touch; rv:11.0
4 | Windows NT 6.3 | Trident/7.0; rv:11.0
1 | X11; Linux x86_64; rv:31.0 | Firefox/31.0
1 | X11; Ubuntu; Linux x86_64; rv:46.0 | Firefox/46.0
