出演声優から作品を探したい人のためのSQL例の実行結果

SQLの説明

好きな声優から出演作品を探したい人のためのSQLの例です。 この例では、「水瀬沙季」「岩泉まい」「中家志穂」が出演する作品を 以下の条件で探し出しています。

・上記3人のうち、少なくとも1人は出演している。
・レビュー数は10件以上、200件以内。
・最近3年以内に発売されている。

これら条件に該当する作品を対象に 「水瀬沙季」「岩泉まい」「中家志穂」にそれぞれ 6, 3, 1 の重みづけを行い、 それにレビュー平均点を乗じたものを得点として並べています。 適当にコピーして改造してください。

【改造ポイント】
・声優のIDを変更する。
・切り捨て(対象となる作品の絞り込み)条件を変更する。
・得点の算出方法を調整する。

検索結果

実行したSQL

      SELECT
	G.game AS "タイトル"
	, G.brand AS "ブランド"
	, G.cv_1 AS "水瀬沙季"
	, G.cv_2 AS "岩泉まい"
	, G.cv_3 AS "中家志穂"
	, G.score AS "得点"
	, '<span class="'||CASE WHEN G.mid >= 80 THEN 'lv5' WHEN G.mid >= 75 THEN 'lv4' WHEN G.mid >= 65 THEN 'lv3' WHEN G.mid >= 50 THEN 'lv2' ELSE 'lv1' END||'">'||G.mid||'</span>' AS "中央値"
	, '<span class="'||CASE WHEN G.avg >= 80 THEN 'lv5' WHEN G.avg >= 75 THEN 'lv4' WHEN G.avg >= 65 THEN 'lv3' WHEN G.avg >= 50 THEN 'lv2' ELSE 'lv1' END||'">'||G.avg||'</span>' AS "平均値"
	, G.cnt AS "レビュー数"
	, G.sellday AS "発売日"
FROM(
	SELECT
		'<a href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||G0.id||'">'||G0.gamename||'</a>' AS game
		, '<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||BL.id||'">'||BL.brandname||'</a>' AS brand
		, '<center>'||CASE COALESCE(G0.cv_1, 0) WHEN 1 THEN '○' ELSE '-' END||'</center>' AS cv_1
		, '<center>'||CASE COALESCE(G0.cv_2, 0) WHEN 1 THEN '○' ELSE '-' END||'</center>' AS cv_2
		, '<center>'||CASE COALESCE(G0.cv_3, 0) WHEN 1 THEN '○' ELSE '-' END||'</center>' AS cv_3
		-- 【改造ポイント】スコアの計算方法を調整する。
		-- スコア計算 : 水瀬沙季、岩泉まい、中家志穂にそれぞれ 6, 3, 1 の重みづけ
		, FLOOR((COALESCE(G0.cv_1, 0) * 0.6 + COALESCE(G0.cv_2, 0) * 0.3 + COALESCE(G0.cv_3 * 0.1, 0)) * G1.AVG) as score
		, G0.median AS mid
		, FLOOR(G1.AVG) AS avg
		, G1.CNT AS cnt
		, G0.sellday AS sellday
	FROM
		-- ゲーム情報 + お気に入り声優出演フラグ
		(
			SELECT
				GL.*
				, CV_1.flag AS cv_1
				, CV_2.flag AS cv_2
				, CV_3.flag AS cv_3
			FROM
				gamelist AS GL
				-- 【改造ポイント】"SS.creater = nnnn" の数値を変更する。
				-- 水瀬沙季
				LEFT OUTER JOIN (SELECT SS.game, 1 AS flag FROM shokushu AS SS WHERE SS.creater = 8705) AS CV_1 ON GL.id = CV_1.game
				-- 岩泉まい
				LEFT OUTER JOIN (SELECT SS.game, 1 AS flag FROM shokushu AS SS WHERE SS.creater = 5978) AS CV_2 ON GL.id = CV_2.game
				-- 中家志穂
				LEFT OUTER JOIN (SELECT SS.game, 1 AS flag FROM shokushu AS SS WHERE SS.creater = 5999) AS CV_3 ON GL.id = CV_3.game
			WHERE
				CV_2.flag = 1 OR CV_1.flag = 1 OR CV_3.flag = 1
			ORDER BY
				GL.sellday DESC
		) AS G0
	
		-- ブランド情報
		LEFT OUTER JOIN brandlist AS BL ON G0.brandname = BL.id
	
		-- レビュー情報
		LEFT OUTER JOIN (
			SELECT
				UR.game AS id
				, AVG(UR.tokuten) AS AVG
				, COUNT(*) AS CNT
			FROM
				userreview AS UR
			GROUP BY
				UR.game
		) AS G1 ON G0.id = G1.id
	-- 【改造ポイント】切り捨て条件を変更する。
	-- 切り捨て条件 : 平均60以上、レビュアーが10-200人、発売から3年以内
	WHERE
		G1.AVG > 60
		AND G1.CNT BETWEEN 10 AND 200
		AND G0.sellday > now() + '-3 years'
	ORDER BY
		score DESC
) AS G