@fugaco

2つのテーブルを横にunionする方法(PostgreSQL)

男性の一覧テーブルと、女性の一覧テーブルがあったときに、ランダムに男女のペアを作るSQLを考えました。(本当はもっと業務用のデータで、同じようなことが必要だったんです。念のため。)


男性のテーブルと、女性のテーブルの構成はこうなっています。IDと名前だけ。

-- 男性リスト
create table men (
id integer not null,
name character varying(256)
);

-- 女性リスト
create table women (
id integer not null,
name character varying(256)
);

データには男性を1人多く入れておきましょう。1人余っちゃうけど、ごめんね。

-- 男性データ
insert into men (id, name) values
(11, 'John'),
(12, 'Jack'),
(13, 'Dan'),
(14, 'Ken'),
(15, 'Tom');

-- 女性データ
insert into women (id, name) values
(21, 'Sue'),
(22, 'Jane'),
(23, 'Beth'),
(24, 'Maria');

この2つのテーブルを普通にunionすると、縦一列に並んでしまい、男女のペアが作れません。

select * from men
union
select * from women;

---

id;name
11;"John"
12;"Jack"
13;"Dan"
14;"Ken"
15;"Tom"
21;"Sue"
22;"Jane"
23;"Beth"
24;"Maria"

cross joinすると2つのテーブルの直積になってしまい、全員浮気し放題です。

select * from men, women;

-----

id;name;id;name;
11;"John";21;"Sue"
11;"John";22;"Jane"
11;"John";23;"Beth"
11;"John";24;"Maria"
12;"Jack";21;"Sue"
12;"Jack";22;"Jane"
12;"Jack";23;"Beth"
12;"Jack";24;"Maria"
13;"Dan";21;"Sue"
13;"Dan";22;"Jane"
13;"Dan";23;"Beth"
13;"Dan";24;"Maria"
14;"Ken";21;"Sue"
14;"Ken";22;"Jane"
14;"Ken";23;"Beth"
14;"Ken";24;"Maria"
15;"Tom";21;"Sue"
15;"Tom";22;"Jane"
15;"Tom";23;"Beth"
15;"Tom";24;"Maria"

こういうときは、行番号を使ってjoinすると良いようです。スピードはちょっと遅くなりますが、ペアを作るためなので仕方ない。

-- カップル作成SQL
select
*
from (
select *, row_number() over(order by random()) as rownumber
from men) m
join (
select *, row_number() over(order by random()) as rownumber
from women) w
on w.rownumber = m.rownumber;

-----

id;name;rownumber;id;name;rownumber
11;"John";1;23;"Beth";1
12;"Jack";2;21;"Sue";2
14;"Ken";3;22;"Jane";3
15;"Tom";4;24;"Maria";4

ここでのポイントは、「row_number() over()」を使っているところです。男性テーブルと女性テーブルでそれぞれ、条件なしのselect結果の各行に、行番号のカラム(as rownumber)を追加しています。これは1から順に振られるので、このカラムを使って2つのテーブルをつなげることができます。

「over()」の中には、行番号を付ける際の条件を書くことができます。ここでは「order by random()」とランダムにソートして行番号を付与しています。おかげで運の悪い男性が1人、あぶれています。

おしまい。

脆弱性のあるPHPの普及率が高すぎる件

W3Techsという調査会社のデータを元に、脆弱性のあるPHPの普及率をまとめた記事があったので紹介します。

PHP Install Statistics
http://blog.ircmaxell.com/2014/12/php-install-statistics.html



PHPはバージョンによって、脆弱性が発見されているものと、そうでないものがあります。使用しているPHPがセキュアでなかったら、すぐにでも上の(マイナー)バージョンに更新しましょう。


