Блог

Прежде всего, я пытаюсь добиться следующего:

Сначала я использовал MS SQL, чтобы выяснить, как я могу выровнять теги вместе.

Вот схема, если вы хотите воссоздать ненормализованную таблицу.

 CREATE TABLE unnormalized(
  vendor_tag varchar(200),
  vendor_tag_name varchar(200),
  vendor_id int
  );
  
 INSERT INTO unnormalized
VALUES
('5,8,30,24','Burgers,Desserts,Fries,Salads',1),
('5','Burgers',2),
('8,42','Desserts,Mexican',3),
('1,5,30,16','American,Burgers,Fries,Sandwiches',4),
('1,5,30,16','American,Burgers,Fries,Sandwiches',5);

 

Вот код для нормализованной таблицы

 SELECT
    --*
    DISTINCT CAST(tag_id AS INT) as tag_id ,tag_name
FROM unnormalized 
CROSS APPLY 
(
    (SELECT 
        value as tag_id,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
     FROM STRING_SPLIT(vendor_tag,',') 
    ) a1
    INNER JOIN 
    (SELECT 
        value as tag_name,
        ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
     FROM STRING_SPLIT(vendor_tag_name,',') 
     ) a2
    ON a1.rn = a2.rn
) 
ORDER BY tag_id
 

Теперь я пытаюсь переписать этот код с помощью SQLite. Однако есть несколько отличий, таких как ‘CROSS APPLY’ и ‘STRING_SPLIT’, отсутствующих в SQLite. Я огляделся и обнаружил, что ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ, возможно, похоже на «ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ» в SQLite и, возможно, использует что-то подобное, чтобы отделить строку от первой найденной запятой??

 WITH split(vendor_id, vendor_tag, str) AS (
    SELECT vendor_id, '', vendor_tag||',' FROM unnormalized
    UNION ALL SELECT vendor_id,
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',') 1)
    FROM split 
    WHERE str
) 

SELECT vendor_id, vendor_tag
FROM split 
WHERE vendor_tag
ORDER BY vendor_id;
 

В SQLite вы можете сделать это с помощью рекурсивного CTE:

 WITH cte AS (
  SELECT 
    vendor_tag, 
    vendor_tag_name,
    SUBSTR(vendor_tag, 1, INSTR(vendor_tag || ',', ',') - 1) col1,
    SUBSTR(vendor_tag_name, 1, INSTR(vendor_tag_name || ',', ',') - 1) col2
  FROM unnormalized 
  UNION ALL 
  SELECT 
    SUBSTR(vendor_tag, LENGTH(col1)   2), 
    SUBSTR(vendor_tag_name, LENGTH(col2)   2), 
    SUBSTR(SUBSTR(vendor_tag, LENGTH(col1)   2), 1, INSTR(SUBSTR(vendor_tag, LENGTH(col1)   2) || ',', ',') - 1),
    SUBSTR(SUBSTR(vendor_tag_name, LENGTH(col2)   2), 1, INSTR(SUBSTR(vendor_tag_name, LENGTH(col2)   2) || ',', ',') - 1)
  FROM cte  
  WHERE LENGTH(vendor_tag) AND LENGTH(vendor_tag_name)
)
SELECT DISTINCT col1 vendor_tag, col2 vendor_tag_name
FROM cte
WHERE NOT (INSTR(col1, ',') OR INSTR(col2, ',')) AND (LENGTH(col1) AND LENGTH(col2))
ORDER BY vendor_tag   0
 

Посмотрите демонстрацию.
Результаты:

 > vendor_tag | vendor_tag_name
> :--------- | :--------------
> 1          | American       
> 5          | Burgers        
> 8          | Desserts       
> 16         | Sandwiches     
> 24         | Salads         
> 30         | Fries          
> 42         | Mexican