文档章节

给Mysql加自定义函数计算百分位数(percentile)。

小水熊
 小水熊
发布于 2017/07/06 16:16
字数 1130
阅读 866
收藏 0

百分位数(percentile)的详细定义见百度百科

在这里我用一个通俗的例子来补充解释, 例如今年有900万人参加了高考,自然有900万个成绩,某个学校计划择优招生分数最高的前900个,那么分数线应该是多少呢,这就涉及到百分位的计算,这个学校录取的百分比就是万分之一,分数线的位置在万分之九千九百九十九,化成百分比就是99.99%,那对应这个百分比位置的具体数值是多少呢,这个说的就是百分位(percentile)。

Mysql中要实现根据若干条记录的某个字段获取指定百分位的值,用常规的自定义函数似乎行不通。我们用C/C++语言写一个mysql插件。

udf_percentile.cc, 这是插件的源代码。

/*
  returns the percentile of the values in a distribution 

  input parameters:
  data (real or int)
  desired percentile of result, 0-1 (double)
  decimals of output (optional, int)

  output:
  percentile value of the distribution (real)

  compiling
  gcc -fPIC -Wall -I /usr/include/mysql51/mysql/ -shared -o udf_percentile.so  udf_percentile.cc 
  udf_percentile.so /usr/lib64/mysql/plugin/

  registering the function:
  CREATE AGGREGATE FUNCTION percentile RETURNS REAL SONAME 'udf_percentile.so';

  getting rid of the function:
  DROP FUNCTION percentile;

  hat tip to Jan Steemann's udf_median function http://mysql-udf.sourceforge.net/
*/


#ifdef STANDARD
#include <stdio.h>
#include <stdlib.h> 
#include <string.h>
#ifdef __WIN__
typedef unsigned __int64 ulonglong;
typedef __int64 longlong;
#else
typedef unsigned long long ulonglong;
typedef long long longlong;
#endif /*__WIN__*/
#else
#include <my_global.h>
#include <my_sys.h>
#endif
#include <mysql.h>
#include <m_ctype.h>
#include <m_string.h>

#ifdef HAVE_DLOPEN


#define BUFFERSIZE 1024



extern "C" {
my_bool percentile_init( UDF_INIT* initid, UDF_ARGS* args, char* message );
void percentile_deinit( UDF_INIT* initid );
void percentile_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
void percentile_clear(UDF_INIT* initid, char *is_null, char *error);
void percentile_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
double percentile( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char *error );
}


struct percentile_data
{
  unsigned long count;
  unsigned long abscount;
  unsigned long pages;
  double *values;
};


my_bool percentile_init( UDF_INIT* initid, UDF_ARGS* args, char* message )
{

  if ( args->arg_count < 2 || args->arg_count>3)
  {
    strcpy(message,"wrong number of arguments: percentile() requires two - three arguments");
    return 1;
  }
  

  if (args->arg_type[0]!=REAL_RESULT && args->arg_type[0]!=INT_RESULT)
  {
    strcpy(message,"percentile() requires a real or int as parameter 1");
    return 1;
  }

  
  if (args->arg_type[1]!=REAL_RESULT && args->arg_type[1]!=INT_RESULT && args->arg_type[1]!=DECIMAL_RESULT)
  {
    asprintf(&message,"percentile() requires a real/int as parameter 2, instead %d",args->arg_type[1]);
    return 1;
  }
    
  if (args->arg_count>2 && args->arg_type[2]!=INT_RESULT)
  {
    strcpy(message,"percentile() requires an int as parameter 3");
    return 1;
  }
  
  initid->decimals=2;

  
  if (args->arg_count==3 && (*((ulong*)args->args[2])<=16))
  {
    initid->decimals=*((ulong*)args->args[2]);
  }
  
  percentile_data *buffer = new percentile_data;
  buffer->count = 0;
  buffer->abscount=0;
  buffer->pages = 1;
  buffer->values = NULL;

  initid->maybe_null    = 1;
  initid->max_length    = 32;
  initid->ptr = (char*)buffer;

  return 0;
}


