数据库内核月报-2017年04期-合理利用数据库特性实现需求之自连接

合理利用数据库特性实现需求之自连接

需求描述

• 数据库环境
  MSSQL Server 2008 R2
• 需求描述
  给某表限定日期范围条件的前提下，检索出每个用户的日期中断区间记录
• 场景模拟
  假设数据（日期范围为2017-01-01至2017-01-31）：
  C_UID　　　｜ C_DATEBEGIN　｜　C_DATEEND
  -———————————————————————
  1　　　　｜　2017-01-05　｜　2017-01-10
  1　　　　｜　2017-01-15　｜　2017-01-20
  1　　　　｜　2017-01-25　｜　NULL
  2　　　　｜　2017-01-01　｜　2017-01-05
  2　　　　｜　2017-01-15　｜　2017-01-20
  3　　　　｜　2016-12-26　｜　2017-01-05
  3　　　　｜　2017-01-15　｜　2017-01-20
  4　　　　｜　2016-12-26　｜　2017-01-05
  4　　　　｜　2017-01-15　｜　2017-01-31

期望结果：
C_UID　　　｜ C_DATEBEGIN　｜　C_DATEEND
-———————————————————————
1　　　　｜　2017-01-01　｜　2017-01-04
1　　　　｜　2017-01-11　｜　2017-01-14
1　　　　｜　2017-01-26　｜　2017-01-31
2　　　　｜　2017-01-06　｜　2017-01-14
2　　　　｜　2017-01-21　｜　2017-01-31
3　　　　｜　2017-01-06　｜　2017-01-14
3　　　　｜　2017-01-21　｜　2017-01-31
4　　　　｜　2017-01-06　｜　2017-01-14

初始方案

• 实现思路
  通过用户及起始日期排序，循环处理每条记录，缓存上一条记录的结果集，并与本条记录集比对，得到的差异结果集存入临时表。结果集首尾及及所属用户有变化时特殊处理
• 实现代码

USE [DB_ZF]
GO

CREATE PROCEDURE [dbo].[P_QueryDateRange]
    @startDate VARCHAR(300),
    @endDate VARCHAR(300)
AS
BEGIN
    Declare @uid VARCHAR(300) --表记录变量-用户ID
    Declare @dateBegin VARCHAR(300) --表记录变量-起始日期
    Declare @dateEnd VARCHAR(300) --表记录变量-截止日期

    Declare @tempUid VARCHAR(300) --临时变量-用户ID
    Declare @tempDateBegin VARCHAR(300) --临时变量-起始日期
    Declare @tempDateEnd VARCHAR(300) --临时变量-截止日期
    Declare @intervalDays INT --间隔天数
    Declare @resultDateBegin VARCHAR(300) --日期结果变量-开始
    Declare @resultDateEnd VARCHAR(300) --日期结果变量-结束

    -- 创建临时表
    IF NOT EXISTS(SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID('TEMPDB..##TEMP_RESULT'))
      CREATE TABLE ##TEMP_RESULT
    (
            C_UID CHAR(32),
        C_DATEBEGIN VARCHAR(300),
        C_DATEEND VARCHAR(300)
    )
    -- 清空临时表
    TRUNCATE TABLE ##TEMP_RESULT
    SET @tempUid = ''
    SET @tempDateBegin = ''
    SET @tempDateEnd = ''
    --查询原始表中符合日期范围的记录
    Declare sourceRec Cursor FOR SELECT C_UID, C_DATEBEGIN, C_DATEEND FROM (SELECT C_UID, C_DATEBEGIN, C_DATEEND FROM ##TEMP_TEST 
      WHERE C_DATEBEGIN >= @startDate AND C_DATEBEGIN <= @endDate AND ISNULL(C_DATEEND, @endDate) <= @endDate
      UNION
      SELECT C_UID, C_DATEBEGIN, C_DATEEND FROM ##TEMP_TEST
      WHERE C_DATEEND >= @startDate AND C_DATEEND <= @endDate
      ) REC ORDER BY C_UID, C_DATEBEGIN

    OPEN sourceRec
    Fetch NEXT FROM sourceRec INTO @uid, @dateBegin, @dateEnd
    while @@FETCH_STATUS = 0
    BEGIN
        --切换用户时处理首尾记录
        IF @tempUid != @uid
        BEGIN
          --截止日期不为空，并且小于范围截止日期-为上一用户结束（直接插入记录）
          IF @tempDateEnd != '' AND @tempDateEnd < @endDate
          BEGIN
            INSERT INTO ##TEMP_RESULT VALUES (@tempUid, @tempDateEnd, @endDate)  
          END
          --起始日期大于范围起始日期-为开始（只修改临时变量，后续当做一条正常的记录处理）
          IF @dateBegin > @startDate
          BEGIN
            SET @tempDateBegin = @dateBegin
            SET @tempDateEnd = CONVERT(VARCHAR(300), DATEADD(DAY, -1, @startDate), 23)      
          END
        END

        IF @tempDateEnd != '' AND @dateBegin >= @tempDateEnd
        BEGIN
            SET @intervalDays = DATEDIFF(DAY, @tempDateEnd, @dateBegin) 
            SET @resultDateBegin = CONVERT(VARCHAR(300), DATEADD(DAY, 1, @tempDateEnd), 23)
            SET @resultDateEnd = CONVERT(VARCHAR(300), DATEADD(DAY, -1, @dateBegin), 23)
            INSERT INTO ##TEMP_RESULT VALUES (@uid, @resultDateBegin, @resultDateEnd)
        END

        SET @tempUid = @uid
        SET @tempDateBegin = @dateBegin
        SET @tempDateEnd = @dateEnd
        Fetch NEXT FROM sourceRec INTO @uid, @dateBegin, @dateEnd
    END
    --处理最后一条记录
    IF @dateEnd != '' AND @dateEnd < @endDate
    BEGIN
      INSERT INTO ##TEMP_RESULT VALUES (@uid, CONVERT(VARCHAR(300), DATEADD(DAY, 1, @dateEnd), 23), @endDate) 
    END
    CLOSE sourceRec
    DEALLOCATE sourceRec
    --查询结果集
    SELECT C_UID, C_DATEBEGIN, C_DATEEND FROM ##TEMP_RESULT ORDER BY C_UID, C_DATEBEGIN
    --删除临时表
    IF EXISTS(SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE ID = OBJECT_ID('TEMPDB..##TEMP_RESULT'))
      DROP TABLE ##TEMP_RESULT
