Make組ブログ

Python、Webサービスや製品開発、ライブラリー開発についてhirokikyが書きます

SQLAlchemyのSQL表現言語で集計する

SQLAlchemyのSQL表現言語で集計する

前回の Djangoで売上を集計/集約処理する に続いて、また集計します。

今回はDjango(のORM)ではなく SQLAlchemy を使います。バージョンは0.8。 ただしORMとしてではなく、SQLAlchemyのSQL表現言語(SQLExpression)のみ使います。 (私はSQLAlchemyのド素人で、ORMとして使ったことがないです。ただ、SQLAlchemyの SQL表現言語が素晴らしいなーと思ったので、試してみました)

SQLExpressionのチュートリアルも参考にしてください:

前回同様ユースケースにあわせて、集計をしてみます。

今回も:

  • 売上合計金額/件数の算出
  • 円グラフの算出
  • ランキングの算出
  • 折れ線グラフの算出

をやってみます。

さて、今回も考えるのはお人形屋さんです。このお人形屋さんの売上情報、商品の情報 などをもとに集計処理を行なって行きましょう。

想定するデータ構造

はじめにテーブルから見ていきましょう。

テーブルは3つで、売上情報、商品と商品のカテゴリーです。 以下のようにテーブル定義を書きました:

categories = Table('categories', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
)
items = Table('items', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('price', Integer),
    Column('category_id', None, ForeignKey('categories.id')),
)
histories = Table('histories', metadata,
    Column('id', Integer, primary_key=True),
    Column('item_id', None, ForeignKey('items.id')),
    Column('sold_datetime', DateTime)
)
  • histories: 売上情報/履歴。商品へのFK(item_id)と、その商品が売れた日時 (sold_datetime)を持っています
  • items: 商品。商品名(name)、価格(price)、カテゴリーへのFK(category_id)を持って います。
  • categories: カテゴリー。カテゴリー名を持っています。

売上合計金額/件数の算出

早速集計といきましょう。

jan = (datetime.datetime(2012, 1, 1), datetime.datetime(2012, 2, 1))

# 2012年1月の売上件数
select([func.count()],
       histories.c.sold_datetime.between(*jan))

# 同期間売上金額
select([func.sum(items.c.price)],
       histories.c.sold_datetime.between(*jan) & \
       (items.c.id == histories.c.item_id))

先に jan に、betweenに渡す引数を作っています。

円グラフの算出

円グラフ、GROUP BYしてSUMですね。 せっかくカテゴリーというテーブルを設けたので、カテゴリーごとの売上金額を求め ましょう。「売上の6割はウィッグなんだねぇ」とかが分かるわけです。 (そうなんだ。すごいね)

# 2012年1月のカテゴリーごとの売上金額
select([categories.c.name, func.sum(items.c.price)],
       histories.c.sold_datetime.between(*jan) & \
       (items.c.category_id == categories.c.id) & \
       (items.c.id == histories.c.item_id)).\
       group_by(categories.c.id)

ランキングの算出

商品ごとのランキングを算出します。 商品でGROUP BYしてSUMとって、それでORDER BYですね。 これで1番売上をあげている商品が分かります。

# 2012年1月の商品ごとの売上金額のランキング
select([items.c.name, func.sum(items.c.price).label('total_price')],
       histories.c.sold_datetime.between(*jan) & \
       (items.c.id == histories.c.item_id)).\
       group_by(items.c.id).\
       order_by('total_price'),

.label() で商品ごとの売上金額に名前つけて、それを .order_by() で指定して います。 商品が増えてきたら .limit(100) などを追加するのも良いかもしれません。

折れ線グラフの算出

折れ線グラフというのは横軸に日時、縦軸に売上金額(もしくは件数)をとったものを 考えます。日付はある単位ごとにまとめたものになりますね。例えば日毎の売上、月毎の 売上などです。

# 全期間の日次売上金額
select([func.date(histories.c.sold_datetime).label('sold_date'),
       func.sum(items.c.price)]).\
       group_by('sold_date'),

# 全期間の月次売上金額
select([func.strftime('%Y-%m', histories.c.sold_datetime).label('sold_date'),
       func.sum(items.c.price)]).\
       group_by('sold_date')

前回 では なかなか苦戦した覚えありますが、そうでもない感じですね。

売上がない日/月はそもそも結果にでないのでアプリ側で補完するなり、もっと良い方法 を考えるなりしてください。

おわりに

けっこう分かりやすいし、書きやすいですね。

今回書いたものはGistにあげています:

面倒臭かったのでベタベタに書いていますが、まぁいいでしょう。

ただ1点、後々「いいなー」と思った書き方:

import sqlalchemy as sa

sa.create_engine(...)

これのように sqlalchemy そのままimportしておいて、毎回書いてやることですね。

あとはまぁ、SQL表現言語を、SQLAlchemyのORMと併せて使ってやるのも面白いようです。 なので気が向いたらやってみようと思います。