サブセットかどうかを判断するためのMySQLメソッドの手順

1. 問題

この話は、エラーと脱落率を照会するレポートから始まりました。クエリ結果には 2 つあり、1 つはレポートに追加された項目に関するもので、もう 1 つはレポートに追加する必要がある項目に関するものです。

省略がないとはどういう意味ですか?つまり、追加すべき項目はすべて追加されている

報告完全率は、報告総数に対する完了した報告数の比率です。

ここに 2 つのレポートの例を示します (1 つはすべて追加されたもの、もう 1 つは一部が欠落しているもの)

まず、最初の結果、つまりレポートに追加する必要がある項目を見つけます。

選択 
     r.id ASレポートID、m.project_idをプロジェクトに追加する必要があります。 
  報告 
  INNER JOIN アプリケーション a ON r.app_id=a.id
  application_sample s を a.id=s.app_id に内部結合します
  application_sample_item si を s.id=si.sample_id に右結合します       
  右結合 set_project_mapping m ON si.set_id=m.set_id
r.id が ('44930','44927') にある場合
r.id、m.project_id で並べ替えます。

次に、2番目の結果を見つけて、追加されたアイテムを報告します

レポート r から r.id を report_id、i.project_id を added_project として選択します。 
右結合 report_item i ON r.id=i.report_id
r.id が ('44930','44927') の場合;

上記は比較したい結果セットです。レポート 44927 は完全ですが、44930 には同じ数の項目がありますが、実際には 758 項目が追加され、112 項目が不足しているため、欠落レポートであることは容易にわかります。

2. 解決策

質問から判断すると、明らかにサブセットであるかどうかを判断する問題です。追加した項目と追加すべき項目を別々に走査することができます。追加すべき項目が追加した項目と一致する場合、追加すべき項目は追加した項目のサブセットであり、つまり漏れがないことを意味します。

この問題はループして比較することで確かに解決できますが、SQL でのカルテシアン積のクロス結合は、多くの場合、大きなオーバーヘッドとクエリ速度の低下を意味します。この問題を回避する方法はあるのでしょうか?

オプション1:

FIND_IN_SETとGROUP_CONCAT関数の助けを借りて、まず次の2つの関数を理解します。

FIND_IN_SET(文字列、文字列リスト)

str: 照会する文字列
strlist:パラメータは英語の「,」で区切られます(例: (1,2,6,8,10,22))

FIND_IN_SET 関数は、ターゲット文字列内で照会する文字列の位置を返します。

GROUP_CONCAT( [個別] 接続するフィールド [ソートフィールドによる順序 asc/desc ] [区切り文字 'separator'] )

GROUP_CONCAT() 関数は、複数のレコードの同じフィールドの値を 1 つのレコードに連結して返すことができます。デフォルトの区切り文字は英語の「,」です。

ただし、GROUP_CONCAT()のデフォルトの長さは1024です。

したがって、スプライシングの長さが 1024 を超える必要がある場合は、不完全な切り捨てが発生するため、長さを変更する必要があります。

グローバルgroup_concat_max_lenを102400に設定します。
セッションgroup_concat_max_len=102400を設定します。

上記の2つの関数の紹介から、 FIND_IN_SETとGROUP_CONCATは英語の「,」（太字）で区切られていることがわかります。

したがって、GROUP_CONCAT を使用して、追加された項目を文字列に連結し、次に FIND_IN_SET を使用して、追加する項目が文字列内に存在するかどうかを 1 つずつ照会することができます。

1. 問題の説明にあるSQLを修正し、GROUP_CONCATを使用して追加した項目を文字列に連結します。

SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS 追加されたプロジェクトリスト FROM report r 
LEFT JOIN report_item i ON r.id=i.report_id
r.id が ('44930','44927') にある場合
GROUP BY r.id;

2. FIND_IN_SETを使用して、追加する項目が文字列内に存在するかどうかを1つずつ確認します。

SELECT Q.id,FIND_IN_SET(W.追加するアイテムのリスト、Q.既に追加されているアイテムのリスト) AS Is FROM missing 
   （
   -- 追加されたプロジェクトを報告します SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS added project list FROM report r 
      LEFT JOIN report_item i ON r.id=i.report_id
      r.id が ('44930','44927') にある場合
      r.id によるグループ化
   )Q、
   （
   -- 追加すべき項目をレポートするSELECT 
         r.id、s.app_id、m.project_idはプロジェクトリストを追加する必要があります 
         報告 
         INNER JOIN アプリケーション a ON r.app_id=a.id
         application_sample s を a.id=s.app_id に内部結合します
         内部結合 application_sample_item si ON s.id=si.sample_id       
         内部結合 set_project_mapping m ON si.set_id=m.set_id
      r.id が ('44930','44927') にある場合
      r.id、m.project_id で並べ替え
   )W
   ここで、Q.id=W.id;

