如何利用TSQL的MAX/MIN/IIF函数进行针尖式行透视分析

迪鲁宾
103 阅读10分钟

简介

在最近的一个涉及文本文档记录的Tsql开发项目中，客户希望有一个产品能够显示存储在SQL Server 2014数据库中的特定文档文本的最新和最新的早期版本。一个结果集行必须包含每个文档的所有相关信息：如上所述的文档版本，以及相关的元数据。这本质上需要细粒度的行透视。本文将描述该解决方案背后的工程，并将该解决方案与SQL Server PIVOT操作符进行比较。

样本数据库

本文将使用一个名为DOCUMENT_VERSIONING的样本数据库，在Windows 10 PC上内置SQL Server 2014标准版。该数据库有 一个名为DOCUMENTS的表，如图所示。

否则，该数据库没有用户定义的函数或存储过程、触发器等。要用下图所示的脚本建立DOCUMENT_VERSIONING数据库及其数据，首先创建C:\DOCUMENT_VERSIONING目录。这个目录将存放数据库创建脚本所要建立的.LDF和.MDF数据库文件的组件。为了建立数据库本身，在Management Studio中连接到主数据库，并在SQL Server Management Studio查询分析器窗口中运行这个Tsql脚本。


  USE [master]
  GO
  
  IF EXISTS (SELECT name FROM sys.databases WHERE name = 'DOCUMENT_VERSIONING')
  
  --	DROP ANY EXISTING CONNECTIONS TO DOCUMENT_VERSIONING
  
    ALTER DATABASE DOCUMENT_VERSIONING SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE DOCUMENT_VERSIONING
  GO
  
  /****** Object:  Database [DOCUMENT_VERSIONING]    Script Date: 10/19/2021 5:32:15 PM ******/
  CREATE DATABASE [DOCUMENT_VERSIONING]
   CONTAINMENT = NONE
   ON  PRIMARY 
  ( NAME = N'DOCUMENT_VERSIONING', FILENAME = N'C:\DOCUMENT_VERSIONING\DOCUMENT_VERSIONING.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
   LOG ON 
  ( NAME = N'DOCUMENT_VERSIONING_log', FILENAME = N'C:\DOCUMENT_VERSIONING\DOCUMENT_VERSIONING_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
  GO
  
  USE DOCUMENT_VERSIONING;
  
  CREATE TABLE [dbo].[DOCUMENTS](
    [DOCUMENT_ID] [int] NOT NULL,
    [DOCUMENT_NAME] [nvarchar](150) NOT NULL,
    [DOCUMENT_TEXT] [nvarchar](2500) NOT NULL,
    [DATE_TIME_STAMP] [datetime] NOT NULL
  ) ON [PRIMARY]
  
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (1, N'DOCUMENT_1', N'OLD TEXT', CAST(N'2019-03-17 00:00:00.000' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (4, N'DOCUMENT_4', N'NEW TEXT', CAST(N'2017-02-01 11:28:52.320' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (4, N'DOCUMENT_4', N'OLD TEXT', CAST(N'2017-02-01 02:28:52.320' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (1, N'DOCUMENT_1', N'OLD TEXT', CAST(N'2019-03-27 00:18:20.000' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (1, N'DOCUMENT_1', N'NEW TEXT', CAST(N'2020-04-27 00:18:23.090' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (1, N'DOCUMENT_1', N'TEXT', CAST(N'2014-04-27 00:08:08.190' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (2, N'DOCUMENT_2', N'NEW TEXT', CAST(N'2008-04-27 03:12:02.450' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (3, N'DOCUMENT_3', N'NEW TEXT', CAST(N'2016-12-01 17:22:59.123' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (3, N'DOCUMENT_3', N'UNENCRYPTED TEXT', CAST(N'2000-04-01 00:22:59.123' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (3, N'DOCUMENT_3', N'RE-ENCRYPTED TEXT', CAST(N'2000-04-01 00:22:59.123' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (3, N'DOCUMENT_3', N'OLD TEXT', CAST(N'2012-10-17 14:28:52.320' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (5, N'DOCUMENT_5', N'NEW TEXT', CAST(N'2018-07-18 12:09:19.320' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (5, N'DOCUMENT_5', N'OLD TEXT', CAST(N'2018-07-18 12:09:19.320' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (5, N'DOCUMENT_5', N'NEW TEST TEXT', CAST(N'2018-07-12 17:22:29.557' AS DateTime))
  GO
  INSERT [dbo].[DOCUMENTS] ([DOCUMENT_ID], [DOCUMENT_NAME], [DOCUMENT_TEXT], [DATE_TIME_STAMP]) VALUES (5, N'DOCUMENT_5', N'EDGE TEST TEXT', CAST(N'2018-02-02 11:39:45.220' AS DateTime))
  GO
  USE [master]
  GO

这个脚本会成功运行，但在第一次运行时可能会返回这个错误信息。

Msg 3701, Level 11, State 1, Line 9 Cannot drop the database ‘DOCUMENT_VERSIONING’, because it does not exist or you do not have permission. 忽略这个错误。

表的数据

这张截图显示了我们将使用的样本数据。

除了DOCUMENT_ID=2，每个文档都有多个版本。这个查询按DOCUMENT_ID升序排列，然后按DATE_TIME_STAMP降序排列。

解决方案

我们将用这段代码解决这个问题。

USE DOCUMENT_VERSIONING;
 
  DECLARE @DOCUMENT_TABLE_VAR TABLE
  (
    DOCUMENT_ID		INT,
    DOCUMENT_NAME		NVARCHAR(150),
    DOCUMENT_TEXT		NVARCHAR(2500),
    DATE_TIME_STAMP	DATETIME,
    VERSION_NUM		INT
  )
  
  INSERT INTO @DOCUMENT_TABLE_VAR
  SELECT	DOCUMENT_ID, DOCUMENT_NAME, DOCUMENT_TEXT, DATE_TIME_STAMP, VERSION_NUM
  FROM
  (
    SELECT	DOCUMENT_ID, DOCUMENT_NAME, DOCUMENT_TEXT, DATE_TIME_STAMP,
      ROW_NUMBER() OVER (
            PARTITION BY	DOCUMENT_ID
            ORDER BY	DATE_TIME_STAMP DESC
              )	AS VERSION_NUM
    FROM	DOCUMENTS
  ) TMP
  WHERE	TMP.VERSION_NUM <= 2
  
  SELECT	DOCUMENT_ID, DOCUMENT_NAME,
  
      MAX(IIF ((VERSION_NUM = 1), DOCUMENT_TEXT, NULL)) AS NEW_DOCUMENT_TEXT,
      MIN(IIF ((VERSION_NUM = 1), DATE_TIME_STAMP, NULL)) AS NEW_DATE_TIME_STAMP,
      MAX(IIF ((VERSION_NUM = 2), DOCUMENT_TEXT, NULL)) AS OLD_DOCUMENT_TEXT,
      MIN(IIF ((VERSION_NUM = 2), DATE_TIME_STAMP, NULL)) AS OLD_DATE_TIME_STAMP
  FROM		@DOCUMENT_TABLE_VAR
  GROUP BY	DOCUMENT_ID, DOCUMENT_NAME

这个截图显示了SQL Server查询分析器窗口中的代码。

The Tsql code solution.

我们将逐行检查这段代码。

第2行的USE语句将查询分析器窗口指向存放DOCUMENT_VERSIONING数据库的DOCUMENTS表。代码将使用第4行到第11行之间声明的表变量@DOCUMENT_TABLE_VAR，作为其操作的 "抓板"。Tsql代码将在第10行定义的VERSION_NUM列中生成并存储文档的版本号。对于每个独立的DOCUMENT_ID所 "拥有 "的行，文档的版本号将映射到这些行，按DATE_TIME_STAMP降序排列。一个文档的最新行的VERSION_NUM值为1，下一个最新行的VERSION_NUM值为2，等等。

从第13行到第24行的INSERT语句将DOCUMENTS表的行插入到@DOCUMENT_TABLE_VAR表变量中。在该语句中，第17行到第22行之间的SELECT语句返回DOCUMENTS表中的所有列。此外，它还为VERSION_NUM列建立了数值。第18行和第21行之间的ROW_NUMBER语句建立了表变量VERSION_NUMBER列的值。在第19行中，Tsql PARTITION BY子句在从源DOCUMENTS表中提取的数据中建立了分区，或者说 "行窗口"。这一行将一个分区定义为具有相同DOCUMENT_ID列值的所有记录。在这些分区中，第20行ORDER BY子句按照DATE_TIME_STAMP降序对记录进行排序。这将把每个文档的最新版本行放在每个分区的顶部，下一个最新版本行就在下面， 等。第21行将ROW_NUMBER值列命名为VERSION_NUMBER。第22行指定DOCUMENTS表作为数据源。这张截图显示了这个SELECT查询的结果集。

Tsql code to build a result set with a VERSION_NUM column.

注意，它与原始的DOCUMENTS表数据相匹配，并增加了一个VERSION_NUM列。这里显示的结果集在每个行窗口中按DATE_TIME_STAMP降序对版本行进行排序，VERSION_NUM列有预期值。回到代码中，第23行基本上是将第17至22行的查询包裹在一个名为 "TMP "的表别名中。第24行过滤这个查询，排除VERSION_NUM 值超过3的记录。这个方法将只保留VERSION_NUM值为1（最新版本）和2（次新版本）的记录。将第24行的WHERE子句直接放在第17行的查询中似乎是合乎逻辑的。然而，这种方法是行不通的，因为SQL Server Tsql查询会在WHERE子句之后 "看到"SELECT子句。因此，WHERE子句永远不会看到SELECT子句中的VERSION_NUMBER列，而查询会崩溃。如果我们把WHERE子句放在这个查询之外，就能解决这个问题。

第26至33行的Tsql查询 ，返回完成的结果集。第32行的FROM子句定义了@DOCUMENT_TABLE_VAR表变量作为查询源表。在第26行，SELECT子句直接从源表中列出列。第28行到第31行依靠VERSION_NUM值将DOCUMENT_TEXT和DATE_TIME_STAMP行值放入最终查询结果集中的四个新列中。

VERSION_NUM = 1 	--	LATEST VERSION
  VERSION_NUM = 2	--	NEXT LATEST VERSION
  
  
  NEW_DOCUMENT_TEXT
  NEW_DATE_TIME_STAMP 
  OLD_DOCUMENT_TEXT
  OLD_DATE_TIME_STAMP

我们将从 "内部 "检查第28行，以了解工程的情况。第28行通过以下代码为NEW_DOCUMENT_TEXT列建立数值。

  MAX(IIF ((VERSION_NUM = 1), DOCUMENT_TEXT, NULL)) AS NEW_DOCUMENT_TEXT

对于每个结果集行，如果VERSION_NUM等于1，TSQL IIF函数下面的内部返回 DOCUMENT_TEXT值。

 IIF ((VERSION_NUM = 1), DOCUMENT_TEXT, NULL)

对于所有其他的VERSION_NUM值，这个IIF返回NULL。第28行将这个值传递给MAX聚合函数。TSQL的MAX函数只需要一个参数，要么是一个列名，在本例中是一个字符串值。对于一个列名，MAX函数返回该列的最高值，或者在查询的GROUP BY子句中定义的每一组记录。现在，当MAX函数收到一个字符串值参数时，它将返回该字符串，如该截图所示。

The Tsql MAX function with a one-string input parameter.

微软文档解释说，MAX函数忽略了 NULL值。基于所有这些，如果VERSION_NUM不等于1，第28行将返回NULL，如果VERSION_NUM等于1，则为该行返回DOCUMENT_TEXT。最后，第28行将列名别名为 "NEW_DOCUMENT_TEXT"。第30行的操作与 "OLD_DOCUMENT_TEXT "列相同。第29行和第31行显示，MIN函数的工作方式与MAX函数类似。Tsql的MIN和MAX聚合函数希望在第33行有GROUP BY子句。

上面的第二张截图显示了原始的DOCUMENTS表数据。为了与查询结果集进行比较，这张截图再次显示了DOCUMENTS表的数据。

这张截图显示了我们运行整个代码样本时的结果集。

The solution result set.

DOCUMENT_1、DOCUMENT_3和DOCUMENT_4行 ，符合我们的预期。DOCUMENT_2行看起来不错，因为DOCUMENTS表只有DOCUMENT_2一条记录。在第29行，IIF函数返回NULL。

MAX(IIF ((VERSION_NUM = 2), DOCUMENT_TEXT, NULL)) AS OLD_DOCUMENT_TEXT

TSQL的MAX函数忽略了NULL值，并将其传递给结果集，我们在结果集第2行的OLD_DOCUMENT_TEXT列中看到。 在第30行，MIN函数以同样的方式操作。DOCUMENT_5行涉及到一个边缘案例，因为最新的两行DOCUMENT_5有相同的2018-07-18 12:09:19.320 DATE _TIME_STAMP值。因此，第19行的ORDER BY子句的行为可能是不可预测的。它可以根据匹配的值随机地对行进行排序。

透视操作符怎么样？

上面描述的解决方案以一种定制的方式对DOCUMENTS表的数据进行透视。也许SQL Server的PIVOT操作符也可以解决这个问题。使用DOCUMENTS表的数据，这段代码使用PIVOT操作符来透视DOCUMENT_TEXT列，作为部分解决方案。

DECLARE @DOCUMENT_TABLE_VAR TABLE
  (
    DOCUMENT_ID		INT,
    DOCUMENT_NAME		NVARCHAR(150),
    DOCUMENT_TEXT		NVARCHAR(2500),
    DATE_TIME_STAMP	DATETIME,
    VERSION_NUM		INT
  )
  
  INSERT INTO @DOCUMENT_TABLE_VAR
  SELECT	DOCUMENT_ID, DOCUMENT_NAME, DOCUMENT_TEXT, DATE_TIME_STAMP, VERSION_NUM
  FROM
  (
    SELECT	DOCUMENT_ID, DOCUMENT_NAME, DOCUMENT_TEXT, DATE_TIME_STAMP,
      ROW_NUMBER() OVER (
            PARTITION BY	DOCUMENT_ID
            ORDER BY DATE_TIME_STAMP DESC
              )	AS VERSION_NUM
    FROM	DOCUMENTS
  ) TMP
  WHERE TMP.VERSION_NUM <= 2
  
  SELECT	DOCUMENT_ID, DOCUMENT_NAME, [1] AS 'NEW_DOCUMENT_TEXT',
    [2] AS 'OLD_DOCUMENT_TEXT'
  FROM	@DOCUMENT_TABLE_VAR
  PIVOT
  (
    MAX(DOCUMENT_TEXT)
    FOR VERSION_NUM IN ([2], [1])
  )
  AS PivotTable
  ORDER BY DOCUMENT_ID ASC

这个截图显示了查询分析器窗口中的代码。

The Tsql PIVOT operator as a potential solution.

第26至31行的PIVOT操作符将原始的DOCUMENT_TEXT列透视为不同的列。它为最新的DOCUMENT_TEXT版本建立了一个名为 "1 "的列，为次最新的DOCUMENT_TEXT版本建立了 "2 "的列。第28行的Tsql MAX函数作为PIVOT运算符所期望的聚合 函数。MAX在这里的工作方式与先前的解决方案相同，只是它不需要GROUP BY子句。在第29行，FOR子句告诉PIVOT运算符要使用的VERSION_NUM值来生成透视列。第32行的ORDER BY子句对结果集进行排序。第23行和第24行的SELECT子句建立了结果集的列列表，将原来的"[1]"和"[2]"列名改为更具描述性的名称。这个屏幕截图显示了结果集。

The Tsql PIVOT operator result set.

这个查询将DOCUMENT_TEXT值透视到NEW_DOCUMENT_TEXT和OLD_DOCUMENT_TEXT列。不幸的是，它并没有 "合并 " 行。我们想要的结果集可能需要一个单独的结果集，涉及到DATE_TIME_STAMP列的透视，这将有同样的问题。然后，我们必须以某种方式将两个透视的、合并的结果集合并为一个结果集。整个事情会变得非常复杂。前面显示的实际解决方案显然是更好的方法。

结论

对于SQL Server的数据版本管理，我们需要一种灵活、有效的技术来弥补PIVOT操作符无法处理的空白。正如我们所看到的，Tsql的MAX和MIN聚合函数，结合IIF，将以细粒度的控制解决这个问题。

avatar
文章
阅读
粉丝