バージョン 普及率(%) セキュア?
5.6.4 0.0268 Yes
5.6.3 0.1164 No
5.6.2 0.1272 No
5.6.1 0.0208 No
5.6.0 0.1088 No
5.5.20 0.216 Yes
5.5.19 0.852 No
5.5.18 0.852 No
5.5.17 0.336 No
5.5.16 0.27 No
5.5.15 0.198 No
5.5.14 0.174 No
5.5.13 0.108 No
5.5.12 0.162 Yes
5.5.11 0.174 No
5.5.10 0.096 No
5.5.9 1.818 Yes
5.5.8 0.192 No
5.5.7 0.084 No
5.5.6 0.066 No
5.5.5 0.036 No
5.5.4 0.03 No
5.5.3 0.258 No
5.5.2 0.006 No
5.5.1 0.036 No
5.5.0 0.036 No
5.4.36 0.2376 Yes
5.4.35 5.4912 No
5.4.34 4.5936 No
5.4.33 1.7688 No
5.4.32 1.452 No
5.4.31 1.1616 No
5.4.30 1.2144 No
5.4.29 0.924 No
5.4.28 0.8184 No
5.4.27 0.9504 No
5.4.26 0.7656 No
5.4.25 0.5016 No
5.4.24 0.7128 No
5.4.23 0.5016 No
5.4.22 0.3432 No
5.4.21 0.2904 No
5.4.20 0.3168 No
5.4.19 0.264 No
5.4.18 0.0792 No
5.4.17 0.3168 No
5.4.16 0.4224 Yes
5.4.15 0.0792 No
5.4.14 0.1584 No
5.4.13 0.0792 No
5.4.12 0.1056 No
5.4.11 0.1056 No
5.4.10 0.0528 No
5.4.9 0.1848 No
5.4.8 0.0528 No
5.4.7 0.1056 No
5.4.6 0.1848 No
5.4.5 0.0264 No
5.4.4 2.1384 Yes
5.4.3 0.0264 No
5.4.2 0 No
5.4.1 0 No
5.4.0 0.0264 No
5.3.29 10.8783 No
5.3.28 7.1145 No
5.3.27 3.3048 No
5.3.26 1.1016 No
5.3.25 0.4131 No
5.3.24 0.459 No
5.3.23 0.6885 No
5.3.22 0.2754 No
5.3.21 0.3672 No
5.3.20 0.2754 No
5.3.19 0.3672 No
5.3.18 0.4131 No
5.3.17 0.459 No
5.3.16 0.2295 No
5.3.15 0.4131 No
5.3.14 0.2754 No
5.3.13 1.4688 No
5.3.12 0 No
5.3.11 0 No
5.3.10 4.131 Yes
5.3.9 0.1377 No
5.3.8 0.6885 No
5.3.7 0 No
5.3.6 0.5508 No
5.3.5 0.3213 No
5.3.4 0.0459 No
5.3.3 10.3734 Yes
5.3.2 1.0557 Yes
5.3.1 0.0459 No
5.3.0 0.0459 No
5.2.x 20.1 No
5.1.6 1.1376 Yes
5.1.5 0.0024 No
5.1.4 0.0168 No
5.1.3 0 No
5.1.2 0.0372 No
5.1.1 0.0048 No
5.1.0 0 No
続きを読む "脆弱性のあるPHPの普及率が高すぎる件"

リーダブルコードを(いまさら)読んでみました



正直、この歳にもなるとそんなに目新しいことはなかったのですが、いくつかピックアップしたいと思います。


results = Database.all_objects.filter("year <= 2011")

p.30

「filter」だと、「2011年以下のもの」を「抜き出している」のか、「除外している」のかが分かりにくいので、「select」や「exclude」などにした方がいいという話です。個人的にはjQueryのfilterに慣れてしまっているので、「抜き出している」一択ですが、必ずしもそうではないんですね。

限界値(以上、以下)にはminとmaxを使う
範囲(以上〜以下)にはfirstとlastを使う
範囲(以上〜未満)にはbeginとendを使う


p.31〜p.33

限界値の例だと、「パスワードの文字数を4〜8文字にしてください」などが思いつきますね。minとmaxは私もよく使っています。しっくり来ます。

if ($password < MIN_PASS_LENGTH) {
// 入力エラー:短すぎるよ
}
else if ($password > MAX_PASS_LENGTH) {
// 入力エラー:長すぎるよ
}

両端を含めた範囲がstartとlastというのもよくあるパターンだと思います。

print_numbers($first = 1, $last = 10, $step = 1);
// 1, 2, ..., 10 が出力されるようだ

でも、endだと終端を含まないっていうのはどうでしょう。人それぞれな気がします。

News.find({start: '2014/01/01', end: '2015/01/01'});
// 2014/01/01 00:00:00:0000 - 2014/12/31 59:59:59:9999 らしい

少し話は逸れますが、日付の終了日の検索って面倒ですよね。ユーザーが終了日に「1/31」を選んだら、それに1を加算して「2/1未満のもの」という条件式を作らないといけないんですもん(時分もデータに持つ場合)。


定数にコメントをつける

p.62

その限界値を決めた経緯を書いておくといい、だそうです。たしかに。

// 合理的な限界値。人間はこんなに読めない。
MAX_RSS_SUBSCRIPTIONS = 1000;

これなら1200にしても大して問題ないように思えます。逆に500にしたら少なすぎるかもしれません。

// ユーザー数が最大のXXX人まで登録された場合、一人当たり使用できるデータベース容量の限度がこれ
MAX_RSS_SUBSCRIPTIONS = 1000;

なーんて書かれていたら1200に変更するのもはばかられますよね(全員が全員MAXまで使うとは限りませんが…)。


関数から早く返す

p.91

結果がわかっている段階でさっさとreturnしようということです。

// 文字列$stringが文字列$substringを含んでいるか
// どうかのbool値を返す関数
function Contains($string, $substring) {
if ($string === null || $substring === null) {
return false;
}
if ($substring === "") {
return false;
}
// ...
}

私もこういう書き方をよくやります。一種の入力値チェックというか、フィルター的な感じです。

逆に以下のような書き方はあまり良くないと。

function Contains($string, $substring) {
$ret = false;
if ($string !== null && $substring !== null) {
if ($substring !== "") {
if (/* trueになる条件 */) {
$ret = true;
}
}
}
return $ret;
}

でも最後に必ず同じ処理をしたいことがよくあって(ファイルクローズとか、ビューへのデータ渡しとか)、そういうときは後者のように大きくifで囲っちゃうときが多いです。PHPってデストラクタとか無いし。いわゆるクリーンアップコードをどうするかですね。

ちなみにgoto文はスパゲッティコードを生成する悪しき仕様ですが、後方のクリーンアップコードに飛ばす用途にだけは使ってもOKだそうです。そういえば以前InstallShieldでWindowsのインストーラを作った時に、goto finishみたいなコードを頻繁に書きましたよ。


ド・モルガンの法則を使う

p.101

これ懐かしいですね。論理演算式(&&とか||とか)の書き換えです。

!(A || B) = !A && !B
not a and not b
via wikipedia

!(A && B) = !A || !B
not a or not b
via wikipedia

これを使ってif文をシンプルに書き直すといいと思います

// システム管理者または機能管理者でメールが設定されている以外ならば
if (!((is_system_admin || is_func_admin) && has_email)) {
// エラー:統計情報を送ることができません
}

// システム管理者でも機能管理者でもないか、メールが設定されていなければ
if (!is_system_admin && !is_func_admin || !has_email) {
// エラー:統計情報を送ることができません
}

若い子のプロジェクトを引き継いだりすると、けっこう前者のような読みにくい条件式を見かけます。こういうの数学でやらなかったのかな。

ABA AND BA OR BA XOR Bなどなど
TRUETRUETRUETRUEFALSE
TRUEFALSEFALSETRUETRUE
FALSETRUEFALSETRUETRUE
FALSEFALSEFALSEFALSEEFALSE