3. 見逃したレポートを除外する

 SELECT Q.id,CASE WHEN FIND_IN_SET(W.追加するアイテムのリスト、Q.既に追加されているアイテムのリスト)>0 THEN 1 ELSE 0 END AS Is FROM missing 
   （
   -- 追加されたプロジェクトを報告します SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS 追加されたプロジェクト リスト FROM report r 
      LEFT JOIN report_item i ON r.id=i.report_id
      r.id が ('44930','44927') にある場合
      r.id によるグループ化
   )Q、
   （
   -- 追加すべき項目をレポートするSELECT 
         r.id、s.app_id、m.project_idはプロジェクトリストを追加する必要があります 
         報告 
         INNER JOIN アプリケーション a ON r.app_id=a.id
         application_sample s を a.id=s.app_id に内部結合します
         内部結合 application_sample_item si ON s.id=si.sample_id       
         内部結合 set_project_mapping m ON si.set_id=m.set_id
      r.id が ('44930','44927') にある場合
      r.id、m.project_id で並べ替え
   )W
   Q.id=W.id の場合
   Q.id によるグループ化
   HAVING COUNT(`欠落していますか`)=SUM(`欠落していますか`);

4. 私たちの最終目標は、脱落率ゼロを見つけることです

 SELECT COUNT(X.id) 欠落項目のないレポートの数、Y.total レポートの合計数、CONCAT(FORMAT(COUNT(X.id)/Y.total*100,2),'%') AS 欠落項目のないプロジェクト率 FROM 
（
  SELECT Q.id,CASE WHEN FIND_IN_SET(W.追加するアイテムのリスト、Q.既に追加されているアイテムのリスト)>0 THEN 1 ELSE 0 END AS Is FROM missing 
   （
   -- 追加されたプロジェクトを報告します SELECT r.id,GROUP_CONCAT(i.project_id ORDER BY i.project_id,'') AS added project list FROM report r 
      LEFT JOIN report_item i ON r.id=i.report_id
      r.id が ('44930','44927') にある場合
      r.id によるグループ化
   )Q、
   （
   -- 追加すべき項目をレポートするSELECT 
         r.id、s.app_id、m.project_idはプロジェクトリストを追加する必要があります 
         報告 
         INNER JOIN アプリケーション a ON r.app_id=a.id
         application_sample s を a.id=s.app_id に内部結合します
         内部結合 application_sample_item si ON s.id=si.sample_id       
         内部結合 set_project_mapping m ON si.set_id=m.set_id
       r.id が ('44930','44927') にある場合
    r.id、m.project_id で並べ替え
   )W
   Q.id=W.id の場合
   Q.id によるグループ化
   HAVING COUNT(`欠落しているか`)=SUM(`欠落しているか`)
 )X、
 （
    -- レポートの総数 SELECT COUNT(E.nums) AS total FROM
    （
      SELECT COUNT(r.id) AS nums FROM report r 
      r.id が ('44930','44927') にある場合
      r.id によるグループ化
    )E    
 )はい 
 ;

オプション2:

上記のソリューション 1 では、行ごとの走査と比較は回避されますが、本質的には項目を 1 つずつ比較することになります。比較を回避する方法はあるのでしょうか?

もちろん答えはイエスです。統計量に基づいて完全に含まれているかどうかを判断できます。

1. 重複を削除せずに、追加された項目と追加される項目を結合するには、union all を使用します。

 （
 -- 追加すべき項目SELECT 
  r.id、m.プロジェクトID
から 
   報告 
INNER JOIN アプリケーション a ON r.app_id=a.id
application_sample s を a.id=s.app_id に内部結合します
内部結合 application_sample_item si ON s.id=si.sample_id       
内部結合 set_project_mapping m ON si.set_id=m.set_id
r.id が ('44930','44927') にある場合
r.id、m.project_id で並べ替え
）
ユニオンオール
（
 -- プロジェクトを追加しました。レポート r、レポート項目 i から r.id、i.project_id を選択します。 
ここで、r.id = i.report_id であり、r.id IN ('44930','44927')
r.app_id、i.project_id でグループ化
 ）

結果から、同じレポートの下に重複した項目があることがわかります。これは、追加する必要がある項目と追加された項目を表しています。

2. 共同表の結果によると、統計報告書の重複項目の数

# すでに追加されているものと重複するアイテムの数を追加する必要があります select tt.id,count(*) count from 
（
   t.id、t.project_id、count(*) を選択 
   （
      （
        -- 追加すべき項目SELECT 
          r.id、m.プロジェクトID
        から 
          報告 
          INNER JOIN アプリケーション a ON r.app_id=a.id
          application_sample s を a.id=s.app_id に内部結合します
          内部結合 application_sample_item si ON s.id=si.sample_id       
          内部結合 set_project_mapping m ON si.set_id=m.set_id
        r.id が ('44930','44927') にある場合
        r.id、m.project_id で並べ替え
      ）
      ユニオンオール
      （
        -- プロジェクトを追加しました。レポート r、レポート項目 i から r.id、i.project_id を選択します。 
        ここで、r.id = i.report_id であり、r.id IN ('44930','44927')
        r.app_id、i.project_id でグループ化
      ）
      
   ）
   t.id、t.project_id でグループ化
   count(*) >1 を持つ 
) tt.id による tt グループ化

