регулярные - Число совпадений между двумя строками mysql



mysql регулярные выражения (1)

Итак, это проблема:

У меня две таблицы:

Эталон:

+-----+-----+-----+-----+----+
|  e1 |  e2 |  e3 |  e4 | e5 |
+-----+-----+-----+-----+----+
|  01 |  02 |  03 |  04 | 05 |
+-----+-----+-----+-----+----+

А кандидаты:

+-----+----+-----+-----+-----+----+----+
| ID  | c1 | c2  | c3  | c4  | c5 | nn |
+-----+----+-----+-----+-----+----+----+
| 00  | 03 | 08  | 02  | 01  | 06 | ** |
+-----+----+-----+-----+-----+----+----+
| 01  | 05 | 04  | 03  | 02  | 01 | ** |
+-----+----+-----+-----+-----+----+----+
| 02  | 06 | 07  | 08  | 09  | 10 | ** |
+-----+----+-----+-----+-----+----+----+
| 03  | 08 | 06  | 09  | 02  | 07 | ** |
+-----+----+-----+-----+-----+----+----+

Какой запрос я должен использовать, чтобы найти и сохранить (в nn столбце) количество совпадений между двумя строками (e1, e2, e3, e4, e5 и c1, c2, c3, c4, c5) для каждой строки в кандидате таблицы?

Должен быть следующий результат:

Кандидаты:

|-----|----|-----|-----|-----|-----|----|
| ID  | c1 | c2  | c3  | c4  | c5  | nn |
|-----|----|-----|-----|-----|-----|----|
| 00  | 03 | 08  | 02  | 01  | 06  | 03 |
|-----|----|-----|-----|-----|-----|----|
| 01  | 05 | 04  | 03  | 02  | 01  | 05 |
|-----|----|-----|-----|-----|-----|----|
| 02  | 06 | 07  | 08  | 09  | 10  | 00 |
|-----|----|-----|-----|-----|-----|----|
| 03  | 08 | 06  | 09  | 02  | 07  | 01 |
|-----|----|-----|-----|-----|-----|----|

Результат для nn:

0 - no matches
1,2,3,4,5 - numbers of matches 

Как я могу это достичь?


Цель состоит в том, чтобы установить максимальное частичное совпадение между главной строкой и каждой строкой таблицы клиентов без учета соответствующих идентификаторов столбцов.

Идея состоит в том, чтобы абстрагироваться от идентификаторов столбцов, представляя содержимое столбца по-другому. Поскольку вы указали, что область значений является {1, ..., 10} , можно выбрать первые 10 простых чисел {p_1, ...,p_10} = { 2, 3, 5, 7, 11, 13, 17, 19, 23, 29 } , сопоставление i с p_i . Сравнение будет основано на продукте отображаемых значений столбцов. Этот подход использует уникальность простой факторизации, т. Е. каждое положительное целое факторизуется в единственное множество множеств простых чисел.

Однократное автономное заявление об обновлении sql довольно сложно записать, поэтому мы создаем временную таблицу, содержащую продукты отображаемых значений:

CREATE TEMPORARY TABLE t_pp (
      id            NUMBER
    , mp_candidates NUMBER
    , mp_etalon     NUMBER
    , nn            NUMBER
);
INSERT INTO t_pp ( id, mp_candidates, mp_etalon )
     SELECT id
          ,   CASE c1
                  WHEN  1 THEN  2
                  WHEN  2 THEN  3
                  WHEN  3 THEN  5
                  WHEN  4 THEN  7
                  WHEN  5 THEN 11
                  WHEN  6 THEN 13
                  WHEN  7 THEN 17
                  WHEN  8 THEN 19
                  WHEN  9 THEN 23
                  WHEN 10 THEN 29
                  ELSE         31
              END
            * CASE c2 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
            * CASE c3 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
            * CASE c4 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
            * CASE c5 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
                mp_candidates

          ,   CASE e1
                  WHEN  1 THEN  2
                  WHEN  2 THEN  3
                  WHEN  3 THEN  5
                  WHEN  4 THEN  7
                  WHEN  5 THEN 11
                  WHEN  6 THEN 13
                  WHEN  7 THEN 17
                  WHEN  8 THEN 19
                  WHEN  9 THEN 23
                  WHEN 10 THEN 29
                  ELSE         31
              END
            * CASE e2 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
            * CASE e3 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
            * CASE e4 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
            * CASE e5 WHEN  2 THEN  3 WHEN  3 THEN  5 WHEN  4 THEN  7 WHEN  5 THEN 11 WHEN  6 THEN 13 WHEN  7 THEN 17 WHEN  8 THEN 19 WHEN  9 THEN 23 WHEN 10 THEN 29 ELSE 31 END
                mp_etalon
          , 0   nn
       FROM candidates
 CROSS JOIN etalon     
          ;

Теперь для прохождения №2 - подсчеты совпадений:

UPDATE t_pp
   SET nn =
             CASE WHEN mp_candidates MOD  2 = 0 AND mp_etalon MOD  2 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD  3 = 0 AND mp_etalon MOD  3 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD  5 = 0 AND mp_etalon MOD  5 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD  7 = 0 AND mp_etalon MOD  7 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD 11 = 0 AND mp_etalon MOD 11 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD 13 = 0 AND mp_etalon MOD 13 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD 17 = 0 AND mp_etalon MOD 17 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD 19 = 0 AND mp_etalon MOD 19 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD 23 = 0 AND mp_etalon MOD 23 = 0  THEN 1 ELSE 0 END
           + CASE WHEN mp_candidates MOD 29 = 0 AND mp_etalon MOD 29 = 0  THEN 1 ELSE 0 END
     ;

Наконец, перенос результатов в исходную таблицу и очистка:

UPDATE candidates c
   set nn = ( SELECT p.nn FROM t_pp p WHERE p.id = c.id )
     ;
DELETE TEMPORARY TABLE t_pp;

Еще несколько примечаний:

  • Приведенная схема предполагает, что значения ячеек уникальны в каждой строке. Тем не менее, его можно легко расширить, чтобы допускать множественные вхождения значений.
  • В принципе, это можно обернуть в одном заявлении sql - по очевидным причинам это не рекомендуется.
  • Rdbms, кроме mysql, следуют стандарту sql и предоставляют предложение WITH , которое устраняет необходимость в таблице временных рядов.
  • Значение 31 в ветви ELSE вышеуказанных выражений CASE является фиктивным значением.




numbers