[转载]扩展求方差的mysql函数例子

原帖及讨论：http://bbs.bc-cn.net/dispbbs.asp?BoardID=196&ID=97485

源码
// 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-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 &#39;variance.dll&#39;;
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>

希望大家帮我把这个帖发给你身边的人，谢谢！

好漂亮的说！~~

君子以自强不息 地势坤 君子以厚德载物

谢 谢