sql - 计算嵌入在字符串中的总文件名和唯一文件名

Question

我有一个表格，其中有一列名为 ExcelLinks 的列包含如下记录:

=INDEX('\\san1\engData[BT_500.0_Structural_Position.xls]Concrete'!$B$4:$IK$83,MATCH($K$9,'\\san1\engData[BT_500.0_Structural_Position.xls]Concrete'!$A$4:$A$83,0),MATCH(C212,'\\san1\engData[BT_500.0_Structural_Position.xls]Concrete'!$B$3:$IK$3,0))/1000000

=INDEX('\\san1\engData[GK_600.0_Pumps.xls]Pumps'!$B$4:$BD$39,MATCH($K$9,'\\san1\engData[TT_640.0_Generator.xls]Generator'!$A$4:$A$39,0),MATCH(C214,'\\san1\engData[GK_600.0_Pumps.xls]Pumps'!$B$3:$BD$3,0))/1000000

=INDEX('\\san1\engData[TT_640.0_Generator.xls]Generator'!$B$4:$HU$83,MATCH($K$9,'\\san1\engData[GK_600.0_Pumps.xls]Pumps'!$A$4:$A$83,0),MATCH(C218,'\\san1\engData[TT_640.0_Generator.xls]Generator'!$B$3:$HU$3,0))/1000000

理想的输出是:

_______________________________________
| Row  |  LinkCount |  UniqueLinkCount |
| 1    |     3      |        1         |
| 2    |     3      |        2         |
| 3    |     3      |        2         |

我想查询此数据并查看每条记录使用的文件数和唯一文件数。

我在网上进行了搜索，但找不到任何可以做到这一点的东西。

我想我会制作一个游标，对于每条记录，我都会检测以 \\ 开头的字符。并以 '!$ 结尾并计算文件的数量。

难点是带有 =INDEX 的 ExcelLinks和 MATCH使用多个互连(可能是不同的文件)的函数。

该表中有超过 1200 万条记录，所以我担心使用游标的性能。

有一些更好的方法可以使用 Oracle using RegEx's 来做到这一点。 .我知道 SQL Server 没有 RegEx，如果这是最简单的选择，我愿意编写/使用 CLR 存储过程。

Answer 1

一、grab this string splitting CLR function from Adam Machanic .将代码编译成 DLL(using csc 如果你没有 Visual Studio)，将 DLL 复制到你的服务器，然后按如下方式注册 DLL(你必须在这里替换一些变量部分，例如文件路径、您想调用程序集的名称等):

CREATE ASSEMBLY CLRStuff 
  FROM 'C:\DLLs\CLRStuff.dll'  
  WITH PERMISSION_SET = SAFE;
GO

CREATE FUNCTION dbo.SplitStrings
(
   @List      NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE ( Item NVARCHAR(4000) )
  EXTERNAL NAME CLRStuff.UserDefinedFunctions.SplitString_Multi;
GO

有了这个，查询本身就很容易了。让我们创建一个包含几行的简单表变量(为简洁起见，我缩短了路径):

DECLARE @x TABLE(i INT, ExcelLink VARCHAR(MAX));

INSERT @x

    -- 3 files, 1 unique: 
    SELECT 1,'=INDEX(''\\san1\a.xls''!$B$4:$IK$83,MATCH($K$9,''\\san1\a.xls'
    + '''!$A$4:$A$83,0),MATCH(C212,''\\san1\a.xls''!$B$3:$IK$3,0))/1000000'

UNION ALL 

    -- 3 files, 3 unique:
    SELECT 2,'=INDEX(''\\san1\a.xls''!$B$4:$BD$39,MATCH($K$9,''\\san1\b.xls'
    + '''!$A$4:$A$39,0),MATCH(C214,''\\san1\c.xls''!$B$3:$BD$3,0))/1000000'

UNION ALL 

    -- 3 files, 2 unique:
    SELECT 3,'=INDEX(''\\san1\b.xls''!$B$4:$HU$83,MATCH($K$9,''\\san1\c.xls'
    + '''!$A$4:$A$83,0),MATCH(C218,''\\san1\c.xls''!$B$3:$HU$3,0))/1000000'

UNION ALL 

    -- 1 file, 1 unique:
    SELECT 4,'=INDEX(''\\san1\foo.xls''!$B$4:$HU$83,0)';

-- the above was just inserts; the remainder is all of the query:

;WITH x(i,part) AS 
(
  SELECT x.i, SUBSTRING(t.Item, CHARINDEX('''\\', t.Item), 2048) 
    FROM @x AS x CROSS APPLY dbo.SplitStrings(x.ExcelLink, '!$') AS t
)
SELECT i, [file_count] = COUNT(part), [unique_files] = COUNT(DISTINCT part)
  FROM x WHERE part LIKE '''\\%'
  GROUP BY i ORDER BY i;

结果:

i   file_count  unique_files
--  ----------  ------------
1   3           1
2   3           3
3   3           2
4   1           1

这依赖于 \\除了作为文件路径的开头之外，不会自然地出现在数据中，并且所有文件路径都驻留在网络共享上。

这可能不是您能获得的最有效的方法 - 我确信某些 RegEx 向导可以使用这种方法而不是拆分( here is a good article to get you started )来改进这一点，但这不是我的强项。大部分成本将是扫描整个表所需的 I/O，而不是计数或替换。

如果您不能使用 CLR，则可以将该函数替换为任意数量的非 CLR 版本 ( here is an example that would be a functionally suitable replacement )，但请记住，其他方法可能无法获得最佳性能。