Как повторно использовать рекурсивное предложение WITH в SQLite?

#sql #sqlite #common-table-expression #recursive-query

#sql #sqlite #common-table-expression #рекурсивный запрос

Вопрос:

У меня есть это хорошее рекурсивное предложение WITH:

 WITH RECURSIVE split(seq, word, str) AS (
        SELECT 0, null, replace('name one two three.jpg', '.jpg', ' ')
        UNION ALL SELECT
            seq 1,
            substr(str, 0, instr(str, ' ')),
            substr(str, instr(str, ' ') 1)
        FROM split WHERE str != ''
    ) SELECT word FROM split where seq>1

И результат:

 one
two
three

Теперь, как я могу повторно использовать это предложение, применяя SELECT name from Images вместо этой постоянной строки 'name one two three.jpg' ?

Цель состоит в том, чтобы извлечь все уникальные строки ‘ suffix’, которые можно найти во всем наборе имен изображений. Например, это образец данных:

 DROP TABLE IF EXISTS ImagesTemp;
CREATE TEMP TABLE ImagesTemp (name );
INSERT INTO ImagesTemp (name)
VALUES
  ('IMG_0403 newport malboro kool.jpg'),
  ('IMG_0404 camel newport.JPG'),
  ('IMG_0405 dunhill doral malboro.png');
SELECT * from ImagesTemp

И ожидаемый результат:

 word    count
malboro 2
newport 2
kool    1
dunhill 1
doral   1
camel   1

Ответ №1:

Рассмотрите возможность замены привязки CTE на select из вашей таблицы, например:

 WITH RECURSIVE split(seq, word, str) AS (
    SELECT 0, null, replace(name, '.jpg', ' ')
    FROM images
    UNION ALL 
    SELECT
        seq 1,
        substr(str, 0, instr(str, ' ')),
        substr(str, instr(str, ' ') 1)
    FROM split 
    WHERE str != ''
) 
SELECT word FROM split WHERE seq>1

1. Сначала я попробовал это, но это заставляло «DB Browser for SQLite» зависать. Добавление LIMIT 10000 внутри оператора inner select предотвратило зависание запроса, хотя я не доверял результатам. Но спасибо, что вы указали мне на решение.

Ответ №2:

Понял это! Хитрость здесь заключалась в том, чтобы убедиться, что имя файла заканчивается на ‘ ‘, чтобы рекурсивное предложение работало без зависания. Я также добавил счетчик для расширений имен файлов.

 WITH RECURSIVE split(seq, word, str, filename) AS (
    SELECT 0, null, lower(replace(name, '.', ' .')||' '), name from ImagesTemp
    UNION ALL 
    SELECT
        seq 1,
        substr(str, 0, instr(str, ' ')),
        substr(str, instr(str, ' ') 1),
        filename
    FROM split 
    WHERE str != ''
) 
SELECT distinct word, count(*) as count, filename as sample
FROM split WHERE word != '' and seq>1
GROUP BY word
ORDER BY count DESC

Результат:

 word    count   sample
newport 2   IMG_0403 newport malboro kool.jpg
malboro 2   IMG_0403 newport malboro kool.jpg
.jpg    2   IMG_0404 camel newport.JPG
kool    1   IMG_0403 newport malboro kool.jpg
dunhill 1   IMG_0405 dunhill doral malboro.png
doral   1   IMG_0405 dunhill doral malboro.png
camel   1   IMG_0404 camel newport.JPG
.png    1   IMG_0405 dunhill doral malboro.png