DjangoのORMを使って集計方法を学ぶレシピ

この技術ノートではDjangoのORMを用いたデータの集計方法を学ぶことができます。

Djangoが提供する以下のメソッドを使ってとあるECショップの商品の販売データを集計するという事例で具体的に解説していきます。

実際にデモデータの環境を準備して操作しながら学ぶことができる内容になっています。

DjangoのORMで提供される基本的なメソッドについては以下の記事で学習することができますので、こちらも確認してみてください。

1.事前準備

ORMの各メソッドの動作を確認するための環境の準備を行います。

動作確認用のDjangoプロジェクトと初期データを準備したGitリポジトリを用意してありますので、以下の手順で環境を準備しましょう。

まず、任意のディレクトリ上で以下のコマンドを実行してリポジトリをクローンします。

git clone https://github.com/sinjorjob/django-aggregate.git
cd django-aggregate

仮想環境を作成してアクティベートし、必要なモジュールをインストールします。

python -m venv env
env\scripts\activate
pip install django PyYAML

マイグレーションを実行します。

python manage.py makemigrations
python manage.py migrate

管理者ユーザを作成します。

python manage.py createsuperuser

以下のコマンドを実行して、動作確認用のデモデータをインポートします。

python manage.py loaddata --format=yaml ec_site/fixtures/sample_data.yaml

以下のコマンドを実行して開発サーバを起動したらhttp://127.0.0.1:8000/adminにアクセスして管理者ユーザでログオンします。

python manage.py runserver

以上で環境準備は完了です。

2.テーブル構成について

今回利用するテーブル構成について簡単に解説します。

まずは、ec_site\models.pyを参照してください。
以下のようなモデルが定義されています。

from django.db import models
from django.contrib.auth.models import User
class Category(models.Model):
    class Meta:
        verbose_name = 'カテゴリ'
        verbose_name_plural = "カテゴリ"
    name = models.CharField(max_length=100)
    def __str__(self):
        return self.name
class Product(models.Model):
    class Meta:
        verbose_name = '商品'
        verbose_name_plural = "商品"
    name = models.CharField(verbose_name = '製品名', max_length=150, null = False, blank=False)
    price = models.IntegerField(verbose_name = '価格')
    category = models.ForeignKey(Category, on_delete = models.PROTECT,verbose_name ="カテゴリ")
    def __str__(self):
        return self.name
class OrderItem(models.Model):
    class Meta:
        verbose_name = '注文データ'
        verbose_name_plural = '注文データ'
    user = models.ForeignKey(User,verbose_name = 'ユーザ',on_delete = models.CASCADE, null=True)
    items = models.ManyToManyField('Product', related_name='order', blank=True)
    created_date = models.DateField(auto_now_add=True)
    price = models.DecimalField(verbose_name="合計金額",max_digits=7, decimal_places=0, null=True, blank=True)
    name = models.CharField(verbose_name="氏名", max_length=50, blank=True)
    def __str__(self):
        return f'注文日: {self.created_date.strftime("%b %d %Y %I:%M %p")}'
class OrderItemDetail(models.Model):
    class Meta:
        verbose_name = '注文明細'
        verbose_name_plural = '注文明細'
    invoice = models.ForeignKey(OrderItem, on_delete=models.CASCADE)
    product = models.ForeignKey(Product,verbose_name='商品',on_delete=models.CASCADE)
    quantity = models.IntegerField(verbose_name='数量')

以下の4つのテーブルを定義しています。

通常のショップサイトでは、1回の注文で複数の商品を複数個購入することができます。
そのようなテーブル構造にするため、1つの注文データ（OrderItem)に対して複数の商品(Product)が紐づき、複数の明細テーブル（OrderItemDetail)が紐づくようなテーブル構成になっています。
また、製品(Product)に対しては１つカテゴリを割り当てる構成にしています。

