Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
178 views
in Technique[技术] by (71.8m points)

tsql - How to do this in SQL Server query instead of function?

I have a table that has a string in one of its columns.

My table look like this:

RowCnt Lvl TargetID Codes
1000 1 0 1,1,0,1,0,1,...,1,0,0,0,0
1000 1 1 0,0,1,0,1,0,...,0,1,1,1,1
1000 1 2 1,0,0,0,1,1,...,0,0,0,0,0
1000 1 3 0,1,1,1,0,1,...,1,1,1,1,1
1000 1 4 1,1,0,0,1,0,...,0,0,1,0,0
1000 2 0 0,0,1,1,0,1,...,0,1,0,1,1
1000 2 1 0,1,0,1,1,1,...,1,1,1,1,0
1000 2 2 0,0,0,0,0,1,...,0,0,0,0,1
1500 1 0 1,1,1,1,1,0,...,1,1,1,1,0
1500 1 1 1,0,0,0,0,1,...,0,0,0,0,1
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

A faster version of your function would be an inline Table Valued Function.

CREATE OR ALTER FUNCTION dbo.Similar (@x varchar(max), @y varchar(max))
RETURNS TABLE AS RETURN

SELECT COUNT(CASE WHEN xJ.value <> yJ.value THEN 1 END) * 1.0 / COUNT(*) AS Pct
FROM (
    SELECT *,
      ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn
    FROM STRING_SPLIT(@x, ',')
) xJ
JOIN (
    SELECT *,
      ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rn
    FROM STRING_SPLIT(@y, ',')
) yJ ON yJ.rn = xJ.rn;

However, STRING_SPLIT with a row-number is not guaranteed to always return results in the actual order of the string. It may do it once, it may do it a million times, but there is always a chance the compiler could rearrange things. So instead you could use OPENJSON

CREATE OR ALTER FUNCTION dbo.Similar (@x varchar(max), @y varchar(max))
RETURNS TABLE AS RETURN

SELECT COUNT(CASE WHEN xJ.value <> yJ.value THEN 1 END) * 1.0 / COUNT(*) AS Pct
FROM OPENJSON('[' + @x + ']') xJ
JOIN OPENJSON('[' + @y + ']') yJ ON yJ.[key] = xJ.[key];

You would use it like this

WITH Y AS (
    select
      a.RowCnt,
      a.Lvl,
      a.TargetID a_TargetID,
      b.targetid b_TargetID,
      a.codes a_codes,
      b.codes b_codes,
      sim.Pct sim
    from TargetsComp A
    inner join TargetsComp B
        on a.RowCnt = b.RowCnt 
       and a.TargetID < b.TargetID
    CROSS APPLY dbo.sim(a.codes, b.codes) sim
)
insert into TargetFilled
  (RowCnt, Lvl, a_TargetID, b_TargetID, a_codes, b_codes, sim)
SELECT RowCnt, Lvl, a_TargetID, b_TargetID, a_codes, b_codes, sim
FROM Y;
-- you may want to add
-- WHERE sim.Pct < 100

I have removed the ORDER BY from the insert as I don't think it's necessary.

You should index your table as follows

CLUSTERED INDEX ON TargetsComp (RowCnt, TargetID)

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...