END

• 结果

C_UID C_DATEBEGIN C_DATEEND
1    2017-01-01    2017-01-04
1    2017-01-11    2017-01-14
1    2017-01-21    2017-01-24
2    2017-01-06    2017-01-14
2    2017-01-21    2017-01-31
3    2017-01-06    2017-01-14
3    2017-01-21    2017-01-31
4    2017-01-06    2017-01-14

• 优点
  逻辑简单清晰、可直接移植数据处理部分代码到java代码块中，对技术要求不高
• 缺点
  无论是否移植数据处理部分代码，都需要针对现有代码块进行大量改造，并且不利于后续维护

改进方案

• 实现思路
  如果通过日期范围过滤后的条件是有序，并且针对每个用户都有单独的组序号时，这个结果集就是一个有内联关系的结果集。若结果集是有内联关系的话，我们可以使用自连接来实现上述的需求

  • 实现结果集内联关系
    我们目前可用的信息是用户ID和日期，通过这两个字段排序的话我们得到的就是一个有序的结果集。但是这个结果集没有物理上的对应关系，因为我们没有每个用户的组序号
    但是MSSQL给我们提供了一个开窗函数ROW_NUMBER()，这个函数可以以某个字段为组，单独给每个组进行排序并生成序号
    这时候的结果集就是一个有序的、有关联关系的结果集：每个用户ID相同的结果集组中，序号n记录对应的截止日期到序号n+1记录对应的起始日期就是我们要的日期中断区间
  • 提取处理后的结果集特征
    此时的结果集可以分为三类，分别为：（以每个用户为单位）可以直接通过自连接找到对应关系的记录、第一条记录的开始日期大于范围日期开始的记录，和最后一条记录截止日期非空并且小于范围日期结束的记录。这三类数据的判断规则均不相同，所以需要单独处理
  1. 可以直接通过自连接找到对应关系的记录
     结果集中最多的就是此类数据：同用户ID序号n记录截止日期与对应的同用户ID序号n+1记录起始日期之间的间隔就是我们要的结果
  2. 第一条记录的开始日期大于范围日期开始的记录
     当用户最小起始日期大于日期范围开始的时候，那么我们需要增加一个日期范围开始到当前用户最小起始日期的区间值
  3. 最后一条记录截止日期非空并且小于范围日期结束的记录
     当用户所有记录的截止日期均不为空，并且截止日期小于日期范围结束的时候，我们需要增加一个最大截止日期到范围日期结束的区间值
  • 缓存实现内联关系的结果集
    因为我们分别处理的三类数据，均基于同一份已经实现内联关系的结果集。所以我们可以利用数据库的特性WITH来缓存该结果集，替代每个查询内的相同子查询
• 实现代码

