はじめに

日頃、業務でデータを分析をおこなっています。
分析するデータはMySQLに置いておき、簡単な集計はSQL、複雑な計算はRやPythonで
という棲み分けをしていますが、チームにはSQLはわかるけど他はチョット...というメンバーもいるので、統計量などはSQLでさくっと見れるようにしたいところ。
今回プロシージャの勉強もしつつ、現状実装されていない（はず）の中央値や四分位数、
相関係数を簡単に求められるようにしたいと考えました。

使用したデータ

ECのデータをKaggleから取得します。
英国を拠点とするオンライン小売業者の8カ月間 (2010年12月1日～2011年12月9日) の全取引を含むトランザクションデータセット
https://www.kaggle.com/carrie1/ecommerce-data/home

実行環境

・macOS Catalina バージョン10.15.17
　MacBook Pro (13-inch, 2020, Four Thunderbolt 3 ports)
・mysql Ver 8.0.22 for osx10.15 on x86_64 (Homebrew)
・MySQL Workbench version 8.0.21

my.cnfの設定（環境に応じて適宜読み替えてください）

vi /usr/local/etc/my.cnf

[mysqld]
sql_mode=''
secure-file-priv = ''

なお、データベースの構築方法などは割愛します。

データの準備（すでに手元にデータがあれば不要）

CREATE TABLE `ECdata` (
`InvoiceNo` varchar(10) DEFAULT NULL,
`StockCode` varchar(15) DEFAULT NULL,
`DESCription` varchar(100) DEFAULT NULL,
`Quantity` int DEFAULT NULL,
`InvoiceDate` datetime DEFAULT NULL,
`UnitPrice` decimal(10,3) DEFAULT NULL,
`CustomerID` varchar(20) DEFAULT NULL,
`Country` varchar(50) DEFAULT NULL
);

#import
LOAD DATA INFILE '~/ディレクトリ/data.csv'
INTO TABLE ECdata
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES 
(InvoiceNo, StockCode, DESCription,Quantity, @InvoiceDate,UnitPrice,CustomerID,Country)
SET InvoiceDate = STR_TO_DATE(@InvoiceDate, '%c/%e/%Y %k:%i'); #月/日/年 時:分

#空の件数を確認
SELECT
count(CASE WHEN InvoiceNo = "" THEN 1 ELSE NULL END )AS InvoiceNo_count
,count(CASE WHEN StockCode = "" THEN 1 ELSE NULL END )AS StockCode_count
,count(CASE WHEN DESCription = "" THEN 1 ELSE NULL END )AS DESCription_count
,count(CASE WHEN Quantity = "" THEN 1 ELSE NULL END )AS Quantity_count
,""AS InvoiceDate_count #,count(CASE WHEN InvoiceDate = "" THEN 1 ELSE NULL END )AS InvoiceDate_count # 設定にもよるがエラーになる
,"" AS UnitPrice_count#,count(CASE WHEN UnitPrice = "" THEN 1 ELSE NULL END )AS UnitPrice_count # UnitPrice = ""だと0がhitする
,count(CASE WHEN CustomerID = "" THEN 1 ELSE NULL END )AS CustomerID_count
,count(CASE WHEN Country = "" THEN 1 ELSE NULL END )AS Country_count
FROM ECdata;

#国別の件数を確認
SELECT Country, count(*) FROM ECdata
GROUP BY Country  ORDER BY count(*) DESC;

#合計金額を更新
ALTER TABLE ECdata ADD COLUMN TotalPrice float ; 
UPDATE ECdata SET TotalPrice = UnitPrice * Quantity;

#今回は最も多い国(UK)に絞って計算を行うことにしました
SELECT count(*)
FROM ECdata
WHERE (Quantity > 0 AND TotalPrice > 0)
AND Country = "United Kingdom";

#Recency Frequency Monetalyをそれぞれ計算します。
CREATE TABLE RFMdata
SELECT CustomerID,count(distinct InvoiceNo) AS F,sum(TotalPrice)AS M 
,datediff("2011-12-31 00:00:00",max(InvoiceDate)) AS R #日付が古く元のデータと差が大きいので近いところで11年の年末を設定
FROM ECdata
WHERE (Quantity > 0 AND TotalPrice > 0)
AND Country = "United Kingdom"
GROUP BY CustomerID;

