The chain is the month and last month's difference in the proportion of the last month's value. In the complex OLAP computation we often use the concept of the Year-on-year chain, the requirements of the last dimension of a field of the implementation statement is very concise, such as SSAS MDX statements like [dimensions]. Currentmember.prevmember on it,
.
Such issues can also be extended to invoicing-like batch calculations, which also focus on other historical records to determine the status of a current record.
SQL statements cannot easily implement similar functions of an MDX statement, and must be contrasted with a crosstab association. Here we use CLR functions to implement similar syntax for MDX statements. It's OK to make a cache of what you've got when you select it. Efficiency should be improved a lot.
The code for the CLR is compiled as TestFun.dll and copied to the SQL Server's file directory.
--------------------------------------------------------------------------------
Copy Code code as follows:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class Userdefinedfunctions
{
//Save current group Current value
private static System.Collections.Generic.Dictionary <string, SqlString > _listvalue = new System.Collections . Generic.dictionary <string, SqlString > ();
Save the current group
private static System.Collections.Generic.Dictionary <string, string > _listgroup = new System.Collections.Ge Neric. Dictionary <string, String > ();
///<summary>
///Gets the value of the current group record
///</summary>
///<param name= "key" > Concurrency key </param>
///<param name= "CurrentGroup" > Current group </param>
///<param name= "CurrentValue" > Current group Current value </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];
//Group Change
if (_listgroup[key. Value]!= currentgroup.value)
{
Prevmembervalue = SqlString. Null;
_listgroup[key. Value] = Currentgroup.value;
}
//value change
_listvalue[key. Value] = CurrentValue;
return prevmembervalue;
}
Catch
{
return SqlString. Null;
}
}
///<summary>
///initializes the concurrency key
///</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>
///releases the concurrency key
///</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;
}
}
};
--------------------------------------------------------------------------------
Deployment and generation of custom functions, which take into account concurrency, we still need a concurrency key to express the current query
--------------------------------------------------------------------------------
Copy Code code as follows:
CREATE ASSEMBLY testforsqlclr from ' e:/sqlclrdata/testfun.dll ' with permission_set = UnSAFE;
--
Go
CREATE FUNCTION dbo. Xfn_getprevmembervalue
(
@key nvarchar (255),
@initByDim nvarchar (255),
@currentValue nvarchar (255)
)
RETURNS nvarchar (255)
as EXTERNAL NAME testforsqlclr. [Userdefinedfunctions]. Getprevmembervalue
Go
CREATE FUNCTION dbo. Xfn_initkey
(
@key nvarchar (255)
)
RETURNS bit
As EXTERNAL NAME testforsqlclr. [Userdefinedfunctions]. Initkey
Go
CREATE FUNCTION dbo. Xfn_disposekey
(
@key nvarchar (255)
)
RETURNS bit
as EXTERNAL NAME testforsqlclr. [Userdefinedfunctions]. Disposekey
--------------------------------------------------------------------------------
So we can use the test script as follows, Xfn_getprevmembervalue is the function that gets the price last month.
--------------------------------------------------------------------------------
--Establish a test environment
Copy Code code as follows:
declare @t table (
[Area] [varchar] (4) COLLATE chinese_prc_ci_as NULL,
[Trademonth] [varchar] (7) COLLATE chinese_prc_ci_as NULL,
[Trademoney] [float] NULL,
[Tradearea] [float] NULL,
[Tradeprice] [float] NULL
)
INSERT INTO @t
Select ' Minhang ', ' 2007-03 ', ' 2125714.91 ', ' 241.65 ', ' 8796.67 ' union
select ' Minhang ', ' 2007-04 ', ' 8408307.64 ', ' 907.32 ', ' 9267.19 ' union
select ' Minhang ', ' 2007-05 ', ' 10230321.95 ', ' 1095.88 ', ' 9335.26 ' union
Select ' Pudong ', ' 2007-01 ', ' 12738432.25 ', ' 1419.05 ', ' 8976.73 ' union
Select ' Pudong ', ' 2007-02 ', ' 4970536.74 ', ' 395.49 ', ' 12568.05 ' union
Select ' Pudong ', ' 2007-03 ', ' 5985405.76 ', ' 745.94 ', ' 8023.98 ' union
Select ' Pudong ', ' 2007-04 ', ' 21030788.61 ', ' 1146.89 ', ' 18337.23 ' union
select ' Putuo ', ' 2007-01 ', ' 1863896 ', ' 161.39 ', ' 11549.02 ' union
select ' Putuo ', ' 2007-02 ', ' 1614015 ', ' 119.59 ', ' 13496.24 ' union
select ' Putuo ', ' 2007-03 ', ' 1059235.19 ', ' 135.21 ', ' 7834 '
--Test statement
Copy Code code as follows:
declare @key varchar (40)
declare @b bit
Set @key = NEWID ()
Select @b= dbo. Xfn_initkey (@key)
Select area, Trademonth, Tradeprice, Lastmonthprice,
Cast (Round (tradeprice-lastmonthprice) * 100/lastmonthprice, 2) as varchar) + '% ' as chain from (
SELECT *, CAST (dbo. Xfn_getprevmembervalue (@key, Region, Tradeprice) as float) as Lastmonthprice from @t
) T
Select @b= dbo. Xfn_disposekey (@key)
--Results
/*
Area trademonth tradeprice lastmonthprice chain
-------------------------------------------- -------------------------
Minhang 2007-03 8796.67 null null
Minhang 2007-04 9267.19 8796.67 5.35%
Minhang 2007-05 9335.26 9267.19 0.73%
Pudong 2007-01 8976.73 null
Pudong 2007-02 12568.05 8976.73-40.01%
Pudong 2007-03 8023.98 12568-36 16%
2007-04 18337.23 8023.98 128.53%
Putuo 2007-01 11549.02 null null
Putuo 2007-02 13496.24 11549 16.86%
Putuo 2007-03 7834 13496.2-41.95%
*/
------------------------------------------------------------------------ --------
This function is still relatively coarse, and if further refinement can be used to define how to obtain the method of the previous dimension. This is only cached in terms of query order. Interested friends can be perfected.