void percentile_deinit( UDF_INIT* initid )
{
  percentile_data *buffer = (percentile_data*)initid->ptr;

  if (buffer->values != NULL)
  {
    free(buffer->values);
    buffer->values=NULL;
  }
  delete initid->ptr;
}

void percentile_clear(UDF_INIT* initid, char *is_null, char *error){
  percentile_data *buffer = (percentile_data*)initid->ptr;
  buffer->count = 0;
  buffer->abscount=0;
  buffer->pages = 1;
  *is_null = 0;
  *error = 0;

  if (buffer->values != NULL)
  {
    free(buffer->values);
    buffer->values=NULL;
  }

  buffer->values=(double *) malloc(BUFFERSIZE*sizeof(double));
}

void percentile_reset( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* is_error )
{
  percentile_clear(initid, is_null, is_error);
  percentile_add( initid, args, is_null, is_error );
}


void percentile_add( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* is_error )
{
  if (args->args[0]!=NULL)
  {
    percentile_data *buffer = (percentile_data*)initid->ptr;
    if (buffer->count>=BUFFERSIZE)
    {
      buffer->pages++;
      buffer->count=0;
      buffer->values=(double *) realloc(buffer->values,BUFFERSIZE*buffer->pages*sizeof(double));
    }
    if(args->arg_type[0]==INT_RESULT){
      buffer->values[buffer->abscount++] = (double) *((longlong*)args->args[0]);
    }    
    else{
      buffer->values[buffer->abscount++] = *((double*)args->args[0]);
    }
    buffer->count++;
  }
}

int compare_doubles (const void *a, const void *b)
{
  const double *da = (const double *) a;
  const double *db = (const double *) b;

  return (*da > *db) - (*da < *db);
}

double percentile( UDF_INIT* initid, UDF_ARGS* args, char* is_null, char* is_error )
{
  percentile_data* buffer = (percentile_data*)initid->ptr;
  double pctile;

  if (buffer->abscount==0 || *is_error!=0)
  {
    *is_null = 1;
    return 0.0;
  }

  *is_null=0;
  if (buffer->abscount==1)
  {
    return buffer->values[0];
  }

  qsort(buffer->values,buffer->abscount,sizeof(double),compare_doubles);
  if(args->arg_type[1]==INT_RESULT){
    pctile = (double) *((longlong*)args->args[1]);
  }
  else if(args->arg_type[1]==DECIMAL_RESULT){
    pctile = atof(args->args[1]);
  }
  else{
    pctile = *((double*)args->args[1]);
  }
  
  int n = buffer->abscount - 1;

  if(pctile <=0){
    return buffer->values[0];
  }
  if(pctile>=1){
    return buffer->values[n];
  }
  double i = n * pctile;
  int ix = (int) i;
  
  if (i == ix) 
  {
    return buffer->values[ix];
  } else
  {
    int j = ix + 1 >= n ? n : ix + 1;
    return(buffer->values[ix]*(j - i) + buffer->values[j]*(i - ix));
  }
  
}

#endif

将以上源代码编译成.so文件

gcc -fPIC -Wall -I /usr/include/mysql/ -shared -o udf_percentile.so udf_percentile.cc

在ubuntu下,如果没有/usr/include/mysql, 需要先安装libmysqlclient-dev:sudo apt-get install libmysqlclient-dev。如果是mysql5.x,你可以下载我编译好的so文件

将so文件拷贝到mysql的插件目录下:

sudo cp udf_percentile.so /usr/lib/mysql/plugin/

以管理员身份进入mysql,注册自定义函数:

CREATE AGGREGATE FUNCTION percentile RETURNS REAL SONAME 'udf_percentile.so';

使用方法:

SELECT percentile(score, 0.9991,5) FROM candidates;

0.9991就是百分位(99.91%),5是指结果的精度(小数位数)。

没有mysql服务的管理权限的,只能通过程序实现了,最后附上percentile的JAVA和JS实现。

JAVA:

