[SQL] 計算資料比數所佔的比例 @ MySQL 5.6

這個需求好像滿常見的？例如有一張表記錄著許多操作的流水帳，例如：

CREATE TABLE `play_action` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`play_result` INT (10) NOT NULL
)

想要以天為單位：

SELECT DATE_FORMAT(timestamp, "%Y-%m-%d %a") AS d, play_result, count(*) FROM play_action GROUP BY d, play_result

就能得知一天當中，所有操作結果的次數，那如果是比例呢？只好要先 SUM 加總一下 XD 最後在 JOIN 一下即可。此例就用暫存表來處理，其中暫存表不予許同一個 SQL 內有多次存取，所以只好多開一張暫存表來處理，連續動作：

Step 1: 建立一張暫時表，以天為單位，根據播放後的狀態，依序是播放次數、播放日期、播放結果，其中播放結果 0 是正常的

CREATE TEMPORARY TABLE `play` (
`play_count` INT (10) NOT NULL,
`play_date` VARCHAR(32) NOT NULL,
`play_result` INT (10) NOT NULL,
UNIQUE KEY `play_date` (`play_date`, `play_result`)
);

Step 2: 從 play_action 撈資料來填補，此時可以用條件式挑選想要的資料，此例是 4 月之後的資料

INSERT INTO `play` (play_result, play_date, play_count) SELECT play_result, DATE_FORMAT(timestamp, "%Y-%m-%d %a") AS d, count(*) as c FROM play_action WHERE timestamp >= '2016-04-01 00:00:00' GROUP BY play_result, d ORDER BY d ON DUPLICATE KEY UPDATE play_count=VALUES(play_count);

Step 3: 建立另一張暫存表，單純以天記錄總播放次數

CREATE TEMPORARY TABLE `play_sum` (
`play_count` INT (10) NOT NULL,
`play_date` VARCHAR(32) NOT NULL,
UNIQUE KEY `play_date` (`play_date`)
);

INSERT INTO `play_sum` (play_date, play_count) SELECT play_date, SUM(play_count) AS play_total FROM play GROUP BY play_date;

Step 4: 簡易運算，輸出為日期、播放成功次數的比例，總播放次數

SELECT play.play_date, play.play_result, play.play_count / play_sum.play_count * 100, play_sum.play_count AS play_total FROM play, play_sum WHERE play.play_date = play_sum.play_date;

+----------------+-------------+---------------------------------------------+------------+
| play_date      | play_result | play.play_count / play_sum.play_count * 100 | play_total |
+----------------+-------------+---------------------------------------------+------------+
| 2016-04-01 Fri |           0 |                                     54.9398 |        687 |
| 2016-04-02 Sat |           0 |                                     68.9445 |        972 |
| 2016-04-03 Sun |           0 |                                     75.6607 |       1748 |
| 2016-04-04 Mon |           0 |                                     71.3967 |       1112 |
| 2016-04-05 Tue |           0 |                                     70.2852 |        886 |
| 2016-04-06 Wed |           0 |                                     49.8085 |        880 |
| 2016-04-07 Thu |           0 |                                     53.2239 |        596 |
+----------------+-------------+---------------------------------------------+------------+

Bash 筆記 - 使用時間區間備份 MySQL 資料表

由於資料屬性的關係，剛好有幾個 table 都有 timestamp 這個資訊，拿來備份恰恰好，設計一下備份方式：

• 一次備份 n 個月
• 限制每 n 個月一次或是每次執行只備份前面符合條件的 n 個月

例如 n=6 時，在 1~6 月只能備份去年 7~12 月資料，在 7~12 月時，才能 1~6 月份的資料，並且讓程式能夠小小容錯，例如以產生過的資料就不要再匯出、匯出的資料要做壓縮、壓縮完要測試解壓縮、產生 md5 驗證等。

總之，技術細項：

1. 使用 data 指令得知現況時間並計算出備份範圍
2. 使用 mysqldump 搭配 --where 指令匯出指定 timestamp 資料
3. 使用 tar -zcvf 備份資料為 tar.gz 格式
4. 使用 gunzip -t 測試壓縮資料
5. 使用 md5sum 建立壓縮檔案的驗證檔

執行方式，採用 bash, mysqldump, tar, gunzip, md5sum, date 指令：

#!/bin/bash
month_offset=0
month_range=6

current_month=`date +"%m"`
if [ ${current_month:0:1} -eq '0' ] ; then
        current_month=${current_month:1:1}
fi
month_offset=$(( $(($current_month + $month_range - 1)) % $month_range  ))
timestamp_begin_date=`date +"%Y-%m-01 00:00:00" -d "-$(($month_offset + $month_range)) month"`
timestamp_end_date=`date +"%Y-%m-01 00:00:00" -d "-$month_offset month"`
timestamp_init_date=`date +"%Y-%m-01"  -d "-$month_offset month"`
timestamp_out_date=`date +"%Y-%m-%d" -d "$timestamp_init_date -1 day" `
job_id=`date +"%Y-%m" -d "-$(($month_offset + 1)) month"`

DIR=/data/routine
log_dir=$DIR/log
dump_dir=$DIR/db
mkdir -p $log_dir $dump_dir
echo "[INFO] Job ID: $job_id , Begin: $timestamp_begin_date , End: $timestamp_end_date"
echo "[INFO] Working Dir: $DIR , log: $log_dir , export: $dump_dir"
echo "[INFO] Output suffix: $timestamp_out_date"
echo

arget_host=MyDBServerIP
target_user=UserID
target_pass=UserPassword
target_db=DatabaseName
target_table=("table1" "table2" "table3")
for table in "${target_table[@]}" ;do
if [ ! -f "$dump_dir/$table.$timestamp_out_date.sql" ] ; then
cmd="time mysqldump -h $target_host -u $target_user -p$target_pass --where \"timestamp >= '$timestamp_begin_date' AND timestamp < '$timestamp_end_date'\" --single-transaction --databases $target_db --tables $table > $dump_dir/$table.$timestamp_out_date.sql"
echo $cmd;
        fi        
done

echo
echo -n "Wanna go (y/N): "
read ans

if [ "$ans" == 'Y' ] || [ "$ans" == 'y' ] ; then
for table in "${target_table[@]}" ;do
output_file=$table.$timestamp_out_date.sql
output_archive_file=$output_file.tar.gz
output_archive_check_file=$output_archive_file.md5
echo
echo "build target: $output_file"
echo "\t$output_archive_file"
echo "\t$output_archive_check_file"
echo "..."
if [ ! -f "$dump_dir/$output_file" ] ; then
cmd="time mysqldump -h $target_host -u $target_user -p$target_pass --databases $target_db --tables $table --single-transaction --where \"timestamp >= '$timestamp_begin_date' AND timestamp < '$timestamp_end_date'\" > $dump_dir/$output_file"
echo $cmd
time mysqldump -h $target_host -u $target_user -p$target_pass --databases $target_db --tables $table --single-transaction --where "timestamp >= '$timestamp_begin_date' AND timestamp < '$timestamp_end_date'" > $dump_dir/$output_file
fi
if [ ! -f "$dump_dir/$output_archive_file" ] ; then
echo "time tar -zcf \"$table.$timestamp_out_date.sql.tar.gz\" \"$table.$timestamp_out_date.sql\""
cd $dump_dir && time tar -zcf "$output_archive_file" "$output_file"
fi
if [ -f "$dump_dir/$output_archive_file" ] && [ ! -f "$dump_dir/$output_archive_check_file" ] ; then
echo "time gunzip -t $output_archive_file && time md5sum $output_archive_file > $output_archive_check_file"
cd $dump_dir && time gunzip -t $output_archive_file && time md5sum $output_archive_file > $output_archive_check_file
fi
done
fi

若還有其他興趣的話，可以再結合 AWS glacier 的上傳，例如：

aws_glacier_bin="java -jar /path/uploader-0.0.8-SNAPSHOT-jar-with-dependencies.jar "
aws_glacier_region=https://glacier.region-id.amazonaws.com
aws_glacier_dir=db-log
aws_glacier_partsize=134217728

if [ "$aws_glacier" == 'Y' ] || [ "$aws_glacier" == 'y' ] ; then
for table in "${target_table[@]}" ;do
output_file=$table.$timestamp_out_date.sql
output_archive_file=$output_file.tar.gz
output_archive_check_file=$output_archive_file.md5
echo
echo "selected: $output_archive_file"
if [ -f "$dump_dir/$output_archive_file" ] && [ -f "$dump_dir/$output_archive_check_file" ] ; then
echo "do upload: $output_archive_file"
cmd="time $aws_glacier_bin --endpoint \"$aws_glacier_region\" -v \"$aws_glacier_dir\" --multipartupload \"$output_archive_file\" --partsize $aws_glacier_partsize  "
echo $cmd;
cd $dump_dir && eval $cmd;
fi
done
fi