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

サブセットかどうかを判断するための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 をよろしくお願いいたします。

以下もご興味があるかもしれません:
  • MySql8 WITH RECURSIVE 再帰クエリ親子コレクションメソッド

<<:  Nginx ロードバランシングとは何か、そしてそれをどのように設定するか

>>:  CSSは座席選択効果を実現するためにautoflow属性を使用する

推薦する

シンプルなカルーセル効果を実現するネイティブ js

この記事では、シンプルなカルーセル効果を実現するためのjsの具体的なコードを参考までに紹介します。具...

複数のフィールドを変更するためのMysql更新の構文の詳細な分析

MySQL でレコードを更新すると、構文は正しいのですが、レコードが更新されません...質問文実行前...

Linux環境でログファイルを表示するコマンドの詳細な説明

目次序文1. catコマンド: 2. moreコマンド: 3. lessコマンド: 4. headコ...

Nodejs モジュール システムのソースコード分析

目次概要CommonJS 仕様Node の CommonJS 仕様の実装モジュールのエクスポートとイ...

標準SQL更新ステートメントの3つの用途についての簡単な理解

1. 環境: MySQL-5.0.41-win32 Windows XP プロフェッショナル2. テ...

格納可能なセカンダリメニューを実装するための JavaScript

JavaScriptで格納可能なセカンダリメニューを実装するための具体的なコードは参考までに。具体...

MySQL サーバー 5.5 の接続失敗の解決策

mysqlに接続できない問題の解決方法を参考までに紹介します。具体的な内容は以下のとおりです。昨日は...

CSSでフォントアイコンを使用する方法をお教えします

まず、フォントアイコンとは何でしょうか?表面的にはアイコンですが、実際はテキストです。テキストの設定...

JS の querySelector メソッドと getElementById メソッドの違い

目次1. 概要1.1 querySelector() と querySelectorAll() の使...

DIV、テーブル、XHTML のウェブサイト構築の違いの分析と説明

簡単に言えば、ウェブサイト構築とは、「この人はどんな外見をしているのか」と「この人はどんな内面を持っ...

MySQL 圧縮版 zip のインストールに関する問題の解決策

本日、MySQLの圧縮版をインストールする際に問題が発生しました。サービスが起動できず、2、3時間苦...

nginx でディレクトリ ホワイトリストと IP ホワイトリストを設定する方法

1. ディレクトリホワイトリストを設定する:指定されたリクエストパスに制限を設定しないでください。た...

NginxはIP経由の直接アクセスを禁止し、カスタム500ページにリダイレクトします

設定ファイルに直接 サーバー{ listen 80 default; # IPへの直接アクセスを禁止...

検索履歴を実装するjQueryプラグイン

毎日jQueryプラグイン - 検索履歴を作成するためのものです。参考までに、具体的な内容は次のとお...

MySQLの日次統計レポートでは、その日にデータがない場合には0が入力されます。

1. 問題の再現:各日の合計数を日ごとにカウントします。データのない日がある場合、グループ化によっ...