数理論理学は、誰が嘘ついているかとかを式で書いて計算できるおもしろい分野ですよね。

実例

p.197

本の後半では実際にコードをリファクタリングして、コード数や計算量、メモリ使用量などが比較されていて興味深いです

書籍の紹介


「この本を読み終わったら、次はこれを読むといい」って書いてあるのはいいですよね。モチベーションを保ったまま次へ行けますし。
ここでは10冊紹介されていました。

高品質のコードを書くための書籍




何年もほしいものリストに入っているやつではないですか。そこかしこでお勧めされていますが、実際に手に取ったことはないです。



これもずっとほしいものリストに入ってます…。買ったらリファクタリングしたくなっちゃう(本来の仕事が捗らなくなっちゃう)と思って買ってないんですが、新しく書くコードにも役立つことがありそうなので、次の注文で買ってみようと思います。



初めて見ました。立ち読みしてみないと何とも言えないです。



これ持っています!プログラマーの自分に喝を入れようとして昔買いました。「毎年新しい言語を学ぼう」みたいなことが書いてあって、自己啓発本になった気がします。今読み返したら また新しい発見がありそうです。随所でお勧めされていますよね。



これもときどき見かけます。ケーススタディが多い本みたいです。業務から学べるといいんですけど、なかなかそう恵まれた人ばかりじゃないですよね。

プログラミングに関する書籍




これはとってもお勧めです。JavaScriptの見落としがちな仕様を確認することができます。これを読んで何が勉強になったかをブログに書いたのですが、もう4年も前なんですね。



Javaの本だけど他の言語にも使えることが多くて著者のお勧めらしです。でも当面読む予定はないかな。ごめんなさい。



パターン本の原典だそうです。







なぜこれが紹介されているのかは、本書の「読みやすいコードは 何も書かれていないコードである」という主張と、この本の「コード量が少なければ読み込みは早い」という主張が通じる っていうことらしいです。それ以外にもパフォーマンスを上げる方法がたくさん書かれているのでお勧めで。私が読んだ感想はこちらです


-----
先日列挙したオライリー本のうち、本書を合わせて2冊読み終わりました。今もう2冊を読み途中です。片方はタウンページのような厚さですが(笑)。次に買う本も決まっているので、また感想を書こうと思います

数独も解けるAlgorithm Xとは

数独って知っていますか。

sudoku

同じ数字が縦、横、ブロック内で被らないように1~9の数字を埋めていくパズルです。学生時代、懸賞目当てにやってました。解き方は大体このまとめにある通りです。


で、最近こんな記事を読みました。

Solving a Sudoku with Discrete math
離散数学で数独を解く


Donald KnuthのAlgorithm Xというアルゴリズムを使うようです。クヌースってどっかで聞いた名前だと思ったら、TeXの人ではないですか。そういえばプログラミングの本も出していましたね。



xkcdのコミックでも取り上げられていました

そんな前置きは終わりにして、さっそくその手法を紹介します。


Dual Interpretation


Algorithm Xを使うためにはまず、Dual Interpretation(二元的解釈?)を作ります。これは列に制約(A, B, ...)、行に解の候補(1, 2, ...)を並べた二次元の表です。また、各制約を満たす解にはそれぞれ印を付けます。

ABCDEF
1
2
3
4
5
6
7


例えば、テトリスのようなピースで画面上のマスを全部敷き詰める問題を思い浮かべてください。その場合、上記のA~Fは各マスの番号(正確には「マスAにピースを埋めなければならない」という制約)で、1~7はピースの置き方のパターンです。これらのパターンからどの組み合わせを選べば、A~Fの条件すべてを重複せずに(ピースは重ねて置けないので)満たすかを考えます。


数独の場合


数独の場合、制約は以下のようになります。

使える数字の制約
  • 1. マス(1,1)には1から9までの数字のどれか一つしか入らない
  • 2. マス(1,2)には1から9までの数字のどれか一つしか入らない
  • ...
  • 81. マス(9,9)には1から9までの数字のどれか一つしか入らない

