血液検査結果の数値改善をサポートする「その1」

こんにちは、デザイン・システム室の岩田です。


普段は「ミールタイム」の開発・改修を担当していることが多いです。
特に社内でお電話に出ている栄養士・管理栄養士のメンバーが使うシステムを担当しています。フロントエンドからデータベース、サーバー運用と色々多岐に渡りながら、日々を過ごしています。

お客様の数値改善をサポートするのは、実際に電話に出る栄養士・管理栄養士達だけではありません。エンジニア的にどのようにサポートしていくのかということについてお伝えしたいと思います。

この内容の記事を書こうと思った背景

初めに「ミールタイム」の概要を簡単にご紹介させていただきます。

「ミールタイム」とは、糖尿病・脂質異常症・高血圧・痛風・メタボ・腎臓病などの疾病に悩む方向けに開発された健康食、およびこちらをご提供するサービスです。
オペレーターは、全て当社社員の管理栄養士・栄養士が行っています。
電話でご注文をいただく際には、食事制限の内容や血液検査の結果などをお伺いし、
その上で管理栄養士・栄養士の専門知識を生かして、お客様一人ひとりに合わせたメニューをご提案し、血液検査数値の改善に努めております。

この中で、一人でも多くのお客様の血液検査項目を正常値の範囲になるようにアプローチできるように管理栄養士・栄養士の方をサポートしていくのが私の役割になります。

日々サービス運営するには、データが欠かせません。今日は、「必要なデータを必要な時に抽出する」という要望に、私がどのように取り組んでいくのかをお話しします。

巨大なデータを取り扱う上で想定されること

実務で取り扱うデータは単一のテーブルや少量のデータや簡易な条件分岐で対応できるとは限らず、考慮しないといけない複数の要素を抱えています。下記に想定される事項をまとめてみました。

・いくつにも渡ってテーブルが分かれているため、その都度結合が必要になる

必要なデータがいくつものテーブルに渡っている場合にはそのデータを取得するまでにいくつもの結合を必要とする場合が考えられます。

一例として日本のどこに住んでいるか(住所)を表すテーブルを想定してみましょう。

都道府県、市区町村、何丁目、何番地、何号、建物名、階数などの各テーブルごとの情報が分けて格納されているとすると、地球の中で日本のどこに住んでいるかを正確に求めるには、
47「都道府県の数」× 1900「市区町村のおおよその数」× 15「何丁目かのおおよその数」 × ・・・といったように各テーブルごとの数を掛け算しただけデータを取得しないと求められない可能性がありますね。

・データ量が多い

データベースの仕組み上レコードを1件ずつ参照していくので、取得したい情報がたまたまデータベースの最後に参照するものだったとするとその分だけ余分に時間がかかってしまう可能性が考えられます。

アミューズメント施設のデータで1000万人目のお客様を知りたいみたいな例であると、
999万9999人分のデータを事前に取得する必要があるかもしれません。

・whenもしくはif等による多重分岐でコードの記述の仕方が複雑になる

各12星座の方ごとにお祝いのメッセージをお送りする場合を想定してみます。すると誕生日ごとの判定を行う必要があるのでその分だけ条件分岐をさせてあげる必要があり、下記のように

case birthday
when birthday between '2021-03-21' and '2021-04-19' then #おひつじ座の場合 
when birthday between '2021-04-20' and '2021-05-20' then #おうし座の場合
...
when birthday between '2022-01-20' and '2022-02-18' then #みずがめ座の場合
when birthday between '2022-02-19' and '2022-03-20' then #うお座の場合
else
end

コードが複雑になるので、他の開発者がこの部分を読もうと思った時に読みづらくなる(可読性が下がる)可能性が生じます。さらに各when句の中で年代ごとに分けて対応するとなったら、if ~ else を繰り返し書くことになり、なおさら複雑になってしまうでしょう。

実際の業務で工夫したこと

上記想定した内容を元に、必要なデータの抽出を行っていきました。作成にあたって考慮したことを以下にまとめます。

・ActiveRecordによる抽出から生のSQLを実行する方針に変更

Ruby on Railsで標準装備されているActiveRecordの仕組みのみを利用して必要なデータを抽出することも可能です。ただし、今回はいち早くクエリの処理をすることが求められ、なおかつ複雑な条件検索がいくつも絡んでいたことから、SQLの方がパフォーマンスが出ると考えました。
ただ、常にSQLの方が優れているとは限らないです。自分が書いたコードを他の人が見た時には
Ruby on Railsでコードを書くよりも可読性が下がってしまうのでこれはケースバイケースです。

・一時テーブルの利用

「With」句の利用で、複数のテーブルにまたがる情報を一つのテーブルにまとめることができるので活用を検討しました。同様の手段として「View」の導入も検討しましたが今回は採用せずに
クエリを作成しました。

・パフォーマンスの向上

ただ、単に一時テーブルを利用して順々につなげていくだけだと、パフォーマンスを十分に考慮することはできません。「With」句で作成するテーブル一つそのものが結合に時間がかかる可能性があるからです。

そこで、集計関数を利用する部分(最小値)、havingを利用する部分を結合の処理を分割する対応を行いました。結合してから条件絞りこみ→条件を絞りこんでから結合にしました。

・処理頻度の検討

上記内容を考慮しクエリを再構築したのですが、これでもデータの規模を増やしていくと、10分、1時間、数時間・・・と時間がかなり膨れ上がってしまい、要求が満たせないということがありました。

なのでテーブルのデータ量と処理速度を考慮し、処理できるものは事前に処理して
関連データの新規作成・更新があった際に都度対応させるように方針を立てました。
(といっても、事前の処理だけでも何時間もかかるタイプの処理なので行う時間は別途考える必要があります)

さらなる血液検査結果の数値改善者の発見機能の向上に向けて

今回は、サーバー環境がオンプレミスということもありSQLを直に書いてチューニングし、どちらかといいますと力技で頑張って機能を作成しましたが、この方法に限らずビックデータを取り扱うにはもっと様々な方法があり、またより効率よくできると先輩から後にお聞きしました。

3大クラウドサービス「AWS・GCP・Azure」やデータウェアハウス用ツールを使う方法が
今回のケースでは選択肢としてあげられるかと思います。

この続きは「その2」で取り上げていこうと考えております。お楽しみください。

終わりに

社内システム開発・改修を行う際にはユーザーが近くにいるためすぐフィードバックをいただけ、ありがとうという感謝の言葉をいただけるのが会社で働く上でのモチベーションの一つとなっていたりもします。

デザイン・システム室では、現在一緒にデザイン・システム開発をしたい方を積極的に募集しております。上記内容に関心を持ってくださった方はぜひ一度お話を聞きにきてください!

募集要項およびエントリーフォーム >