ルックアップ列を変更した場合のMySQLルックアップの最も近い値または完全に一致する値

debugcn 投稿 Dev

ジョシュ

私は次のデータセットを持っています...

状態監視ロケーションデータ（CMLテーブル）

+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+
| CML_ID |     POF_COLUMN     |      CML_TYPE      | SAMPLE_VALUE | COMPLIANCE | CORROSION_SEVERITY | LR_LD | POF |
+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+
|      1 | SAMPLE_VALUE       | MIC_SAMPLING_POINT |            5 | NO         | MINOR              |     1 |     |
|      2 | SAMPLE_VALUE       | MIC_SAMPLING_POINT |          0.5 | NO         | MINOR              |     2 |     |
|      3 | SAMPLE_VALUE       | MIC_SAMPLING_POINT |           20 | NO         | MINOR              |     3 |     |
|      4 | COMPLIANCE         | VALVE_ROTATED      |            0 | YES        | MINOR              |     4 |     |
|      5 | LR_LD              | PIPING_THICKNESS   |            0 | YES        | MINOR              |   0.1 |     |
|      6 | CORROSION_SEVERITY | VESSEL_SHELL       |            0 | NO         | SEVERE             |     0 |     |
|      7 | CORROSION_SEVERITY | NOZZLE             |            0 | NO         | LOW                |     0 |     |
+--------+--------------------+--------------------+--------------+------------+--------------------+-------+-----+

障害ルックアップデータの確率（POFテーブル）

+--------------------+--------------------+-------------+-----+
|     POF_COLUMN     |      CML_TYPE      | VALUE_RANGE | POF |
+--------------------+--------------------+-------------+-----+
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 1           |   5 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 5           |   4 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 10          |   3 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 15          |   2 |
| SAMPLE_VALUE       | MIC_SAMPLING_POINT | 100         |   1 |
| COMPLIANCE         | VALVE_ROTATED      | YES         |   5 |
| COMPLIANCE         | VALVE_ROTATED      | NO          |   1 |
| LR_LD              | PIPING_THICKNESS   | 2           |   5 |
| LR_LD              | PIPING_THICKNESS   | 1.5         |   4 |
| LR_LD              | PIPING_THICKNESS   | 1           |   3 |
| LR_LD              | PIPING_THICKNESS   | 0.8         |   2 |
| LR_LD              | PIPING_THICKNESS   | 0.5         |   1 |
| CORROSION_SEVERITY | VESSEL_SHELL       | NEGLIGIBLE  |   5 |
| CORROSION_SEVERITY | VESSEL_SHELL       | LOW         |   4 |
| CORROSION_SEVERITY | VESSEL_SHELL       | MEDIUM      |   3 |
| CORROSION_SEVERITY | VESSEL_SHELL       | HIGH        |   2 |
| CORROSION_SEVERITY | VESSEL_SHELL       | SEVERE      |   1 |
| CORROSION_SEVERITY | NOZZLE             | NEGLIGIBLE  |   5 |
| CORROSION_SEVERITY | NOZZLE             | LOW         |   5 |
| CORROSION_SEVERITY | NOZZLE             | MEDIUM      |   5 |
| CORROSION_SEVERITY | NOZZLE             | HIGH        |   3 |
| CORROSION_SEVERITY | NOZZLE             | SEVERE      |   2 |
+--------------------+--------------------+-------------+-----+

また、CMLの各CMLレコードの[POF_Column]、[CML_Type]、および[SAMPLE_VALUE]、[COMPLIANCE]、[CORROSION_SEVERITY]、[LR_LD]のいずれかのフィールドを使用して、POFテーブルのクローゼットまたは完全に一致するレコードを返す必要がありますテーブル。次に、POFをCMLテーブルに記録するように更新します。

たとえば、CML_ID = 2を見ると。

[POF_COLUMN] = 'SAMPLE_VALUE'、[CML_TYPE] = 'MIC_SAMPLING_POINT'および[VALUE_RANGE]でPOFテーブルをフィルタリングします-[SAMPLE_VALUE]列の値（この場合は0.5）は最小です。

この場合、POFテーブルのこの最初のレコードと一致し、POF = 5の値を返します。

別のケースを見ると。CML_ID = 7。

[POF_COLUMN] = 'CORROSION_SEVERITY'、[CML_TYPE] = 'NOZZLE'、[VALUE_RANGE] = [CORROSION_SEVERITY]列の値（この場合は 'LOW'）でPOFテーブルをフィルタリングします。

この場合、POFテーブルの下から4番目の行に一致し、POF = 5の値を返します。

要約すると、次の結果を表示するには、CMLテーブルを更新する必要があります...

+--------+---+-----+
| CML_ID | … | POF |
+--------+---+-----+
|      1 | … |   4 |
|      2 | … |   5 |
|      3 | … |   2 |
|      4 | … |   5 |
|      5 | … |   1 |
|      6 | … |   1 |
|      7 | … |   5 |
+--------+---+-----+

誰かが私がこれを達成する方法を知っていますか？これを試みたサンプルコードを以下に示します。これは、完全に一致する値を検索するために機能しますが、最も近い一致する値を検索するためには機能しません。

DROP DATABASE IF EXISTS VESSELS;
CREATE DATABASE VESSELS;
USE VESSELS;

