sql server实现自定义分割月功能

本文目录列表:

1、为何出现自定义分割月需求

2、sql server实现自定义分割月功能

3、测试验证效果

4、总结语

5、参考清单列表

1、为何出现自定义分割月的需求

今天梳理一个平台的所有函数时,发现了一个自定义分割月函数,也就是指定分割月的开始日索引值(可以从1-31闭区间内的任何一个值)来获取指定日期所对应的分割月数值。这个函数当时是为了解决业务部门获取非标准月(标准月就是从每个月的第一天到最后一天组成一个完成的标准月份)的统计汇总数据的。例如:如果指定分割月的开始日索引值为5则表示某个月的5号到下个月的4号之间作为一个完整的分割月;同样地如果指定分割月的开始日索引值为1则表示标准月等等。

我仔细梳理了这个函数进行了重构简化以及扩展,该自定义分割月函数的实现区别之前写的SQL Server时间粒度系列----第3节旬、月时间粒度详解文章中将一个整数值和月份日期相互转换功能,这个是按照标准月来实现的,虽然思路大致相同,但是并没有针对之前的月份日期和整数值转换函数对来进行扩展而是独立开发新的功能函数。也是为了尽量做到函数功能职责单一性、稳定性、可维护性以及可扩展性。

2、sql server实现自定义分割月功能

自定义分割月功能函数包括两个标量函数:ufn_SegMonths和ufn_SegMonth2Date。ufn_SegMonths获取指定的日期在自定义分割月对应的分割月数值;ufn_SegMonth2Date获取指定一个分割月数值赌对应的月份日期。

sql server 版本的实现T-SQL代码如下:

IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[ufn_SegMonths];
END
GO
  
--==================================
-- 功能:根据自定义月开始索引值获取指定日期所在的自定义月数。
-- 说明:自定义分割月数 = 年整数值*100 + 当前所在分割月值。
-- 环境:SQL Server 2005+。
-- 调用:SET @intSegMonths = dbo.fn_SegMonths('2008-01-14', 15)。
-- 创建:XXXX-XX-XX XX:XX-XX:XX XXX 创建函数实现。
-- 修改:XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths]
(
     @dtmDate AS DATETIME                        -- 日期
    ,@tntSegStartIndexOfMonth AS INT = 15        -- 自定义分割月开始索引值(1-31)
)
RETURNS INT
AS
BEGIN   
    IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >= 32)
    BEGIN
        SET @tntSegStartIndexOfMonth = 15;
    END
  
    DECLARE
         @intYears AS INT
        ,@tntMonth AS TINYINT
        ,@sntDay AS SMALLINT;       
    SELECT
         @intYears = DATEDIFF(YEAR, '1900-01-01', @dtmDate)
        ,@tntMonth = DATEPART(MONTH, @dtmDate)
        ,@sntDay = DATEPART(DAY, @dtmDate);
  
    IF (@sntDay >= @tntSegStartIndexOfMonth)
    BEGIN
        SET @tntMonth = @tntMonth + 1;   
    END
  
    IF (@tntMonth > 12)
    BEGIN
        SELECT
             @intYears = @intYears + 1
            ,@tntMonth = @tntMonth - 12;
    END
  
    RETURN @intYears * 100 + @tntMonth;
END
GO
  
IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL
BEGIN
    DROP FUNCTION [dbo].[ufn_SegMonths2Date];
END
GO
  
--==================================
-- 功能:获取自定义分割月数对应的自定义分割月日期。
-- 说明:自定义分割月日期 = 自定义分割月数/100对应的年整数日期“组合”当前所在分割月值。
-- 环境:SQL Server 2005+。
-- 调用:SET @dtmSegMonthDate = dbo.fn_SegMonths2Date(11602)。
-- 创建:XXXX-XX-XX XX:XX-XX:XX XXX 创建函数实现。
-- 修改:XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。;
--==================================
CREATE FUNCTION [dbo].[ufn_SegMonths2Date]
(
     @intSegMonths AS INT                        -- 自定义分割月数
)
RETURNS DATETIME
AS
BEGIN       
    DECLARE @dtmDefaultBasedate AS DATETIME;
    SET @dtmDefaultBasedate = '1900-01-01';
  
    IF ((@intSegMonths IS NULL) OR (@intSegMonths <= 0))
    BEGIN
        RETURN @dtmDefaultBasedate;
    END
  
    DECLARE
         @intYears AS INT
        ,@intMonth AS INT;   
    SELECT
         @intYears = @intSegMonths / 100
        ,@intMonth = @intSegMonths % 100;   
  
    RETURN DATEADD(MONTH, @intMonth - 1, DATEADD(YEAR, @intYears, @dtmDefaultBasedate));
