SQLでデータの不一致を探す

次のようなテーブル「height」があったとします。名前と身長のデータです。
Suzukiが二つ入力されています。データが同じならば問題はないかもしれませんが、身長が異なります。
SQLを使って、このようなデータの不一致を探す方法を紹介します。Access2007で確認しました。 (さらに…)

SQLで重複を除いてカウントする

Accessで次のようなテーブル「member」があったとします。

id name roomid weight date
1 Ito 1 60 2013/1/1
2 Abe 1 62 2013/1/1
3 Sato 2 61 2013/1/1
4 Eda 2 68 2013/1/1
5 Ito 1 65 2013/7/1

ある学校の体重の管理簿だと考えてください。
roomidが教室番号です。weightが体重で、dateがそれを量った日です。

このテーブルを使って教室毎の人数を数えてください。

単純に数えるには次のようになります。 (さらに…)

SQLで順位を付ける方法

次のようなテーブル「member」があったとします。

id name height
1 Ochiai 179
2 Tanaka 172
3 Sato 177
4 Suzuki 176
5 Inoue 175
6 Ueno 179
7 Endo 178
8 Ito 180
9 Yamamoto 175
10 Aoki 170

これに身長(height)の高い順に順位を付けます。Access2007で確認しました。

SQLは次の通りです。

SELECT (
    SELECT Count(member.id)
    FROM member
    WHERE member.height > member1.height
    ) + 1 AS rank, member.name, member.height
FROM member , member AS member1
WHERE member.id = member1.id
ORDER BY member.height DESC;

「member AS member1」として同じテーブル「member1」を作ります。
「SELECT Count(member.id) FROM member WHERE member.height > member1.height」として、自分より身長の高い者の数を数えています。これに1を加えることで順位になります。自分より身長の高い者がいない場合は0で、これに1を加えて「1」、一人いる場合は1で、これに1を加えて「2」となります。

結果は次の通りです。

rank name height
1 Ito 180
2 Ueno 179
2 Ochiai 179
4 Endo 178
5 Sato 177
6 Suzuki 176
7 Yamamoto 175
7 Inoue 175
9 Tanaka 172
10 Aoki 170

同じ身長の場合、同じ順位になっています。

WassUpで人気記事を取得するSQL

WordPressにはWassUpという、アクセス状況を調べることのできるプラグインがあります。
WassUpはアクセスのログをMySQLのテーブルに保存しているのでSQLを使えば簡単にアクセス状況を集計をすることができます。

そこで「30日間にアクセスの多かった記事」を取得するSQLを考えてみました。

SELECT post_title, count( urlrequested ) AS ct
FROM wp_wassup
JOIN wp_posts ON replace( urlrequested, "/wp/archives/", "" ) = wp_posts.id
WHERE instr( urlrequested, "archives" ) >0
AND instr( urlrequested, "category" ) =0
AND UNIX_TIMESTAMP( ) - timestamp <30 *24 *60 *60
GROUP BY urlrequested
ORDER BY count( urlrequested ) DESC
LIMIT 0 , 10

URLを「/wp/archives/3815」としている場合の例です。「wp」はWordPressのフォルダです。
ログは「wp_wassup」テーブルに保存されています。
記事は「wp_posts」テーブルに保存されているので、結合して記事のタイトルを取得します。
「30 *24 *60 *60」の「30」が日数です。

次が実行結果です。

post_title ct
ワンピースARカードダスのレアカード 7226
ワンピースARカードダス一覧 第3弾 6166
ワンピースARカードダス一覧 第2弾 4436
ワンピースARカードダス一覧 4196
伝説ポケモンのARマーカー 2243
GoogleカレンダーにCSVファイルから予定をインポートする方法 1936
ワンピースARカードダス、40枚中34枚を発見 1668
「ショットノート」を自作 1633
ポケモン立体図鑑ARマーカーリスト 1310
番号と日付がセットできる自作SHOT NOTE 1241

ワンピースとポケモンばかりだ。

WordPressのタイトルを取得するSQL

WordPressはMySQLで投稿データを管理しています。
そのためSQLを使って直に必要なデータを取り出すことができます。

投稿データのIDとタイトルを取り出す場合のSQLは次の通り。

SELECT id, post_title
FROM wp_posts
WHERE post_status = "publish"
ORDER BY id

WordPressは実際にブログ上に表示されるデータだけでなく下書きも保存されるので、ステータスが「publish」となっているものだけを抽出します。

IDを指定して投稿データを取り出す場合のSQLは次の通り。

SELECT id, post_title
FROM wp_posts
WHERE id
IN ( 3144, 3604, 3195 )
ORDER BY id

タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報