【MYSQL】【複数条件抽出】ORDER BYの後に「rand()」を含めた複数の条件指定の方法【PDO/PHP】

Question

現在、プログラムの勉強をしている者です。

勉強目的でサイトを作成して簡単なアクセス解析を作成中です。

登録しているブログの記事やURLをデータベースに登録して
登録しているブログからの当サイトへのIN数や
当サイトから登録しているブログへの送ったアウト数によって
登録しているブログの記事を紹介していくというサイトを勉強目的で作成しています。
（データベース登録・削除・更新等の操作があるため）

アクセス解析の中の
IN数・アウト数を集計する事に成功しましたので
登録しているブログを紹介している回数をプログラムで操作していきたいと思っているのですが
「複数の条件指定」の所で思い通りの操作が出来ずに壁にぶつかっています。

テーブル名：blog
カラム名：
id,
title,
url,
name,
date

説明：
id:登録ブログの記事ID
title:登録ブログの記事タイトル
url:登録ブログの記事URL
name:登録ブログ名
date:登録ブログの記事の更新日時

テーブル名：site
カラム名：
id,
name,
rssurl,
incount,
outcount,
kangen,
rsscount

説明：
id:登録ブログID
name:登録ブログ名
rssurl:登録ブログのURL
incount:インカウント数（送って貰えた値）
outcount:アウトカウント数（送った値）
kangen:登録ブログへの還元率・返還率の値が入ります。
rsscount:登録ブログを紹介した回数が入ります。

複数の条件を指定して「紹介する登録ブログ」を決定する為の
抽出を行います。

ドットインストールさんやネットで色々調べて自分なりに作成してみました。

条件：

テーブル名：siteのカラム名：henkan（還元率）の値が「2」以下でなおかつ
テーブル名：siteのカラム名：rsscount（紹介回数）の値が「10」未満でなおかつ
テーブル名：siteのカラム名：incount（登録ブログから送って貰えた値）の値の上位5位のブログでなおかつ
テーブル名：blogのカラム名：date（記事の更新日時）が「24時間」以内の登録しているブログの記事を
ランダムに混ぜて抽出します。

