search
HomeDatabaseMysql TutorialSQL2005CLR函数扩展-深入环比计算的详解

SQL2005CLR函数扩展-深入环比计算的详解

Jun 07, 2016 pm 04:18 PM
functionExpandgo deepcalculateDetailed explanation

环比就是本月和上月的差值所占上月值的比例。在复杂的olap计算中我们经常会用到同比环比等概念,要求的上个维度的某个字段的实现语句非常简练,比如ssas的mdx语句类似[维度].CurrentMember.Prevmember就可以了 此类问题还可以延伸到类似进销存的批次计算中,

环比就是本月和上月的差值所占上月值的比例。在复杂的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 _listValue = new System.Collections.Generic.Dictionary ();
// 保存当前组
private static System.Collections.Generic.Dictionary _listGroup = new System.Collections.Generic.Dictionary ();

///


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

/// 并发键
/// 当前组
/// 当前组当前值
///
[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;
}
}
///
/// 初始化并发键
///

///
///
[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 ;
}
}
///
/// 释放并发键
///

///
///
[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;
--
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


--------------------------------------------------------------------------------
这样我们就可以使用了,,测试脚本如下, xfn_GetPrevMemberValue就是获取上月价格的函数。
--------------------------------------------------------------------------------
-- 建立测试环境

复制代码 代码如下:


declare @t table (
[ 区域 ] [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 ' 闵行 ' , '2007-03' , '2125714.91' , '241.65' , '8796.67' union
select ' 闵行 ' , '2007-04' , '8408307.64' , '907.32' , '9267.19' union
select ' 闵行 ' , '2007-05' , '10230321.95' , '1095.88' , '9335.26' union
select ' 浦东 ' , '2007-01' , '12738432.25' , '1419.05' , '8976.73' union
select ' 浦东 ' , '2007-02' , '4970536.74' , '395.49' , '12568.05' union
select ' 浦东 ' , '2007-03' , '5985405.76' , '745.94' , '8023.98' union
select ' 浦东 ' , '2007-04' , '21030788.61' , '1146.89' , '18337.23' union
select ' 普陀 ' , '2007-01' , '1863896' , '161.39' , '11549.02' union
select ' 普陀 ' , '2007-02' , '1614015' , '119.59' , '13496.24' union
select ' 普陀 ' , '2007-03' , '1059235.19' , '135.21' , '7834'


-- 测试语句

复制代码 代码如下:

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How does MySQL differ from SQLite?How does MySQL differ from SQLite?Apr 24, 2025 am 12:12 AM

The main difference between MySQL and SQLite is the design concept and usage scenarios: 1. MySQL is suitable for large applications and enterprise-level solutions, supporting high performance and high concurrency; 2. SQLite is suitable for mobile applications and desktop software, lightweight and easy to embed.

What are indexes in MySQL, and how do they improve performance?What are indexes in MySQL, and how do they improve performance?Apr 24, 2025 am 12:09 AM

Indexes in MySQL are an ordered structure of one or more columns in a database table, used to speed up data retrieval. 1) Indexes improve query speed by reducing the amount of scanned data. 2) B-Tree index uses a balanced tree structure, which is suitable for range query and sorting. 3) Use CREATEINDEX statements to create indexes, such as CREATEINDEXidx_customer_idONorders(customer_id). 4) Composite indexes can optimize multi-column queries, such as CREATEINDEXidx_customer_orderONorders(customer_id,order_date). 5) Use EXPLAIN to analyze query plans and avoid

Explain how to use transactions in MySQL to ensure data consistency.Explain how to use transactions in MySQL to ensure data consistency.Apr 24, 2025 am 12:09 AM

Using transactions in MySQL ensures data consistency. 1) Start the transaction through STARTTRANSACTION, and then execute SQL operations and submit it with COMMIT or ROLLBACK. 2) Use SAVEPOINT to set a save point to allow partial rollback. 3) Performance optimization suggestions include shortening transaction time, avoiding large-scale queries and using isolation levels reasonably.

In what scenarios might you choose PostgreSQL over MySQL?In what scenarios might you choose PostgreSQL over MySQL?Apr 24, 2025 am 12:07 AM

Scenarios where PostgreSQL is chosen instead of MySQL include: 1) complex queries and advanced SQL functions, 2) strict data integrity and ACID compliance, 3) advanced spatial functions are required, and 4) high performance is required when processing large data sets. PostgreSQL performs well in these aspects and is suitable for projects that require complex data processing and high data integrity.

How can you secure a MySQL database?How can you secure a MySQL database?Apr 24, 2025 am 12:04 AM

The security of MySQL database can be achieved through the following measures: 1. User permission management: Strictly control access rights through CREATEUSER and GRANT commands. 2. Encrypted transmission: Configure SSL/TLS to ensure data transmission security. 3. Database backup and recovery: Use mysqldump or mysqlpump to regularly backup data. 4. Advanced security policy: Use a firewall to restrict access and enable audit logging operations. 5. Performance optimization and best practices: Take into account both safety and performance through indexing and query optimization and regular maintenance.

What are some tools you can use to monitor MySQL performance?What are some tools you can use to monitor MySQL performance?Apr 23, 2025 am 12:21 AM

How to effectively monitor MySQL performance? Use tools such as mysqladmin, SHOWGLOBALSTATUS, PerconaMonitoring and Management (PMM), and MySQL EnterpriseMonitor. 1. Use mysqladmin to view the number of connections. 2. Use SHOWGLOBALSTATUS to view the query number. 3.PMM provides detailed performance data and graphical interface. 4.MySQLEnterpriseMonitor provides rich monitoring functions and alarm mechanisms.

How does MySQL differ from SQL Server?How does MySQL differ from SQL Server?Apr 23, 2025 am 12:20 AM

The difference between MySQL and SQLServer is: 1) MySQL is open source and suitable for web and embedded systems, 2) SQLServer is a commercial product of Microsoft and is suitable for enterprise-level applications. There are significant differences between the two in storage engine, performance optimization and application scenarios. When choosing, you need to consider project size and future scalability.

In what scenarios might you choose SQL Server over MySQL?In what scenarios might you choose SQL Server over MySQL?Apr 23, 2025 am 12:20 AM

In enterprise-level application scenarios that require high availability, advanced security and good integration, SQLServer should be chosen instead of MySQL. 1) SQLServer provides enterprise-level features such as high availability and advanced security. 2) It is closely integrated with Microsoft ecosystems such as VisualStudio and PowerBI. 3) SQLServer performs excellent in performance optimization and supports memory-optimized tables and column storage indexes.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),