查詢範例

這些查詢範例取自 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 運算式、聯合以及其他一些零星項目。

擷取所有內容

從設施資料表擷取所有資訊。

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))))

基本字串搜尋

您如何產生名稱中包含「網球」一詞的所有設施清單？

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)))

移除重複項目，並排序結果

您如何產生會員資料表中前 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()

聯結與子查詢

此類別主要處理關聯式資料庫系統中的基礎概念：聯結。聯結允許您結合多個資料表中相關的資訊來回答問題。這不僅有助於簡化查詢：缺乏聯結功能會鼓勵資料非正規化，這會增加保持資料內部一致性的複雜性。

本主題涵蓋內部聯結、外部聯結和自我聯結，並花一些時間討論子查詢（查詢中的查詢）。

擷取會員預訂的開始時間

您如何產生名為「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 年 9 月 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 年 9 月 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'])

修改資料

查詢資料很好，但在某個時候您可能想要將資料放入資料庫！本節介紹如何插入、更新和刪除資訊。像這樣更改資料的操作統稱為資料操作語言（Data Manipulation Language），或 DML。

在先前的章節中，我們會回傳您所執行查詢的結果。由於像本節中所做的修改不會回傳任何查詢結果，因此我們改為顯示您應該處理的表格的更新內容。

將一些資料插入表格

俱樂部正在新增一個新設施 - 水療中心。我們需要將其加入設施表格中。使用以下值：facid：9，名稱：'Spa'，會員成本：20，訪客成本：30，初始支出：100000，每月維護費：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'，會員成本：20，訪客成本：30，初始支出：100000，每月維護費：800。

facid：10，名稱：'Squash Court 2'，會員成本：3.5，訪客成本：17.5，初始支出：5000，每月維護費：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'，會員成本：20，訪客成本：30，初始支出：100000，每月維護費：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 和月份排序。當計算所有月份和所有 facid 的聚合值時，在月份和 facid 列中傳回 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()))

查詢範例

模型定義

綱要建立

基本練習

擷取所有內容

從資料表擷取特定欄位

控制擷取的列

控制擷取的列 - 第 2 部分

基本字串搜尋

比對多個可能的值

將結果分類到群組

處理日期

移除重複項目，並排序結果

合併多個查詢的結果

簡單的彙總

更多彙總

聯結與子查詢

擷取會員預訂的開始時間

計算網球場預訂的開始時間

列出所有推薦其他會員的會員

列出所有會員，以及他們的推薦人

列出所有使用過網球場的會員

列出費用高昂的預訂

列出所有會員，以及他們的推薦人，不使用聯結。

使用子查詢列出費用高昂的預訂

修改資料

將一些資料插入表格

將多行資料插入表格

將計算出的資料插入表格

更新一些現有資料

同時更新多行和多列

根據另一列的內容更新一列

刪除所有預訂

從 cd.members 表格中刪除一個會員

根據子查詢刪除

聚合

計算設施數量

計算昂貴設施的數量

計算每位會員的推薦次數。

列出每個設施預訂的總時段數

列出指定月份每個設施預訂的總時段數

列出每月每個設施預訂的總時段數

找出至少預訂過一次的會員人數

列出預訂時段數超過 1000 的設施

找出每個設施的總收入

找出總收入低於 1000 的設施

輸出預訂時段數最多的設施 ID

列出每月每個設施預訂的總時段數，第 2 部分

列出每個指定設施預訂的總時數

列出每位會員在 2012 年 9 月 1 日之後的首次預訂

產生一個會員姓名列表，每列包含會員總數

產生一個編號的會員列表

再次輸出預訂時段數最多的設施 ID

按（四捨五入的）使用時數對會員進行排名

找出收入最高的三個設施

按價值對設施進行分類

遞迴

找出會員 ID 27 的向上推薦鏈