質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.90%

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

受付中

回答 2

投稿

  • 評価
  • クリップ 1
  • VIEW 2,729

score 90

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

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

登録しているブログの記事や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時間」以内の登録しているブログの記事を
ランダムに混ぜて1件のみを抽出します。
$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時間」以内の登録しているブログの記事を
ランダムにシャッフルして抽出します。

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

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

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

+2

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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/13 16:05 編集

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

    キャンセル

  • 2017/12/13 16:13

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

    キャンセル

  • 2017/12/13 16:21 編集

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

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

    キャンセル

+1

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 句を削除して同じサブクエリを複数指定しても大丈夫ですよ。

な感じがしますな

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/12/15 08:35

    回答有難うございます!

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

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

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.90%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • トップ
  • PHPに関する質問
  • 【MYSQL】【複数条件抽出】ORDER BYの後に「rand()」を含めた複数の条件指定の方法【PDO/PHP】