複数ファイルのダウンロード

運用メンバーが利用するページを、イートスマートでは"バックエンド"と呼んでいます。 バックエンドでは、商品データの操作やクチコミの監視、ニュースの編集等の業務が行われています。 それらの業務のなかで、CSVをダウンロードするものがあります。 今回は、このCSVダウンロードを改修した話をきます。

複数のCSVを一括でダウンロードしたい

こんな要望が運用メンバーからあがりました。 もぐナビにはプレゼントの企画がありますが、プレゼントの数を増やしたことで増えた業務を効率化したいとこのとでした。 現状のダウンロードは以下のような流れになります

[プレゼント一覧]→[プレゼントを選択]→[CSVをダウンロード]

以前は問題ありませんでしたが、プレゼントの数が増えたことで問題になりました。 解決策として以下の３つが候補になりました。

• 複数CSVをエクセルのシートにして1度にダウンロード
• 複数CSVをZIPにアーカイブして１度にダウンロード
• 複数CSVを１度にダウロード

複数CSVを１度にダウンロードする

１と２はCSVを出力するAPIに改修が必要なので、今回は３を選びました。 既存のAPIは１つのCSVを出力するので、これを複数リクエストすることにします。 方法としては、チェックボックスで選択されたプレゼントのCSVをダウンロードするAPIを、動的に作成したiframeで実現します。

jQuery('body').append('<iframe src="' + [APIエンドポイント]+ '" width="1" height="1"></iframe>');

以上で、フロントエンドの改修のみで複数CSVのダウンロードを実現できました。

井桁と嬰記号

