Существует ли версия функции 'CONTAINS' в SQLITE, отличная от 'LIKE'?

#sql #string #sqlite #csv

#sql #строка #sqlite #csv

Вопрос:

Я пытаюсь найти итоговые значения для каждого числа в диапазоне от 1 до 7. Но данные содержат разные комбинации этих чисел. Например, для 1; 2; 3,7; 1,2,3 и так далее. Я хочу найти общее количество раз, когда появляется каждое число. По сути, мне нужен код для SQLite, который выглядит так:

 select <fields>, count(*)
from tablexyz
where <field> contains '2' (and '3','4',... individually)

Когда я ввожу «where like’2%'» и тому подобное, это дает мне только все серии, которые начинаются с 2, но отрицает серию, которая начинается с 1, но содержит 2.

Любая помощь будет оценена!

1. Пожалуйста, предоставьте пример исходных данных и пример вывода, которые вы ожидаете от этих исходных данных.

2. Что вы имеете в виду CONTAINS ? Это не стандартный оператор SQL. Например, в T-SQL это оператор произвольного текстового поиска, который требует, чтобы индексы FTS обеспечивали достойную производительность. Вы могли бы использовать LIKE '%2%' для поиска полей, содержащих 2, в любом месте. Если вам нужен оператор, который будет разделять данные и определять конкретные поля, вы используете неправильный дизайн. Вы должны создать отдельную таблицу для хранения этих данных. SQLite — это встроенная база данных без множества наворотов. У него даже нет типов, а тем более массивов

3. Встроенная БД запускается вашим собственным приложением. Если вы пытаетесь делать то, с чем он не может справиться эффективно, вы в конечном итоге тратите время по сравнению с простым чтением данных и их анализом в своем коде. Вот что происходит в этом случае. SQLite не может индексировать эти значения, поэтому он должен сканировать их все. Было бы лучше, если бы вы просто прочитали все эти строки и использовали, например, регулярное выражение для проверки отдельных значений

Ответ №1:

Я хочу найти общее количество раз, когда появляется каждое число

Ваш пример кода и решение, которое, по вашим словам, вы хотите, не совсем совпадают. Самое близкое, о чем я могу думать, это

 with  t (txt) as -- a sample record from your table

(select '1; 2; 3,7; 1,2,3'),

t2 (num) as  -- a lookup table we can create for range of numbers 1-7

(select 1 union all
 select 2 union all
 select 3 union all
 select 4 union all
 select 5 union all
 select 6 union all
 select 7)

select t2.num, length(t.txt) - length(replace(t.txt,t2.num,'')) as num_occurence
from t2
left join t on t.txt like '%' || t2.num || '%'

Выводит

  ----- --------------- 
| num | num_occurence |
 ----- --------------- 
|   1 | 2             |
|   2 | 2             |
|   3 | 2             |
|   4 | NULL          |
|   5 | NULL          |
|   6 | NULL          |
|   7 | 1             |
 ----- ---------------

ДЕМОНСТРАЦИЯ

Ответ №2:

Используя приведенное ниже решение, вы можете создать «таблицу» из чисел от 1 до 7, затем присоединить ее к исходной таблице данных, чтобы подсчитать, встречается ли число в этой строке, а затем суммировать их вместе.

Запрос

 WITH
    sample_data (nums)
    AS
        (SELECT '1,2,3,4,5,6' 
         UNION ALL
         SELECT '3,4,5,6' 
         UNION ALL
         SELECT '1,2,7,6' 
         UNION ALL
         SELECT '6' ),
    search_nums (search_num)
    AS
         (VALUES(1)
          UNION ALL
          SELECT search_num 1 FROM search_nums WHERE search_num<7)
select search_num, sum(count_of_num) from (
SELECT s.nums,
       n.search_num,
       case
         instr(s.nums, n.search_num)
         when 0 then 0
         else 1
       end as count_of_num
  FROM sample_data  s, search_nums n
) group by search_num;

Результат

 search_num  sum(count_of_num)
1           2
2           2
3           2
4           2
5           2
6           4
7           1