プロシージャの作成

中央値

計算の流れとしては次のようになります。
①.データの行数をカウント
②.2で割った時に余りがあれば行数が奇数→floor(x/2)で整数値を取得
　割り切れれば偶数→floor(x/2)-1で整数値-1 を取得
　→offsetに使用
③.2で割った時に奇数であれば1を、偶数であれば2を取得
　→limitに使用
④.①〜③の数値を使って中央値の値をavg()で計算します。
例えば、データ数が5件であれば、2で割って2.5になるので、floorによって2（②より）が返ってきます。
offsetによって2行目まで飛ばして3行目からデータを使用することになります。
limitはこの時1（③より）となるので3行目のデータが中央値として取得できます。
4の時はoffsetが1、limitが2になるので、2行目と3行目の値の平均値が取得できます。

第一四分位、及び第三四分位も似たような出し方になっています。

#計算したい列とテーブル名を入力すると中央値をユーザー定義変数にsetする
DROP PROCEDURE IF EXISTS select_median;
delimiter //
CREATE PROCEDURE select_median(IN col text, IN tablename text, OUT median decimal(20,3))
BEGIN
    SET @col = col;
    SET @tablename = tablename;
    SET @all_row_count = NULL;
    BEGIN
        SET @row_count_sql = concat('SELECT count(*) into @all_row FROM ',@tablename);
        PREPARE stmt FROM @row_count_sql;
        EXECUTE stmt ;
        DEALLOCATE PREPARE stmt;
        SET @all_row_count = @all_row;
    END;

    SET @all_ofs = cast((SELECT CASE WHEN @all_row_count % 2 = 0 THEN floor(@all_row_count/2) -1 else floor(@all_row_count/2) END )AS SIGNED);
    SET @all_lim = (SELECT CASE WHEN @all_row_count % 2 = 0 THEN 2 ELSE 1 END );
    SET @all_sql = concat('SELECT avg(col) into @median FROM (SELECT ', @col,' AS col FROM ', @tablename,' ORDER BY col ASC LIMIT ? OFFSET ?) AS tmp');
    PREPARE stmt_median FROM @all_sql;
    EXECUTE stmt_median USING @all_lim,@all_ofs;
    DEALLOCATE PREPARE stmt_median;
    SET median = @median;
END//
delimiter ;

第一四分位数

DROP PROCEDURE IF EXISTS select_first_quartile;
delimiter //
CREATE PROCEDURE select_first_quartile(IN col text, IN tablename text, IN median decimal(20,2), OUT first_quartile decimal(20,2))
BEGIN
    SET @col = col;
    SET @tablename = tablename;
    SET @first_quartile_row_count = NULL;
    BEGIN
        SET @row_sql = concat('SELECT count(*) into @first_quartile_row FROM ',@tablename,' WHERE ', @col,' < ',@median);
        PREPARE row_counter FROM @row_sql;
        EXECUTE row_counter;
        DEALLOCATE PREPARE row_counter;
        SET @first_quartile_row_count = @first_quartile_row;
    END;

    SET @ofs = CAST((SELECT CASE WHEN @first_quartile_row_count % 2 = 0 THEN floor(@first_quartile_row_count/2) -1 else floor(@first_quartile_row_count/2) END )AS SIGNED);
    SET @lim = (SELECT CASE WHEN @first_quartile_row_count % 2 = 0 THEN 2 ELSE 1 END );
    SET @sql = CONCAT('SELECT avg(col) into @first_quartile FROM (SELECT ', @col,' AS col FROM ',@tablename,' WHERE ',@col,' < ',median,' ORDER BY col ASc limit ? offSET ?) AS tmp');

    PREPARE stmt_first_quartile FROM @sql;
    EXECUTE stmt_first_quartile USING @lim,@ofs;
    DEALLOCATE PREPARE stmt_first_quartile;
    SET first_quartile = @first_quartile;
END//
delimiter ;

第三四分位数

第一四分位数の出し方とほとんど同じです

