C# HttpClientでタイムアウトを設定する

C#でAPIのコールなどに使われるHttpClientクラス(System.Net.Http名前空間)でタイムアウトを設定・制御する方法です。

• HttpClientのTimeoutプロパティ(TimeSpanクラス)にタイムアウト時間を設定する（デフォルトは100秒）
• タイムアウト時にTaskCanceledExceptionがスローされるので、それをキャッチしてタイムアウト時の処理を記述する

using System;
using System.Net.Http;
using System.Threading.Tasks;

namespace ConsoleApplication
{
    public class Program
    {
        public static void Main()
        {
            GetKenikki().Wait();
        }

        private static async Task GetKenikki()
        {
            var client = new HttpClient();

            // タイムアウト時間の設定(3秒)
            client.Timeout = TimeSpan.FromMilliseconds(3000);

            try
            {
                // このブログへGETアクセス
                var response = await client.GetStringAsync("http://ohke.hateblo.jp/");
                Console.WriteLine(response);
            }
            catch (TaskCanceledException e)
            {
                // タイムアウトの場合、TaskCancelExceptionがスローされる
                Console.WriteLine(e.Message);
            }
        }
    }
}

タイムアウト時間を1msなどに設定すると、例外がスローされることを確認できます。

System.Threading.Tasks.TaskCanceledException: A task was canceled.
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
   at System.Net.Http.HttpClient.<FinishSendAsync>d__58.MoveNext()
・・・

「データ集計・分析のためのSQL入門」 まとめ

データ分析ソリューション事業を軸に展開されているALBERTの方々によって書かれた「データ集計・分析のためのSQL入門」を読みましたので、備忘録としてまとめておきます。

全5章で構成されており、集計・分析レポートの基本(Chapter 1)、SQLの基本(Chapter 2)、基本統計量や偏差値の算出(Chapter 3)、デシル分析・RFM分析(Chapter 4)、アソシエーション分析・時系列分析・アトリビューション分析(Chapter 5)といった内容となってます。

データ集計・分析のためのSQL入門

今回もDVD Rental ERモデルを使ったSQLとなっています。

PostgreSQL Sample Database

Chapter 3

尖度

正規分布と比較したときのデータ分布が尖り具合を表す指標で、正の値となるときは分布が集中している（＝尖っている）、負の値となるときは分布が分散している（＝なだらか）ことを示します。

select (
    select
      (t1.n*(t1.n + 1.0)) / ((t1.n - 1.0)*(t1.n - 2.0)*(t1.n - 3.0))
    from
      (
        select 
          count(amount) as n
        from
          payment
      ) t1
  ) * (
    select
      sum(power(payment.amount - t2.a, 4)) / (1.0 / (t2.n - 1) * sum(power(payment.amount - t2.a, 2)))
    from
      (
        select
          count(amount) as n
          , avg(amount) as a
        from
          payment
      ) t2
      , payment
    group by
      t2.n
      , t2.a
  ) - (
    select
      (3.0 * power((t3.n - 1.0), 2) / ((t3.n - 2.0) * (t3.n - 3.0)))
    from
      (
        select
          count(amount) as n
        from
          payment
      ) t3
  ) as kurtosis
;

歪度

正規分布と比較したときのデータ分布の歪み具合を表す指標で、正の値となるときは分布が正の方向に歪んでいる、負の値となるときは分布が負の方向に歪んでいることを示します。

select (
    select
      t1.n / ((t1.n - 1.0)*(t1.n - 2.0))
    from
      (
        select 
          count(amount) as n
        from
          payment
      ) t1
  ) * (
    select
      sum(power(payment.amount - t2.a, 3)) / power(1.0 / (t2.n - 1) * sum(power(payment.amount - t2.a, 2)), 1.5)
    from
      (
        select
          count(amount) as n
          , avg(amount) as a
        from
          payment
      ) t2
      , payment
    group by
      t2.n
      , t2.a
  ) as skewness
;

Chapter 4

WITH ROLLUP関数（SQL Server、MySQL）

GROUP BY句で指定した列ごとに合計を持つ行を追加されます。 以下では、最終行に男性・女性・totalの総合計値が表示されます。