adminサイト(http://127.0.0.1:8000/admin)にアクセスして、実際のサンプルデータを確認してみましょう。

商品テーブルをクリックすると、下図のように商品名の情報が登録されていることが確認できます。

注文テーブルをクリックすると、18件の注文データが登録されていることが確認できます。

また、注文テーブルをクリックすると下図のように複数の注文明細データが表示されます。

注文明細テーブルをクリックすると下図のように明細データが表示されます。

それでは、次から実際に注文データの分析を始めていきます。

3.各テーブルのレコード数をチェック

まずは、各テーブルのレコード数をチェックしてみましょう。

まず、以下のコマンドを実行してDjangoのシェルモードを起動します。

python manage.py shell

これから扱うモデルクラスをインポートします。

from ec_site.models import *

それでは、countメソッドを使ってCategory、Product、OrderItem、OrderItemDetailのレコード数をチェックしてみましょう。

>>> Category.objects.count()
3
>>> Product.objects.count()
9
>>> OrderItem.objects.count()
18
>>> OrderItemDetail.objects.count()
24

上記の通り、カテゴリは3件、製品は9件、注文データは18件、注文の明細データは24件のデータが存在していることが確認できます。

4.全期間の全商品の売上合計金額を求める

まずは注文データ（OrderItem)からすべての売上金額(price)の合計を求めます。

>>> from django.db.models import Sum
>>> all_sales = OrderItem.objects.all()
>>> total_price = all_sales.aggregate(Sum('price'))
>>> total_price
{'price__sum': Decimal('337220')}
>>> total_price = int(total_price['price__sum'])
>>> total_price
337220

まず、OrderItem.objects.all()ですべての注文データを取得し、all_sales変数に格納します。

集計を行うにはaggregateメソッドを利用します。
また、合計を求めるためにSumクラスを利用します。

以下のフォーマットで集計したい項目の合計値を計算することができます。

＜集計対象のQueryset>.aggregate(Sum('集計したい項目'))

集計結果は以下の様に辞書型データとして返ってきます。

{'price__sum': Decimal('337220')}

数値型として取り出したい場合は、以下の様にintをかぶせてあげればOKです。

int(total_price['price__sum'])

また、辞書型のキー名はフィールド名__集計関数という形式で自動生成されますが、以下の様にすると明示的にキー名を指定することができます。

>>> total_price = all_sales.aggregate(total_price =Sum('price'))
>>> total_price
{'total_price': Decimal('337220')}

5.月毎の全商品の売上合計金額を求める

次は、月毎の注文データの売上金額(price)の合計金額を求めます。

5-1.注文データの年月を取得する

月毎の売上データを計算するには、OrderItemテーブル（注文データ）から月ごとのデータセットを取得する必要があります。

今回のサンプルデータの場合、2021年6月と2021年7月の売上データが存在しているため、「2021年6月1日～2021年6月30日」までのデータ一式と、「2021年7月1日～2021年7月31日」までのデータ一式を取得する必要があります。

その後、取得した月事のデータの売上金額（price)の合計金額を計算すれば目的の情報が取得できます。

OrderItemテーブル（注文データ）の注文日（created_date)の「年月」を重複を除外して取得するにはdates()メソッドを利用します。

モデルで定義したDateFieldフィールドに対してdates()メソッドを使うと、特定の種類(年、月、週、日）の利用可能なすべての日付を表すdatetime.dateオブジェクトを取得してくれます。

OrderItem.objects.dates('created_date', 'month', order='ASC')
<QuerySet [datetime.date(2021, 6, 1), datetime.date(2021, 7, 1)]>

datesメソッドの利用方法は以下の通りです。

dates(field, kind, order='ASC')

より詳細な情報は以下公式サイトを確認してみてください。

QuerySet API reference | Django ドキュメント | Django

それでは、月毎の全商品の売上合計金額を求めていきます。

まずは、OrderItemから売り上げ年月を重複を排除した形で取得するには以下のコードを実行します。

 #各月の初日を取得 （重複を排除）
dates_q = OrderItem.objects.dates('created_date', 'month', order='ASC')
for date in dates_q:
    print(date)
...
2021-06-01
2021-07-01

datesメソッドの第1引数にOrderItemクラスのcreated_dateを指定し、集計単位は月であるmonthを指定します。

5-2.月末日を取得する

次に、先ほど取得した年月データのクエリセットから各月の月末日を取得します。
月末日を取得するためにpython標準ライブラリのcalendarモジュールのmonthrangeメソッドを利用します。
monthrangeメソッドは以下の様にして指定した年月の初日の曜日と月末日の情報をタプルで返してくれます。

import calendar
print(calendar.monthrange(2021, 7))

上記を利用して、先ほど取得したdates_q（注文データの年月が格納されている）をループ処理して月末日を取得します。

dates_list = [ [date, date.replace(day=calendar.monthrange(date.year,date.month)[1])] for date in dates_q]
for date in dates_list:
    print(date)
...
[datetime.date(2021, 6, 1), datetime.date(2021, 6, 30)]
[datetime.date(2021, 7, 1), datetime.date(2021, 7, 31)]

注文データの月初日と月末日を格納するリスト変数dates_listを定義します。
その後、forループでdates_qを1つ1つ取り出し、月初日(date)と月末日をdates_listに追加していきます。

月末日は、以下のコードで取得しています。

date.replace(day=calendar.monthrange(date.year,date.month)[1])

5-3.各月ごとの注文データを取得して合計する。

注文データ（OrderItem)に存在している年月の月初日、月末日が取得できたので、この情報を使って各月毎の注文データの売上合計を計算します。

まず、以下の様にfilterメソッドとrangeを組み合わせて指定した期間（1か月分のデータ）の注文データを抽出します。

OrderItem.objects.filter(created_date__range=(<開始日>, <終了日>)

更に、抽出した1か月分の注文データの売上（price)の合計金額を求めるためaggregateメソッドを使います。

aggregateメソッドはQuerysetに対しての集計を行ってくれる機能です。
基本的な使い方は以下の通りです。

aggregate(集計方法('集計対象のカラム名'))

集計方法には、以下のような種類があります。

すでにSumについては解説しましたが、上記集計メソッドを使うには予め以下の様にインポートしておく必要があります。

from django.db.models import Sum, Max, Min, Avg

これまで説明したfilterメソッドとaggregateメソッドを以下の様に組み合わせることで、1月の注文データの合計金額を取得することができます。

from django.db.models import Sum
for date in dates_list:
    OrderItem.objects.filter(created_date__range=(date[0], date[1])).aggregate(total_price=Sum('price'))
{'total_price': Decimal('221740')}
{'total_price': Decimal('115480')}

まず、 月初と月末データが格納されているdates_listリストをforループで1つずつ取り出し、rangeに指定することで各月の1か月分の注文データを取得します。
さらにaggregateメソッドで売上金額列（price）の合計を計算します。
戻り値は辞書型データになります。

集計データを使ってグラフを描画したい場合などは、以下の様にしてリストデータで取得するとよいでしょう。

total_amount_of_sales = []
for date in dates_list:
    total_amount_of_sales.append([date[0].strftime('%Y/%m/%d'), int(OrderItem.objects.filter(created_date__range=(date[0], date[1])).aggregate(total_price=Sum('price'))['total_price'])])
[['2021/06/01', 221740], ['2021/07/01', 115480]]

上で、月毎の全商品の売上合計金額が求まりました。

6.全期間の商品毎の売上合計金額を求める

全期間の商品毎の売上合計金額を求めるには、まず注文明細テーブル（OrderItemDetail）に存在する各商品毎の注文数を知る必要があります。

注文数が分かれば、製品の価格に個数を掛けることで商品毎の売り上げ合計額がわかります。

注文明細テーブル（OrderItemDetail）で製品名(product)ごとの注文数(quantity)を求めるには、以下の様にvaluesとannotateを組み合わせます。
valuesで指定したカラム毎にannotateで指定した計算処理（以下の場合はquantityの合計値）を実行してくれます。