DROP PROCEDURE IF EXISTS select_third_quartile;
delimiter //
CREATE PROCEDURE SELECT_third_quartile(IN col text, IN tablename text, IN median decimal(20,2), OUT third_quartile decimal(20,2))
BEGIN
    SET @col = col;
    SET @tablename = tablename;
    SET @third_quartile_row_count = NULL;
    BEGIN
        SET @row_sql = concat('SELECT count(*) into @row FROM ',@tablename,' WHERE ',@col,' > ',@median);
        PREPARE row_counter FROM @row_sql;
        EXECUTE row_counter;
        DEALLOCATE PREPARE row_counter;
        SET @row_count = @row;
    END;
    SET @ofs = CAST((SELECT CASE WHEN @row_count % 2 = 0 THEN floor(@third_quartile_row_count/2) -1 else floor(@third_quartile_row_count/2) END )AS SIGNED);
    SET @lim = (SELECT CASE WHEN @third_quartile_row_count % 2 = 0 THEN 2 ELSE 1 END );
    SET @sql = concat('SELECT avg(col) into @third_quartile FROM (SELECT ', @col,' AS col FROM ',@tablename,' WHERE ',@col,' > ',median,' ORDER BY col ASc limit ? offSET ?) AS tmp');
    PREPARE stmt_third_quartile FROM @sql;
    EXECUTE stmt_third_quartile USING @lim,@ofs;
    DEALLOCATE PREPARE stmt_third_quartile;
    SET third_quartile = @third_quartile;
END//
delimiter ;

相関係数

相関係数の計算式をsqlに置き換えています。

DROP PROCEDURE IF EXISTS select_corr;
delimiter //
CREATE PROCEDURE select_corr(IN first_col text, IN second_col text, IN tablename text, OUT corr_value decimal(20,5))
BEGIN
    SET @col1 = first_col;
    SET @col2 = second_col;
    SET @avg_sql = concat('SELECT avg(',@col1,'),avg(',@col2,') into @s, @t FROM ',tablename);
    PREPARE stmt_avg FROM @avg_sql;
    EXECUTE stmt_avg ;
    DEALLOCATE PREPARE stmt_avg;

    SET @corr_sql = concat('SELECT (sum((',@col1,' - @s) * (',@col2,' - @t))/count(*))/ (stddev_pop(',@col1,') * stddev_pop(',@col2,'))  into  @corr FROM ',tablename);
    PREPARE stmt_corr FROM @corr_sql;
    EXECUTE stmt_corr ;
    DEALLOCATE PREPARE stmt_corr;
    SET corr_value = round(@corr,5);
END//
delimiter ;

出力

テスト用のデータを当てはめて計算します。


#最大、最小、平均、標準偏差、標本数をセット
SELECT max(F),min(F),avg(F), stddev(F),count(*) into @max, @min, @ave, @std,@cnt FROM RFMdata;

#中央値、四分位を取得
#(カラム名,テーブル名, 出力したい値をセットする変数)
CALL select_median("F", "RFMdata", @median);

#(カラム名,テーブル名,中央値 ,出力したい値をセットする変数)
CALL select_first_quartile("F", "RFMdata",@median, @first_quartile);
CALL select_third_quartile("F", "RFMdata",@median, @third_quartile);

#(相関を見たい列1, 相関を見たい列2, テーブル名, 出力したい値をセットする変数)
CALL select_corr("F", "M", "RFMdata", @corr);

#表示
SELECT @min,@first_quartile, @median, @ave, @third_quartile, @max,@std,@cnt,@corr;

おわりに

ひとまずそれぞれの計算を実行できるようになりました。
作成したプロシージャをCALLするプロシージャを作れば、さらにまとめて出力も可能だと思います。
また、今回はプロシージャを作成したスキーマ上でなければ実行ができないので、
用意されている関数のようにどこでも使えないのは課題となりました。（UDF勉強してみる）　

先にも書きましたが、RやPython(numpy,pandas)であればこんな面倒なことはせずとも用意されている関数でさっと出すことができます。
しかしながら、今回このように作成することにより、プロシージャの挙動やどうやって計算しているのかと、それぞれの数式の確認もできたので良かったかなと思います。
こっちの書き方の方がいいよ、などありましたら是非ご教授いただけますと幸いです！