3. 2 番目のステップの金額と追加する金額を比較します。等しい場合は、漏れがないことを意味します。

bb.idを選択すると、aa.countが追加され、bb.countを追加する必要があります。
    aa.count/bb.count=1 の場合 1
    それ以外の場合 0
    END AS '行方不明ですか' 
から 
（
# すでに追加されているものと重複するアイテムの数を追加する必要があります select tt.id,count(*) count from 
（
   t.id、t.project_id、count(*) を選択 
   （
      （
        -- 追加すべき項目SELECT 
          r.id、m.プロジェクトID
        から 
          報告 
          INNER JOIN アプリケーション a ON r.app_id=a.id
          application_sample s を a.id=s.app_id に内部結合します
          内部結合 application_sample_item si ON s.id=si.sample_id       
          内部結合 set_project_mapping m ON si.set_id=m.set_id
        r.id が ('44930','44927') にある場合
        r.id、m.project_id で並べ替え
      ）
      ユニオンオール
      （
        -- プロジェクトを追加しました。レポート r、レポート項目 i から r.id、i.project_id を選択します。 
        ここで、r.id = i.report_id であり、r.id IN ('44930','44927')
        r.app_id、i.project_id でグループ化
      ）
      
   ）
   t.id、t.project_id でグループ化
   count(*) >1 を持つ 
) tt.id による tt グループ化 
) aa 右結合
（
  -- 追加するアイテムの数SELECT 
    r.id、s.app_id、COUNT(m.project_id) カウント
  から 
    報告 
    INNER JOIN アプリケーション a ON r.app_id=a.id
    application_sample s を a.id=s.app_id に内部結合します
    内部結合 application_sample_item si ON s.id=si.sample_id       
    内部結合 set_project_mapping m ON si.set_id=m.set_id
  r.id が ('44930','44927') にある場合
  r.id によるグループ化
  r.id、m.project_id で並べ替え
) bb ON aa.id = bb.id 
aa.idで注文する

4. 欠落率を調べる

選択 
    SUM(asr.`欠落していますか?) AS 欠落数なし、COUNT(asr.id) AS 合計数、CONCAT(FORMAT(SUM(asr.`欠落していますか?)/COUNT(asr.id)*100,5),'%') AS 欠落率なしを報告 
（
  bb.idを選択すると、aa.countが追加され、bb.countを追加する必要があります。
      aa.count/bb.count=1 の場合 1
      それ以外の場合 0
      END AS '行方不明ですか' 
  から 
  （
  # すでに追加されているものと重複するアイテムの数を追加する必要があります select tt.id,count(*) count from 
  （
     t.id、t.project_id、count(*) を選択 
     （
        （
          -- 追加すべき項目SELECT 
            r.id、m.プロジェクトID
          から 
            報告 
            INNER JOIN アプリケーション a ON r.app_id=a.id
            application_sample s を a.id=s.app_id に内部結合します
            内部結合 application_sample_item si ON s.id=si.sample_id       
            内部結合 set_project_mapping m ON si.set_id=m.set_id
          r.id が ('44930','44927') にある場合
          r.id、m.project_id で並べ替え
        ）
        ユニオンオール
        （
          -- プロジェクトを追加しました。レポート r、レポート項目 i から r.id、i.project_id を選択します。 
          ここで、r.id = i.report_id であり、r.id IN ('44930','44927')
          r.app_id、i.project_id でグループ化
        ）
        
     ）
     t.id、t.project_id でグループ化
     count(*) >1 を持つ 
  ) tt.id による tt グループ化 
  ) aa 右結合
  （
    -- 追加するアイテムの数SELECT 
      r.id、s.app_id、COUNT(m.project_id) カウント
    から 
      報告 
      INNER JOIN アプリケーション a ON r.app_id=a.id
      application_sample s を a.id=s.app_id に内部結合します
      内部結合 application_sample_item si ON s.id=si.sample_id       
      内部結合 set_project_mapping m ON si.set_id=m.set_id
    r.id が ('44930','44927') にある場合
    r.id によるグループ化
    r.id、m.project_id で並べ替え
  ) bb ON aa.id = bb.id 
  aa.idで注文する
) asr;

MySQL がサブセットであるかどうかを判断する手順については、これでこの記事は終了です。MySQL がサブセットであるかどうかを判断する方法の詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: