1. SQLのWindowFunctionが強力

SQLについては必要なときには調べれば大抵の処理は実装できるだろうと舐めているところがありました。

私の若かかりし頃にはなかった分析関数がいつの間にか実装されていて、その機能がまた強力なのです。

そして、実際に試してみないと分析関数が何ができるのか、何が強力なのかが理解するのが難しいのが悩ましいところ。

実際に分析関数を多用する状況に追い込まれて、やっとその便利さに気づきました。

そして分析関数と同様の処理をPandasで実装するにはどうすれば良いのかと当然ながら思います。

この記事では分析関数に関する問題を一つ作成してSQLとPandasでの回答例を紹介します。

「データサイエンティスト協会スキル定義委員」の「データサイエンス100本ノック（構造化データ加工編）」を利用しています

2. レシート明細データフレーム（df_receipt）の売上金額（amount）を日付（sales_ymd）ごとに集計し、各日付のデータに対しその日付までの最小値の売上金額を計算せよ。

自作の問題です。

分析関数はTimeSeriesのデータに対して便利な機能を提供しています。

しっかりと理解するにはWindowRangeやWindowFrameを理解する必要があり難しいです。私自身も色々試して理解を深めていかなければなりません。

この問題は分析関数の一つの利用例になります。

3. SQLでの回答例

分析関数を使用します。このSQL文ではWindow Frameを良く理解する必要があります。

処理結果が直ぐに分からない場合はWindow Frameなどで検索してみてください。

answer.sql
with amount_by_ymd as(
  select
    sales_ymd,
    sum(amount) amount
  from
    receipt
  group by
    sales_ymd
)
select
  sales_ymd,
  amount,
  min(amount)
  over (order by sales_ymd)
  min_amount_ever
from
  amount_by_ymd
limit 10
;
output
 sales_ymd | amount | min_amount_ever
-----------+--------+-----------------
  20170101 |  33723 |           33723
  20170102 |  24165 |           24165
  20170103 |  27503 |           24165
  20170104 |  36165 |           24165
  20170105 |  37830 |           24165
  20170106 |  32387 |           24165
  20170107 |  23415 |           23415
  20170108 |  24737 |           23415
  20170109 |  26718 |           23415
  20170110 |  20143 |           20143
(10 rows)

4. pandasの回答例

expanding()を使用します。

answer.py
answer = (
    df_receipt
    .groupby('sales_ymd')
    .agg(
        amount=('amount', 'sum')
    )
    .assign(
        min_amount_ever=lambda x:
        x.amount.expanding().min()
    )
)
answer.head(10)
output
           amount  min_amount_ever
sales_ymd
20170101    33723          33723.0
20170102    24165          24165.0
20170103    27503          24165.0
20170104    36165          24165.0
20170105    37830          24165.0
20170106    32387          24165.0
20170107    23415          23415.0
20170108    24737          23415.0
20170109    26718          23415.0
20170110    20143          20143.0

5. まとめ

SQLは分析のためのデータ加工ツールとしては機能が不足していると舐めていましたが分析関数の強力さを思い知らされました。

分析関数を使いこなすのは難しく、まだまだ色々な応用例が数多くあります。

これからも修練を積み重ねたいと思います。