同じ列で数字は被らない制約
  • 1. 数字1は(1,1)から(1,9)までのどこかに入る
  • 2. 数字1は(2,1)から(2,9)までのどこかに入る
  • ...
  • 81. 数字9は(9,1)から(9,9)までのどこかに入る

同じ列で数字は被らない制約
  • 1. 数字1は(1,1)から(9,1)までのどこかに入る
  • 2. 数字1は(1,2)から(9,2)までのどこかに入る
  • ...
  • 81. 数字9は(1,9)から(9,9)までのどこかに入る

ボックス内で数字は被らない制約
  • 1. 数字1は1つ目のボックスのどこかに入る
  • 2. 数字1は2つ目のボックスのどこかに入る
  • ...
  • 81. 数字9は9つ目のボックスのどこかに入る

全部で324個の制約です。多w

これに対し解の候補は以下の729通りです。
  • 1. 数字1が(1,1)に入る
  • 2. 数字1が(1,2)に入る
  • ...
  • 729. 数字9が(9,9)に入る

各解はそれぞれ4つの制約を満たし、各制約にはそれぞれ9通りの解の選択肢があります(数独の問題で最初から数字が埋まっているところは、選択肢は1つです)。


ミニ数独の場合


実際の数独は表が大きすぎるので、2×2マスに集約してみます。ああ、簡単。

12
21

制約を列挙します。ボックスが無くなるので、4つ目の制約は除外します。

使える数字の制約
  1. マス(1,1)には1から2までの数字のどれか一つしか入らない
  2. マス(1,2)には1から2までの数字のどれか一つしか入らない
  3. マス(2,1)には1から2までの数字のどれか一つしか入らない
  4. マス(2,2)には1から2までの数字のどれか一つしか入らない

同じ列で数字は被らない制約
  1. 数字1は(1,1)から(1,2)までのどこかに入る
  2. 数字1は(2,1)から(2,2)までのどこかに入る
  3. 数字2は(1,1)から(1,2)までのどこかに入る
  4. 数字2は(2,1)から(2,2)までのどこかに入る

同じ列で数字は被らない制約
  1. 数字1は(1,1)から(2,1)までのどこかに入る
  2. 数字1は(1,2)から(2,2)までのどこかに入る
  3. 数字2は(1,1)から(2,1)までのどこかに入る
  4. 数字2は(1,2)から(2,2)までのどこかに入る

解の選択肢を列挙します。
  1. 数字1が(1,1)に入る
  2. 数字1が(1,2)に入る
  3. 数字1が(2,1)に入る
  4. 数字1が(2,2)に入る
  5. 数字2が(1,1)に入る
  6. 数字2が(1,2)に入る
  7. 数字2が(2,1)に入る
  8. 数字2が(2,2)に入る

これらを表にまとめます。

数字の制約行の制約列の制約
x in (1,1)x in (1,2)x in (2,1)x in (2,2)1in (1,x)1 in (2,x)2 in (1,x)2 in (2,x)1 in (x,1)1 in (x,2)2 in (x,1)2 in (x,2)
1 in (1,1)
1 in (1,2)
1 in (2,1)
1 in (2,2)
2 in (1,1)
2 in (1,2)
2 in (2,1)
2 in (2,2)


Algorithm X


Dual Interpretationを作れたので、次はこれを解く方法です。Algorithm Xの仕組みは以下のようになります。

続きを読む "数独も解けるAlgorithm Xとは"

WordPressのアーカイブウィジェットの投稿数を、現ページのカテゴリーIDで絞り込む方法

昨日の『WordPressのアーカイブウィジェットで年月を階層化し、クリックで開閉できるようにする方法(プラグイン無し)』の続きのようなものです。

WordPressのアーカイブウィジェットでは年や月ごとに投稿件数を表示することができます。

  • 2014 (123)
  • 2013 (45)

 

