将分隔列拆分为单独表的高效查询 [英] Efficient query to split a delimited column into a separate table
问题描述
我有一些数据,其中包含带有分隔数据的列.同一列中本质上有多个记录:
I have some data that includes a column with delimited data. There are multiple records in the same column essentially:
A0434168.A2367943.A18456972.A0135374.A0080362.A0084546.A0100991.A0064071.A0100858
这些值的长度可变,并以句点分隔.我一直在尝试使用游标为这些数据创建一个查找表.由于数据量大,游标速度异常缓慢.
The values are of variable length, and delimited by periods. I've been attempting to create a lookup table for this data, using a cursor. Due to the volume of data, the cursor is unreasonably slow.
我的光标如下所示:
DECLARE @ptr nvarchar(160)
DECLARE @aui nvarchar(15)
DECLARE @getmrhier3 CURSOR
SET @getmrhier3 = CURSOR FOR
SELECT cast(ptr as nvarchar(160)),aui
FROM mrhier3
FORWARD_ONLY
OPEN @getmrhier3
FETCH NEXT
FROM @getmrhier3 INTO @ptr, @aui
WHILE @@FETCH_STATUS = 0
BEGIN
if(len(@ptr) > 0)
begin
if(charindex('.',@ptr) > 0)
begin
insert into mrhierlookup(hieraui,aui)
values (substring(@ptr,0,charindex('.',@ptr)),@aui)
update mrhier3
set ptr = substring(@ptr,charindex('.',@ptr)+1,LEN(@ptr))
where aui = @aui
and ptr = @ptr
end
else
begin
insert into mrhierlookup(hieraui,aui)
values (@ptr,@aui)
update mrhier3
set ptr = ''
where aui = @aui
and ptr = @ptr
end
end
FETCH NEXT
FROM @getmrhier3 INTO @ptr, @aui
END
CLOSE @getmrhier3
DEALLOCATE @getmrhier3
游标的当前版本只作用于列的前导值.所有长度都是任意的.该列最多约 150 个字符长.
The current version of the cursor just works on the leading value of the column. All lengths are arbitrary. The column is at most ~150 characters long.
使用当前数据集,构建查找表可能需要数天时间.它将有几百万条记录.
With the current dataset, building the lookup table will likely take days. It will have several million records.
是否有更好的方法可以有效(快速)将这些数据解析到单独的表中,以便更快地执行连接操作?
Is there a better way to efficiently (quickly) parse out this data into a separate table for the purpose of performing join operations more quickly?
推荐答案
创建拆分函数:
CREATE FUNCTION dbo.SplitStrings(@List NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN ( SELECT Item FROM
( SELECT Item = x.i.value('(./text())[1]', 'nvarchar(max)')
FROM ( SELECT [XML] = CONVERT(XML, '<i>'
+ REPLACE(@List, '.', '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY [XML].nodes('i') AS x(i) ) AS y
WHERE Item IS NOT NULL
);
GO
然后去掉所有的光标和循环废话,然后这样做:
Then get rid of all the cursor and looping nonsense and do this:
INSERT dbo.mrhierlookup
(
heiraui,
aui
)
SELECT s.Item, m.aui
FROM dbo.mrhier3 AS m
CROSS APPLY dbo.SplitStrings(m.ptr) AS s
GROUP BY s.Item, m.aui;
这篇关于将分隔列拆分为单独表的高效查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!