select
  coalesce((cast(age10s as varchar(8)), 'total') as '年代'
  , sum(case when sex = 'Man' then 1 else null end) as '男性'
  , sum(case when sex = 'Woman' then 1 else null end) as '女性'
  , count(*) as 'total'
from
  test
group by 
  age10s
with rollup
;

PERCENT_RANKウィンドウ関数

デシル分析に使う関数で、order byで指定した値が全体の値域の中で上位何％に入るのかを計算します。 以下のSQLではcustomer_id毎の売上をランキング化しています。

select
  payment_id
  , amount
  , percent_rank() over (order by amount desc) as pct_rank
from
  payment
order by
  payment_id
;

RFM分析

「売上」という１つの指標だけではなく、Recency（より最近購入している顧客のほうが優良顧客）、Frequency（より購買頻度が高い顧客のほうが優良顧客）、Monetary（より購買金額が大きい顧客のほうが優良顧客）の３つの指標を導入することで、それぞれのグループの特性を捉えて分析できるようになります。

閾値を設けてRFMそれぞれで顧客を3段階に分類するSQLは以下のようになります。 ランクごとにcase文でラベルを付けています。

• 2007/5/1を起点として、最終訪問が1日前までならR1、2日前までならR2、それより前ならR3
• 2007/5/1までの、訪問回数が25回以上ならF1、25回未満20回以上ならF2、20回未満ならF3
• 2007/5/1までの、合計金額が120以上ならM1、120未満100以上ならM2、100未満ならM3

select
  t.customer_id
  , case
      when t.diff_date <= 1 then 'R1'
      when t.diff_date > 1 and t.diff_date <= 2 then 'R2'
      when t.diff_date > 2 then 'R3'
    end as recency_rank
  , case
      when t.frequency >= 25 then 'F1'
      when t.frequency < 25 and t.frequency >= 20 then 'F2'
      when t.frequency < 20 then 'F3'
    end as frequency_rank
  , case
      when t.total_amount >= 120 then 'M1'
      when t.total_amount < 120 and t.total_amount >= 100 then 'M2'
      when t.total_amount < 100 then 'M3'
    end as monetary_rank
from
  (
    select
      customer_id
      , date_part('day', '2007-05-01' - max(payment_date)) as diff_date
      , count(amount) as frequency
      , sum(amount) as total_amount
    from
      payment
    where
      payment_date < '2007-05-01'
    group by
      customer_id
  ) t
order by
  customer_id
;

Chapter 5

アソシエーション分析

「商品Xを購入した顧客は高確率で商品Yも購入している」といったルールを導き出して、レコメンデーションなどの施策に繋げていくのが、アソシエーション分析で、Confidence(信頼度)、Support(支持度)、Lift(リフト)の指標で定量化されます。

• 信頼度は、Aを買う場合にBも一緒に購入される割合で、商品間の関連性の度合いを表す指標
  • AとBを購入した回数 ÷ Aを購入した回数
  • 方向性があり、先の例では「Aを買う場合にBも一緒に購入される割合」と「Bを買う場合にAも一緒に購入される割合」は、一般に一致しない
• 支持度は、全購入の内、商品Aと商品Bが一緒に購入される割合で、その関連が全体の中でどの程度ボリュームがあるのかを表す指標
  • AとBを購入した回数 ÷ 全購入回数
  • たまたま1回AとBを一緒に購入された場合に、信頼度が100%になるが、支持度を使って足切りする
• リフトは、商品Aを買った人に商品Bをおすすめすることで、何もしない場合と比べて、商品Bの購買確率がどの程度上がるのかを表す割合
  • Aを購入するとBも購入する確率 ÷ 全購入回数の中でBが購入される確率
  • リフトが1より小さい場合は、おすすめしないほうが良い
• 組合せ爆発を起こすので、支持度が一定以下のアイテムについては、はじめから信頼度を計算しないことで計算量を減らすことができる(アプリオル・アルゴリズム)

以下のSQLではcustomer_id毎に、レンタルしたfilm_idをセルフジョインして、film_id間の信頼度・支持度・リフトを算出しています。

select
  t1.film_id as a
  , t2.film_id as b
  , count(*) / sum(count(*)) over (partition by t1.film_id) as confidence
  , count(*) / sum(count(*)) over () as support
  , (count(*) / sum(count(*)) over (partition by t1.film_id)) / ((sum(count(*)) over (partition by t2.film_id)) / (sum(count(*)) over())) as lift
from
  (
    select
      r.customer_id
      , i.film_id
    from
      rental as r
    inner join
      inventory as i
    on
      r.inventory_id = i.inventory_id
  ) as t1
  inner join
  (
    select
      r.customer_id
      , i.film_id
      , 1 as count
    from
      rental as r
    inner join
      inventory as i
    on
      r.inventory_id = i.inventory_id
  ) as t2
  on
    t1.customer_id = t2.customer_id
where
  t1.film_id <> t2.film_id
group by
  t1.film_id
  , t2.film_id
order by
  t1.film_id
  , t2.film_id
;

まとめ

SQLでは疎かにしがちな基本的な統計値(中央値、最頻値、標準偏差、尖度、歪度など)の計算を丁寧に書き下されているので、様々な場面でデータの大まかな傾向をチェックするために使えそうです。 またRFM分析やバスケット分析など、あるあるケースを例題に説明されており、実利用でも役立つかと思います。

とはいえ、Chapter 5ではデシジョンツリーや時系列分析やなど、最大6ページに及ぶ長大なSQLが並び、さすがにそこまでやろうとするとSQLでは苦しいなあという印象でした。

「10年戦えるデータ分析入門」第1部 まとめ

最近は生データの分析などで難しめのSQLを書くことが増えてきましたので、SQLの地力を鍛えるために、クックパッド・青木さんが著者の「10年戦えるデータ分析入門」を読みました。

本書は2部構成となっており、第1部ではSQLを使ったデータ分析のテクニック、第2部では分析のためのインフラシステムの構築についてそれぞれ述べられてます。 今回は第1部で知らなかったことや「これは使いでがあるな」と思ったことを、備忘録として整理します。

10年戦えるデータ分析入門 SQLを武器にデータ活用時代を生き抜く (Informatics &IDEA)

なお、掲載するSQLの一部は、DVD Rental ERモデルを使ったものとなっています。

PostgreSQL Sample Database

第3章

ランダムサンプリング

random()で0.0以上1未満の値が得られるので、where句で使うとランダムサンプリングができます。

select
  *
from 
  payment
where
  random() < 0.001
;

第5章

date_trunc関数

date_trunc('month', request_time)で、月以降を"1日 00:00:00"で切り捨てることができるので、年ごと・月ごと・日ごとなどに集計する場合に便利です。

select 
  *
from
  payment
where
  date_trunc('month', payment_date) = timestamp '2007-02-01 00:00:00'
;

第7章

coalesce関数

nullでない最初の値を返す。nullを含むカラムの値に初期値を設定するなど、使い途が多いです。

select 
  coalesce(staff_id, 0) as staffid
  , count(*)
from
  payment
group by
  staffid
;

併売率

「ある商品を購入する場合、この商品も合わせて購入されている」割合を併売率というそうです。 セルフジョインで併売率を求めることができます。

select
  c.item_id
  , c.item_id2
  , cast(c.order_count as real) / i.order_count as confidence
from
  (
    select
      l.item_id
      , r.item_id as item_id2
      , count(distinct l.order_id) as order_count
    from
      order_details as l
      inner join order_details as r
      on l.order_id = r.order_id
        and l.item_id <> r.item_id
    where
      l.order_time between timestamp '2015-04-01 00:00:00' and timestamp '2015-04-30 23:59:59'
  ) c
  inner join
  (
    select
      item_id
      , count(distinct order_id) as order_count
    from
      order_details
    where
      order_time between timestamp '2015-04-01 00:00:00' and timestamp '2015-04-30 23:59:59'
  ) i
  on c.item_id = i.item_id
;

第8章

恥ずかしながら、ウィンドウ関数をよく知らなかったので、8章は特に勉強になりました。

スカラーサブクエリ

select節やwhere節に式の一部としてもサブクエリが使える。

-- 顧客毎の支払合計が全体の支払合計に占める割合を計算
select 
  customer_id
  , sum(amount)
  , sum(amount) / (select sum(amount) from payment)
from
  payment
group by
  customer_id
;

sumウィンドウ関数

partition byで指定された値ごとに合計を算出できます。

ウィンドウ関数全体に言えることですが、行を集約されずに(group byと異なる点)、集約関数の結果の値を得られるという利点を持っています。

-- 顧客毎の総支払額を3番目のカラムで表示
select 
  customer_id
  , amount
  , sum(amount) over (
      partition by customer_id
    ) as customer_total
from
  payment
;

rankウィンドウ関数

ウィンドウ関数の計算はwhereやhavingよりも後のため、絞り込む場合はサブクエリ化して、外側のクエリのwhereで絞る必要があります。

-- 支払総額上位3名の支払時刻・支払額の一覧を列挙する。
select
  *
from (
  select 
    customer_id
    , payment_date
    , amount
    , rank() over (
      partition by customer_id
      order by amount desc
    ) as customer_rank
  from
    payment
) tmp
where
  customer_rank <= 3
order by
  payment_date
;

累積和

sumウィンドウ関数を使って累積和を計算します。 rows between unbounded preceding and current rowとすることで、「過去の全ての行から現在の行まで」と指定するのがミソで、ウィンドウフレームと言うそうです。 n行前ならn preceding、n行後ならn followingという指定もできます。

-- スタッフ毎の売上の累積和を計算する
select
  t.sales_date
  , t.staff_id
  , t.sales_amount
  , sum(sales_amount) over (
      partition by staff_id
      order by sales_date
      rows between unbounded preceding and current row
    ) as cumulative_sales_amount
from (
  select
    date_trunc('day', payment_date) sales_date
    , staff_id
    , sum(amount) sales_amount
  from
    payment
  group by
    sales_date
    , staff_id
  ) t
;

デシル分析

デシル分析は、ある値でソート（昇順・降順）し、行を任意のn個に等分割し、上から番号を振っていく分析で、売上額に応じて顧客をランク付けするといったケースに使えます。

-- 月ごとの売上に応じて顧客を3段階のランクに分ける
select
  t.sales_month
  , t.customer_id
  , t.sales_amount
  , ntile(3) over (
      partition by sales_month
      order by sales_amount desc
    ) as customer_rank
from (
  select
    date_trunc('month', payment_date) sales_month
    , customer_id
    , sum(amount) sales_amount
  from
    payment
  group by
    sales_month
    , customer_id
  ) t
;

第9章

generate_seriesテーブル関数（PostgreSQL）

連番のみのテーブルを生成する関数です。 以下の場合では、1から4の値が入った1カラム×4行のテーブルが生成されます。

select * from generate_series(1, 4);

JSONをカラムに展開する（PostgreSQL）

「そんなことできるんだ！」と驚いたSQLの1つですが、JSON文字列が入った1つのカラムを、キー毎に複数のカラムへ展開させています。 以下のSQLではjson_paramsに{"view_seconds":36,"scroll_ratio":0.7,"click_button":"discount_campaign"}といった文字列が入っており、3つのカラムに展開しています。ポイントは3点です。

• PostgreSQLにはjson型があり、文字列からキャストできる
• json_to_recordテーブル関数で、view_seconds、scroll_ratio、click_buttonの3カラムからなる1行のテーブルを生成できる
• 最後にクロスジョインして、元のaccess_logテーブルにくっつける

select
  l.request_time
  , v.view_seconds
  , v.scroll_ratio
  , v.click_button
from
  access_log as l
  cross join json_to_record(cast(l.json_params as json))
    as v (
      view_seconds integer
      , scroll_ratio real
      , click_button text
    )
;

第10章

lagウィンドウ関数

前の行の値を取得できる関数です。 前回のアクセスからどのくらい時間が経ったか、などを算出できます。

-- 顧客毎に前回の支払が何時間前かを計算する
select
  payment_date
  , customer_id
  , date_trunc('hour',
      payment_date - lag(payment_date) over (
        partition by customer_id
        order by payment_date
      )
    ) as payment_interval
from
  payment
;

正規表現とのマッチング

今までlikeくらいしか使ってきませんでしたが、正規表現によるマッチングとCaseを組み合わせると、強力なツールになりそうです。

-- カテゴリ名でo・・・e・・・yに該当する行数をカウント（'Comedy'など）
select
  sum(case when name ~ 'o+[a-z]*e+[a-z]*y$' then 1 else 0 end)
from
  category
;

まとめ

アクセスログのセッション化やコンバージョンレートの算出など、実際の分析に近いSQLとなっているので、理解が捗りました。 おすすめの1冊です。