SQL Server - 使用聚集索引时如何存储数据页

Question

Bob Klimes

Asked: 2018-01-10 11:37:06 +0800 CST2018-01-10 11:37:06 +0800 CST 2018-01-10 11:37:06 +0800 CST

在没有函数的情况下以相同的顺序拆分两个分隔的字符串

772

我正在尝试将带有分隔字符串的两列拆分为行。每个字符串中值的位置是相关的，因此我试图将其拆分，以便相关值在一行中。我无法使用函数，因为我无法在数据库中创建对象

这是示例表和数据

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

我想要的输出是

ID  key  val
1   1    one
1   2    two
1   3    three
2   4    four
2   5    five
2   6    six
3   7    seven
3   8    eight
3   9    nine

如果我只拆分一列，我的查询就可以工作，所以我用 row_number 定义了两个 CTE，并在 ID 和 row_number 上加入。这确实提供了所需的输出，但我的实时表非常大，我希望有一种方法可以只通过表一次，而不是两次。

with keys as(
SELECT id,keys,vals,
       keys.keyid.value('.', 'VARCHAR(8000)') AS keyid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<Keys><key>'+REPLACE(keys, ',', '</key><key>')+'</key></Keys>' AS XML) AS tempkeys
    FROM #temp
) AS temp
CROSS APPLY tempkeys.nodes('/Keys/key') AS keys(keyid)),
vals as(
SELECT id,keys,vals,
       vals.val.value('.', 'VARCHAR(8000)') AS valid,
      row_number() over(order by (select null)) as rn
FROM
(SELECT id,keys,vals,
           CAST('<vals><val>'+REPLACE(vals, ',', '</val><val>')+'</val></vals>' AS XML) AS tempvals
    FROM #temp
) AS temp
CROSS APPLY tempvals.nodes('/vals/val') AS vals(val))


SELECT k.id, k.keyid, v.valid
FROM keys AS k
     INNER JOIN vals AS v
     ON k.id = v.id
        AND k.rn = v.rn;

2 个回答

Voted

Aaron Bertrand · Answer 1 · 2018-01-10T12:51:00+08:00

msdb在其他地方或其他地方创建函数。

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  varchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH src(r) AS 
      (
        SELECT 1 UNION ALL SELECT r + 1 FROM src WHERE r < 10
      ),
      Numbers(Number) AS 
      (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
        FROM src AS s1, src AS s2 -- add more if you need longer strings
      ),
      parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1 
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2 
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM Numbers AS n1, Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );

然后，正如@gbn 所指出的，在您的查询必须运行的任何地方通过 3 部分名称引用它。

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

SELECT t.id, f.s1, f.s2 FROM #temp AS t
  CROSS APPLY msdb.dbo.SplitTwoStringsWithSameOrder(keys, vals, ',') AS f
  ORDER BY t.id, f.r1;
GO

DROP TABLE #temp;

结果：

计划资源管理器中显示的最终计划（免责声明：我是产品经理）不是我见过的最漂亮的东西（点击放大一点）：

但是只有一次扫描#temp（4% 的成本）。最大的成本是两种和一个线轴，并且由于工作台而存在一些 I/O，我不确定这是否可以避免。

如果您知道这些字符串中的任何一个都只能有 50 个字符，那么您可以使用内置Numbers表格获得一个更简单的计划（人们反对这些，但它们非常有用，而且它们几乎总是在内存中如果你足够引用它们）。这对 I/O 没有帮助，但删除递归 CTE 和其他在函数内部构建数字的结构对 CPU 等非常有帮助。

一、数字表：

DROP TABLE dbo.Numbers;

;WITH n AS
(
    SELECT
        TOP (50) rn = ROW_NUMBER() OVER
        (ORDER BY [object_id])
    FROM sys.all_columns 
    ORDER BY [object_id]
)
SELECT [Number] = rn - 1
INTO dbo.Numbers
FROM n;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

然后是函数的第二个版本：

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder2
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  nvarchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1 
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2 
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM dbo.Numbers AS n1
        INNER JOIN dbo.Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );
