SQL | You Look Too Cool

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

You Look Too Cool

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

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

SQLで順位を付ける方法

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

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

タグ

カテゴリー

最近の投稿

最近のコメント

固定ページ

アーカイブ

stabucky

写真

メタ情報