プロジェクトでは、次のタスクに直面しました。写真のニュースフィードがあり、ユーザーは1枚の写真にのみ投稿でき、ギャラリー形式で一緒に表示する必要があります。 つまり、すべての選択行を論理的に組み合わせて各著者の複数の「タイムウィンドウ」にし、表示するときにこれを使用する必要があります。
次の投稿を1つずつグループ化するように頼みますが、これは適切ではありません：2人のユーザーが何百もの写真を並行してゆっくりとアップロードおよびダウンロードすると、それらは1つずつフィードに追加されます。
MySQLのソリューションの場合
問題の声明
選択中に投稿をグループ化することは間違っているということをすぐに予約してください。 したがって、各投稿は、ギャラリーで視覚的に表されるある種の「グループ」に明確に関連する必要があります。
解決策は万能薬ではありませんが、まさにこのアプローチが役立つタスクのサークルがあります。
最初に、実験用のテーブルを作成しましょう。
CREATE TABLE `feed`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `tm` INT UNSIGNED NOT NULL COMMENT 'timestamp', `user_id` INT UNSIGNED NOT NULL COMMENT 'author id', `image` VARCHAR(255) NOT NULL COMMENT 'posted image filename', `group` INT UNSIGNED NULL DEFAULT NULL COMMENT 'post group', PRIMARY KEY(`id`), INDEX(`user_id`), INDEX(`tm`,`group`) );
`feed`
は投稿のリストです。 各投稿には
tm
、著者
user_id
へのリンク、写真自体を追加する時間があり
user_id
、画像をギャラリーにグループ化できる特別な列
group
も追加します。 新しいエントリを追加する場合、
group=NULL
。
オプションが間違っています
最初は、最新の投稿を選択してから、半径1時間以内の同じユーザーの投稿を選択し、それらにすべて
group= id--
を割り当てます。 この場合にのみ、各投稿はグループにのみ属することが判明しました。 いいえ、適切ではありません:)
グルーピング
最初に、投稿の時間的近接性の基準を決定する必要があります。
SET @granularity:=60*60;
したがって、1時間以内のすべての投稿は1つのギャラリーにグループ化されます。
次に、次の論理的な動きを行います。各投稿をグループの「基盤」にしましょう。
SELECT `g`.`id` AS `group` FROM `feed` `g`;
そして、そのようなグループには、「ベース」から1時間ごとの半径の線が含まれます（時差は1時間以内です）。
SELECT `g`.`id` AS `group`, `f`.* FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` )
そのため、各行には基本的な候補が多数あります。 選択基準：1時間ごとの半径で最大数の投稿を含む投稿を「基本」として選択します。
不要な計算でMySQLに負担をかけないために、半径の代わりに基準
`f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`
、
`f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`
、
`f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`
、
`f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`
、
`f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`
、
`f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`
の基準を使用します
`f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`
：
SELECT MAX(`g`.`id`) AS `group`, `f`.* FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` ) GROUP BY `f`.`id`
そこで、すべての行を選択し、それぞれにグループを設定しました。
ニュアンスがあることに注意してください。 そのようなテープを表示するとき、
`group` DESC
ソートします。 次に、上記のコードで
MAX()
関数が使用されている場合、テープのソート時に、最新の「グループ」（最新の更新を受信）が最上部にジャンプします。
この振る舞いは簡単に変更できます：その後、要素が別の要素に移動できないように、一定のグループを取得します：このためには、
MIN()
関数を使用するだけで十分です：ベースは常に最も古い投稿になり、グループは新しい着信写真でのみ補完できます：
SELECT MIN(`g`.`id`) AS `group`, `f`.* FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm` AND `g`.`tm`+@granularity ) GROUP BY `f`.`id`
次に、このクエリの結果に基づいてテーブルを更新する必要があります
`group`
カラムの値を設定します。 MySQLは、1つの
UPDATE
クエリで読み取られるテーブルの更新を許可しないため、最初に選択を一時テーブルに転送する必要があります。
CREATE TEMPORARY TABLE `_feedg` SELECT MAX(`g`.`id`) AS `group`, `f`.`id` FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm` ) WHERE `f`.`group` IS NULL OR `f`.`tm` >= (UNIX_TIMESTAMP()-2*@granularity) GROUP BY `f`.`id`;
表示される
WHERE
注意してください。これは最適化に使用され、最新のレコードの中でテーブルの最上部でのみ再グループ化が実行されるようにします。
これで、一時テーブルを使用して、元のテーブルを更新できます。
UPDATE `feed` `f` CROSS JOIN `_feedg` `g` USING(`id`) SET `f`.`group` = `g`.`group`;
挿入するたびにリクエストを実行することをお勧めしますが、最適化のために、クラウンによってこれを行うことができ、出力アルゴリズムで作業の一部を残します。
UPD： Melkijが提案したように-実際、読み取り要求がJOINではなくサブクエリになっている場合は更新できます。 次に、
CREATE TEMPORARY TABLE
を完全に削除すると、
UPDATE
クエリは次のようになります。
UPDATE `feed` `f` CROSS JOIN ( SELECT MAX(`g`.`id`) AS `group`, `f`.`id` FROM `feed` `g` CROSS JOIN `feed` `f` ON (`f`.`user_id` = `g`.`user_id` AND `f`.`tm` BETWEEN `g`.`tm`-@granularity AND `g`.`tm`) WHERE `f`.`group` IS NULL OR `f`.`tm` >= (UNIX_TIMESTAMP()-2*@granularity) GROUP BY `f`.`id` ) `g` USING(`id`) SET `f`.`group` = `g`.`group`;
セレクション
さて、そのようなテーブルから正しく選択する方法は？
すべての行に
`group`
れている場合、
SELECT * FROM `feed` ORDER BY `group` DESC, `tm` DESC;
ただし、上記のリクエストがクラウンで実行されるため、
group=NULL
行がいくつかあり、出力ロジックの一部をスクリプトレンダラーに割り当てる必要があり、選択は次のように行う必要があります。
SELECT * FROM `feed` ORDER BY `group` IS NULL, `group` DESC, `tm` DESC;
参照資料
私の質問はstackoverflow： Stackoverflow：ユーザーフィードでのシリアル投稿のグループ化です。 ここでは、「タイムウィンドウ」を使用してOracleでこれがどのように行われるかを鑑賞できます。
SQLfiddle、遊ぶ： SQLfiddle
お役に立てば幸いです。