--------(--)

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

2011-10-23(Sun)

Postgesqlでランキングしてみる

ranking.sqlファイルを作成する。

CREATE TABLE ranking (
id integer NOT NULL,
name character varying(128) NOT NULL,
score integer NOT NULL
);


ALTER TABLE public.ranking OWNER TO postgres;

CREATE SEQUENCE ranking_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;


ALTER TABLE public.ranking_id_seq OWNER TO postgres;
ALTER SEQUENCE ranking_id_seq OWNED BY ranking.id;

SELECT pg_catalog.setval('ranking_id_seq', 1, false);

ALTER TABLE ranking ALTER COLUMN id SET DEFAULT nextval('ranking_id_seq'::regclass);
ALTER TABLE ONLY ranking ADD CONSTRAINT "ranking_pkey" PRIMARY KEY (id);

createdb testdbというDBを作成しこのファイルを読み込ませる。


$ createdb testdb
$ psql -f ranking.sql testdb
確認してみる
$ psql testdb
testdb=# \d ranking
Table "public.ranking"
Column | Type | Modifiers
--------+------------------------+------------------------------------------------------
id | integer | not null default nextval('ranking_id_seq'::regclass)
name | character varying(128) | not null
score | integer | not null
Indexes:
"ranking_pkey" PRIMARY KEY, btree (id)


データを入れてみる。

testdb=# insert into ranking (name, score) values ('taro', 8);
testdb=# insert into ranking (name, score) values ('hanako', 3);
testdb=# insert into ranking (name, score) values ('nozomi', 6);
testdb=# insert into ranking (name, score) values ('katsu', 2);
testdb=# insert into ranking (name, score) values ('mika', 2);
testdb=# insert into ranking (name, score) values ('jun', 1);
testdb=# insert into ranking (name, score) values ('tetsu', 1);
testdb=# insert into ranking (name, score) values ('misato', 4);
testdb=# insert into ranking (name, score) values ('tomoe', 3);


内容を確認してみる。

testdb=# select * from ranking;
id | name | score
----+--------+-------
1 | taro | 8
2 | hanako | 3
3 | nozomi | 6
5 | mika | 2
6 | jun | 1
7 | tetsu | 1
8 | misato | 4
9 | tomoe | 3
4 | katsu | 2
(9 rows)


ランキング表のクエリーを使ってみる。

SELECT
r1.name,
r1.score,
(SELECT count(r2.score)
FROM ranking as r2
WHERE r2.score>r1.score)+1 as rank
FROM ranking as r1
ORDER BY r1.score DESC;

結果は以下の通り。
testdb=# SELECT
testdb-# r1.name,
testdb-# r1.score,
testdb-# (SELECT count(r2.score)
testdb(# FROM ranking as r2
testdb(# WHERE r2.score>r1.score)+1 as rank
testdb-# FROM ranking as r1
testdb-# ORDER BY r1.score DESC;
name | score | rank
--------+-------+------
taro | 8 | 1
nozomi | 6 | 2
misato | 4 | 3
tomoe | 3 | 4
hanako | 3 | 4
katsu | 2 | 6
mika | 2 | 6
tetsu | 1 | 8
jun | 1 | 8
(9 rows)


特定のメンバーのランキングは以下の通りとなる。

testdb=# SELECT
r1.name,
r1.score,
(SELECT count(r2.score)
FROM ranking as r2
WHERE r2.score>r1.score)+1 as rank
FROM ranking as r1 WHERE name = 'jun';
name | score | rank
------+-------+------
jun | 1 | 8
(1 row)

参考資料:
PostgreSQL完全機能リファレンス―実行例を通して「理解」を深める。
新標準PostgreSQL (オープンソースRDBMSシリーズ)
関連記事
スポンサーサイト

コメントの投稿

管理者にだけ表示を許可する

コメント

プロフィール

kumagonjp2

Author:kumagonjp2
Python,Django,R,Mongo,MySQL,Struts,Spring,データマイニングなどサーバー関係のメモを残していきます。

最新記事
最新コメント
最新トラックバック
月別アーカイブ
カテゴリ
雪が3Dで降るブログパーツ ver2

マウスで見る方向変えられます

検索フォーム
RSSリンクの表示
リンク
ブロとも申請フォーム

この人とブロともになる

QRコード
QR
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。