--后续需要多次使用该结果集，利用with语句缓存该结果集
-- 1.缓存结果集
WITH tempTable AS (
  --1.2给符合条件的结果集以用户ID为单位分组，并排序得到序号
  SELECT ROW_NUMBER() OVER (PARTITION BY C_UID ORDER BY C_DATEBEGIN) RN, C_UID, C_DATEBEGIN, C_DATEEND 
  FROM
    ( --1.1查询出原始表中所有符合条件的记录
      --1.1.1先查询出原始表起始日期在日期范围内，并且截止日期（空则认为其为范围日期结束）小于等于范围日期结束的记录
      SELECT C_UID, C_DATEBEGIN, C_DATEEND FROM ##TEMP_TEST 
      WHERE C_DATEBEGIN >= '2017-01-01' AND C_DATEBEGIN <= '2017-01-31' AND ISNULL(C_DATEEND, '2017-01-31') <= '2017-01-31'
      --1.1.2还需要考虑每个用户最后一条记录的截止日期不为空，并且小于范围日期结束
      UNION
      SELECT C_UID, C_DATEBEGIN, C_DATEEND FROM ##TEMP_TEST
      WHERE C_DATEEND >= '2017-01-01' AND C_DATEEND <= '2017-01-31'
    ) REC
)
--以下查询都是针对所有用户进行判断
--2.处理结果集
--2.1先处理所有用户里面，“最小起始日期”比“日期范围开始”大的记录
SELECT A.C_UID, '2017-01-01', CONVERT(VARCHAR(300), DATEADD(DAY, -1, MIN(C_DATEBEGIN)), 23) FROM tempTable A WHERE NOT EXISTS 
(SELECT 1 FROM tempTable B WHERE A.C_UID = B.C_UID AND B.C_DATEBEGIN <= '2017-01-01') GROUP BY A.C_UID
--2.2再处理普通数据
UNION
SELECT A.C_UID, CONVERT(VARCHAR(300), DATEADD(DAY, 1, A.C_DATEEND), 23), CONVERT(VARCHAR(300), DATEADD(DAY, -1, B.C_DATEBEGIN), 23) FROM 
tempTable A LEFT JOIN tempTable B ON A.C_UID = B.C_UID AND A.RN = (B.RN - 1)
WHERE B.C_UID IS NOT NULL
--2.3最后处理用户所有数据“最后日期”不为空，并且小于“日期范围结束”的记录
UNION 
SELECT A.C_UID, CONVERT(VARCHAR(300), DATEADD(DAY, 1, MAX(C_DATEEND)), 23), '2017-01-31' FROM tempTable A WHERE NOT EXISTS 
(SELECT 1 FROM tempTable B WHERE A.C_UID = B.C_UID AND B.C_DATEEND IS NULL
UNION 
SELECT 1 FROM tempTable B WHERE A.C_UID = B.C_UID AND B.C_DATEEND = '2017-01-31') GROUP BY A.C_UID

• 结果

  C_UID C_DATEBEGIN C_DATEEND
  1    2017-01-01    2017-01-04
  1    2017-01-11    2017-01-14
  1    2017-01-21    2017-01-24
  2    2017-01-06    2017-01-14
  2    2017-01-21    2017-01-31
  3    2017-01-06    2017-01-14
  3    2017-01-21    2017-01-31
  4    2017-01-06    2017-01-14
  

• 优点
  对原有代码逻辑影响不大，且后期维护时不会产生大范围影响

• 缺点
  数据库初学者理解起来较困难，后续维护时需要维护者对数据库有一定了解

结语

通过比对两个方案，我们不难看出：在本文的需求实现中，合理的利用自连接、开窗函数等数据库特性，不但可以在尽量小的影响范围内实现需求，还可以精简大量冗余代码。所以大家以后在实现需求时，可以结合数据库的特性书写出更多高质量的代码

附加部分-测试脚本

• 建表脚本

IF NOT EXISTS (SELECT 1 FROM tempdb..SYSOBJECTS WHERE id = OBJECT_ID('tempdb..##TEMP_TEST'))
CREATE TABLE ##TEMP_TEST(
  C_UID VARCHAR(300),
  C_DATEBEGIN VARCHAR(300),
  C_DATEEND VARCHAR(300)
)

• 初始化数据

INSERT INTO ##TEMP_TEST VALUES('1', '2017-01-05', '2017-01-10')
INSERT INTO ##TEMP_TEST VALUES('1', '2017-01-15', '2017-01-20')
INSERT INTO ##TEMP_TEST VALUES('1', '2017-01-25', NULL)

INSERT INTO ##TEMP_TEST VALUES('2', '2017-01-01', '2017-01-05')
INSERT INTO ##TEMP_TEST VALUES('2', '2017-01-15', '2017-01-20')

INSERT INTO ##TEMP_TEST VALUES('3', '2016-12-26', '2017-01-05')
INSERT INTO ##TEMP_TEST VALUES('3', '2017-01-15', '2017-01-20')

INSERT INTO ##TEMP_TEST VALUES('4', '2016-12-26', '2017-01-05')
INSERT INTO ##TEMP_TEST VALUES('4', '2017-01-15', '2017-01-31')