END
GO

3、测试验证效果

针对以上简单的测试代码如下:

DECLARE
     @dtmStartDate AS DATETIME
    ,@dtmEndDate AS DATETIME;
  
SELECT
     @dtmStartDate = '2000-01-01'
    ,@dtmEndDate = '2016-12-31';
  
SELECT
    [T1].*
    ,[dbo].[ufn_SegMonths2Date]([T1].[SegMonths]) AS SegMonthDate
FROM (
    SELECT
        [T].[CDate]
        ,[dbo].[ufn_SegMonths]([T].[CDate], 28) AS SegMonths
  
    FROM (
        SELECT
            DATEADD(DAY, [Num], @dtmStartDate) AS CDate
        FROM
            [dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate))
    ) AS T
    WHERE [T].[CDate] BETWEEN '2014-12-01' AND '2016-03-31'
) AS T1
WHERE DATEPART(DAY, [T1].[CDate]) >= 27
GO

效果截图如下:

注意:以上测试代码使用了SQL Server数字辅助表的实现这边文章的内联表值函数ufn_GetNums。

4、总结语

这次是梳理平台的功能性函数所进行的重构简化以及扩展的实现。尽量将日期有关的功能函数梳理出来,便于直接在sql server用户数据库中来使用, 也便于BI仓库中使用。国庆一来已经过去一周,原来打算一周一遍的计划还是延期啦,再次严重检讨自己。

继续精进SQL Server,继续进发。

5、参考清单列表

  • SQL Server数字辅助表的实现
  • SQL Server时间粒度系列----第3节旬、月时间粒度详解

原文发布于微信公众号 - 我为Net狂(dotNetCrazy)

原文发表时间:2016-10-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

必备神技能 | MySQL 查找删除重复行

来源:码农有道 ID:b497155298 本文讲述如何查找数据库里重复的行。这是初学者十分普遍遇到的问题。方法也很简单。这个问题还可以有其他演变,例如,如何...

56190
来自专栏腾讯云流计算

Apache Calcite 功能简析及在 Flink 的应用

• Apache Calcite 是一个动态数据的管理框架,可以用来构建数据库系统的语法解析模块

1.8K70
来自专栏维C果糖

史上最简单的 MySQL 教程(二十四)「数据的高级操作 之 查询(中)」

where字句:用来判断数据和筛选数据,返回的结果为0或者1,其中0代表false,1代表true,where是唯一一个直接从磁盘获取数据的时候就开始判断的条件...

42860
来自专栏铭毅天下

干货 | 通透理解Elasticsearch聚合

使用Elasticsearch的过程中,除了全文检索,或多或少会做统计操作,而做统计操作势必会使用Elasticsearch聚合操作。

43630
来自专栏ml

mysql知识初篇(一)

mysql介绍 (1) mysql数据库是瑞典AB开发。 (2) mysql--> sun --> oracle。 (3) mysql数据库的...

36870
来自专栏JavaEdge

MySQL索引及其实现原理(基于MyISAM及InnoDB引擎)

查询是数据库的最主要功能之一。我们都希望查询速度能尽可能快,因此数据库系统的设计者会从查询算法角度优化

7K100
来自专栏更流畅、简洁的软件开发方式

分页控件之分页算法 —— for SQL Server 版。

上两篇随笔: 我的分页控件(未完,待续)——控件件介绍及思路 我自己写的一个分页控件(源码和演示代码)PostBack分页版 for vs2003、SQL ...

26290
来自专栏西枫里博客

rand()随机的效率问题

在平时开发过程中,数据量不超过1W条的,通常执行随机查询是通过对order进行rand操作的进行的。但是随着数据量的增加,rand严重制约了整站的访问速度。...

6210
来自专栏MongoDB中文社区

玩转MongoDB: 索引,速度的引领

数据库索引与书籍的索引类似,有了索引就不需要翻整本书,数据库可以直接在索引中查找,在索引中找到条目后,就可以直接跳到目标文档的位置,这可以让查找的速度提高几个数...

17440
来自专栏java一日一条

mysql索引优化

当数据保存在磁盘类存储介质上时,它是作为数据块存放。这些数据块是被当作一个整体来访问的,这样可以保证操作的原子性。硬盘数据块存储结构类似于链表,都包含数据部分,...

14840

扫码关注云+社区

领取腾讯云代金券