もぐナビの投稿について話をしていた時に、意外とハッシュ(#)とシャープ(♯)の違いに気づいていない人が多かったので記事にしてみました。

ことの起こり

先日、もぐナビニュースの記事コンテンツにコメントできるように機能追加をしました。 コメント内でハッシュタグを使うと自動的にリンクになるように実装しているのですが、ある人から「ハッシュタグのリンクが機能していないよ」と言われました。

ぱっとみた所でハッシュタグに違和感を感じ、よく見てみるとハッシュタグではなくシャープ記号(♯)でした。 どうやらスマフォでハッシュを入力する時に「しゃーぷ」と打って変換していたようで、その時は「これって長州のハッシュドタグと変わらないレベルですよね」と話をしていました。

少しづつですが親切な人達に教えてもらいながらTwitterの機能を勉強してます☀️
まずはハッシュドタグ☺️

井長州力

— 長州力 (@rikichannel1203) March 12, 2020

この後、笑い話のつもりでシステム部のメンバーに話をしたところ、「ほんとだ！違う！」という声が上がり、知らない人も結構いるんだなあと思った次第です。

PCで作業をしていると、ハッシュは自然とShift+3で入力してしまうので、改めてシャープの存在に気が付いたという所でしょうか。

シャープ記号

学生の頃はコンピューターは使っていなかったので、この形の記号は当然のように楽譜のシャープ(嬰記号と呼ぶらしい)だと捉えていたと思います。 シャープ記号は五線譜の横線と重ならないように、横線が斜めになっているそうです。

ちなみにシャープはハッシュと似ていますが、ダブルシャープ(𝄪)という掛け算記号(×)に似ている記号もあるようです。

ハッシュ記号

ハッシュは、番号を意味する記号で、元は重さのポンド(lb)に横棒を引いたものが崩れて#になったそうです。おじさんの間では、井桁と呼ばれていると思います。

No.のように数字の並びの頭につけたり、数字のフォーマットの桁の意味で使ったりしていると思います。 また、多くの言語のコメント記号として使用されたり、C言語のディレクティブに使われています。

通常のキーボードからの入力では、当然のようにハッシュ記号を入力するので、シャープと言いながらハッシュを入力していると思います。 天下のマイクロソフトも、C#のことを「シーシャープ」と読んでいるので、混在甚だしいですが、ことあるごとに「ハッシュ」「ハッシュ」と呼ぶようにすると、少し通っぽく見えるかもしれませんね。

EXISTS句を使ったサブクエリについて

今回は、もぐナビのプロジェクトの実装で学んだことについてまとめていきます。 テーマは、EXISTS句を使ったSQLの振り返りです。

実装の内容

特定条件のクチコミを抽出する際に、任意のユーザーのlikeが既にある場合は 除外クチコミとしたいので、その判定にEXISTS句を使っていこうと思います。 最初にEXISTSを使わない例と比較していきます。

①EXISTS句を使わない実装

最初に実装していた方法として、任意のユーザーのlikeがあるかの判定は、countの戻り値 を利用すればいいかなと思い、以下のようなメソッドを作って処理していました。

public static boolean isOffcialVote(BigDecimal userCommentId) throws SystemException {
        int count = DBConnectionManager.getInstance().getConnection().getInt(
                "SELECT COUNT(*) " +
                "FROM t_user_comment_voting(クチコミに関するlikeがあるテーブル) t " +
                "WHERE t.user_id =  '任意のuserId' AND t.user_comment_id = ? ",
                userCommentId);
        return count > 0;
    }

trueの場合は、countが1以上ということなので、既にlikeありと見なしてlikeの対象にしない。 (つまり、falseになったものが抽出したいコメント対象になります。)
※userCommentIdというのは、クチコミに関してのユニークなIDです。

②EXISTS句を使った実装

public List<UserCommentBean> getNewComment() {
        return getResult(
                "SELECT * "+
                /*対象のコメントを抽出する(期間やユーザーの属性などの条件がある)*/
                ") AS w " +
                "WHERE NOT EXISTS (SELECT t.user_comment_id from t_user_comment_voting t " +
　　　　　　　　　"WHERE t.user_comment_id = w.user_comment_id AND t.user_id = '任意のuserId' ) ",
                UserCommentBean.class);
    }

この例では、対象となるコメントを抜き出す時に、サブクエリでNOT EXISTSを使っています。そうすると、得られる結果は既に任意のユーザーのlikeが除外されたものになります。

EXISTS句を使うことのメリット

上記の例だと、わざわざ①のメソッドのような判定を入れる必要がなくなります。 実行効率の観点でも、また、COUNT関数を使うよりメリットがあります。EXISTS句を利用した場合、 1行でも該当の行が見つかった場合にtrueを返すのでそこでスキャンが終わります。 COUNTの場合、1行見つかった後も検索が走るので、existsに比べて処理が遅くなるようです。

さらに、別の場面でも使う機会があったので、参考までに記載しておきます。
もぐナビで配信しているニュースで、スイーツのタグ(カテゴリを指す)がついたものだけを取得する。

SELECT vbi.title
FROM v_blog_item vbi
WHERE vbi.del_kbn = '01'
AND EXISTS (
SELECT vbit.guid
FROM v_blog_item_tag vbit
WHERE vbit.guid = vbi.guid
AND vbit.tag = 'スイーツ'
) 
ORDER BY vbi.pub_date DESC
LIMIT 10;

---------------------------------------------------------------------------------------------------
結果
スイーツランキング発表！アイス、チョコ、プリンなどお気に入りスイーツの1位とは？
今週のファミリーマート新商品6選☆クリーミーな物からヘルシーな物までご紹介！
今週のローソン新商品6選！桜スイーツからチョコたっぷりスイーツが登場♪
今週のセブン新商品6選！和洋スイーツから菓子パンなど♪
【最強のおやつ】『まるごとバナナ』がさらに美味しくなってる！
今週のローソン新商品6選！チョコづくしのバレンタイン週間♪
ブルボン「ルマンドアイス抹茶」ほろ苦さにホワイトチョコのミルク感♪
ファミマ「たべる牧場チョコ」新発売！優しい甘さの濃厚テイスト♪
コメダ×ゴディバ「シロノワール」など新発売！カカオ香る贅沢なチョコソフトに♪
ロッテ「爽 抹茶フロマージュ」新発売！ひと口食べればリフレッシュ♪

まとめ

以上、簡単ではありますが知っていると使える場面があるし、EXISTSにとどまらずサブクエリを さくさく書いていけると、ほしいデータがすぐ手に入るので今後も勉強を続けていきたいと思いました。

ImageMagickを使った画像縮小について

以前ディスク容量逼迫に伴に、jpegtranを使った画像最適化の記事を紹介しましたが、さらにディスクの整理が必要になり、今回はImageMagickを使った画像縮小方法について紹介します。

eatsmart.hatenablog.com

ImageMagickとは

ImageMagickは画像を操作したり表示したりするためのソフトウェアです。
今回は、Linux上にImageMagickをインストールして、シェルスクリプトによるコマンド操作で画像の縮小を行う事にしました。
インストールは非常に簡単で、ubuntuの場合apt-getコマンドでインストールが行えます。

sudo apt-get -y install imagemagick

ImageMagickによる画像の縮小

ImageMagickを利用して、画像のサイズ変更を行うにはconvertコマンドを利用します。
基本的なコマンド操作は下記の通りです。

convert -resize (width)x(height) (変換前の画像名) (変換後の画像名)

また、今回は元画像のアスペクト比を維持したまま指定サイズに満たない場合縮小を行わない為、下記オプションを利用しました。

convert -resize (width)x(height)> (変換前の画像名) (変換後の画像名)
例
convert -resize 640x640> src.jpg dst.jpg

ちなみに、「convert -resize 640x640< src.jpg dst.jpg」とした場合、src.jpgの画像サイズが640x640より小さい場合のみ指定サイズへの変換が実行されます。
その他オプションで、縦横比を無視したりパーセンテージで拡大・縮小が行え非常に便利です。

シェルスクリプトによる画像縮小処理について

基本的な操作は、jpegtranの画像最適化で作成したスクリプトと同様ですが、今回はフォルダー毎に縮小する画像サイズを変更したい為、ターゲットのフォルダーと画像サイズをパラメータ化してみました。

image_resize.sh

#!/bin/bash

while getopts d:hs: OPT
do
  case $OPT in
    \?) OPT_ERROR=1; break;;
    d) PATH="$OPTARG";;
    s) SIZE="$OPTARG";;
    h) HELP=true;;
  esac
