当前位置：首页 > news >正文

SQL2005CLR函数扩展-环比计算

news 来源：原创 2024/5/10 6:59:11

环比就是本月和上月的差值所占上月值的比例。在复杂的olap计算中我们经常会用到同比环比等概念，要求的上个维度的某个字段的实现语句非常简练，比如ssas的mdx语句类似[维度].CurrentMember.Prevmember就可以了。此类问题还可以延伸到类似进销存的批次计算中，这也要关注其他历史记录来决定当前某条记录的状态。

sql语句无法简单实现mdx语句的类似功能，必须得用交叉表关联来对比。这里我们用CLR函数来实现mdx语句的类似语法。在select的时候把得到过的做个缓存就可以了。效率应该可以提高不少。

clr的代码如下，编译为TestFun.dll，复制到sql服务器的文件目录下。

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions

{

// 保存当前组当前值

private static System.Collections.Generic.Dictionary <string , SqlString > _listValue = new System.Collections.Generic.Dictionary <string , SqlString >();

// 保存当前组

private static System.Collections.Generic.Dictionary <string , string > _listGroup = new System.Collections.Generic.Dictionary <string , string >();

/// <summary>

/// 获取当前组上条记录数值

/// </summary>

/// <param name="key"> 并发键 </param>

/// <param name="currentGroup"> 当前组 </param>

/// <param name="currentValue"> 当前组当前值 </param>

/// <returns></returns>

[Microsoft.SqlServer.Server.SqlFunction ]

public static SqlString GetPrevMemberValue(SqlString key,SqlString currentGroup,SqlString currentValue)

{

if (key.IsNull || currentGroup.IsNull) return SqlString .Null;

try

{

SqlString prevMemberValue = _listValue[key.Value];

// 组变更

if (_listGroup[key.Value] != currentGroup.Value)

{

prevMemberValue = SqlString .Null;

_listGroup[key.Value] = currentGroup.Value;

}

// 值变更

_listValue[key.Value] = currentValue;

return prevMemberValue;

}

catch

{

return SqlString .Null;

}

/// <summary>

/// 初始化并发键

/// </summary>

/// <param name="key"></param>

/// <returns></returns>

[Microsoft.SqlServer.Server.SqlFunction ]

public static SqlBoolean InitKey(SqlString key)

{

try

{

_listValue.Add(key.Value, SqlString .Null);

_listGroup.Add(key.Value, string .Empty);

return true ;

}

catch

{

return false ;

}

/// <summary>

/// 释放并发键

/// </summary>

/// <param name="key"></param>

/// <returns></returns>

[Microsoft.SqlServer.Server.SqlFunction ]

public static SqlBoolean DisposeKey(SqlString key)

{

try

{

_listValue.Remove(key.Value);

_listGroup.Remove(key.Value);

return true ;

}

catch

{

return false ;

}

};

部署和生成自定义函数，其中考虑到并发，我们还是需要一个并发键来表达当前查询

CREATE ASSEMBLY TestForSQLCLR FROM 'E:\sqlclrdata\TestFun.dll' WITH PERMISSION_SET = UnSAFE;