これをカスタマイズして、以下のようにする方法です。

  • 投稿数は、現在表示しているカテゴリー内の投稿数を表示する
  • リンク先では、そのカテゴリーの条件を引き継ぐ

 

こういったデフォルトのものをカスタマイズするときは、カスタマイズ用のフィルターやアクションをまず探します。

昨日の記事でも書いたように、アーカイブウィジェットでやっていることは wp_get_archives の結果を出力することです。wp_get_archivesの関数を定義している wp-includes/general-template.php を見てみると、「getarchives_where」、「getarchives_join」、「get_archives_link」というフィルターが見つかります。wp_get_archives の関数を使っている場所をすべて調べたところ、これらのフィルターを使っても悪影響が起きなさそうなので(少なくとも私のプロジェクトでは)、このフィルターを使うことにします。

(フィルターやアクションをオンラインの検索エンジンを使って探す方法を以前記事に書いたので、良ければそちらも参考にしてください。)

コメントによると、「getarchives_where」は検索時の条件句(WHERE句)を、「getarchives_join」は結合句(JOIN句)を編集できるようです。「get_archives_link」はリンクの編集ですね。というわけで、以下のようにカスタマイズしてみます。

 

function.php

// カテゴリーIDがある場合、カテゴリーで絞るフィルター
function customarchives_where ( $where_clause ){
// テーブル名を参照するために宣言しておく
global $wpdb;
// 現在のページのカテゴリーIDを取得する
// (URLで不正な値を設定されても、数字以外除外されている)
$catId = get_query_var('cat');
// カテゴリーIDがあったら
if ($catId > 0) {
// JOIN句のフィルターを使用するように宣言する
add_filter( 'getarchives_join', 'customarchives_join' );
// リンクのフィルターを使用するように宣言する
add_filter( 'get_archives_link', 'customarchives_link' );
// 元々のWHERE句にカテゴリーIDの条件を追加する
$where_clause .= " AND $wpdb->term_taxonomy.taxonomy = 'category'
AND $wpdb->term_taxonomy.term_id IN ($catId)";
}
return $where_clause;
}
add_filter( 'getarchives_where', 'customarchives_where' );

// カテゴリーで絞るときに必要なJOIN句のフィルター
function customarchives_join($join_clause) {
global $wpdb;
return $join_clause . "
INNER JOIN $wpdb->term_relationships
ON ($wpdb->posts.ID = $wpdb->term_relationships.object_id)
INNER JOIN $wpdb->term_taxonomy
ON ($wpdb->term_relationships.term_taxonomy_id = $wpdb->term_taxonomy.term_taxonomy_id)";
}

// カテゴリーで絞るときにURLにカテゴリーIDを追加するフィルター
function customarchives_link ( $link ){
$catId = get_query_var('cat');
if ($catId > 0) {
// 「?cat=xx」というURLパラメータを用意する
$cat_param = "cat=$catId";
// 多言語機能を使っているので、すでにURLに
// 「?lang=」が入っていないかチェックして、
// 「?」があれば「&cat=xx」になるようにする
if (strpos($link, '?') !== false) {
$cat_param = '&' . $cat_param;
}
else {
$cat_param = '?' . $cat_param;
}
// 力技ごりごり(笑)
// リンク先の部分に「?/&cat=xx」を追加する
$link = preg_replace('/<a href="\">(.*)<\/a>/', "</a><a href="$1$cat_param">$2</a>", $link);
}
return $link;
}

 

こんな感じです。

ちょくちょく出てきた get_query_var は何かと便利で、cat(カテゴリーID)以外のものも取得することができます。何が取得できるかはこちらで確認できます。

毎年年末になるとWordPressで作ったサイトの更新依頼が来るんですよね。
それではまた。


<参考>
http://blog.guido-handrick.info/2012/01/wordpress-archive-for-single-category/

1/40  | 次のページ »