$stmt = $db->query("select blog.id,blog.title,blog.url,
blog.name
 from blog,site where
blog.name=site.name 
AND site.henkan <= 2
AND site.rsscount < 10
AND blog.date > CURRENT_TIMESTAMP + INTERVAL - 24 HOUR
 order by incount desc, rand()  limit 5");

実行結果：

テーブル名：siteのカラム名：henkan（還元率）の値が「2」以下でなおかつ
↓
成功しました。

テーブル名：siteのカラム名：rsscount（紹介回数）の値が「10」未満でなおかつ
↓
成功しました。

テーブル名：siteのカラム名：incount（登録ブログから送って貰えた値）の値の上位5位のブログでなおかつ
↓
失敗しました。
incountの上位5位ではなく
上位1位のみが選択され続けてしまいます。

テーブル名：blogのカラム名：date（記事の更新日時）が「24時間」以内の登録しているブログの記事を
ランダムに混ぜて抽出します。
↓
24時間以内の記事は成功しましたがランダムにシャッフルするのは失敗しました。
登録ブログの記事がシャッフルされず
必ず1件の記事が抽出されつづけています。

incountの上位5件の登録ブログの記事をランダムにシャッフルするという条件を無くしてみた所
正常にシャッフルして抽出する事が出来ました。

条件：

テーブル名：siteのカラム名：henkan（還元率）の値が「2」以下でなおかつ
テーブル名：siteのカラム名：rsscount（紹介回数）の値が「10」未満でなおかつ
テーブル名：blogのカラム名：date（記事の更新日時）が「24時間」以内の登録しているブログの記事を
ランダムに混ぜて１件のみを抽出します。

$stmt = $db->query("select blog.id,blog.title,blog.url,
blog.name
 from blog,site where
blog.name=site.name 
AND site.henkan <= 2
AND site.rsscount < 10
AND blog.date > CURRENT_TIMESTAMP + INTERVAL - 24 HOUR
 order by rand()  limit 1");

上の条件を削った、抽出条件では正常に抽出する事が出来ました。

---

テーブル名：siteのカラム名：henkan（還元率）の値が「2」以下でなおかつ
テーブル名：siteのカラム名：rsscount（紹介回数）の値が「10」未満でなおかつ
テーブル名：siteのカラム名：incount（登録ブログから送って貰えた値）の値の上位5位のブログでなおかつ
テーブル名：blogのカラム名：date（記事の更新日時）が「24時間」以内の登録しているブログの記事を
ランダムにシャッフルして抽出します。

この抽出する条件の

テーブル名：siteのカラム名：incount（登録ブログから送って貰えた値）の値の上位5位のブログでなおかつ
テーブル名：blogのカラム名：date（記事の更新日時）が「24時間」以内の登録しているブログの記事を
ランダムにシャッフルして抽出します。

この部分を解決する方法を
ネットで調べたり、ドットインストールさんで調べても
上手く抽出する事が出来ませんでした。

解決方法をご存知の方や
何かお気付きの点がある方がいらっしゃいましたら
お力をお貸し頂けると嬉しいです。

どうかよろしくお願いします。

Answer 1

order by rand()は使ったことがないので別の考え方を。
要は抽出したレコードに乱数を追加し、その乱数でソート、上位5件を取ってくれば良いのですよね。
次のようなSQLにしてはどうでしょうか（未検証）

select * from (
	select * from (
		select rand() as randnum, blog.id,blog.title,blog.url,blog.name
		from blog,site
		where blog.name=site.name 
			AND site.henkan <= 2
			AND site.rsscount < 10
			AND blog.date > CURRENT_TIMESTAMP + INTERVAL - 24 HOUR
	)
	order by randnum limit 5
) order by incount desc

追記
なんとなく自分なりに解釈して修正してみました。
考え方のコメント付き（順番通り見てください）
動作は未検証です。

select * from (
	--④抽出した各レコードに乱数を付加
	select rand() as randnum, * from (
		--①siteから上位5件を抽出
		select name from site
			where henkan <= 2 and rsscount < 10
			order by incount desc
			limit 5
		) as t1
		--②抽出した5件のsiteごとにblogレコードを結合
		left join blog as t2 on t1.name=t2.name
		--③blog記事を24時間以内に限定
		where t2.date > CURRENT_TIMESTAMP + INTERVAL - 24 HOUR
--⑤乱数順に並び変え
) order by randnum;

Comments

[shimane]

回答有難うございます！

今までずっと書籍やネットで勉強した「order by rand()」で試していたので
教えて頂いた「乱数作成」という技術が初めてのことで
「はっ」とさせられました。
まさにスキルで問題を乗り切っていく感じが私が想像するプログラム作成者のようで
わくわくしてきます！

さっそく教えて頂いた通りに試してみました所、エラーが表示されました。

エラー文：
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1248 Every derived table must have its own alias' in

日本語訳：
致命的なエラー： 'SQLSTATE [42000]：構文エラーまたはアクセス違反：1248のメッセージを含むキャッチされない例外' PDOException 'すべての派生テーブルには独自のエイリアスが必要です

現在は「独自のエイリアス」と「MYSQL」に関して勉強して
なんとかエラーが表示されないように頑張っているのですが恥ずかしながら私の技術力が追い付いていません。

エラー文言に対して
何かお気付きの点がありましたら教えて頂けると嬉しいです。

[ttyp03]

恐らくこれでしょう。
テーブルには独自のエイリアスが必要です
MySQLの場合サブクエリに別名が必要のようです。
こちらのサイトを参考に別名をつけてみてください。
https://dev.classmethod.jp/server-side/db/mysql_sub_query/

[shimane]

参考になるURLを教えて頂いて有難うございます！

私が見ていたサイトよりもずっと分かりやすくて
実際に試してみました！

＞参考URLにありました通り「サブクエリ」を追加してみました。（test1 test2）
select * from (
select * from (
select rand() as randnum, blog.id,blog.title,blog.url,blog.name
from blog,site
where blog.name=site.name
AND site.henkan <= 2
AND site.rsscount < 10
AND blog.date > CURRENT_TIMESTAMP + INTERVAL - 24 HOUR
)
as test1
order by randnum limit 5
) as test2
order by incount desc

実行結果：
エラー表示：
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'incount' in 'order clause''
日本語訳：
致命的なエラー： 'SQLSTATE [42S22]：列が見つかりません：1054不明な列' incount 'in' order clause ''というメッセージで「PDOException」がキャッチされました

「incount」というカラムが見つからないというエラーだと思いまして
「incount」を「site.incount」に変更してみた所、
同じように「site.incount」が見つからないといったエラーが表示されました。

「incount」を「site.henkan」と変更してみても同じように
「site.henkan」が見つからないというエラーになりました。

試しに「as test2 order by incount desc」の部分の「 order by incount desc」を削除して
実行した所、
エラーが表示されなくなりました。

当然ながら登録しているブログの「incount」の「上位5件」が選択されませんでした。

現在はこの「incount」をどうにかして認識させようと頑張っている所なのですが
上手くいきません、何かお気付きの点がありましたら
お力をお貸し頂けると嬉しいです。お願いします。

[ttyp03]

ああ、すんません。
一番深いselect文のカラムを列挙してるところにincountを追加してください。

[shimane]

有難うございます！

教えて頂いた通りに試してみました！

select * from (
select * from (
select rand() as randnum, 　incount,blog.id,blog.title,blog.url,blog.name
from blog,site
where blog.name=site.name
AND site.henkan <= 2
AND site.rsscount < 10
AND blog.date > CURRENT_TIMESTAMP + INTERVAL - 24 HOUR
)
as test1
order by randnum limit 5
) as test2
order by incount desc

実行結果：
エラーが表示されることが無くなりました。

エラーが表示されなくなったのですが、
「incount」の上位が表示されるのではなく
すべての登録ブログがランダムに表示されてしまっていました。

試しにカラム名の所の「incount」を「site.incount」に変更しても同じで

「order by incount desc」を
「order by incount asc」に変更してみても
incountの降順昇順に関係なく、すべての登録ブログがランダムに表示されていました。

私の方でもなんとか解決しようとあれこれ試しているのですが
悔しい事に解決する事が出来ませんでした。
何か間違っている点やお気付きの点がありましたら
お力をお貸し頂けると嬉しいです。

[ttyp03]

order by が、incountとrandnumが逆ですかね…。
なんか片手落ちが多くてすみませぬ。

[shimane]

有難うございます！

＞なんか片手落ちが多くてすみませぬ。
とんでもないです、知らない知識・技術・発想とても勉強になりますし
感謝の気持ちでいっぱいです。

incount と randnumを変更してみたら
「incount」での並び替えを出来ました！

＞このように変更してみました。
as test1
order by incount desc limit 5
) as test2 order by randnum"

実行結果：
incountの値の大きい順番に並び替えが出来たのですが

登録しているブログの上位の５件(limit 5)をランダムに変更して
その登録しているブログの記事をランダムに変更するというのではなく

「登録しているブログのincountの値が一番大きいブログ」の記事を「ランダム」に並び替えをして

AND site.rsscount < 10
（RSS作成の回数制限：今回は１０回）の制限を超えるまでは
ランダムで登録ブログを並び替えではなく
必ずincountの値が１番大きいブログが常に選択されていました。

例：
1番目の選択：サイトA(incount1位):記事:id:1
2番目の選択：サイトA(incount1位):記事:id:3
3番目の選択：サイトA(incount1位):記事:id:2
4番目の選択：サイトA(incount1位):記事:id:5
5番目の選択：サイトA(incount1位):記事:id:4

今回のやりたい事は上位の5件のブログの記事をランダム表示なので
このようなイメージになります。

例：
1番目の選択：サイトB(incount5位):記事:id:1
2番目の選択：サイトD(incount2位):記事:id:3
3番目の選択：サイトA(incount1位):記事:id:2
4番目の選択：サイトD(incount2位):記事:id:6
5番目の選択：サイトE(incount4位):記事:id:4

といった風です。
説明が難しく上手く説明する事が出来ず、申し訳ないです。

[退会済みユーザー]

なんか、普通に上位5件を取得して PHPで[shuffle]関数で並べなおした方がいいようなきがする

[ttyp03]

質問にあるクエリでランダムに5件の抽出だけができないと思われたので、元のクエリをベースに回答したのですが、そもそもそこから違っていたようです。
質問にある条件を自分なりに解釈して修正してみました。
回答欄に追記していますので確認してみてください。
ただし、相変わらず動作は検証していません。
今更ですが私はSQLは得意ではないので、もっと効率の良い方法があるかもしれないことはご了承ください

[shimane]

回答有難うございます！

いえいえ、とんでもないです、とても助かっています。
頂いた回答を試してみたのですが、
中々上手くいきませんでした。
私もじっくりと時間をかけて今回の問題を考えていこうと思います。

回答頂きまして本当に有難うございます。感謝です！

[ttyp03]

相変わらずちゃんと動いてないようで申し訳ないです。
サブクエリを分解してどこでダメになってるか確認してみてください。
①のみで実行する。
OKなら①+②。
てな感じで。

[gracielue]

asahina1979さんと同じことを思いました。業務で使う場合、order by randはあまり使いません。今回はselectだけなのであまり関係ないかもですが、updateやinsertが伴うときはレプリケーション構成のときは絶対使えないので、上位5件を取得したあと、php側でshuffleを使う方がスマートではないでしょうか？

[ttyp03]

asahina1979さん、gracielueさん＞
単純に5件取得したいだけの問題であれば、limit5にしようが、PHP側で5回Fetchするかで大した差はないでしょう。
問題はシャッフルしたいということであり、また仕様を良く見ると、5件の対象はsiteテーブルであって、最終的に取得したいレコード数ではないということです。
なので何件返されるかわからないので、PHP側でシャッフルは厳しいんじゃないでしょうか。
と思ってSQLで試行錯誤してる次第です。

その後「中々上手くいきませんでした。」で止まっており、詳細な「うまくいかない」情報がわからないのですけど。

[gracielue]

件数は関係ないです。対象になるものを何件でも取ってきて、phpシャッフルして必要な件数だけ頭から表示すれば良いので、難しく考えてらっしゃるのかなと思いました。
最初にsqlで取るデータは条件に合致するすべてを、その後phpで加工すればいいのかと思います。
これは実際に良くやるしくみです。

[ttyp03]

phpでシャッフルするということは、抽出した全てのレコードを保持していないといけないわけですよね。
ま、今の時代リソースは豊富でしょうし、質問の要件からしてレコード数はたいしたことなさそうなので問題なく動くと思いますが、私自身古い世代なのでどうしもリソースは削減したくなってしまいます。
SQLでやってもPHPでやってもどちらでも構いませんが、私はあくまでも質問にあったクエリをベースに修正方法を提案したまでです。
order by rand() は私自身経験がなかったので、各レコードに乱数を付与してソートすれば同じ事が実現できるかと思って回答してみました。

[gracielue]

うーん、件数が多かったらorder by randは劇的に遅くなりますよ、全件操作ですからね。explainするとindexがあっても無駄なのがわかりますので。
失礼、乱数をつけてソートするということなのですね、ソレだとその処理が遅くならなければ有りでしょうか。。

件数が多かったら、リソースのことを考えるとというのは分かります。
ブログ記事なので、タイトルなど必要な情報だけ最低限取得して、シャッフル表示し、ブログの中身はリンク先で再取得するなどすれば、リソースはさほど食わない気もします。
表示したい要件に応じてSQLだけで解決するか、phpもうまく使うかを判断した方がよいかなと思います。

Answer 2

MYSQL 8.x なら WITH句が使えるんだが 実行検証はしてません。

/** WITH START **/
WITH RECURSIVE TEMP_TABLE AS (
	SELECT B.*,
	       rand() AS rand_num
	  FROM blog B 
	  JOIN site S USING(name)
	 WHERE S.henkan   <= 2
	   AND S.rsscount < 10
	   AND B.date     > CURRENT_TIMESTAMP + INTERVAL - 24 HOUR
	 ORDER BY incount DESC LIMIT 5
) 
/** WITH END **/
SELECT *, (SELECT COUNT(*) + 1 AS COUNT
             FROM TEMP_TABLE X 
            WHERE X.incount < A.incount ) AS RANK
  FROM TEMP_TABLE A
 ORDER BY rand_num

※ WITH 句を削除して同等のVIEWでも大丈夫ですよ。
※ WITH 句を削除して同じサブクエリを複数指定しても大丈夫ですよ。

な感じがしますな

Comments

[shimane]

回答有難うございます！

私が今プログラムを勉強中＋ドットインストール様のレッスン通りの環境づくり
ということもありまして
MYSQLのバージョンが5系でして、
悲しい事にせっかく教えて頂いた内容を試すことができません。

ですが、回答して頂いたコードの内容から考え方を自分なりに考えて
これからも頑張って勉強をしていこうと思います。
回答して頂いて有難うございます！

とっても嬉しかったです。