MySQL Math UDF ( User Define Function )

OpenSource 중에 MySQL 함수 관련 소스가 있어 찾아 봤음
통계용 함수를 MySQL의 Plugin 형식으로 개발

1. MySQL Plugin용 Math UDF

--
-- 01. Install
-- ( use root user )

git clone http://github.com/megastep/mysql-udf.git

if os = ubuntu then
 apt install gcc gcc-c++ make libmariadbclient-dev
else if os = centos
 yum install gcc gcc-c++ make MariaDB-devel-xx.xx
end if

vi Makefile

/* MySQL Header File 위치 수정 */

INCL = -l/usr/include/mysql

/* Build */

make

cp udf_math.so /usr/lib64/mysql/plugin

/* function import */

mysql -uroot -p -A mysql -e "source import.sql"

--
-- 02. Check 
--

select * from mysql.func;

name                  ret  dl           type       
-----------------  ------  -----------  -----------
colwidth                2  udf_math.so  aggregate  
confidence_higher       1  udf_math.so  function   
confidence_lower        1  udf_math.so  function   
correlation             1  udf_math.so  aggregate  
faculty                 1  udf_math.so  function   
geomean                 1  udf_math.so  aggregate  
intercept               1  udf_math.so  aggregate  
kurtosis                1  udf_math.so  aggregate  
longest                 0  udf_math.so  aggregate  
median                  1  udf_math.so  aggregate  
noverm                  1  udf_math.so  function   
skewness                1  udf_math.so  aggregate  
slope                   1  udf_math.so  aggregate  
stdnorm_density         1  udf_math.so  function   
stdnorm_dist            1  udf_math.so  function   
weightedavg             1  udf_math.so  aggregate  
steyx                   1  udf_math.so  aggregate  

Function Spec

UDF MEDIAN()

Function name               median
Purpose                     calculate the median of a column
Aggregate function          Yes
Input parameter(s)          1 (column: REAL)
Conditions                  -
Output value(s)             1 per group (REAL)
Examples                    SELECT MEDIAN(age) FROM bar
                            SELECT gender, MEDIAN(age),AVG(age) FROM bar GROUP BY gender
Registration                CREATE AGGREGATE FUNCTION median RETURNS REAL SONAME 'udf_median.so';

UDF CORRELATION()

Function name               correlation
Purpose                     calculate the correlation coefficient of the linear regression of two sets of variables
Aggregate function          Yes
Input parameter(s)          1 (dependent variable: REAL, 
                             independent variable: REAL)
Conditions                  -
Output value(s)             1 per group (REAL)
Examples                    SELECT correlation(income,age) FROM customers
Registration                CREATE AGGREGATE FUNCTION correlation RETURNS REAL SONAME 'udf_correlation.so';

UDF INTERCEPT()

Function name               intercept
Purpose                     calculate the intercept of the linear regression of two sets of variables
Aggregate function          Yes
Input parameter(s)          2 (dependent variable: REAL, 
                             independent variable: REAL)
Conditions                  -
Output value(s)             1 per group (REAL)
Examples                    SELECT intercept(income,age) FROM customers
Registration                CREATE AGGREGATE FUNCTION intercept RETURNS REAL SONAME 'udf_intercept.so';

UDF SLOPE()

Function name               slope
Purpose                     calculate the slope of the linear regression of two sets of variables
Aggregate function          Yes
Input parameter(s)          2 (dependent variable: REAL, 
                             independent variable: REAL)
Conditions                  -
Output value(s)             1 per group (REAL)
Examples                    SELECT slope(income,age) FROM customers
Registration                CREATE AGGREGATE FUNCTION slope RETURNS REAL SONAME 'udf_slope.so';

UDF SKEWNESS()

Function name               skewness
Purpose                     calculate the skewness of a distribution of values
Aggregate function          Yes
Input parameter(s)          1 (data: REAL)
Conditions                  -
Output value(s)             1 per group (REAL)
Examples                    SELECT skewness(age) FROM customers
Registration                CREATE AGGREGATE FUNCTION skewness RETURNS REAL SONAME 'udf_skewness.so';

UDF KURTOSIS()

Function name               kurtosis
Purpose                     calculate the kurtosis of a distribution of values
Aggregate function          Yes
Input parameter(s)          1 (data: REAL)
Conditions                  -
Output value(s)             1 per group (REAL)
Examples                    SELECT kurtosis(age) FROM customers
Registration                CREATE AGGREGATE FUNCTION kurtosis RETURNS REAL SONAME 'udf_kurtosis.so';

UDF CONFIDENCE_HIGHER()

Function name               confidence_higher
Purpose                     calculate the upper bound of the confidence interval for a given standard deviation, sample size, mean and confidence probabilty
Aggregate function          No
Input parameter(s)          4 or 5 (confidence probability p: INT or REAL, 
                                               sample size n: INT, 
                                          arithmetic mean my: INT or REAL, 
                                        standard deviation s: INT or REAL, 
                                          number of decimals: INT, optional)