public static double percentile(double[] data,double p){  
    int n = data.length;  
    Arrays.sort(data);  
    double px =  p*(n-1);  
    int i = (int)java.lang.Math.floor(px);  
    double g = px - i;  
    if(g==0){  
        return data[i];  
    }else{  
        return (1-g)*data[i]+g*data[i+1];  
    }  
} 

JS:

const percentile = (data, p) =>{  
    let n = data.length;  
    data.sort((a, b) => a - b);  
    let px =  p*(n-1);  
    let i = Math.floor(px);
    let g = px - i;
    if(g==0){  
        return data[i];  
    }else{  
        return (1-g)*data[i]+g*data[i+1];  
    }  
} 

 

© 著作权归作者所有

小水熊

小水熊

粉丝 68
博文 61
码字总数 41633
作品 1
静安
架构师
私信 提问
加载中

评论(1)

雪狼1999
雪狼1999
mysql8.0的SO文件创建不了。有没有8.0直接能用的SO文件?
hive函数----集合统计函数

集合统计函数1. 个数统计函数: count 语法: count(), count(expr), count(DISTINCT expr[, expr_.]) 返回值: int 说明: count()统计检索出的行的个数,包括NULL值的行;count(expr)返回指定字...

超人学院
2015/05/27
3.9K
0
JMeter 聚合报告之 90% Line 参数说明

其实要说明这个参数的含义非常简单,可能你早就知道他的含义,但我对这个参数一直有误解,而且还一直以为是“真理”,原于一次面试,被问到了这个问题,所以引起我这个参数的重新认识。 先说...

长平狐
2013/03/12
264
0
hive聚合函数

★聚合函数 count,sum,min,max,avg var_pop(col) 返回指定列的方差 var_samp(col) 返回指定列的样本方差 stddevpop(col) 返回指定列的偏差(标准差) stddevpop = stddev stddev_samp(c...

gmitt
2016/09/22
251
0
prometheus的summary和histogram指标的简单理解

prometheus的客户端与服务端 客户端是提供监控指标数据的一端(如写的exporter)。prometheus提供了各种语言的客户端库,需要通过Prometheus客户端库把监控的代码放在被监控的服务代码中。当...

xiaomin0322
10/11
12
0
深度解析机器学习中的置信区间(附代码)

机器学习很多时候需要估计某个算法在未知数据上的性能。 置信区间是一种对估计不确定性的量化方法,它们可以用来在总体参数(例如平均值mean,就是从总体中的一个独立观测样本上估计而来)上...

技术小能手
2018/07/03
0
0

没有更多内容

加载失败,请刷新页面

加载更多

关于运维,该怎么决定它的方向,这个似工作又似兴趣的存在

我之前主要从事网络、桌面、机房管理等相关工作,这些工作使我迷惘,这应该是大多数运维人都经历过的过程; 18年国庆,我从国内前三的消费金融公司裸辞,下海创业,就是想要摆脱这样的困境。...

网络小虾米
3分钟前
1
0
Java Timer的用法

Timer timer = new Timer(); timer.schedule(new TimerTask() { public void run() { System.out.println("11232"); } }, 200000 , 1000); public void schedule(TimerTask task, long delay......

林词
7分钟前
3
0
使用js动态加载外部js文件以及动态创建script脚本

动态脚本指的是在页面加载时不存在,但将来的某一时刻通过修改该DOM动态添加的脚本。和操作HTML元素一样,创建动态脚本也有两种方式:插入外部文件和直接插入JavaScript代码。 动态加载外的外...

Bing309
15分钟前
2
0
从零开始入门 K8s | Kubernetes 网络概念及策略控制

作者 | 阿里巴巴高级技术专家 叶磊 一、Kubernetes 基本网络模型 本文来介绍一下 Kubernetes 对网络模型的一些想法。大家知道 Kubernetes 对于网络具体实现方案,没有什么限制,也没有给出特...

阿里巴巴云原生
19分钟前
2
0
天气获取

本文转载于:专业的前端网站➨天气获取 $.get("http://wthrcdn.etouch.cn/WeatherApi", { citykey: cityCode }, function (d) { //创建文档对象 var parser = new ......

前端老手
19分钟前
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部