done

shift $(( $OPTIND - 1 ))

count=0

for TARGET in `/usr/bin/find ${PATH} \( -name \*.jpeg -o -name \*.JPEG -o -name \*.JPG -o -name \*.jpg \) -and -daystart -mtime +3 | /usr/bin/head -n 500`

do
    echo '####target size is '$SIZE
    echo '####target list is '$TARGET
    /usr/bin/convert -resize $SIZE'x'$SIZE'>' "${TARGET}" "${TARGET}"

    echo $count
    count=$((count+1))
    if [ $((count%1)) -eq 0 ]; then
       echo 'sleep'
       /bin/sleep 0.01
    fi
done

また、上記シェルスクリプトの使い方は以下の通りです。

image_resize.sh -d /tmp -s 640
※tmpフォルダー配下にあるjpeg画像を再帰的にアスペクト比を維持したまま640ピクセルに縮小する

今回は、トータル170G近くあった画像を、130G位まで減らすことが出来ました。
ImageMagickは画像の拡大縮小以外にも、画像形式・フォーマットの変換、画像の回転/反転等いろんな操作が行えるのでよかったら活用してみて下さい。

データベースサーバのディスク枯渇への対処

2018年3月にデータセンターからさくらインターネットへ移行して、まもなく２年が経ちます。 移行に際しての計画から実施は下記に書かれています。

eatsmart.hatenablog.com

クラウド・専用サーバを中心にVPSも併用していますが、データベースサーバに利用している専用サーバのディスク残量が時間とサービス成長に伴い減少してきました。 そして今週に入り、とうとうZabbixからアラートが飛んでくるようになりました。

Zabbixのアラートのしきい値は20%に指定しています。 専用サーバのディスク容量は400GBなので20%を切ってすぐに影響が出るわけではありませんが、以前から行いたかった利用していないインデックスの削除を行いたいと思います。

利用していないインデックスを探す

インデックスはテーブル設計時や機能追加時に作成すると思います。 作成時は必要だったものが後の改修等で利用しなくなることもあります。 PostgreSQLで利用しないインデックスを、稼働統計情報から以下のクエリで抽出しました。

SELECT 
    'DROP INDEX IF EXISTS ' || t1.relname || ';' as drop_index_query,
    'VACUUM ANALYZE ' || t2.relname || ';' as vacuum_query,
    t1.relname,
    t1.relfilenode,
    to_char((t1.relpages::int8 * 8192), 'fm999,999,999,999') as display_bytes,
    (t1.relpages::int8 * 8192) as bytes,
    t2.idx_scan,
    t2.idx_tup_read,
    t2.idx_tup_fetch
FROM pg_class t1, pg_stat_user_indexes t2
WHERE t1.relname = t2.indexrelname
and t1.relname like 'idx_%'
AND t2.idx_scan < 10
ORDER BY BYTES DESC

イートスマートでは、インデックス名を"idx_"で始めることにしているので、名前で絞り込みました。 idx_scanがインデックススキャンの実施回数となります。この数値が0のものは未使用となるため、削除する対象となります。

今回問題となったデータベースからは、合計約16GBのインデックスを削除することが出来ました。 ディスク容量の削減だけではなく、無駄なインデックスを作成する処理も無くすことが出来ました。

ランキング作成時に使ったSQL手法

先日、弊社サービスのもぐナビで、食品ランキングのリニューアルを行いました。

prtimes.jp

今回は、ランキング構築等で使用したいくつかのSQL手法を共有したいと思います。 ちなみに、弊社で使用しているRDBMSはPostgreSQLです。

カテゴリ毎のランクの振り方

これはなんてことのない内容なのですが、今回はカテゴリごとにランキングを作っているので、ランクもカテゴリごとに振っています。 一括でまとめてランクを出すために、ランク関数(RANK)と集約関数(OVER)を使って出しました。

例えば、カテゴリ(大カテゴリ:LARGE_CATEGORY、中カテゴリ:MIDDLE_CATEGORY)ごとに、スコア(SCORE)の高い順に並べてランキングを作る場合は、

SELECT
    LARGE_CATEGORY,
    MIDDLE_CATEGORY,
    NAME,
    RANK() OVER (
        PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ORDER BY SCORE DESC
    ) AS RANKING
FROM SCORE_TABLE

というSQLを組んで、

INSERT INTO RANKING_TABLE
SELECT ・・・ FROM SCORE_TABLE

みたいな感じで、順位を振って一気にランキングテーブルにデータを作成しています。

上位3位までの対照表を作る

もぐナビのランキングでは、「食べたいランキング」と「オススメランキング」の2種類があり、それぞれ食べたい気持ちの期待度と食べた感想の評価をランキングしています。

今回は、カテゴリ毎に上位3位まで抽出し、左に食べたいランキング、右にオススメランキングを並べて対比してみようという事になりました。

大カテ	中カテ	順位	食べたい	おすすめ
お菓子	チョコ	1位	ダース	ポッキー
お菓子	チョコ	2位	パイの実	レーズンクランチ
お菓子	チョコ	3位	チェリーブランデー	ガーナ
お菓子	ガム	1位	Fit's デカビタC	キシリトール
お菓子	ガム	2位	Fit's Crispop	キシリッシュハイパークール
お菓子	ガム	3位	マイニチケア	キシリッシュライムクール

普通に考えると、カテゴリのマスタ表を主表にして、食べたいランキング表とオススメランキング表をJOINすれば良いという事になります。

ただし、食べたいランキングもおすすめランキングも、カテゴリが全て揃っているとは限らないので、LEFT OUTER JOINにする必要があります。また、順位(1位〜3位)も全て揃っているとは限らないので、順位の軸となる表も必要となります。

今回は、順位の軸となる表をGENERATE_SERIESを使用して

SELECT
    CAT.LARGE_CATEGORY_NAME,
    CAT.MIDDLE_CATEGORY_NAME,
    RANKING.NO,
    TABETAI.NAME,
    OSUSUME.NAME
FROM CATEGORY_TABLE CAT
INNER JOIN GENERATE_SERIES(1,3) AS RANKING(NO)
    -- 1から3までの順位をRANKING表のNO列として作る
ON 1=1    -- 結合しないのでダミー条件を入れる
LEFT OUTER JOIN (
    SELECT * 
    FROM TABETAI_RANKING_TABLE
    WHERE RANKING <= 3
) TABETAI
ON CAT.LARGE_CATEGORY = TABETAI.LARGE_CATEGORY
AND CAT.MIDDLE_CATEGORY = TABETAI.MIDDLE_CATEGORY
LEFT OUTER JOIN (
    SELECT * 
    FROM OSUSUME_RANKING_TABLE
    WHERE RANKING <= 3
) OSUSUME
ON CAT.LARGE_CATEGORY = OSUSUME.LARGE_CATEGORY
AND CAT.MIDDLE_CATEGORY = OSUSUME.MIDDLE_CATEGORY
;

という感じで実現しました。

その他、小ネタ

ランキングのスコア算出時などに、偏差値やパーセンタイル値を使用しています。 このあたりは、最新のPostgreSQLであれば標準の関数で算出できるのですが、弊社で使用しているバージョンでは関数が使えなかったので、SQLを使って算出しました。

偏差値の算出

偏差値は平均値と標準偏差を使用して算出します。 具体的には、得点(POINT)と平均値の差を標準偏差で割ったものを10倍し、中心を50にするために50を足すことで算出できます。

先ほどのテーブルでSQL実装をすると

SELECT
    LARGE_CATEGORY,
    MIDDLE_CATEGORY,
    NAME,
    (((POINT-POINT_AVG)/POINT_STDDEV * 10) + 50) AS DEV_VALUE
FROM (
    SELECT
        LARGE_CATEGORY,
        MIDDLE_CATEGORY,
        NAME,
        POINT,
        STDDEV(POINT) OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ) AS POINT_STDDEV,
        AVG(POINT) OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ) AS POINT_AVG
    FROM SCORE_TABLE
) S

という感じです。

パーセンタイル値の算出

パーセンタイル値とは、データを小さい順に並べた時に、全体のN%目に該当する値のことです。 全体の90%が入る境目の値を90パーセンタイル値と呼びます。

ランキング算出時の特異値などを平準化するための足切りなどに使います。

算出するためには、値の小さい順に順位をつけて全体の件数で%を算出すれば良いので、

SELECT
    LARGE_CATEGORY,
    MIDDLE_CATEGORY,
    POINT,
    PERCENTILE
FROM (
    SELECT
        LARGE_CATEGORY,
        MIDDLE_CATEGORY,
        POINT,
        RANK() OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
            ORDER BY POINT
        )*100 / COUNT(*) OVER (
            PARTITION BY LARGE_CATEGORY,MIDDLE_CATEGORY
        ) AS PERCENTILE
    FROM SCORE_TABLE
) S

のようになります。

SQLって工夫すると色々できるので、本当に楽しいですね！

今週は以上です。

CSVのエクスポート処理とSQL

今回は、クスパのオンライン決済のプロジェクトの実装で学んだことについてまとめていきます。 テーマは、CSVのエクスポートと出力内容を取得するSQLの内容を振り返りです。

実現したい処理の流れ

簡単に概要ですが、下記のような仕様を実現します。
オンライン決済を利用した先生の振込情報を抽出→期間、振込日、支払状態によってソート→CSVの出力

CSVの出力情報を追加する関するアクション

public class MonthlyPaymentCsvOutputAction {
    protected void output(T form) throws ServerException {
        String fromDate = ((MonthlyPaymentCsvBean) form).getFromDate();
        String toDate = ((MonthlyPaymentCsvBean) form).getToDate();

        //formで入力された情報(日付、振込日、支払状態)を元に、SQLを発行し対象となる振込情報のデータを取得します。
        List<MonthlyPaymentCsvBean> list = CsvUtil.※getMonthlyPaymentCsv(((MonthlyPaymentCsvBean) form).getTransferDate(), ((MonthlyPaymentCsvBean) form).getRevenueStatusKbn(),fromDate,toDate);
        //StringBuilderをインスタンス化し、出力する内容を文字列で追加していく。
        StringBuilder sb = new StringBuilder();
        //見出しとなる情報を追加。
        sb.append("抽出期間　"+ fromDate.substring(0,4) + "/" + fromDate.substring(4,6) +"～" + toDate.substring(0,4) + "/" + toDate.substring(4,6)  ).append("\n");
        sb.append("運営者ID,先生名,先生売上月,銀行名,先生振込月日,売上金額,クスパ決済利用料,振込対象金額,状態" ).append("\n");
        //listで取得した内容をforループで回し、1件毎のデータsbに追加。
        for (MonthlyPaymentCsvBean bean : list) {
            sb.append(bean.getOrganizerId()).append(",");
            sb.append(bean.getTeacherName()).append(",");
            sb.append(bean.getGinkoName()).append(",");
            sb.append(bean.getTransferDate()).append(",");
            省略
        }
        try {
            //文字コードを引数に指定して、バイト形式に変換する。
            buffer = sb.toString().getBytes("windows-31j");
            //文字コードのエンコードがサポートされていない場合に例外をスロー
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
    }
}

この後、CSVを出力するクラスで呼び出し、ファイルの書き込みが行われ、CSVファイルが 作成される流れになります。 上記でapendしていく処理については、下記の書き方も可能です。

sb.insert(sb.length(),bean.getRevenueAmount()).append(",");

フィードバックを受けて学んだこと

出力条件の指定で、先生の状態によって出力内容を変えるということができていなかったので、 まとめたいと思います。以下のように日付の他にradioボタンの形式で状態(振込の状態を指す)によって、 出力内容を変えることが可能です。

<tr>
    <th>状態</th>
    <td>
        <input type="radio" name="revenueStatusKbn" value="03" checked="checked">未支払い
        <input type="radio" name="revenueStatusKbn" value="01">支払済
        <input type="radio" name="revenueStatusKbn" value="02">未確定
                //全てのvalue属性を空で扱う
        <input type="radio" name="revenueStatusKbn" value="">全て
    </td>
</tr>

ここで、「全て」を選択した場合にSQLで対象のデータを取得するためには、以下のように書くことで 「全て」の条件に合致するデータを拾うことができます。

出力対象のデータを取得するSQLを一部抜粋(※getMonthlyPaymentCsv()の中で利用)

 SELECT
     CSV出力に必要なカラムをviewから取り出す
    FROM
    view_table　v
    WHERE
    (v.revenue_status_kbn = /*revenueStatusKbn*/
        OR CAST(/*revenueStatusKbn*/ AS CHARACTER) IS NULL
        OR CAST(/*revenueStatusKbn*/ AS CHARACTER) = ''
    )
)

フォームで渡されてきた値をNULL又は空文字としてキャストすることで、対応できます。 もしくは、「全て」のinputのvalue属性を"00"としてあげて、WHERE句を以下に変更することで目的のデータが取り出せます。

WHERE
(v.revenue_status_kbn = /*revenueStatusKbn*/
        OR /*revenueStatusKbn*/ = '00'
    )

まとめ

以上となりますが、CSVの出力の一連の流れととその過程で学んだSQLの記法について 振り返ってみました。ファイル出力の処理だけでなく、バリデーション、エラー処理、 インポートの処理についても触れることができので、実装の材料として良かったと感じました。