查询示例
这些查询示例取自网站 PostgreSQL Exercises。可以在 入门页面 上找到示例数据集。
以下是这些示例中使用的架构的可视化表示
模型定义
要开始使用数据，我们将定义与图表中的表相对应的模型类。
注意
在某些情况下，我们会明确指定特定字段的列名。这是为了使我们的模型与用于 postgres 练习的数据库架构兼容。
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 表达式、并集以及其他一些零碎内容。
检索所有内容
从 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、名称、费用和月度维护费。
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、姓氏、名字和加入日期。
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)
生成推荐其他会员的所有会员的列表
如何输出推荐其他会员的所有会员的列表？确保列表中没有重复项，并且结果按（姓氏、名字）排序。
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))
生成所有会员及其推荐人的列表
如何输出所有会员的列表，包括推荐他们的个人（如果有）？确保结果按（姓氏、名字）排序。
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 美元的预订列表？请记住，客人与会员的费用不同（列出的费用是每半小时“时段”），并且访客用户始终为 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)
生成所有成员的列表，以及他们的推荐人，不使用连接。
如何输出所有成员的列表，包括推荐他们的个人（如果有），而不使用任何连接？确保列表中没有重复项，并且每个名字 + 姓氏配对都格式化为一列并按顺序排列。
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。
在前面的部分中，我们向你返回了你执行的查询的结果。由于我们在本部分中进行的修改不会返回任何查询结果，因此我们向你显示你应该处理的表的更新内容。
将一些数据插入表中
俱乐部正在增加一个新设施 - 水疗中心。我们需要将其添加到设施表中。使用以下值：facid: 9，名称：'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())
将多行数据插入表中
在前面的练习中，你学习了如何添加设施。现在，你将在一个命令中添加多个设施。使用以下值
facid: 9，名称：'Spa'，membercost: 20，guestcost: 30，initialoutlay: 100000，monthlymaintenance: 800。
facid: 10，名称：'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()
将计算后的数据插入表中
让我们尝试再次将水疗中心添加到设施表中。不过，这一次，我们希望自动生成下一个 facid 的值，而不是将其指定为常量。对其他所有内容使用以下值：名称：'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()
更新一些现有数据
我们在输入第二个网球场的数据时出错。初始支出是 10000，而不是 8000：你需要更改数据来修复错误。
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())
根据另一行的内容更新一行
我们希望更改第二个网球场的价格，使其比第一个网球场高出 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())
删除所有预订
作为清理数据库的一部分，我们希望从预订表中删除所有预订。
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()
聚合
聚合是真正让你体会到关系数据库系统强大功能的功能之一。它允许你超越仅仅持久化你的数据，进入提出真正有趣的问题的领域，这些问题可用于为决策提供信息。此类别详细介绍聚合，利用标准分组以及更新的窗口函数。
计算设施数
对于我们对聚合的首次尝试，我们将坚持一些简单的事情。我们想知道有多少设施存在 - 只需生成一个总数。
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))
查找预订至少一次的成员数
查找预订至少一次的成员总数。
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、月份和时段。在计算所有月份和所有设施 ID 的聚合值时，在月份和设施 ID 列中返回 null 值。
仅限 Postgres。
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))
列出每个指定设施预订的总小时数
生成按设施预订的总小时数列表，请记住一个时段持续半小时。输出表应包含按设施 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))
生成会员姓名列表，每行包含会员总数
生成会员姓名列表，每行包含会员总数。按加入日期排序。
仅限 Postgres（按原样编写）。
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 并不保证是连续的。
仅限 Postgres（按原样编写）。
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。确保在出现平局的情况下，输出所有平局结果。
仅限 Postgres（按原样编写）。
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)
按（四舍五入）使用小时数对成员进行排名
生成成员列表，以及他们在设施中预订的小时数，四舍五入到最近的十小时。按此四舍五入数字对他们进行排名，生成姓、名、四舍五入的小时数、排名的输出。按排名、姓和名进行排序。
仅限 Postgres（按原样编写）。
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))
查找前三个创收设施
生成前三个创收设施的列表（包括并列）。按排名和设施名称进行排序，输出设施名称和排名。
仅限 Postgres（按原样编写）。
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().
按价值对设施进行分类
根据收入将设施分为高、中、低三个大小相等的组。按分类和设施名称进行排序。
仅限 Postgres（按原样编写）。
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()))