Conditions                  -
Output value(s)             1 per row (REAL)
Examples                    SELECT confidence_higher(0.95,2000,550,60,4)
Registration                CREATE FUNCTION confidence_higher RETURNS REAL SONAME 'udf_confidence_higher.so';

UDF CONFIDENCE_LOWER()

Function name               confidence_lower
Purpose                     calculate the lower bound of the confidence interval for a given standard deviation, sample size, mean and confidence probabilty
Aggregate function          No
Input parameter(s)          4 or 5 (confidence probability p: INT or REAL,
                                               sample size n: INT,
                                          arithmetic mean my: INT or REAL,
                                        standard deviation s: INT or REAL,
                                          number of decimals: INT, optional)
Conditions                 -
Output value(s)            1 per row (REAL)
Examples                   SELECT confidence_lower(0.95,2000,550,60,4)
Registration               CREATE FUNCTION confidence_lower RETURNS REAL SONAME 'udf_confidence_lower.so';

UDF STDNORM_DENSITY()

Function name              stdnorm_density
Purpose                    calculate the value of the density function of the standard normal distribution for a given point
Aggregate function         No
Input parameter(s)         1 or 2 (           point p: INT or REAL,
                                   number of decimals: INT, optional)
Conditions                 -
Output value(s)            1 per row (REAL)
Examples                   SELECT stdnorm_density(0,4)
Registration               CREATE FUNCTION stdnorm_density RETURNS REAL SONAME 'udf_stdnorm_density.so';

UDF STDNORM_DIST()

Function name             stdnorm_dist
Purpose                   calculate the value of the distribution function of the standard normal distribution for a given point
Aggregate function        No
Input parameter(s)        1 or 2 (           point p: INT or REAL,
                                  number of decimals: INT, optional)
Conditions                -
Output value(s)           1 per row (REAL)
Examples                  SELECT stdnorm_dist(0,4)
Registration              CREATE FUNCTION stdnorm_dist RETURNS REAL SONAME 'udf_stdnorm_dist.so';

UDF GEOMEAN()

Function name             geomean
Purpose                   calculate the geometric mean of a column
Aggregate function        Yes
Input parameter(s)        1 (data: REAL)
Conditions                -
Output value(s)           1 per group (REAL)
Examples                  SELECT geomean(price) FROM products
Registration              CREATE AGGREGATE FUNCTION geomean RETURNS REAL SONAME 'udf_geomean.so';

UDF WEIGHTEDAVG()

Function name             weightedavg
Purpose                   calculate the weighted average of a values
Aggregate function        Yes
Input parameter(s)        2 (data: REAL)
                          (weight: REAL)
Conditions                -
Output value(s)           1 per group (REAL)
Examples                  SELECT weightedavg(voting,categoryweight) FROM votes
Registration              CREATE AGGREGATE FUNCTION weightedavg RETURNS REAL SONAME 'udf_weightedavg.so';

UDF NOVERM()

Function name            noverm
Purpose                  calculate the number of possible combinations of size m from a set of n total values
                         (without regarding the order of values in combinations)
Aggregate function       No
Input parameter(s)       2 (n: INTEGER, 
                            m: INTEGER)
Conditions               n must be greater than 1, m must be between 1 and m
Output value(s)          1 (REAL)
Examples                 SELECT noverm(10,2)
                         SELECT noverm(foo,bar) FROM baz
Registration             CREATE FUNCTION noverm RETURNS INTEGER SONAME 'udf_noverm.so';

UDF FACULTY()

Function name            faculty
Purpose                  calculate the faculty (n!) of a number (1*2*3*4*5*6...*n)
Aggregate function       No
Input parameter(s)       1 (n: INTEGER)
Conditions               n must be greater than 0
Output value(s)          1 (REAL)
Examples                 SELECT faculty(20)
SELECT                   faculty(foo) FROM bar
Registration             CREATE FUNCTION faculty RETURNS REAL SONAME 'udf_faculty.so';

UDF COLWIDTH()

Function name            colwidth
Purpose                  calculate the length of the longest value in a STRING column
Aggregate function       Yes
Input parameter(s)       1 (column: STRING)
Conditions               -
Output value(s)          1 per group (INTEGER)
Examples                 SELECT COLWIDTH(request_vars) FROM request_log
                         SELECT session.id,COLWIDTH(session_vars.value) FROM session,session_vars 
                         WHERE  session.id=session_vars.session_id 
                         GROUP BY session_id
Registration             CREATE AGGREGATE FUNCTION COLWIDTH RETURNS INTEGER SONAME 'udf_colwidth.so';

UDF LONGEST()

Function name            longest
Purpose                  get the longest value in a STRING column
Aggregate function       Yes
Input parameter(s)       1 (column: STRING)
Conditions               -
Output value(s)          1 per group (STRING)
Examples                 SELECT LONGEST(name) FROM users
                         SELECT LONGEST(request_vars) FROM request_log GROUP BY script_name
Registration             CREATE AGGREGATE FUNCTION LONGEST RETURNS STRING SONAME 'udf_longest.so';

