みなさんこんにちは！ 今日、データサンプリングに興味深い問題があり、その解決策を共有することにしました。
だから、与えられた：
2つのテーブル-
ショップと
製品おおまかに言って、それは異なる店舗が商品を置くプラットフォームです。
そのため、メインページで商品を配送する必要がありましたが、ユーザーが1つの店舗から商品の束を見ることがないようにしました。 店舗を変更する必要があります。
ショップ：- id int not null auto_increment主キー、
- 名前varchar（255）null
製品：- id int not null auto_increment主キー、
- shop_id intがnullではない、
- 名前varchar（255）null、
少しグーグル-賢明な解決策が見つかりませんでした。 しかし、さまざまな店舗を選択して実装する方法が考え出されました。
最初に、アルゴリズムについて説明します。
- すべての製品を選択し、店舗ごとに並べ替える必要があります。
- 次に、店舗の数に等しい間隔で、1から始まる各製品に番号を付けます。
- ナンバリングの際、1つの店舗の商品が終了するとすぐに、ナンバリングはゼロにリセットされ、1ずつシフトされ、再び開始されます
- 番号付きフィールドで並べ替えて製品を選択します
これはすべてMySQLツールを使用して行います。 そしてできれば、単一のリクエストで。
計画を念頭に置いて、実装を開始できます。 何が必要ですか？
- @i-製品に番号を付けるカウンター
- @cnt-店舗数
- @delta-次の店舗の商品に番号を付けるときにカウンターをシフトするデルタ
- @cur-新しいストアに番号を付けるときにデルタを追加し、カウンターをリセットするための現在のストアのID
変数を宣言します。
set @cnt = 0; set @i = 0; set @delta = 0; set @cur = 0;
次に、初期値（店舗の数と最初の店舗のID）を割り当てます。
select @cur:=id from shops order by id limit 1; select @cnt:=count(id) from shops;
これで、選択自体を開始できます。 何が必要ですか？
- 店舗の数に等しい間隔で商品に番号を付ける必要があります。
- 1つの店舗の商品の最後に-カウンターをリセットし、デルタを追加して、現在の店舗を変更します。
私は次のリクエストを受け取りました：
select id, shop_id, @i:=@i+@cnt as counter, IF(@cur<>shop_id,@delta:=@delta+1,@delta) as delta, IF(@cur<>shop_id,@i:=@delta,@i) as cur, IF(@cur<>shop_id,@cur:=shop_id,@cur) as curshop from t_product order by shop_id
ここにあるものの詳細：
@i:=@i+@cnt
各行で、店舗の数に等しい数だけカウンターを増やします。 つまり、5つのストアがある場合、0、5、10、15などの番号が付けられます。
IF(@cur<>shop_id,@delta:=@delta+1,@delta) as delta
新しい店舗ができたらすぐに、シフトを1つ増やします。 つまり 最初のストアの場合、シフトは0、2番目のストアの場合-1などになります。
IF(@cur<>shop_id,@i:=@delta,@i) as cur,
店舗を変更するときは、シフトを追加することを忘れずに、カウンターをリセットし、最初から商品の番号付けを開始する必要もあります。
IF(@cur<>shop_id,@cur:=shop_id,@cur) as curshop
そして最後に-現在のストアを更新し、その製品に番号が付けられています...
その結果、次のタイプのサンプルを取得します。
| id | shop_id | カウンター | デルタ | cur | Curshop |
|---|
| 43989 | 1 | 10 | 0 | 10 | 1 |
| 46989 | 1 | 20 | 0 | 20 | 1 |
| 114172 | 1 | 30 | 0 | 30 | 1 |
| 83989 | 1 | 40 | 0 | 40 | 1 |
| 67172 | 1 | 50 | 0 | 50 | 1 |
| 94672 | 2 | 11 | 1 | 11 | 2 |
| 6489 | 2 | 21 | 1 | 21 | 2 |
| 41989 | 2 | 31 | 1 | 31 | 2 |
| 61672 | 2 | 41 | 1 | 41 | 2 |
| 97489 | 3 | 12 | 2 | 12 | 3 |
ここで、カウンターが正しく追加されていることがわかります。ストアを変更すると、リセットされ、シフトが追加され、番号が最初から始まります（シフトを考慮に入れます）。
実際には、問題は小さなものに任されています。 結果の選択をサブクエリでラップし、カウンターで並べ替えます。
select id as product_id, shop_id, cur from ( select id, shop_id, @i:=@i+@cnt as counter, IF(@cur<>shop_id,@start:=@delta+1,@delta) as delta, IF(@cur<>shop_id,@i:=@delta,@i) as cur, IF(@cur<>shop_id,@cur:=shop_id,@cur) as curmag from products order by shop_id ) as A order by cur ;
出来上がり！ 店舗を交互に並べた商品を選択しました。
| product_id | shop_id | cur |
|---|
| 4187 | 1 | 10 |
| 7483 | 2 | 11 |
| 4045 | 3 | 12 |
| 9091 | 4 | 13 |
| 1457 | 5 | 14 |
| 2387 | 6 | 15 |
| 8109 | 7 | 16 |
| 1445 | 8 | 17 |
| 2102 | 9 | 18 |
| 9245 | 10 | 19 |
| 6744 | 1 | 20 |
| 7854 | 2 | 21 |
| 2164 | 3 | 22 |
1つのマイナスがあります-各店舗の商品は順番に行きます。 つまり 最初に、最初の店舗の最初の製品、次に2番目の店舗の最初の製品、3番目、4番目などが表示されます。 次に、2番目のストアの商品、3番目のストアの商品などに進みます。
このパターンを取り除くには、最初のサンプルの商品を混ぜて、別のサブクエリでラップする必要があります。
select id as product_id, shop_id, cur from ( select id, shop_id, @i:=@i+@cnt as counter, IF(@cur<>shop_id,@start:=@delta+1,@delta) as delta, IF(@cur<>shop_id,@i:=@delta,@i) as cur, IF(@cur<>shop_id,@cur:=shop_id,@cur) as curmag from products order by shop_id from (select id, shop_id from products order by shop_id, rand()) as A order by shop_id) as B order by cur;
そのため、当社の製品は番号が付けられる前にすでに混合されています。
実際、これは問題の解決が終わった場所です。 完全なリクエストはカットの下で表示できます。
完全インターリーブクエリ set @cnt = 0; set @i = 0; set @start = 0; set @cur = 0; select @cur:=id from shops order by id limit 1; select @cnt:=count(id) from shops; select id as product_id, shop_id, cur from ( select id, shop_id, @i:=@i+@cnt as counter, IF(@cur<>shop_id,@start:=@delta+1,@delta) as delta, IF(@cur<>shop_id,@i:=@delta,@i) as cur, IF(@cur<>shop_id,@cur:=shop_id,@cur) as curmag from products order by shop_id from (select id, shop_id from products order by shop_id, rand()) as A order by shop_id) as B order by cur;
興味のために-私はサンプリングレートを見ました。 私の意見では、結果は悪くありませんでした：
10店舗、10,000アイテム-〜16ms（0.016s）
100店舗、1,000,000アイテム-〜2568ms（2.568s）
100店舗、10,000,000アイテム-129951ms（2m 9.951s）
もちろん、戦闘モードでテストする必要がありますが、これらは良い結果だと思います。
PS私にとっては、不明瞭な質問が1つだけ残っています。 すべては問題ありませんが、ページネーションはどうしますか？ 結局のところ、次の各ページは新しいリクエストです。
したがって、店舗で混合された商品は新しいシリアル番号を受け取り、サンプルに複数回表示される場合があります。
これについて何か考えがあれば、コメントで聞いてくれてありがたいです。ご清聴ありがとうございました）