原帖及讨论:http://bbs.bccn.net/thread-97485-1-1.html 源码 // Author: JiangMiao // Name: 扩展求方差的mysql函数列子 // Date: 2006-10-19 // Link: http://blog.sina.com.cn/u/1259926384 - JiangMiao的Blog #include "winsock2.h" #include "mysql.h" #include <vector> using namespace std; #define SAFE_DELETE(p) if(p!=NULL){delete p;p=NULL;} #define CDLLEXPORT extern "C" __declspec(dllexport) typedef __int64 longlong; typedef vector<double> vec_double; typedef unsigned long ulong; class VAR { private: vec_double datas; double total; public: VAR():total(0.0) {} //加入num void push_back(double num) { datas.push_back(num); total+=num; } void clear() { datas.clear(); total=0.0; } //取方差 double getVariance() { size_t count=datas.size(); double avr=0.0; avr=(total/count); //平均数 double rt=0.0; for(size_t i=0;i<count;i++) { double k=(datas[i]-avr); rt+=k*k; } return rt/count; }
}; CDLLEXPORT my_bool variance_init(UDF_INIT *initid, UDF_ARGS *args, char *message) { initid->ptr = NULL; if(args->arg_count!=1) //参数个数为1 { return 1; } if(args->arg_type[0]!=REAL_RESULT||args->arg_type[0]!=INT_RESULT) //参数类别为整型或double { return 1; } initid->ptr = (char*)new VAR(); return 0; } CDLLEXPORT void variance_deinit(UDF_INIT *initid) { VAR* ptr=(VAR*)initid->ptr; delete ptr; } CDLLEXPORT double variance(UDF_INIT *initid, UDF_ARGS *args,char *is_null, char *error) { VAR* ptr=(VAR*)initid->ptr; return ptr->getVariance(); } CDLLEXPORT void variance_clear(UDF_INIT *initid, char *is_null, char *error) { VAR* ptr=(VAR*)initid->ptr; ptr->clear(); } CDLLEXPORT void variance_add(UDF_INIT *initid, UDF_ARGS *args, char *is_null, char *error) { VAR* ptr=(VAR*)initid->ptr; char* argo=args->args[0]; double arg; if(args->arg_type[0]==REAL_RESULT) { arg=*(double*)argo; } if(args->arg_type[0]==INT_RESULT) { arg=(double)*(__int64*)argo; } ptr->push_back(arg); }
编译后得到variance.dll 复制到bin目录下
测试 mysql> use test; Database changed mysql> create table vartest (realtest real,inttest int); Query OK, 0 rows affected (0.11 sec) mysql> insert into vartest values(5,5),(6,6),(9,9),(10,10),(5,5); Query OK, 5 rows affected (0.03 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> create aggregate function variance returns real soname 'variance.dll'; Query OK, 0 rows affected (0.00 sec) mysql> select variance(realtest),variance(inttest) from vartest; +--------------------+-------------------+ | variance(realtest) | variance(inttest) | +--------------------+-------------------+ | 4.4 | 4.4000 | +--------------------+-------------------+ 1 row in set (0.00 sec) mysql> |