- データベースのN個のエントリから、一連のk個のサンプル（主にk = 2）でm（3-5）のランダムな行を選択する必要があります。
そして今、人間の言語でも同じことが言えます。テーブルから3〜5個のランダムエントリを2回選択する必要があります。 この場合、重複はなく、サンプリングはランダムに行われます。
頭に浮かぶ最初のもの：
SELECT * FROM data_set WHERE id NOT IN (1,2,3,4, 5) ORDER BY random() LIMIT 5;
そして、それも機能します。 それはまさにそのような決定の価格です...
したがって、私は「より高い精神」を使わなければなりませんでした。そして、それは解決のヒントを出しました。
WITH RECURSIVE r AS ( WITH b AS (SELECT min(id), max(id) FROM table1) ( SELECT id, min, max, array[]::integer[] AS a, 0 AS n FROM table1, b WHERE id > min + (max - min) * random() LIMIT 1 ) UNION ALL ( SELECT t.id, min, max, a || t.id, rn + 1 AS n FROM table1 AS t, r WHERE t.id > min + (max - min) * random() AND t.id <> all(a) AND rn + 1 < 10 LIMIT 1 ) ) SELECT t.id FROM table1 AS t, r WHERE r.id = t.id;
唯一の解決策は...「少し」理解できない。 しかし、あいまいな決定をプロジェクトにドラッグすることは、いくぶん消極的です。 したがって、 実績のある方法で進むことが決定され、 再帰クエリの本質の説明を見つけることができました。
まあ何。 一般的なロジックは多かれ少なかれ明確になっています。n回、最小ID（主キー）からランダムにオフセットした1行を選択します。 したがって、クエリは、テーブルのコンテンツを反復処理するのではなく、最大N行に影響します。
しかし、「紙の上では滑らかでした。」 「そのまま」（テーブル/フィールド名に合わせて調整）を使用しようとすると、「驚き」が浮上しました：
- 4行目の配列は空で作成されます。 このため、最終サンプルに重複が表示される場合があります。 行4〜7のクエリはid == 5を返します。 その後、 UNIONブロック（9〜15行目）で要求を満たし、ある段階で同じid == 5を返します。これは、以前のid値が「 a 」配列に該当せず、13行目のチェックが「t.id <> all （a）「成功しました。
- 「出力」の値の数は、示された数（p。14）に過ぎませんでした。 しかし、それ以下-簡単に。 この金額が保証されていたとしても、表にありました。 空の選択が返されることがありました（値の数は「0」です）。
最初の「機能」を扱うのは比較的簡単であることが判明しました-配列の初期化で行をわずかに変更するだけで十分でした。 このようなもの：
SELECT id, min, max, array[]::integer[] || id AS a, 0 AS n
しかし、2番目のポイントは私を洗脳させました。 キャッチはアルゴリズムのまさに「中心」で発見されました-範囲からのサンプル記録。 実際、再帰クエリは条件が満たされる行を選択しようとします。
id > min + (max - min) * random()
ただし、 random（）が「1」を返す場合、この条件は次のように変換されます。
id > max
当然、この場合、リクエストは何も検出せず、動作を停止します。 リクエストの最初のパスでこれが発生した場合、「出力」は空になります。 データベースに明らかに必要なレコードが含まれている場合でも。
最初の衝動は、条件をわずかに変更して、次のような状態にすることでした。
id >= min + (max - min) * random()
これは、いわば、出口で少なくとも1行を取得できるソリューションです。 しかし、結果として、指定された行数の受信をまったく保証しませんでした。 さらに考えなければなりませんでした。
多くの審議の後、多くの試みと覚醒剤のリットルが生まれました
そのようなオプション：
リクエストコード
WITH RECURSIVE r AS ( WITH b AS ( SELECT min(t.id), ( SELECT t.id FROM table1 AS t ORDER BY t.id DESC LIMIT 1 OFFSET 9 ) max FROM table1 AS t ) ( SELECT id, min, max, array[]::integer[] || id AS a, 0 AS n FROM table1 AS t, b WHERE id >= min + ((max - min) * random())::int LIMIT 1 ) UNION ALL ( SELECT t.id, min, max, a || t.id, rn + 1 AS n FROM {table} AS t, r WHERE t.id >= min + ((max - min) * random())::int AND t.id <> all(a) AND rn + 1 < 10 LIMIT 1 ) ) SELECT t.* FROM table1 AS t, r WHERE r.id = t.id
5-11行目のすべての塩。 つまり 「出力」にN個の要素が存在することを保証するために、考えられる最悪のシナリオから進めなければなりません。 この場合、ランダムNは1 N回連続して返されるため、maxの前にN-1個の値を知っている必要があります。 リクエストでこれを達成する方法は？ または、IDですべてのレコードを降順でソートし、N行ずつ「下に」シフトします。 また、19行目と25行目では "> ="が使用されているため、オフセットは1つ少ない（N-1）で示すことができます。
それは良いことです-主な問題は解決され、「些細なこと」は残ります。現在の形式の要求はほとんど役に立ちません。 結局のところ、いくつかの条件を考慮して選択する必要があります。 最も単純なケースでは、前の手順で選択したレコードのIDを選択から除外します。 さらに、テーブル内の行に課せられた追加条件（is_active = true、is_deleted = false、...）を使用する可能性を排除することはできません。 少し熟考した後、要求のすべての重要な部分（ほとんどすべてのサブクエリ）に条件を入れる必要があることが明らかになります。 次のテンプレートのように：
パターンコード
WITH RECURSIVE r AS ( WITH b AS ( SELECT min(t.{pk}), ( SELECT t.{pk} FROM {table} AS t WHERE {exclude} t.is_active ORDER BY t.{pk} DESC LIMIT 1 OFFSET {limit} - 1 ) max FROM {table} AS t WHERE {exclude} t.is_active ) ( SELECT t.{pk}, min, max, array[]::integer[] || t.{pk} AS a, 0 AS n FROM {table} AS t, b WHERE t.{pk} >= min + ((max - min) * random())::int AND {exclude} t.is_active LIMIT 1 ) UNION ALL ( SELECT t.{pk}, min, max, a || t.{pk}, rn + 1 AS n FROM {table} AS t, r WHERE t.{pk} >= min + ((max - min) * random())::int AND t.{pk} <> all(a) AND rn + 1 < {limit} AND {exclude} t.is_active LIMIT 1 ) ) SELECT {fields} FROM {table} AS t, r WHERE r.{pk} = t.{pk}
ここでは、中括弧内に置き換えられる名前付きパラメーターがあります。
- {table} - テーブルの名前。
- {pk} -PrimaryKeyフィールドの名前。
- {fields} -選択するフィールドのリスト（「*」を指定できます）;
- {exclude} -選択からレコードを除外する条件（条件のセット）。 たとえば、「t.id NOT IN（1,2,3,4）」;
- {limit} -最終選択のレコード数
そして最後に、リストの最後の質問ですが、重要ではありません。「ゲームはろうそくの価値がありますか？」 この脳構造からの「利益」とは何ですか？ 確認します。
最初に、実験を実施するテーブルを作成します。
DROP TABLE IF EXISTS ttbl; CREATE TABLE IF NOT EXISTS ttbl ( id serial NOT NULL, is_active BOOL NOT NULL DEFAULT true, CONSTRAINT ttbl_pkey PRIMARY KEY (id) ) WITH (OIDS=FALSE);
次に、データを入力します。 さらに、id値が連続して移動するのではなく、「穴」があることが必要です。 つまり 「1、2、3、4、5 ...」ではなく、少なくとも「1、4、5、8 ....」。 これを行うために、簡単なスクリプトをスケッチします。
スクリプトコード
import random import psycopg2 DB_TABLE = 'ttbl' PK_NAME = 'id' DATABASES = { 'NAME': 'test_db', 'USER': 'user_test', 'PASSWORD': 'R#Q9Jw*ZEKWOhBX+EP|3/xGkQn3', 'HOST': 'localhost', 'PORT': '', } TOTAL = 10000000 current = 0 step = 10000 dev = 8 while current <= TOTAL: data = set() for x in range(current, current+step, 10): data.add(x + int(random.random() * dev)) x = cur.execute( "INSERT INTO {t_table} VALUES {t_items};".format( t_table=DB_TABLE, t_items='(' + '), ('.join(map(str, data)) + ')' ) ) current += step print(x, current) cur.execute('COMMIT;')
コードからわかるように、各リクエストは100個のレコードを挿入します。 値は約10ずつ増加します。 およそ それぞれの値は、0〜devの範囲のランダムな値だけ逸脱する場合があります。 つまり x「340」と「350」の2つの連続した値で、340-348と350-358（342、347、340 ...; 351、355、358 ...）の範囲の値をテーブルに挿入できます。
ターンテーブルの合計
select count(id) from ttbl;
1,001,000エントリ
かなりまともな量。 選択を試みましょう。 単一のサンプルが指標ではないことは明らかです。 そのため、一連の連続起動を行います。 明確にするため、各タイプの一連の5つのクエリが開始されます。 結果を表にして平均を計算します。
最初に簡単なリクエスト
select t.* from ttbl t where t.id not in (1, 3, 10, 89, 99, 22, 24, 25, 28, 30) AND t.is_active order by random() limit 5;
結果：
| 番号p / p | 時間ms |
|---|---|
| 1 | 697 |
| 2 | 605 |
| 3 | 624 |
| 4 | 593 |
| 5 | 611 |
ご覧のとおり、クエリの平均実行時間は約600ミリ秒です。
そして今-「モンスター」。
モンスターを見る
WITH RECURSIVE r AS ( WITH b AS ( SELECT min(t.id), ( SELECT t.id FROM ttbl AS t WHERE t.id NOT IN (1, 3, 10, 89, 99, 22, 24, 25, 28, 30) AND t.is_active ORDER BY t.id DESC LIMIT 1 OFFSET 5 - 1 ) max FROM ttbl AS t WHERE t.id NOT IN (1, 3, 10, 89, 99, 22, 24, 25, 28, 30) AND t.is_active ) ( SELECT id, min, max, array[]::integer[] || id AS a, 0 AS n FROM ttbl AS t, b WHERE id >= min + ((max - min) * random())::int AND t.id NOT IN (1, 3, 10, 89, 99, 22, 24, 25, 28, 30) AND t.is_active LIMIT 1 ) UNION ALL ( SELECT t.id, min, max, a || t.id, rn + 1 AS n FROM ttbl AS t, r WHERE t.id > min + ((max - min) * random())::int AND t.id <> all( a ) AND rn + 1 < 5 AND t.id NOT IN (1, 3, 10, 89, 99, 22, 24, 25, 28, 30) AND t.is_active LIMIT 1 ) ) SELECT t.* FROM ttbl AS t, r WHERE r.id = t.id
結果：
| 番号p / p | 時間ms |
|---|---|
| 1 | 15 |
| 2 | 17 |
| 3 | 8 |
| 4 | 12 |
| 5 | 12 |
平均時間は約* 15msです。
合計差は約1.5桁（40〜50倍）です。 それは価値があった。
リクエストが確認されました コールドスタート中（マシン/デーモンの再起動後）。 また、ランタイムは絶対的に変化しましたが、比率は一定のままでした（可能な限り）。 つまり 再帰クエリは、常に「額」ソリューションよりも数十倍高速でした。
注釈
*なぜなら、 役割の正確な値は、Postgresキャッシュ、オペレーティングシステム、ハードウェア、その他のソフトウェアの影響などによって引き起こされる偏差のために再生されません。