March 07, 2013

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

今回は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)

折れ線グラフの算出

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

# 全期間の日次売上金額
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にあげています:

https://gist.github.com/hirokiky/5108354

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

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

import sqlalchemy as sa

sa.create_engine(...)

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

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

Posted by Hiroki KIYOHARA

Tags: sqlalchemy, aggregation

Djangoで売上を集計/集約処理する

集計/集約します。 (この記事ではaggregationを「集計」と訳します。集約、よりもヒットしやすそうだったから)

Django 1.4 日本語ドキュメント(暫定) ではこちらにあります。

集約(Aggregation) - Django 1.4 documentation

ちなみにDjangoでaggregationの機能が追加されたのは1.1からですね。 (1.1 リリースノート)。

正直、上記のドキュメント読めば集計処理の大体は網羅できると思います。ですが今回はユースケースにあわせて、集計の機能を紹介します。

今回は

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

をやってみます。これくらいの項目があれば十分ですかね。お店の売れ行きや人気商品は掴めると思います。

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

想定するデータ構造

はじめにテーブル/Djangoのモデルからみていきましょう。

必要なモデルは2つで、お店に並ぶ商品と売上情報です。 models.pyは以下のようになると思います。

from django.db import models
from django.contrib.auth.models import User

class Item(models.Model):
    """お店の商品
    """
    name = models.CharField(u"商品名", max_length=255)
    price = models.PositiveIntegerField(u"価格")


class SalesHistory(models.Model):
    """売上情報
    """
    item = models.ForeignKey('Item')
    user = models.ForeignKey(User)
    sold_datetime = models.DateTimeField(u"販売日時")

こんなところかと。では早速集計をしていきましょう。

Note

今回は省きますが、Itemには他にも商品のカテゴリや販売元などのフィールドが追加できると考えられますね。例えば販売元ごとに折れ線グラフを集計してやることで「今どこの販売元の商品が注目されているか」をみることができちゃうと思います。

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

簡単なところから、売上合計金額と件数です。

集計の対象期間は「今月」として話をすすめます。つまり今月の初日から昨日までが対象です。まずはその期間内のSalesHistoryを取得してみましょう。

>>> import datetime
>>> from dateutil.relativedelta import relativedelta

>>> today = datetime.date.today()
>>> first_of_thismonth = today + relativedelta(day=1)

>>> from dollshop.models import SalesHistory
>>> SalesHistory.objects.filter(sold_datetime__range=(first_of_thismonth, today))

こんなところと思います。当日のdate、relativedeltaを使って月の初日(first_of_thismonth)をとりました。最後の行でSalesHistoryをfilterしてます。

>>> sales_of_thismonth = SalesHistory.objects.filter(sold_datetime__range=(first_of_thismonth, today))

>>> # 売上件数
>>> sales_of_thismonth.count()

>>> # 売上金額
>>> from django.db.models import Sum
>>> sales_of_thismonth.aggregate(Sum('item__price'))

簡単ですね。 aggregateを使います。lookupはSum。

円グラフの算出

>>> # 商品ごと
>>> sales_of_thismonth.values('item').annotate(total_price=Sum('item__price'))

>>> # ユーザごと
>>> sales_of_thismonth.values('user').annotate(total_price=Sum('item__price'))

商品ごとなりユーザごとの合計金額を求めて、アプリ側で各々の金額を全部の合計金額で割れば比率はでますね。まぁ大抵のJSライブラリはそのまま値で渡せばよしなにやってくれます。

この例では「商品ごと」というなんともビミョーな円グラフですが、応用すれば「商品のカテゴリごと」などもできますね。

折れ線グラフの算出

折れ線グラフというのは横軸に日時、縦軸に売上金額(もしくは件数)をとったものを考えます。日付はある単位ごとにまとめたものになりますね。例えば日毎の売上、月毎の売上などです。 1年間の売上推移を見るのに日毎の集計をしちゃったら、横に365点とることになるんで非常に見難いですよね。

>>> # 日毎
>>> sales_of_thismonth.extra({'sold_date': 'strftime("%%Y%%m%%d", sold_datetime)'}).values('sold_date').annotate(total_price=Sum('item__price'))

>>> # 月毎
>>> sales_of_thismonth.extra({'sold_month': 'strftime("%%Y%%m", sold_datetime)'}).values('sold_month').annotate(total_price=Sum('item__price'))

extraで、values-annotateのグループ化に使う値を作ってあげてます。日毎の集計ならstrftimeじゃなくてdateでもいけそう。

おわりに

意外とあっさりできましたが、理解するのはちょっとややこしいかも。 annotateの前にvaluesをおいてグループ化してやってるわけですが、SQL脳でみると.group_byなどのメソッドが欲しいなというところ。

これについては何度も議題にあがってるようです。

https://groups.google.com/d/topic/django-developers/dUlwp9z9gGI/discussion

よりORMらしい方法(values-annotateのことかな)が採択されたとのこと。詳細はチケット3566 のようで、このチケットは「ORMで集計できるようにしよう」という提案のようですね。さっと見てみたところ、もともとの提案ではgroup_byなどであったようですが、まぁ時間のあるときにでもじっくり見ときましょ。

Posted by Hiroki KIYOHARA

Tags: django, orm

hirokiky's blog

Hiroki KIYOHARA's Weblog.

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

想定するデータ構造

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

円グラフの算出

ランキングの算出

折れ線グラフの算出

おわりに

Djangoで売上を集計/集約処理する

想定するデータ構造

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

円グラフの算出

ランキングの算出

折れ線グラフの算出

おわりに