クエリ例
これらのクエリ例は、PostgreSQL Exercises サイトから取得されています。サンプルデータセットは、はじめにページにあります。
これらの例で使用されているスキーマの視覚的な表現を以下に示します。
モデル定義
データの操作を開始するには、図のテーブルに対応するモデルクラスを定義します。
注記
場合によっては、特定のフィールドに列名を明示的に指定します。これは、私たちのモデルがPostgreSQL Exercisesで使用されているデータベーススキーマと互換性を持つようにするためです。
from functools import partial
from peewee import *
db = PostgresqlDatabase('peewee_test')
class BaseModel(Model):
class Meta:
database = db
class Member(BaseModel):
memid = AutoField() # Auto-incrementing primary key.
surname = CharField()
firstname = CharField()
address = CharField(max_length=300)
zipcode = IntegerField()
telephone = CharField()
recommendedby = ForeignKeyField('self', backref='recommended',
column_name='recommendedby', null=True)
joindate = DateTimeField()
class Meta:
table_name = 'members'
# Conveniently declare decimal fields suitable for storing currency.
MoneyField = partial(DecimalField, decimal_places=2)
class Facility(BaseModel):
facid = AutoField()
name = CharField()
membercost = MoneyField()
guestcost = MoneyField()
initialoutlay = MoneyField()
monthlymaintenance = MoneyField()
class Meta:
table_name = 'facilities'
class Booking(BaseModel):
bookid = AutoField()
facility = ForeignKeyField(Facility, column_name='facid')
member = ForeignKeyField(Member, column_name='memid')
starttime = DateTimeField()
slots = IntegerField()
class Meta:
table_name = 'bookings'
スキーマ作成
PostgreSQL Exercises サイトから SQL ファイルをダウンロードした場合は、次のコマンドを使用してPostgreSQL データベースにデータを読み込むことができます。
createdb peewee_test
psql -U postgres -f clubdata.sql -d peewee_test -x -q
サンプルデータを読み込まずにPeeweeを使用してスキーマを作成するには、次を実行します。
# Assumes you have created the database "peewee_test" already.
db.create_tables([Member, Facility, Booking])
基本的な演習
このカテゴリでは、SQL の基本について説明します。select句とwhere句、case式、union、その他いくつかの詳細事項を扱います。
すべてのデータの取得
facilities テーブルからすべての情報を取得します。
SELECT * FROM facilities
# By default, when no fields are explicitly passed to select(), all fields
# will be selected.
query = Facility.select()
テーブルから特定の列を取得する
施設の名前とメンバーへのコストを取得します。
SELECT name, membercost FROM facilities;
query = Facility.select(Facility.name, Facility.membercost)
# To iterate:
for facility in query:
print(facility.name)
取得する行を制御する
メンバーへのコストがある施設のリストを取得します。
SELECT * FROM facilities WHERE membercost > 0
query = Facility.select().where(Facility.membercost > 0)
取得する行を制御する - パート 2
メンバーへのコストがあり、その料金が月々の維持費の 1/50 未満である施設のリストを取得します。id、name、cost、monthly-maintenance を返します。
SELECT facid, name, membercost, monthlymaintenance
FROM facilities
WHERE membercost > 0 AND membercost < (monthlymaintenance / 50)
query = (Facility
.select(Facility.facid, Facility.name, Facility.membercost,
Facility.monthlymaintenance)
.where(
(Facility.membercost > 0) &
(Facility.membercost < (Facility.monthlymaintenance / 50))))
基本的な文字列検索
名前の中に「Tennis」という単語が含まれるすべての施設のリストをどのように作成できますか？
SELECT * FROM facilities WHERE name ILIKE '%tennis%';
query = Facility.select().where(Facility.name.contains('tennis'))
# OR use the exponent operator. Note: you must include wildcards here:
query = Facility.select().where(Facility.name ** '%tennis%')
複数の可能性のある値に一致させる
ID が 1 と 5 の施設の詳細を、OR 演算子を使用せずに取得するにはどうすればよいですか？
SELECT * FROM facilities WHERE facid IN (1, 5);
query = Facility.select().where(Facility.facid.in_([1, 5]))
# OR:
query = Facility.select().where((Facility.facid == 1) |
(Facility.facid == 5))
結果をバケットに分類する
月々の維持費が 100 ドルを超えるかどうかによって、「安価」または「高価」としてラベル付けされた施設のリストをどのように作成できますか？問題の施設の名前と月々の維持費を返します。
SELECT name,
CASE WHEN monthlymaintenance > 100 THEN 'expensive' ELSE 'cheap' END
FROM facilities;
cost = Case(None, [(Facility.monthlymaintenance > 100, 'expensive')], 'cheap')
query = Facility.select(Facility.name, cost.alias('cost'))
注記
詳細については、Case のドキュメントを参照してください。
日付の操作
2012年9月以降に登録したメンバーのリストをどのように作成できますか？問題のメンバーのmemid、surname、firstname、joindate を返します。
SELECT memid, surname, firstname, joindate FROM members
WHERE joindate >= '2012-09-01';
query = (Member
.select(Member.memid, Member.surname, Member.firstname, Member.joindate)
.where(Member.joindate >= datetime.date(2012, 9, 1)))
重複の削除と結果の並べ替え
members テーブルの先頭 10 件の姓の順序付けられたリストをどのように作成できますか？リストには重複を含めてはいけません。
SELECT DISTINCT surname FROM members ORDER BY surname LIMIT 10;
query = (Member
.select(Member.surname)
.order_by(Member.surname)
.limit(10)
.distinct())
複数のクエリの結果を組み合わせる
何らかの理由で、すべての姓とすべての施設名の組み合わせリストが必要です。
SELECT surname FROM members UNION SELECT name FROM facilities;
lhs = Member.select(Member.surname)
rhs = Facility.select(Facility.name)
query = lhs | rhs
クエリは、次の演算子を使用して合成できます。
|-UNION+-UNION ALL&-INTERSECT--EXCEPT
単純な集計
最後のメンバーのサインアップ日を取得したいと考えています。この情報を取得するにはどうすればよいですか？
SELECT MAX(join_date) FROM members;
query = Member.select(fn.MAX(Member.joindate))
# To conveniently obtain a single scalar value, use "scalar()":
# max_join_date = query.scalar()
詳細な集計
最後にサインアップしたメンバー（複数の場合もある）の名前を取得したいと考えています。日付だけではありません。
SELECT firstname, surname, joindate FROM members
WHERE joindate = (SELECT MAX(joindate) FROM members);
# Use "alias()" to reference the same table multiple times in a query.
MemberAlias = Member.alias()
subq = MemberAlias.select(fn.MAX(MemberAlias.joindate))
query = (Member
.select(Member.firstname, Member.surname, Member.joindate)
.where(Member.joindate == subq))
結合とサブクエリ
このカテゴリでは、主にリレーショナルデータベースシステムの基本的な概念である結合について扱います。結合を使用すると、複数のテーブルから関連情報を組み合わせて質問に答えることができます。これはクエリの容易さのためだけでなく有益です。結合機能がないとデータの非正規化が促進され、データの内部整合性を維持する複雑さが増します。
このトピックでは、内部結合、外部結合、自己結合について説明し、サブクエリ（クエリ内のクエリ）についても少し時間をかけて説明します。
メンバーの予約開始時刻を取得する
「David Farrell」という名前のメンバーによる予約の開始時刻のリストをどのように作成できますか？
SELECT starttime FROM bookings
INNER JOIN members ON (bookings.memid = members.memid)
WHERE surname = 'Farrell' AND firstname = 'David';
query = (Booking
.select(Booking.starttime)
.join(Member)
.where((Member.surname == 'Farrell') &
(Member.firstname == 'David')))
テニスコートの予約開始時刻を計算する
「2012-09-21」のテニスコートの予約開始時刻のリストをどのように作成できますか？開始時刻と施設名のペアのリストを、時刻順に返します。
SELECT starttime, name
FROM bookings
INNER JOIN facilities ON (bookings.facid = facilities.facid)
WHERE date_trunc('day', starttime) = '2012-09-21':: date
AND name ILIKE 'tennis%'
ORDER BY starttime, name;
query = (Booking
.select(Booking.starttime, Facility.name)
.join(Facility)
.where(
(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 21)) &
Facility.name.startswith('Tennis'))
.order_by(Booking.starttime, Facility.name))
# To retrieve the joined facility's name when iterating:
for booking in query:
print(booking.starttime, booking.facility.name)
他のメンバーを推薦したすべてのメンバーのリストを作成する
他のメンバーを推薦したすべてのメンバーのリストをどのように出力できますか？リストに重複がないこと、結果が（surname、firstname）でソートされていることを確認してください。
SELECT DISTINCT m.firstname, m.surname
FROM members AS m2
INNER JOIN members AS m ON (m.memid = m2.recommendedby)
ORDER BY m.surname, m.firstname;
MA = Member.alias()
query = (Member
.select(Member.firstname, Member.surname)
.join(MA, on=(MA.recommendedby == Member.memid))
.order_by(Member.surname, Member.firstname))
すべてのメンバーとその推薦者のリストを作成する
すべてのメンバーとその推薦者（存在する場合）のリストをどのように出力できますか？結果が（surname、firstname）でソートされていることを確認してください。
SELECT m.firstname, m.surname, r.firstname, r.surname
FROM members AS m
LEFT OUTER JOIN members AS r ON (m.recommendedby = r.memid)
ORDER BY m.surname, m.firstname
MA = Member.alias()
query = (Member
.select(Member.firstname, Member.surname, MA.firstname, MA.surname)
.join(MA, JOIN.LEFT_OUTER, on=(Member.recommendedby == MA.memid))
.order_by(Member.surname, Member.firstname))
# To display the recommender's name when iterating:
for m in query:
print(m.firstname, m.surname)
if m.recommendedby:
print(' ', m.recommendedby.firstname, m.recommendedby.surname)
テニスコートを使用したすべてのメンバーのリストを作成する
テニスコートを使用したすべてのメンバーのリストをどのように作成できますか？出力には、コートの名前と、単一列としてフォーマットされたメンバーの名前を含めます。重複データがなく、メンバー名でソートされていることを確認してください。
SELECT DISTINCT m.firstname || ' ' || m.surname AS member, f.name AS facility
FROM members AS m
INNER JOIN bookings AS b ON (m.memid = b.memid)
INNER JOIN facilities AS f ON (b.facid = f.facid)
WHERE f.name LIKE 'Tennis%'
ORDER BY member, facility;
fullname = Member.firstname + ' ' + Member.surname
query = (Member
.select(fullname.alias('member'), Facility.name.alias('facility'))
.join(Booking)
.join(Facility)
.where(Facility.name.startswith('Tennis'))
.order_by(fullname, Facility.name)
.distinct())
高額な予約のリストを作成する
2012-09-14 のメンバー（またはゲスト）にかかる費用が 30 ドルを超える予約のリストをどのように作成できますか？ゲストはメンバーとは異なるコストがかかり（リストされているコストは 30 分ごとの「枠」です）、ゲストユーザーの ID は常に 0 です。出力には、施設名、単一列としてフォーマットされたメンバー名、コストを含めます。コストの降順で並べ替え、サブクエリは使用しません。
SELECT m.firstname || ' ' || m.surname AS member,
f.name AS facility,
(CASE WHEN m.memid = 0 THEN f.guestcost * b.slots
ELSE f.membercost * b.slots END) AS cost
FROM members AS m
INNER JOIN bookings AS b ON (m.memid = b.memid)
INNER JOIN facilities AS f ON (b.facid = f.facid)
WHERE (date_trunc('day', b.starttime) = '2012-09-14') AND
((m.memid = 0 AND b.slots * f.guestcost > 30) OR
(m.memid > 0 AND b.slots * f.membercost > 30))
ORDER BY cost DESC;
cost = Case(Member.memid, (
(0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost))
fullname = Member.firstname + ' ' + Member.surname
query = (Member
.select(fullname.alias('member'), Facility.name.alias('facility'),
cost.alias('cost'))
.join(Booking)
.join(Facility)
.where(
(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)) &
(cost > 30))
.order_by(SQL('cost').desc()))
# To iterate over the results, it might be easiest to use namedtuples:
for row in query.namedtuples():
print(row.member, row.facility, row.cost)
結合を使用せずに、すべてのメンバーとその推薦者のリストを作成する
結合を使用せずに、すべてのメンバーとその推薦者（存在する場合）のリストをどのように出力できますか？リストに重複がないこと、各 firstname + surname のペアが列としてフォーマットされ、ソートされていることを確認してください。
SELECT DISTINCT m.firstname || ' ' || m.surname AS member,
(SELECT r.firstname || ' ' || r.surname
FROM cd.members AS r
WHERE m.recommendedby = r.memid) AS recommended
FROM members AS m ORDER BY member;
MA = Member.alias()
subq = (MA
.select(MA.firstname + ' ' + MA.surname)
.where(Member.recommendedby == MA.memid))
query = (Member
.select(fullname.alias('member'), subq.alias('recommended'))
.order_by(fullname))
サブクエリを使用して、高額な予約のリストを作成する
「高額な予約のリストを作成する」演習には、いくつかの複雑なロジックが含まれていました。WHERE 句と CASE 文の両方で予約コストを計算する必要がありました。サブクエリを使用してこの計算を簡素化してみてください。
SELECT member, facility, cost from (
SELECT
m.firstname || ' ' || m.surname as member,
f.name as facility,
CASE WHEN m.memid = 0 THEN b.slots * f.guestcost
ELSE b.slots * f.membercost END AS cost
FROM members AS m
INNER JOIN bookings AS b ON m.memid = b.memid
INNER JOIN facilities AS f ON b.facid = f.facid
WHERE date_trunc('day', b.starttime) = '2012-09-14'
) as bookings
WHERE cost > 30
ORDER BY cost DESC;
cost = Case(Member.memid, (
(0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost))
iq = (Member
.select(fullname.alias('member'), Facility.name.alias('facility'),
cost.alias('cost'))
.join(Booking)
.join(Facility)
.where(fn.date_trunc('day', Booking.starttime) == datetime.date(2012, 9, 14)))
query = (Member
.select(iq.c.member, iq.c.facility, iq.c.cost)
.from_(iq)
.where(iq.c.cost > 30)
.order_by(SQL('cost').desc()))
# To iterate, try using dicts:
for row in query.dicts():
print(row['member'], row['facility'], row['cost'])
データの変更
データをクエリするのは良いことですが、ある時点でデータベースにデータを追加したいと思うでしょう！このセクションでは、情報の挿入、更新、削除について説明します。このようにデータを変更する操作は、まとめてデータ操作言語（DML）と呼ばれます。
これまでのセクションでは、実行されたクエリ結果を返しました。このセクションで行っているような変更ではクエリ結果は返されないので、代わりに作業対象のテーブルの更新されたコンテンツを表示します。
テーブルへのデータ挿入
クラブに新しい施設、スパが追加されます。これをfacilitiesテーブルに追加する必要があります。次の値を使用してください: facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800
INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
"initialoutlay", "monthlymaintenance") VALUES (9, 'Spa', 20, 30, 100000, 800)
res = Facility.insert({
Facility.facid: 9,
Facility.name: 'Spa',
Facility.membercost: 20,
Facility.guestcost: 30,
Facility.initialoutlay: 100000,
Facility.monthlymaintenance: 800}).execute()
# OR:
res = (Facility
.insert(facid=9, name='Spa', membercost=20, guestcost=30,
initialoutlay=100000, monthlymaintenance=800)
.execute())
テーブルへの複数行のデータ挿入
前の演習では、施設の追加方法を学びました。今度は、1つのコマンドで複数の施設を追加します。次の値を使用してください。
facid: 9, Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800。
facid: 10, Name: ‘Squash Court 2’, membercost: 3.5, guestcost: 17.5, initialoutlay: 5000, monthlymaintenance: 80。
-- see above --
data = [
{'facid': 9, 'name': 'Spa', 'membercost': 20, 'guestcost': 30,
'initialoutlay': 100000, 'monthlymaintenance': 800},
{'facid': 10, 'name': 'Squash Court 2', 'membercost': 3.5,
'guestcost': 17.5, 'initialoutlay': 5000, 'monthlymaintenance': 80}]
res = Facility.insert_many(data).execute()
計算されたデータのテーブルへの挿入
スパをfacilitiesテーブルに追加してみましょう。ただし、今回は定数として指定するのではなく、次のfacidの値を自動的に生成します。それ以外の値は次のとおりです。Name: ‘Spa’, membercost: 20, guestcost: 30, initialoutlay: 100000, monthlymaintenance: 800。
INSERT INTO "facilities" ("facid", "name", "membercost", "guestcost",
"initialoutlay", "monthlymaintenance")
SELECT (SELECT (MAX("facid") + 1) FROM "facilities") AS _,
'Spa', 20, 30, 100000, 800;
maxq = Facility.select(fn.MAX(Facility.facid) + 1)
subq = Select(columns=(maxq, 'Spa', 20, 30, 100000, 800))
res = Facility.insert_from(subq, Facility._meta.sorted_fields).execute()
既存データの更新
2番目のテニスコートのデータ入力時にミスがありました。初期費用は8000ではなく10000でした。エラーを修正するためにデータを変更する必要があります。
UPDATE facilities SET initialoutlay = 10000 WHERE name = 'Tennis Court 2';
res = (Facility
.update({Facility.initialoutlay: 10000})
.where(Facility.name == 'Tennis Court 2')
.execute())
# OR:
res = (Facility
.update(initialoutlay=10000)
.where(Facility.name == 'Tennis Court 2')
.execute())
複数行と列の同時更新
テニスコートの会員とゲストの両方の価格を値上げしたいと考えています。会員料金を6、ゲスト料金を30に更新します。
UPDATE facilities SET membercost=6, guestcost=30 WHERE name ILIKE 'Tennis%';
nrows = (Facility
.update(membercost=6, guestcost=30)
.where(Facility.name.startswith('Tennis'))
.execute())
別の行の内容に基づいて行を更新する
2番目のテニスコートの価格を、最初のテニスコートの価格より10％高く変更します。価格に定数値を使用せずにこれを行うことで、必要に応じてステートメントを再利用できるようにします。
UPDATE facilities SET
membercost = (SELECT membercost * 1.1 FROM facilities WHERE facid = 0),
guestcost = (SELECT guestcost * 1.1 FROM facilities WHERE facid = 0)
WHERE facid = 1;
-- OR --
WITH new_prices (nmc, ngc) AS (
SELECT membercost * 1.1, guestcost * 1.1
FROM facilities WHERE name = 'Tennis Court 1')
UPDATE facilities
SET membercost = new_prices.nmc, guestcost = new_prices.ngc
FROM new_prices
WHERE name = 'Tennis Court 2'
sq1 = Facility.select(Facility.membercost * 1.1).where(Facility.facid == 0)
sq2 = Facility.select(Facility.guestcost * 1.1).where(Facility.facid == 0)
res = (Facility
.update(membercost=sq1, guestcost=sq2)
.where(Facility.facid == 1)
.execute())
# OR:
cte = (Facility
.select(Facility.membercost * 1.1, Facility.guestcost * 1.1)
.where(Facility.name == 'Tennis Court 1')
.cte('new_prices', columns=('nmc', 'ngc')))
res = (Facility
.update(membercost=SQL('new_prices.nmc'), guestcost=SQL('new_prices.ngc'))
.with_cte(cte)
.from_(cte)
.where(Facility.name == 'Tennis Court 2')
.execute())
すべての予約の削除
データベースのクリアの一環として、bookingsテーブルからすべての予約を削除します。
DELETE FROM bookings;
nrows = Booking.delete().execute()
cd.membersテーブルからメンバーの削除
予約をしたことのないメンバー37をデータベースから削除します。
DELETE FROM members WHERE memid = 37;
nrows = Member.delete().where(Member.memid == 37).execute()
サブクエリに基づいた削除
予約をしたことのないすべてのメンバーを削除するにはどうすればより一般的になりますか？
DELETE FROM members WHERE NOT EXISTS (
SELECT * FROM bookings WHERE bookings.memid = members.memid);
subq = Booking.select().where(Booking.member == Member.memid)
nrows = Member.delete().where(~fn.EXISTS(subq)).execute()
集約
集約は、リレーショナルデータベースシステムの力を本当に実感できる機能の1つです。データの永続化を超えて、意思決定に役立つ本当に興味深い質問をすることができます。このカテゴリでは、標準的なグループ化と、より最近のウィンドウ関数の両方を使用して、集約について詳しく説明します。
施設数のカウント
最初に集計を試みるには、簡単なものから始めます。施設の数がいくつあるかを知りたいので、合計数を算出します。
SELECT COUNT(facid) FROM facilities;
query = Facility.select(fn.COUNT(Facility.facid))
count = query.scalar()
# OR:
count = Facility.select().count()
高額な施設数のカウント
ゲスト料金が10以上の施設数をカウントします。
SELECT COUNT(facid) FROM facilities WHERE guestcost >= 10
query = Facility.select(fn.COUNT(Facility.facid)).where(Facility.guestcost >= 10)
count = query.scalar()
# OR:
# count = Facility.select().where(Facility.guestcost >= 10).count()
各メンバーが行った推奨数のカウント
各メンバーが行った推奨数をカウントします。メンバーIDでソートします。
SELECT recommendedby, COUNT(memid) FROM members
WHERE recommendedby IS NOT NULL
GROUP BY recommendedby
ORDER BY recommendedby
query = (Member
.select(Member.recommendedby, fn.COUNT(Member.memid))
.where(Member.recommendedby.is_null(False))
.group_by(Member.recommendedby)
.order_by(Member.recommendedby))
施設ごとに予約されたスロットの合計数のリスト
施設ごとに予約されたスロットの合計数のリストを生成します。現時点では、施設IDとスロットからなる出力テーブルを生成し、施設IDでソートします。
SELECT facid, SUM(slots) FROM bookings GROUP BY facid ORDER BY facid;
query = (Booking
.select(Booking.facid, fn.SUM(Booking.slots))
.group_by(Booking.facid)
.order_by(Booking.facid))
特定の月の施設ごとに予約されたスロットの合計数のリスト
2012年9月の施設ごとに予約されたスロットの合計数のリストを生成します。施設IDとスロットからなる出力テーブルを生成し、スロット数でソートします。
SELECT facid, SUM(slots)
FROM bookings
WHERE (date_trunc('month', starttime) = '2012-09-01'::dates)
GROUP BY facid
ORDER BY SUM(slots)
query = (Booking
.select(Booking.facility, fn.SUM(Booking.slots))
.where(fn.date_trunc('month', Booking.starttime) == datetime.date(2012, 9, 1))
.group_by(Booking.facility)
.order_by(fn.SUM(Booking.slots)))
月ごとの施設ごとに予約されたスロットの合計数のリスト
2012年の月ごとの施設ごとに予約されたスロットの合計数のリストを生成します。施設IDとスロットからなる出力テーブルを生成し、IDと月でソートします。
SELECT facid, date_part('month', starttime), SUM(slots)
FROM bookings
WHERE date_part('year', starttime) = 2012
GROUP BY facid, date_part('month', starttime)
ORDER BY facid, date_part('month', starttime)
month = fn.date_part('month', Booking.starttime)
query = (Booking
.select(Booking.facility, month, fn.SUM(Booking.slots))
.where(fn.date_part('year', Booking.starttime) == 2012)
.group_by(Booking.facility, month)
.order_by(Booking.facility, month))
少なくとも1回予約をしたメンバー数の検索
少なくとも1回予約をしたメンバーの総数を検索します。
SELECT COUNT(DISTINCT memid) FROM bookings
-- OR --
SELECT COUNT(1) FROM (SELECT DISTINCT memid FROM bookings) AS _
query = Booking.select(fn.COUNT(Booking.member.distinct()))
# OR:
query = Booking.select(Booking.member).distinct()
count = query.count() # count() wraps in SELECT COUNT(1) FROM (...)
予約されたスロットが1000以上の施設のリスト
予約されたスロットが1000以上の施設のリストを生成します。施設IDと時間からなる出力テーブルを生成し、施設IDでソートします。
SELECT facid, SUM(slots) FROM bookings
GROUP BY facid
HAVING SUM(slots) > 1000
ORDER BY facid;
query = (Booking
.select(Booking.facility, fn.SUM(Booking.slots))
.group_by(Booking.facility)
.having(fn.SUM(Booking.slots) > 1000)
.order_by(Booking.facility))
各施設の総収益の検索
施設とその総収益のリストを生成します。出力テーブルは、施設名と収益で構成され、収益でソートされます。ゲストと会員ではコストが異なることに注意してください！
SELECT f.name, SUM(b.slots * (
CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
FROM bookings AS b
INNER JOIN facilities AS f ON b.facid = f.facid
GROUP BY f.name
ORDER BY revenue;
revenue = fn.SUM(Booking.slots * Case(None, (
(Booking.member == 0, Facility.guestcost),
), Facility.membercost))
query = (Facility
.select(Facility.name, revenue.alias('revenue'))
.join(Booking)
.group_by(Facility.name)
.order_by(SQL('revenue')))
総収益が1000未満の施設の検索
総収益が1000未満の施設のリストを生成します。出力テーブルは、施設名と収益で構成され、収益でソートされます。ゲストと会員ではコストが異なることに注意してください！
SELECT f.name, SUM(b.slots * (
CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
FROM bookings AS b
INNER JOIN facilities AS f ON b.facid = f.facid
GROUP BY f.name
HAVING SUM(b.slots * ...) < 1000
ORDER BY revenue;
# Same definition as previous example.
revenue = fn.SUM(Booking.slots * Case(None, (
(Booking.member == 0, Facility.guestcost),
), Facility.membercost))
query = (Facility
.select(Facility.name, revenue.alias('revenue'))
.join(Booking)
.group_by(Facility.name)
.having(revenue < 1000)
.order_by(SQL('revenue')))
予約されたスロット数が最も多い施設IDの出力
予約されたスロット数が最も多い施設IDを出力します。
SELECT facid, SUM(slots) FROM bookings
GROUP BY facid
ORDER BY SUM(slots) DESC
LIMIT 1
query = (Booking
.select(Booking.facility, fn.SUM(Booking.slots))
.group_by(Booking.facility)
.order_by(fn.SUM(Booking.slots).desc())
.limit(1))
# Retrieve multiple scalar values by calling scalar() with as_tuple=True.
facid, nslots = query.scalar(as_tuple=True)
月ごとの施設ごとに予約されたスロットの合計数のリスト、パート2
2012年の月ごとの施設ごとに予約されたスロットの合計数のリストを生成します。このバージョンでは、施設ごとのすべての月の合計と、すべての施設のすべての月の合計を含む出力行を含めます。出力テーブルは、施設ID、月、スロットで構成され、IDと月でソートされます。すべての月とすべてのfacidの集計値を計算する際には、月とfacidの列にnull値を返します。
PostgreSQLのみ。
SELECT facid, date_part('month', starttime), SUM(slots)
FROM booking
WHERE date_part('year', starttime) = 2012
GROUP BY ROLLUP(facid, date_part('month', starttime))
ORDER BY facid, date_part('month', starttime)
month = fn.date_part('month', Booking.starttime)
query = (Booking
.select(Booking.facility,
month.alias('month'),
fn.SUM(Booking.slots))
.where(fn.date_part('year', Booking.starttime) == 2012)
.group_by(fn.ROLLUP(Booking.facility, month))
.order_by(Booking.facility, month))
名前付き施設ごとに予約された合計時間のリスト
施設ごとに予約された合計時間数のリストを生成します。スロットは30分であることに注意してください。出力テーブルは、施設ID、名前、予約された時間で構成され、施設IDでソートされます。
SELECT f.facid, f.name, SUM(b.slots) * .5
FROM facilities AS f
INNER JOIN bookings AS b ON (f.facid = b.facid)
GROUP BY f.facid, f.name
ORDER BY f.facid
query = (Facility
.select(Facility.facid, Facility.name, fn.SUM(Booking.slots) * .5)
.join(Booking)
.group_by(Facility.facid, Facility.name)
.order_by(Facility.facid))
2012年9月1日以降の各メンバーの最初の予約のリスト
各メンバーの名前、ID、2012年9月1日以降の最初の予約のリストを生成します。メンバーIDでソートします。
SELECT m.surname, m.firstname, m.memid, min(b.starttime) as starttime
FROM members AS m
INNER JOIN bookings AS b ON b.memid = m.memid
WHERE starttime >= '2012-09-01'
GROUP BY m.surname, m.firstname, m.memid
ORDER BY m.memid;
query = (Member
.select(Member.surname, Member.firstname, Member.memid,
fn.MIN(Booking.starttime).alias('starttime'))
.join(Booking)
.where(Booking.starttime >= datetime.date(2012, 9, 1))
.group_by(Member.surname, Member.firstname, Member.memid)
.order_by(Member.memid))
各行にメンバーの総数を記載したメンバー名のリストを生成する
各行にメンバーの総数を記載したメンバー名のリストを生成します。参加日でソートします。
PostgreSQLのみ（記述されているとおり）。
SELECT COUNT(*) OVER(), firstname, surname
FROM members ORDER BY joindate
query = (Member
.select(fn.COUNT(Member.memid).over(), Member.firstname,
Member.surname)
.order_by(Member.joindate))
メンバーの番号付きリストの生成
参加日でソートされたメンバーの単調増加の番号付きリストを生成します。メンバーIDは連続しているとは限らないことに注意してください。
PostgreSQLのみ（記述されているとおり）。
SELECT row_number() OVER (ORDER BY joindate), firstname, surname
FROM members ORDER BY joindate;
query = (Member
.select(fn.row_number().over(order_by=[Member.joindate]),
Member.firstname, Member.surname)
.order_by(Member.joindate))
予約されたスロット数が最も多い施設IDの出力、再び
予約されたスロット数が最も多い施設IDを出力します。同順位の場合、すべての同順位の結果が出力されるようにします。
PostgreSQLのみ（記述されているとおり）。
SELECT facid, total FROM (
SELECT facid, SUM(slots) AS total,
rank() OVER (order by SUM(slots) DESC) AS rank
FROM bookings
GROUP BY facid
) AS ranked WHERE rank = 1
rank = fn.rank().over(order_by=[fn.SUM(Booking.slots).desc()])
subq = (Booking
.select(Booking.facility, fn.SUM(Booking.slots).alias('total'),
rank.alias('rank'))
.group_by(Booking.facility))
# Here we use a plain Select() to create our query.
query = (Select(columns=[subq.c.facid, subq.c.total])
.from_(subq)
.where(subq.c.rank == 1)
.bind(db)) # We must bind() it to the database.
# To iterate over the query results:
for facid, total in query.tuples():
print(facid, total)
(四捨五入した)使用時間に基づいてメンバーをランク付けする
メンバーとその施設で予約した時間数（最寄りの10時間単位で四捨五入）のリストを生成します。この四捨五入した数値に基づいてランク付けし、名、姓、四捨五入した時間、ランクを出力します。ランク、姓、名でソートします。
PostgreSQLのみ（記述されているとおり）。
SELECT firstname, surname,
((SUM(bks.slots)+10)/20)*10 as hours,
rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
FROM members AS mems
INNER JOIN bookings AS bks ON mems.memid = bks.memid
GROUP BY mems.memid
ORDER BY rank, surname, firstname;
hours = ((fn.SUM(Booking.slots) + 10) / 20) * 10
query = (Member
.select(Member.firstname, Member.surname, hours.alias('hours'),
fn.rank().over(order_by=[hours.desc()]).alias('rank'))
.join(Booking)
.group_by(Member.memid)
.order_by(SQL('rank'), Member.surname, Member.firstname))
上位3つの収益を生み出す施設の検索
上位3つの収益を生み出す施設（同順位を含む）のリストを生成します。施設名とランクを出力し、ランクと施設名でソートします。
PostgreSQLのみ（記述されているとおり）。
SELECT name, rank FROM (
SELECT f.name, RANK() OVER (ORDER BY SUM(
CASE WHEN memid = 0 THEN slots * f.guestcost
ELSE slots * f.membercost END) DESC) AS rank
FROM bookings
INNER JOIN facilities AS f ON bookings.facid = f.facid
GROUP BY f.name) AS subq
WHERE rank <= 3
ORDER BY rank;
total_cost = fn.SUM(Case(None, (
(Booking.member == 0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost)))
subq = (Facility
.select(Facility.name,
fn.RANK().over(order_by=[total_cost.desc()]).alias('rank'))
.join(Booking)
.group_by(Facility.name))
query = (Select(columns=[subq.c.name, subq.c.rank])
.from_(subq)
.where(subq.c.rank <= 3)
.order_by(subq.c.rank)
.bind(db)) # Here again we used plain Select, and call bind().
価値による施設の分類
収益に基づいて、施設を高、平均、低の等しいサイズのグループに分類します。分類と施設名でソートします。
PostgreSQLのみ（記述されているとおり）。
SELECT name,
CASE class WHEN 1 THEN 'high' WHEN 2 THEN 'average' ELSE 'low' END
FROM (
SELECT f.name, ntile(3) OVER (ORDER BY SUM(
CASE WHEN memid = 0 THEN slots * f.guestcost ELSE slots * f.membercost
END) DESC) AS class
FROM bookings INNER JOIN facilities AS f ON bookings.facid = f.facid
GROUP BY f.name
) AS subq
ORDER BY class, name;
cost = fn.SUM(Case(None, (
(Booking.member == 0, Booking.slots * Facility.guestcost),
), (Booking.slots * Facility.membercost)))
subq = (Facility
.select(Facility.name,
fn.NTILE(3).over(order_by=[cost.desc()]).alias('klass'))
.join(Booking)
.group_by(Facility.name))
klass_case = Case(subq.c.klass, [(1, 'high'), (2, 'average')], 'low')
query = (Select(columns=[subq.c.name, klass_case])
.from_(subq)
.order_by(subq.c.klass, subq.c.name)
.bind(db))
再帰
共通テーブル式を使用すると、クエリの実行中に独自のテンポラリテーブルを作成できます。これは主に、より読みやすいSQLを作成するための便宜的なものです。ただし、WITH RECURSIVE修飾子を使用すると、再帰クエリを作成できます。これは、ツリー構造データやグラフ構造データの処理に非常に有利です。たとえば、グラフノードのすべての関係を特定の深さまで取得することを考えてみてください。
メンバーID 27の上方推薦チェーンの検索
メンバーID 27の上方推薦チェーンを検索します。つまり、そのメンバーを推薦したメンバー、そのメンバーを推薦したメンバーなどです。メンバーID、名、姓を返します。メンバーIDで降順にソートします。
WITH RECURSIVE recommenders(recommender) as (
SELECT recommendedby FROM members WHERE memid = 27
UNION ALL
SELECT mems.recommendedby
FROM recommenders recs
INNER JOIN members AS mems ON mems.memid = recs.recommender
)
SELECT recs.recommender, mems.firstname, mems.surname
FROM recommenders AS recs
INNER JOIN members AS mems ON recs.recommender = mems.memid
ORDER By memid DESC;
# Base-case of recursive CTE. Get member recommender where memid=27.
base = (Member
.select(Member.recommendedby)
.where(Member.memid == 27)
.cte('recommenders', recursive=True, columns=('recommender',)))
# Recursive term of CTE. Get recommender of previous recommender.
MA = Member.alias()
recursive = (MA
.select(MA.recommendedby)
.join(base, on=(MA.memid == base.c.recommender)))
# Combine the base-case with the recursive term.
cte = base.union_all(recursive)
# Select from the recursive CTE, joining on member to get name info.
query = (cte
.select_from(cte.c.recommender, Member.firstname, Member.surname)
.join(Member, on=(cte.c.recommender == Member.memid))
.order_by(Member.memid.desc()))