products_count = OrderItemDetail.objects.values('product').annotate(total = Sum('quantity'))
products_count
<QuerySet [{'product': 1, 'total': 3}, {'product': 2, 'total': 2}, {'product': 3, 'total': 2}, {'product': 4, 'total': 2}, {'product': 5, 'total': 12}, {'product': 6, 'total': 2}, {'product': 7, 'total': 3}, {'product': 8, 'total': 3}, {'product': 9, 'total': 4}]>
>>>

上記の通り、戻り値はproductID毎の注文数（total）が返ってきます。

上記のクエリセットを使って、製品IDから製品名、製品の価格情報を取得すれば、各製品毎の合計金額を求めることができます。
例えば、以下のようなコードを実行すれば、製品名、注文数、合計金額を表示することができます。

for product in products_count:
    product_data = Product.objects.get(pk=product['product'])
    print(f"{product_data},{product['total']}個 , 合計金額：{product['total'] * product_data.price}円")
電子レンジ,3個 , 合計金額：75000円
冷蔵庫,2個 , 合計金額：70000円
4Kテレビ,2個 , 合計金額：110000円
コート,2個 , 合計金額：39600円
Tシャツ,12個 , 合計金額：30000円
ハット,2個 , 合計金額：7800円
掛け時計,3個 , 合計金額：15000円
照明器具,3個 , 合計金額：29400円
ティッシュケース,4個 , 合計金額：7920円

以上で、全期間の商品毎の売上合計金額がめられました。

7.月毎の商品毎の売上合計金額を求める

次は、月ごと＆商品ごとに売り上げの合計金額を求めてみましょう。

基本的なやり方としては、全期間の商品毎の売上合計金額で求めた方法と同じです。

