月別アーカイブ: 2020年7月

postgresで履歴テーブルから１時間毎の集計をSQLで取得する

投稿日時: 投稿者:

　metabaseでpostgresデータを可視化するようにした所、履歴データが格納されているテーブルから１時間毎の集計を取りたいって事で、SQL1発で出来るんかいなと試行錯誤。

　今回の履歴テーブルはtimestamp型で開始時間と終了時間がセットされていて、誰が何をいつからいつまで実施していたかが記録されている、ような形になっています。これを１時間単位でその時間帯にどのくらい実施していたかをサマリたいという内容です。

具体的にはこんな感じでデータがあります。

selet * from xx_history ;

id, start_time, end_time, user_id, task_id
10, 2020-07-27 16:15:23, 2020-07-27 18:17:43, 432, 2
11, 2020-07-27 18:43:54, 2020-07-27 19:02:04, 154, 4
12, 2020-07-27 19:14:52, 2020-07-27 19:45:21, 432, 5
13, 2020-07-27 19:02:15, 2020-07-27 19:42:53, 154, 4

　これを下記のように１時間毎に時間を合計するような形で抽出したいという事になります。

時間範囲（XX時台）, 合計時間
2020-07-27 16:00, 00:44:37
2020-07-27 17:00, 01:00:00
2020-07-27 18:00, 00:33:49
2020-07-27 19:00, 01:13:11

　履歴テーブルの開始時間と終了時間に対して、その時間帯が該当するのは、下記４つのパターンがあり、それぞれ時間の取り方が変わってくるのも面倒なトコロです。

・開始時間と終了時間がその時間範囲内
　→　開始時間から終了時間までの時間
・開始時間が時間範囲より過去だが、終了時間が時間範囲内
　→　その時間範囲の開始時刻から終了時間までの時間
・開始時間が時間範囲内だが、終了時間は時間範囲より未来
　→　開始時間からその時間範囲の終了時刻までの時間
・開始時間は時間範囲より過去で、終了時間が時間範囲より未来
　→　1時間固定

　調べているとコチラを発見、postgresにはgenerate_seriesという便利なものがあるようです。

　generate_seriesで１時間毎のレコードを一定期間分生成出来るようなので、履歴テーブルとJoinしてSQLを作ります。

select time,
sum(case when xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - xx_history.start_time
     when xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours' then xx_history.end_time - time
     when xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - xx_history.start_time
     when xx_history.start_time < time and xx_history.end_time > time + interval '1 hours' then time + interval '1 hours' - time
	 ELSE time - time END)
from generate_series( date_trunc('day', current_timestamp) - interval '2 days' , date_trunc('day', current_timestamp), '1 hours') as time
left join xx_history 
   on (xx_history.start_time >= time and xx_history.end_time <= time + interval '1 hours')
      or (xx_history.start_time < time and xx_history.end_time >= time and xx_history.end_time <= time + interval '1 hours')
      or (xx_history.start_time >= time and xx_history.start_time <= time + interval '1 hours' and xx_history.end_time > time + interval '1 hours')
      or (xx_history.start_time < time and xx_history.end_time > time + interval '1 hours')
group by time
order by time

　whereとCaseの条件は一緒なので、Whereの方はもっとコストをかけない条件に見直しした方がよいですね。
　実際はもっと複雑な形になるのですが、とりあえずSQL1発で出来そうな感じです。

「Twilio」で電話をかけてみた

投稿日時: 投稿者:

自動で電話をかけたいな。
という話で、Twilioをトライアルしてみました。
要件としては架電して相手に日本語で要件を連絡、通話できたかを判定して・・・というところです。

Twilioにアカウントを作成し、ダッシュボードで電話番号を取得します。
DOCを参照しながら、とりあえず試すだけなので、Javaで適当に書いていきます。
基本的なところは、下記を参考にさせて頂きました。

[blogcard url=”https://qiita.com/mosin_nozomi/items/d30811022f1a19e620e0″]

SMSの発信や電話をかけるところまでは特に問題ありませんでしたが、上記サイトの内容とは現時点で変わっているのか、なかなか日本語を喋ってくれません。
あと、トライアル着信の「アカウントをアップグレードしてね」文句の後に何か押さなくてはいけないというのも暫くして気が付きました。。

どうしたら日本語を喋ってくれるのかと、ドキュメントを見ながらaliceとかを試してみたりしましたが、何かゴニョゴニョ言ってるけど何を言っているのか不明。。
結果的にダッシュボード左メニューのサービス→Programmable Voice→TwiML→テキスト音声変換のText-to-Speechという所に日本語はMizukiだと書いてあったので、やってみるとやっと喋ってくれました。ありがとうMizukiさん。

あと、TwiMLをインターネット上のURLからPOSTで取得できるようにしておく、という謎の制約もかなり厄介だったのですが、こちらも結果的に下記のようにすると必須では無かったようです。

import com.twilio.Twilio;
import com.twilio.rest.api.v2010.account.Call;
import com.twilio.type.PhoneNumber;
import com.twilio.type.Twiml;

public class Example {
    public static final String ACCOUNT_SID = "ACXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX";
    public static final String AUTH_TOKEN = "your_auth_token";

    public static void main(String[] args) throws Exception {
        Twilio.init(ACCOUNT_SID, AUTH_TOKEN);
        String callText = "お疲れ様です。了解頂けましたら、了解の旨を応答頂けますでしょうか？";
        String TwiMLstr = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><Response><Say language=\"ja-JP\" voice=\"Mizuki\">"
                + callText + "</Say><Pause length=\"10\"/><Say language=\"ja-JP\" voice=\"Mizuki\">それでは宜しくお願いします。</Say></Response>";
        Twiml TwiML = new Twiml(TwiMLstr);

        Call call = Call.creator(new PhoneNumber("+81登録した電話番号"), new PhoneNumber("+取得した電話番号"), TwiML).create();

        String id = call.getSid();
        for(int i = 0 ; i < 10 ; i++){
            Thread.sleep(1000*5);
            call = Call.fetcher(id).fetch();
            if(call.getStatus() == Call.Status.RINGING ||
                call.getStatus() == Call.Status.IN_PROGRESS ||
                call.getStatus() == Call.Status.QUEUED){
                continue;
            }
            else if(call.getStatus() == Call.Status.COMPLETED){
                //着信OK
                System.out.println("Call Completed " + call.getAnsweredBy());
            }
            else{
                break;
            }
        }
        System.out.println(call.toString());
    }
}

とりあえず要件は満たせそうです。
通話判定はもっとケースが必要ですし、判定待ちをもっとましな形にしたいところですが、トライアルなのでここまで。

ランニングコストがOKになって実際に導入すると決まったら、もうちょっと調べないとですね。

カテゴリー: Java