CREATE TABLE CML (
  `CML_ID`              INTEGER NOT NULL,
  `POF_COLUMN`          VARCHAR(50),
  `CML_TYPE`            VARCHAR(50),
  `SAMPLE_VALUE`        FLOAT,
  `COMPLIANCE`          ENUM('YES','NO'),
  `CORROSION_SEVERITY`  VARCHAR(50),
  `LR_LD`               FLOAT,
  `POF`                 TINYINT,
  PRIMARY KEY (CML_ID)
);

CREATE TABLE POF (
  `POF_COLUMN`   VARCHAR(50),
  `CML_TYPE`     VARCHAR(50),
  `VALUE_RANGE`  VARCHAR(100),
  `POF`          TINYINT,
  PRIMARY KEY (POF_COLUMN, CML_TYPE, VALUE_RANGE)
);

INSERT INTO CML 
VALUES (1, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 5, 'NO', 'MINOR', 1, NULL),
       (2, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 0.5, 'NO', 'MINOR', 2, NULL),
       (3, 'SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 20, 'NO', 'MINOR', 3, NULL), 
       (4, 'COMPLIANCE', 'VALVE_ROTATED', 0, 'YES', 'MINOR', 4, NULL), 
       (5, 'LR_LD', 'PIPING_THICKNESS', 0, 'YES', 'MINOR', 0.1, NULL),
       (6, 'CORROSION_SEVERITY', 'VESSEL_SHELL', 0, 'NO', 'SEVERE', 0, NULL),
       (7, 'CORROSION_SEVERITY', 'NOZZLE', 0, 'NO', 'LOW', 0, NULL);

INSERT INTO POF 
VALUES ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 1, 5),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 5, 4),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 10, 3),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 15, 2),
       ('SAMPLE_VALUE', 'MIC_SAMPLING_POINT', 100, 1),
       ('COMPLIANCE', 'VALVE_ROTATED', 'YES', 5), 
       ('COMPLIANCE', 'VALVE_ROTATED', 'NO', 1), 
       ('LR_LD', 'PIPING_THICKNESS', 2, 5),
       ('LR_LD', 'PIPING_THICKNESS', 1.5, 4),
       ('LR_LD', 'PIPING_THICKNESS', 1, 3),
       ('LR_LD', 'PIPING_THICKNESS', 0.8, 2),
       ('LR_LD', 'PIPING_THICKNESS', 0.5, 1),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'NEGLIGIBLE', 5),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'LOW', 4),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'MEDIUM', 3),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'HIGH', 2),
       ('CORROSION_SEVERITY', 'VESSEL_SHELL', 'SEVERE', 1),
       ('CORROSION_SEVERITY', 'NOZZLE', 'NEGLIGIBLE', 5),
       ('CORROSION_SEVERITY', 'NOZZLE', 'LOW', 5),
       ('CORROSION_SEVERITY', 'NOZZLE', 'MEDIUM', 5),
       ('CORROSION_SEVERITY', 'NOZZLE', 'HIGH', 3),
       ('CORROSION_SEVERITY', 'NOZZLE', 'SEVERE', 2);

-- UPDATE EXACT MATCHING VALUE
UPDATE CML c
LEFT JOIN POF p
ON c.POF_COLUMN = p.POF_COLUMN
   AND c.CML_TYPE = p.CML_TYPE
   AND ( (c.POF_COLUMN = 'COMPLIANCE' AND c.COMPLIANCE = p.VALUE_RANGE) OR
         (c.POF_COLUMN = 'SAMPLE_VALUE' AND c.SAMPLE_VALUE = p.VALUE_RANGE) OR
         (c.POF_COLUMN = 'LR_LD' AND c.LR_LD = p.VALUE_RANGE) OR
         (c.POF_COLUMN = 'CORROSION_SEVERITY' AND c.CORROSION_SEVERITY = p.VALUE_RANGE)
       )
SET c.POF = p.POF;

SELECT * FROM CML;

âńōŋŷXmoůŜ

サンプルデータと期待される結果を含む非常に詳細な投稿に賛成してください。問題はsample_valueとlr_ldにあります。これは、POFテーブルの正確な値ではない可能性があるためです。ただし、値が値の範囲以下であることに気付くでしょう。

したがって、sample_valueまたはlr_ldが値の範囲以下であるPOF値の最大値を取得する場合は、POF値の最大値を取得するだけで済みます。

このクエリは、sample_valueまたはlr_ldが増加するとPOF値が増加するためにのみ機能します。

UPDATE CML c
  JOIN 
(
    select c.CML_ID, max(p.POF) POF 
    from CML c
    LEFT JOIN POF p
    ON c.POF_COLUMN = p.POF_COLUMN
       AND c.CML_TYPE = p.CML_TYPE
       AND ( (c.POF_COLUMN = 'COMPLIANCE' AND c.COMPLIANCE = p.VALUE_RANGE) OR
             (c.POF_COLUMN = 'SAMPLE_VALUE' AND  c.SAMPLE_VALUE<=p.VALUE_RANGE) OR
             (c.POF_COLUMN = 'LR_LD' AND c.LR_LD  <= p.VALUE_RANGE) OR
             (c.POF_COLUMN = 'CORROSION_SEVERITY' AND c.CORROSION_SEVERITY = p.VALUE_RANGE)
           )
     group by c.CML_ID 
   ) t
 on c.CML_ID = t.CML_ID
set c.POF = t.POF;

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集2021-06-1

コメントを追加

サインイン

分類Dev

Related 関連記事

記事