SQL のカンマ区切り列にまたがってデータを効率的に結合するにはどうすればよいですか?

カンマ区切りデータ列の結合

カンマ区切り値 (CSV) は、一般に、単一列内の複数の値を格納するために使用されます。リレーショナルデータベース。ただし、この形式では、データ操作タスクを実行するときに問題が発生する可能性があります。この記事では、カンマ区切りの列間でデータを結合する手法について説明します。

シナリオ例

次の 2 つのテーブルについて考えてみましょう。

表 1 (T1)

col1	col2
C1	john
C2	alex
C3	piers
C4	sara

表 2 (T2)

col1	col2
R1	C1,C2,C4
R2	C3,C4
R3	C1,C4

必要な出力:

col1	col2
R1	john,alex,sara
R2	piers,sara
R3	john,sara

最適化のための正規化パフォーマンス

理想的には、テーブル 2 からカンマ区切りの値を削除してデータを正規化する必要があります。次の構造を持つ新しいテーブルを作成することで、

CREATE TABLE T2 (
    col1 varchar(2),
    col2 varchar(2),
    PRIMARY KEY (col1, col2),
    FOREIGN KEY (col2) REFERENCES T1 (col1)
);

データを挿入できます。したがって、効率的な結合が有効になります:

INSERT INTO T2 (col1, col2) VALUES ('R1', 'C1'), ('R1', 'C2'), ('R1', 'C4'), ('R2', 'C3'), ('R2', 'C4'), ('R3', 'C1'), ('R3', 'C4');

直接クエリ結合:

正規化されたテーブルを使用すると、単純な結合で目的のデータを取得できます:

SELECT t2.col1, t1.col2
FROM t2
INNER JOIN t1
ON t2.col2 = t1.col1;

カンマ区切り出力の連結:

目的の出力にカンマ区切りの値が必要な場合は、FOR XML PATH 関数と STUFF 関数を使用できます。採用:

SELECT DISTINCT t2.col1, 
STUFF(
    (SELECT DISTINCT ', ' + t1.col2
    FROM t1
    INNER JOIN t2 t ON t1.col1 = t.col2
    WHERE t2.col1 = t.col1
    FOR XML PATH ('')), 1, 1, '') AS col2
FROM t2;

非正規化データの分割関数:

正規化データがない場合は、カンマ区切りの値を個別に分割する分割関数を作成できます。 rows:

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
RETURNS @temptable TABLE (items varchar(MAX))       
AS       
BEGIN      
    DECLARE @idx int       
    DECLARE @slice varchar(8000)       

    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL RETURN       

    WHILE @idx != 0       
    BEGIN       
        SET @idx = CHARINDEX(@Delimiter, @String)       
        IF @idx != 0       
            SET @slice = LEFT(@String, @idx - 1)       
        ELSE       
            SET @slice = @String       

        IF(LEN(@slice) > 0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       

        SET @String = RIGHT(@String, LEN(@String) - @idx)       
        IF LEN(@String) = 0 BREAK       
    END   
RETURN 
END;

CTE (共通テーブル式) 内で分割関数を使用すると、データを処理できます:

WITH CTE AS
(
  SELECT c.col1, t1.col2
  FROM t1
  INNER JOIN 
  (
    SELECT t2.col1, i.items AS col2
    FROM t2
    CROSS APPLY dbo.Split(t2.col2, ',') i
  ) c
    ON t1.col1 = c.col2
) 
SELECT DISTINCT c.col1, 
STUFF(
    (SELECT DISTINCT ', ' + c1.col2
    FROM CTE c1
    WHERE c.col1 = c1.col1
    FOR XML PATH('')), 1, 1, '') AS col2
FROM CTE c

代替 FOR XML PATH クエリ:

別のアプローチには、FOR XML PATH の直接適用が含まれます:

SELECT col1, 
(
  SELECT ', '+t1.col2
  FROM t1
  WHERE ','+t2.col2+',' LIKE '%,'+CAST(t1.col1 AS VARCHAR(10))+',%'
  FOR XML PATH(''), TYPE
).value('SUBSTRING(TEXT()[1], 3)', 'VARCHAR(MAX)') AS col2
FROM t2;

結論

横断的に参加するカンマ区切りのデータでは、パフォーマンスと出力形式を慎重に考慮する必要があります。正規化は最適なパフォーマンスを提供しますが、それが不可能な場合は、分割関数または直接 FOR XML PATH クエリを使用することで代替手段を提供します。これらの手法により、効率的なデータ操作とカンマ区切り列からの取得が可能になります。

以上がSQL のカンマ区切り列にまたがってデータを効率的に結合するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。