まずは、注文明細テーブル(OrderItemDetail）から月ごとのデータを抽出します。

dates_q = OrderItemDetail.objects.dates('created_date', 'month', order='ASC')
dates_list = [ [date, date.replace(day=calendar.monthrange(date.year,date.month)[1])] for date in dates_q]
monthly_detail_data=[]
for date in dates_list:
    monthly_detail_data.append(OrderItemDetail.objects.filter(created_date__range=(date[0], date[1])))

dates_listには以下の様に月初と月末日のデータが格納されます。

[[datetime.date(2021, 6, 1), datetime.date(2021, 6, 30)], [datetime.date(2021, 7, 1), datetime.date(2021, 7, 31)]]

次に、以下のコードでdates_listをループさせて注文明細テーブル(OrderItemDetail）から月毎のデータを抽出します。

monthly_detail_data=[]
for date in dates_list:
    monthly_detail_data.append(OrderItemDetail.objects.filter(created_date__range=(date[0], date[1])))

monthly_detail_dataには、月ごとに抽出した注文明細テーブル(OrderItemDetail）のクエリセットが格納されます。
今回の例では、2021年6月分の明細データと、２０２１年７月分の明細データが格納されます。

このmonthly_detail_dataを元に月毎に製品毎の注文数の合計を求めます。
以下のコードで求めることができます。

for data in monthly_detail_data:
...     data.values('product').annotate(total = Sum('quantity'))
<QuerySet [{'product': 1, 'total': 2}, {'product': 2, 'total': 1}, {'product': 3, 'total': 1}, {'product': 4, 'total': 1}, {'product': 5, 'total': 6}, {'product': 6, 'total': 1}, {'product': 7, 'total': 2}, {'product': 8, 'total': 2}, {'product': 9, 'total': 3}]>
<QuerySet [{'product': 1, 'total': 1}, {'product': 2, 'total': 1}, {'product': 3, 'total': 1}, {'product': 4, 'total': 1}, {'product': 5, 'total': 6}, {'product': 6, 'total': 1}, {'product': 7, 'total': 1}, {'product': 8, 'total': 1}, {'product': 9, 'total': 1}]>

実行結果は、上記の通り月毎に製品IDと注文数の情報がクエリセットとして返ってきます。

この情報を元に製品単価ｘ注文数を計算することで、月毎＆製品毎の合計金額を求めることができます。

完成形のコードは以下の様になります。

for data in monthly_detail_data:
    print("="*50)
    products_count = data.values('product').annotate(total = Sum('quantity'))
    for product in products_count:
        product_data = Product.objects.get(pk=product['product'])
        print(f"{product_data},{product['total']}個 , 単価：{product_data.price},合計金額：{product['total'] * product_data.price}円")```

実行結果は以下の様になります。

==================================================
電子レンジ,2個 , 単価：25000,合計金額：50000円
冷蔵庫,1個 , 単価：35000,合計金額：35000円
4Kテレビ,1個 , 単価：55000,合計金額：55000円
コート,1個 , 単価：19800,合計金額：19800円
Tシャツ,6個 , 単価：2500,合計金額：15000円
ハット,1個 , 単価：3900,合計金額：3900円
掛け時計,2個 , 単価：5000,合計金額：10000円
照明器具,2個 , 単価：9800,合計金額：19600円
ティッシュケース,3個 , 単価：1980,合計金額：5940円
==================================================
電子レンジ,1個 , 単価：25000,合計金額：25000円
冷蔵庫,1個 , 単価：35000,合計金額：35000円
4Kテレビ,1個 , 単価：55000,合計金額：55000円
コート,1個 , 単価：19800,合計金額：19800円
Tシャツ,6個 , 単価：2500,合計金額：15000円
ハット,1個 , 単価：3900,合計金額：3900円
掛け時計,1個 , 単価：5000,合計金額：5000円
照明器具,1個 , 単価：9800,合計金額：9800円
ティッシュケース,1個 , 単価：1980,合計金額：1980円

以上で、月毎の商品毎の売上合計金額が求まりました。

8.全期間のカテゴリ毎の販売数を求める

は、カテゴリ毎にどれだけ商品が売れているか確認してみましょう。
これも今まで出てきたメソッドを活用すれば計算できます。

以下の様に注文明細テーブル（OrderItemDetail)に対してvaluesで指定した項目(product__category)毎にannotateメソッドを使って注文個数(quantity)で合計を集計すればOKです。

OrderItemDetail.objects.values('product__category').annotate(total = Sum('quantity'))
<QuerySet [{'product__category': 1, 'total': 7}, {'product__category': 2, 'total': 16}, {'product__category': 3, 'total': 10}]>

上記のままではカテゴリ名が表示されていないので、以下の様にすればカテゴリ名毎の個数を表示できます。

for data in number_of_sales_per_category:
    print(Product.objects.get(pk=data['product__category']), data['total'])
...
電子レンジ 7
冷蔵庫 16
4Kテレビ 10

9.月毎のカテゴリ毎の販売数を求める

最後に、月毎のカテゴリ毎の販売数を求めてみましょう。
やり方はこれまでと同じです。

dates_q = OrderItemDetail.objects.dates('created_date', 'month', order='ASC')
dates_list = [ [date, date.replace(day=calendar.monthrange(date.year,date.month)[1])] for date in dates_q]
monthly_detail_data=[]
for date in dates_list:
    monthly_detail_data.append(OrderItemDetail.objects.filter(created_date__range=(date[0], date[1])))
for data in monthly_detail_data:
    print("="*50)
    number_of_sales_per_category = data.values('product__category').annotate(total = Sum('quantity'))
    for data in number_of_sales_per_category:
        print(Product.objects.get(pk=data['product__category']), data['total'])

実行結果は以下の様になります。

==================================================
電子レンジ 4
冷蔵庫 8
4Kテレビ 7
==================================================
電子レンジ 3
冷蔵庫 8
4Kテレビ 3

以上でこのレシピは終了です。
お疲れさまでした！