GO

这是产生的更简单的计划（再次点击放大）：

该计划仍然有两个排序操作，但线轴已经消失，仍然只有一次扫描#temp，并且在我的有限测试中，成本数字（绝对成本数字，而不是 %）每次都更好。

我不确切知道这些中的任何一个都会扩展更多的行，但值得测试，如果您将其与其他解决方案进行权衡并且它不能很好地扩展，那么您可能需要重新考虑设计（存储这些关系而不是逗号分隔的集合）。

Filipe Fernando Souza · Answer 2 · 2020-11-24T07:54:54+08:00

我用大量的行和四个列表列遇到了同样的问题。

以前的解决方案不适合我。

@AaronBertrand 的解决方案存在列表中元素数量不同的问题。该问题可以通过在 ROW_NUMBER 上添加分区来解决：

r1 = ROW_NUMBER() OVER (PARTITION BY n2.Number ORDER BY n1.Number)

但是，由于我有大量的行和元素，仍然不适合我。

我创建了以下脚本来解决我的问题而不使用函数：

DROP TABLE IF EXISTS #temp

CREATE TABLE #temp
(
    id   INT,
    keys VARCHAR(4000),
    vals VARCHAR(4000)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

DECLARE @delim VARCHAR(4000) = ',';

WITH split AS (
    SELECT
        id
        ,CONVERT(VARCHAR(4000), CONCAT(keys, @delim)) AS keys
        ,CONVERT(VARCHAR(4000), CONCAT(vals, @delim)) AS vals
        ,1 AS iniciokeys
        ,COALESCE(NULLIF(CHARINDEX(@delim, keys, 1), 0), LEN(keys)) AS fimkeys
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(keys, 1, COALESCE(NULLIF(CHARINDEX(@delim, keys, 1), 0), LEN(keys)) - 1)))) AS vkeys
        ,1 AS iniciovals
        ,COALESCE(NULLIF(CHARINDEX(@delim, vals, 1), 0), LEN(vals)) AS fimvals
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(vals, 1, COALESCE(NULLIF(CHARINDEX(@delim, vals, 1), 0), LEN(vals)) - 1)))) AS vvals
    FROM #temp
    WHERE LEN(keys) > 0
        AND LEN(vals) > 0
    UNION ALL
    SELECT
        id
        ,CONVERT(VARCHAR(4000), keys) AS keys
        ,CONVERT(VARCHAR(4000), vals) AS vals
        ,CONVERT(INT, fimkeys) + 1 AS iniciokeys
        ,COALESCE(NULLIF(CHARINDEX(@delim, keys, fimkeys + 1), 0), LEN(keys)) AS fimkeys
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(keys, fimkeys + 1, COALESCE(NULLIF(CHARINDEX(@delim, keys, fimkeys + 1), 0), LEN(keys))-fimkeys-1)))) AS vkeys
        ,CONVERT(INT, fimvals) + 1 AS iniciovals
        ,COALESCE(NULLIF(CHARINDEX(@delim, vals, fimvals + 1), 0), LEN(vals)) AS fimvals
        ,CONVERT(VARCHAR(4000), RTRIM(LTRIM(SUBSTRING(vals, fimvals + 1, COALESCE(NULLIF(CHARINDEX(@delim, vals, fimvals + 1), 0), LEN(vals))-fimvals-1)))) AS vvals
    FROM split
    WHERE fimkeys < LEN(keys)
        AND fimvals < LEN(vals)
)
SELECT
    id
    ,vkeys
    ,vvals
FROM split
ORDER BY id
    ,vkeys
OPTION(MAXRECURSION 32767)

结果：

查询计划：

如您所见，查询计划非常简单，在#temp 上只有一次表扫描。

该解决方案也具有很强的可扩展性。

在没有函数的情况下以相同的顺序拆分两个分隔的字符串

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

在没有函数的情况下以相同的顺序拆分两个分隔的字符串

2 个回答

相关问题