Blizzard Oracle Developer Paper Exam

 
언젠가 우연하게 받아 봤던 Blizzard 의 Oracle Developer Position 의 1st Paper Exam 문제 이다.
그 때 느낀 건데 내가 너무 자만 하고 준비가 안되어 있구나 했다. 

  One Way Ticket
  Digital Nomad

Battle.net Database Developer Take Home Test #1

The “Awesome Game Show” will be hosting a limited number of tickets per seating tier:
 Tier R 100 seats
 Tier S 500 seats
 Tier A 1000 seats
 Tier B 2000 seats

It is extremely important to maximize concurrency for the ticket sales due to popularity of the “Awesome
Game Show” which can sell out in minutes with an extremely high number of concurrent transactions.
Under the two different scenarios listed below, implement functions/procedures to reserve and cancel reservation using PL/SQL package(s).

Case 1. All seats are pre-assigned to a specific seat number. 

 Tier R Seat Numbers R1-R100
 Tier S S1-S500
 Tier A A1-A1000
 Tier B B1-B2000

Case 2. All seats in a specific tier can be openly assigned. In this case, cancelled reservations do not become available for re-sale. Once tickets are sold out, ticket sales are finished and only cancellations can be made

[Assignments]

1. Use a preferred modeling tool to draw out a logical model and submit a screen captured image file of the model.

2. Submit a screen captured image file of a physical model and table/index creation scripts for Oracle DBMS.

3. Submit Oracle PL/SQL package source file(s) for the following functions: reservation and reservation cancellation.

Battle.net Database Developer Take Home Test #2

“B” Corp. wants to build a virtual currency system and use it as their payment method to purchase their products/services. Requirements for this system in charging up and spending the virtual money are listed as follows.

- Charging up virtual currency.

1. The virtual currency can be charged up using real life currency (Real money credit) using cash, credit/debit card, and other real money payment methods. Virtual money can also be charged up without using real money (Non-real money credit) through compensations and events.

2. Non-real money credits expire in 5 years. Expiration is set to 00:00 on a day of 5 years from the charge up. Expired non-real money credits are unusable and the total balance should reflect this rule.

3. When charging up real money credit maximum balance is limited by a daily, weekly, and monthly threshold with respect to charge up time.

- Spending virtual money.

1. Purchasing products/services must use non-real money credits before using real money credits. However, there is no priority on using different types (compensation vs events) of non-real money credit but credits should be consumed in the order that they were added from oldest to newest.

For example, a user was granted $10 of non-real money credit on January 1, 2014 and then granted another $10 of non-real money credit for a different event on January 15, 2014. When this user initiates a purchase of an in-game character name change, the ending balance should be $5 and this balance must expire on January 5, 2019 00:00:00. You must be able to show that this remaining $5 is a residual amount of credit granted by an event on January 15, 2014 in the amount of $10.

The virtual money system must be rapid and robust since “B” Corp services millions of customers and all purchases from the company uses this system. Implementation of database design and PL/SQL must consider high throughput of B’s needs. Consistency is a must since the virtual currency system is a finance related service.

[Assignments]

1. Use a preferred modeling tool to draw out a logical model and submit a screen captured image file of the model.

2. Submit a screen captured image file of a physical model and table/index creation scripts for Oracle DBMS. The physical model must consider high performance read/write operations even with massive
amount of data.

3. Submit Oracle PL/SQL package source file(s) for the following functions:

o Credit charge up

o Consume credits

o Expose a list of each user’s charge up/consume activities. Search date range will be provided by API caller. Design this API to expose data set to a web server. Web display will use pagination. Therefore, your API should only return matching data set for requested page number. Each page needs to display 10 records.

o Expose each user’s total balance, total real money balance, and total non-real money balance. Non-real money balance must exclude expired credits.

o Take a date range of a minimum of one month of time and expose daily sum of charge up/consume credit amount, 7 day moving average of charge up/consume amount, and delta in daily charge up/consume amount compared to the previous day. The result set should show data for every day and display $0 for the days that no activity is detected but the result set must display data for every day. (Do not use cursor for this API.)

Code Test

1. CPP

#include "iostream"

using namespace std;

int main()
{
    cout << "Hello world!" ;
    return 0;
}

2. JAVA

import java.lang.String;
import java.lang.System;
import java.lang.Exception;

public final class  helloworld
{
        public static void main(String[] args) throws Exception {
                
                System.out.println("hello world");
        }
}

3. PYTHON

#!env python3

import sys

class Hello():
    def __init__(self):
        self.names = 'Myoungsig '
    def sayHello(self):
        print(self.names + ' Hello World!')

if __name__ == '__main__':
        myHello = Hello()
        myHello.sayHello()

4. Bash

#!env bash

echo "hello world!"

5. C

#include "stdlib.h"
#include "stdio h"

int
main(int argc, char **argv)
{
 printf("hello world! \n");

 return 0;
}

6. SQL

select 'hello world!' as cmd
from dual
;

7. PL/SQL

set serveroutput on
declare
    msg varchar2(20) := 'Hello World'
begin
    dbms_output.put_line(msg);
end;
/