[pgsql-jp: 35280] Re: １件ずつ×４＋ランダム２件 のような柔軟な取得方法について

[Atsushi Odagiri]

小田切です。
これでいけますかね。

SELECT id, shop, name, comment
FROM
	((SELECT main.id, main.shop, main.name, main.comment,
		CASE main.shop
			WHEN '大阪' THEN 0
			WHEN '東京' THEN 1
			WHEN '名古屋' THEN 2
			WHEN '福岡' THEN 3
		END AS shop_index
	FROM "Table" main
	JOIN (
		SELECT 
			max(t.id) AS max_id,
			t.shop
		FROM
			"Table" t
		GROUP BY t.shop) sub
	ON
		main.id = sub.max_id
		AND main.shop = sub.shop
	WHERE
		main.shop IN ('大阪', '東京', '名古屋', '福岡')
	ORDER BY shop_index)
	UNION
	(SELECT id, shop, name, comment, random() + 3 AS shop_index
	FROM "Table" t
	WHERE
		t.shop NOT IN ('大阪', '東京', '名古屋', '福岡')
	ORDER BY shop_index limit 2)) as x
ORDER BY shop_index

At 2005/04/18 15:59:47 takada wrote:
> はじめまして、高田と申します。
> 
> 【環境】
> PHP 4.2.3
> PostgreSQL 7.2.2(EUC-JP)
> 
> ある会社が４つの店舗を運営していて、各店舗の取扱商品を次のように登録していま
> す
> 
>    id     |   shop   |   name   | comment
> ----------+----------+----------+----------
>  10001    | 大阪     |   goods1 |    text
>  10002    | 東京     |   goods2 |    text
>  10003    | 名古屋   |   goods3 |    text
>  10004    | 福岡     |   goods4 |    text
>  10005    | 東京     |   goods5 |    text
> 
> Idはシーケンスで管理しています。
> このようなテーブルから「大阪の取扱商品で最新（Idが最大）のものを１件、東京で
> 最新のものを１件、名古屋で最新のものを１件、福岡で最新のものを１件、残りの商
> 品からランダムに２件を取得（取得順もこの通り）」して計６個の商品をリストアッ
> プしたいのですが、これを実現するために以下のようなSQLを使用しました。
> 
> (select * from Table where shop = '大阪' order by id desc limit 1) union
> (select * from Table where shop = '東京' order by id desc limit
> 1)	union
> (select * from Table where shop = '名古屋' order by id desc limit 1) union
> (select * from Table where shop = '福岡' order by id desc limit
> 1)	union
> (select * from Table order by random() limit 2)
> 
> しかし、これでは取得順に問題があるほか、取得件数が変動する（重複行排除の関
> 係？）ため以下のようにしました。
> 
> (
> 	(select * from Table where shop = '大阪' order by id desc limit 1)
> union
> 	(select * from Table where shop = '東京' order by id desc limit 1)
> union
> 	(select * from Table where shop = '名古屋' order by id desc limit 1)
> union
> 	(select * from Table where shop = '福岡' order by id desc limit 1)
> 
> )
> union all
> (
> 	select * from Table where id not in (
> 	(select * from Table where shop = '大阪' order by id desc limit 1)
> union
> 	(select * from Table where shop = '東京' order by id desc limit 1)
> union
> 	(select * from Table where shop = '名古屋' order by id desc limit 1)
> union
> 	(select * from Table where shop = '福岡' order by id desc limit 1)
> 
> 	) order by random()
> )
> limit 6
> 
> これで期待通りの動作になったのですが、この通りSQLが非常に長くなってしまいま
> したので負荷が大変気になっています。実際には同一ページ内で似たようなSQLを３
> 回実行するため、より深刻になっています。
> このような動作をもう少し軽いSQLにて実現することはできませんでしょうか。
> ご教示の程、宜しくお願い致します。
> 
> 
> 
> 
> 
> 
/*
日本システム開発株式会社 第二事業部 IS部
小田切 篤 <Atsushi Odagiri>
email:a.odagiri ＠ nskint.co.jp
*/