リピーターのオススメ作品はこれかも知れない for アトリエかぐやの実行結果

SQLの説明

アトリエかぐや作品を多数プレイした人達を母集団として、平均点・偏差を集計。
全体との比較を行って平均点の差分が多かった順に一覧表示。
「全体の平均点より高い」→「ブランドリピーターが高く評価してる」・・かも知れない。

母集団は「アトリエかぐやの作品を半数以上プレイしてる人」
上記母集団の8割の投票があった作品のみ集計

⊿平均が高く、かつ⊿偏差が低い作品に要注目。

検索結果

実行したSQL

      SELECT
	GL.gamename AS "ゲーム名"
	, TO_CHAR(UR1.avg_score, '990D99') AS "平均"
	, TO_CHAR(UR1.avg_score - UR2.avg_score, '90D99') AS "⊿平均"
	, TO_CHAR(UR1.stddev_score, '990D99') AS "偏差"
	, TO_CHAR(UR1.stddev_score - UR2.stddev_score, '990D99') AS "⊿偏差"
FROM
	(
		SELECT
			UR.game AS game
			, COUNT(U2.*) AS filter_cnt
--			, MAX(UR.tokuten) AS max_score
--			, MIN(UR.tokuten) AS min_score
			, AVG(UR.tokuten) AS avg_score
			, stddev_pop(UR.tokuten) AS stddev_score
		FROM
			(
				SELECT
					U1.uid
				FROM
					(
						SELECT
							UR.uid
							, COUNT(*) AS cnt
						FROM
							userreview AS UR
							, gamelist AS GL
						WHERE
							GL.brandname = 413
							AND UR.game = GL.id
							AND UR.tokuten IS NOT NULL
						GROUP BY
							UR.uid
					) U1
				WHERE
					-- 閾値 : 対象ブランド作品を何割プレイしたユーザーを母集団とするか
					U1.cnt >= (SELECT COUNT(*) FROM gamelist AS GL WHERE GL.brandname = 413) * 0.5
			) U2
			, userreview AS UR
			, (SELECT id FROM gamelist WHERE brandname=413) AS GL
		WHERE
			U2.uid = UR.uid
			AND UR.game = GL.id
		GROUP BY
			UR.game
	) AS UR1
	, (
		SELECT
			UR.game AS game
			, COUNT(UR.*) AS filter_cnt
--			, MAX(UR.tokuten) AS max_score
--			, MIN(UR.tokuten) AS min_score
			, AVG(UR.tokuten) AS avg_score
			, stddev_pop(UR.tokuten) AS stddev_score
		FROM
			userreview AS UR
			, (SELECT id FROM gamelist WHERE brandname = 413) AS GL
		WHERE
			UR.game = GL.id
		GROUP BY
			UR.game
	) AS UR2
	, gamelist AS GL
WHERE
	UR1.game = GL.id
	AND UR1.game = UR2.game
	-- 閾値 : ユーザー母集団のうち何割が投票したゲームを評価するか
	AND UR1.filter_cnt >= (
							SELECT
								COUNT(*)
							FROM
								(
									SELECT
										UR.uid
										, COUNT(*) AS cnt
									FROM
										userreview AS UR
										, gamelist AS GL
									WHERE
										GL.brandname = 413
										AND UR.game = GL.id
										AND UR.tokuten IS NOT NULL
									GROUP BY
										UR.uid
								) U1
							WHERE
								U1.cnt >= (SELECT COUNT(*) FROM gamelist AS GL WHERE GL.brandname = 413) * 0.5
	) * 0.5
ORDER BY
	UR1.avg_score - UR2.avg_score DESC
	, UR1.stddev_score - UR2.stddev_score ASC
;