mysql 调用方差函数,[原创]扩展求方差的mysql函数例子

[原创]扩展求方差的mysql函数例子

源码

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

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 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

{

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>