10. 匯總

你可以使用以下五個聚合函數(shù)進行聲明性查詢：sum()、count()盖腿、min()绎谦、max()、avg()和group_concat()拯刁。

明明是6個嘛......不過最后那個不應(yīng)該算是聚合函數(shù)，只用來進行字符串拼接——gthank

讓我們來看一些使用這些函數(shù)進行簡單查詢的例子：

101組的學(xué)生的總GPA：

sum(sum(s.gpa for s in Student if s.group.number ==101)

GPA超過3的學(xué)生人數(shù)：

Count(s for s in Student if s.gpa > 3)

學(xué)習(xí)哲學(xué)逝段、按字母順序排序的學(xué)生的第一個名字：

min(s.name for s in Student if "Philosophy" in s.course.name)

101組中最年輕的學(xué)生的出生日期：

max(s.dob for s in Student if s.group.number ==101)

文檔中說要返回101班中最年輕的學(xué)生的出生日期垛玻，但SQL語句返回的應(yīng)該是最大的出生日期——gthank存疑

44系的平均GPA：

avg(s.gpa for s in Student if s.group.dept.number ==44)

用逗號連接101組的學(xué)生名字：

group_concat(s.name for s in Student if s.group.number ==101)

盡管 Python 已經(jīng)有了標(biāo)準(zhǔn)的函數(shù) sum()、count()奶躯、min()和 max()帚桩，但 Pony 還是添加了自己的函數(shù)，并以相同的名字命名嘹黔。
此外账嚎，Pony 還添加了自己的 avg() 和 group_concat() 函數(shù)。
這些函數(shù)在pony.orm模塊中實現(xiàn)儡蔓，它們可以從那里 "by the star"或其名稱導(dǎo)入郭蕉。

在 Pony 中實現(xiàn)的函數(shù)擴展了 Python 中標(biāo)準(zhǔn)函數(shù)的行為，因此浙值，如果在程序中以標(biāo)準(zhǔn)的方式使用這些函數(shù)恳不，導(dǎo)入不會影響它們的行為。
但它還允許在函數(shù)內(nèi)部指定一個聲明性的查詢开呐。

如果忘記了從 pony.orm 包中導(dǎo)入這些函數(shù)，那么在使用 Python 標(biāo)準(zhǔn)函數(shù) sum()规求、count()筐付、min()和 max()時，會出現(xiàn)一個錯誤阻肿，并以聲明性查詢作為參數(shù)瓦戚。

TypeError: Use a declarative query in order to iterate over entity

聚類函數(shù)也可以在查詢中使用，例如丛塌，如果你不僅需要查找組中最年輕的學(xué)生的出生日期较解，還需要查找學(xué)生本人，你可以這么查詢：

select(s for s in Student if s.group.number == 101
           and s.dob == max(s.dob for s in Student
                                        if s.group.number == 101))

或者赴邻，例如印衔，得到所有平均GPA高于4.5的組：

select(g for g in Group if avg(s.gpa for s in g.s in g.s students) > 4.5)

如果我們使用Pony屬性提升功能，這個查詢可以更短：

select(g for g in Group if avg(g.schools.gpa) > 4.5)

而這個查詢可以顯示文章的所有標(biāo)簽：

select((article, group_concat(article.tags)) for article in Aricle)

查詢對象聚合函數(shù)

你可以調(diào)用Query對象的聚合方法：

select(sum(s.gpa) for s in Student)

等于下面的查詢：

select(s.gpa for s in Student).sum()

下面是聚合函數(shù)的列表：

Query.avg()
Query.count()
Query.min()
Query.max()
Query.sum()
Quety.group_concat()

在一個查詢中使用多個聚合函數(shù)

SQL允許你在同一個查詢中包含多個聚合函數(shù)姥敛。

例如奸焙，我們可能想同時接收每組的最低和最高GPA。在SQL中，這樣的查詢會是這樣的与帆。

SELECT s.group_number, MIN(s.gpa), MAX(s.gpa)
FROM Student s
GROUP BY s.group_number

這個查詢將返回每個組的最低和最高GPA了赌，在Pony中，你可以使用同樣的方法玄糟。

select((s.group, min(s.gpa), max(s.gpa)) for s in Student)

計數(shù)函數(shù)

聚類查詢經(jīng)常需要計算出某物的數(shù)量勿她，下面是我們?nèi)绾蔚玫?01組中的學(xué)生人數(shù)。

count(s for s in Student if s.group.number == 101)

每組中與系部相關(guān)的學(xué)生人數(shù)44：

select((g, count(g.students)) for g in Group if g.dept.number == 44)

或者是這樣：

select((s.group, count(s)) for s in Student if s.group.dept.number == 44)

在第一個例子中阵翎，聚合函數(shù)count()接收到一個集合嫂拴，Pony會將其轉(zhuǎn)化為一個子查詢，實際上贮喧，這個子查詢會被Pony優(yōu)化筒狠，并被替換成left join。

在第二個例子中箱沦，函數(shù)count()接收的是一個單一對象辩恼，而不是一個集合，在這種情況下谓形，Pony將在SQL查詢中添加一個GROUP BY部分灶伊，并在s.group屬性上進行分組。

如果你使用count()函數(shù)沒有參數(shù)寒跳，這將被翻譯成SQL COUNT(*)聘萨，如果你指定了參數(shù)，將被翻譯成COUNT(DISTINCT列)童太。

有條件的計數(shù)

還有一種方法可以使用count()函數(shù)米辐，假設(shè)我們想得到每組的三個數(shù)字：

GPA小于3的學(xué)生數(shù)
GPA在3至4之間的學(xué)生人數(shù)；
GPA大于4的學(xué)生人數(shù)书释；

該查詢可以這樣構(gòu)造翘贮。

select((g, count(s for s in g.students if s.gpa <= 3),
           count(s for s in g.students if s.gpa > 3 and s.gpa <= 4),
           count(s for s in g.students if s.gpa > 4)) for g in Group)

雖然這個查詢可以工作，但它相當(dāng)長爆惧，而且效率不高--每個計數(shù)都會被翻譯成一個單獨的子查詢狸页。

對于這種情況，Pony提供了一個"conditional COUNT "語法扯再。

select((s.group, count(s.gpa <= 3),
                 count(s.gpa > 3 and s.gpa <= 4),
                 count(s.gpa > 4)) for s in Student)

這樣一來芍耘，我們把條件放到count()函數(shù)中，這個查詢不會有子查詢熄阻，這使得它更有效斋竞。

上面的查詢并不完全等同：如果一個組沒有學(xué)生，那么第一個查詢將把0作為count()的結(jié)果饺律，而第二個查詢根本不會選擇該組窃页。
出現(xiàn)這種情況是因為第二個查詢從表 Student 中選擇了記錄跺株，如果組中沒有學(xué)生，那么表 Student 中就沒有這個組的記錄脖卖。

如果你想得到帶0的記錄乒省，那么有效的SQL查詢應(yīng)該使用left_join()函數(shù)：

left_join((g, count(s.gpa <= 3),
       count(s.gpa > 3 and s.gpa <= 4),
       count(s.gpa > 4)) for g in Group for s in g.students)

更復(fù)雜的聚合查詢

使用Pony，你可以進行更復(fù)雜的分組畦木，例如袖扛，你可以通過屬性部分進行分組：

select((s.dob.year, avg(s.gpa)) for s in Student)

在這種情況下，出生年份不是一個獨立的屬性--它是dob屬性的一部分十籍。

你可以在聚合函數(shù)中使用表達式蛆封。

select((item.order, sum(item.price * item.quantity))
        for item in OrderItem if item.order.id == 123)

下面是另一種相同的查詢方式。

select((order, sum(order.items.price * order.items.quantity))
        for order in Order if order.id == 123)

在第二種情況下勾栗，我們使用屬性提升的概念惨篱，表達式order. items.price會產(chǎn)生一個價格數(shù)組，而order. items.quantity會產(chǎn)生一個數(shù)量數(shù)組围俘。
結(jié)果砸讳，在這個例子中，我們將得到每個訂單項目的數(shù)量之和乘以價格界牡。

使用HAVING進行查詢

SELECT語句有兩個不同的部分WHERE和HAVING簿寂。
WHERE部分用得比較多，包含將應(yīng)用于每一行的條件宿亡。

如果查詢包含聚合函數(shù)常遂，如MAX或SUM，SELECT語句也可以包含GROUP BY和HAVING部分挽荠。HAVING部分的條件是在對SQL查詢結(jié)果進行分組后應(yīng)用的克胳。

通常情況下，HAVING部分的條件總是包含聚合函數(shù)坤按，而WHERE部分的條件可能只包含子查詢中的聚合函數(shù)毯欣。

當(dāng)你寫一個包含聚合函數(shù)的查詢時，Pony 需要確定結(jié)果的 SQL 是否包含GROUP BY和 HAVING部分臭脓，以及它應(yīng)該把 Python 查詢中的每個條件放在哪里。

如果一個條件包含聚合函數(shù)腹忽，Pony 將條件放在HAVING部分来累。否則，它將把條件放在WHERE部分窘奏。

考慮一下下面的查詢嘹锁，它返回的是(Group, count_of_students)的元組。

select((s.group, count(s)) for s in Student
       if s.group.dept.number == 44 and avg(s.gpa) > 4)

在這個查詢中着裹，我們有兩個條件领猾。第一個條件是s.group.dept.number ==44，因為它不包含聚合函數(shù)，所以Pony將把這個條件放在WHERE部分摔竿；第二個條件avg(s.gpa)>4包含了聚合函數(shù)avg面粮，Pony將把這個條件放在HAVING部分。

另一個問題是Pony應(yīng)該在GROUP BY部分中添加什么列继低，根據(jù)SQL標(biāo)準(zhǔn)熬苍，任何放置在SELECT語句中的非聚合列也應(yīng)該添加到GROUP BY部分，讓我們考慮一下下面的查詢袁翁。

SELECT A, B, C, SUM(D), MAX(E), COUNT(F)
FROM T1
WHERE ...
GROUP BY ...
HAVING ...

根據(jù)SQL標(biāo)準(zhǔn)柴底，我們需要將列A、B和C包含在GROUP BY部分粱胜，因為這些列是在SELECT列表中出現(xiàn)的柄驻，并沒有用任何聚合函數(shù)包裝。

Pony正是這樣做的焙压，如果你的聚合的Pony查詢返回一個有多個表達式的元組鸿脓，任何非聚合的表達式都會被放入GROUP BY部分。

讓我們再考慮一下同樣的Pony查詢：

select((s.group, count(s)) for s in Student
       if s.group.dept.number == 44 and avg(s.gpa) > 4)

這個查詢返回元組（Group, count_of_students）冗恨，元組的第一個元素答憔，即Group實例，沒有被聚合掀抹，所以它將被放置到GROUP BY部分虐拓。

SELECT "s"."group", COUNT(DISTINCT "s"."id")
FROM "Student" "s", "Group" "group-1"
WHERE "group-1"."dept" = 44
  AND "s"."group" = "group-1"."number"
GROUP BY "s"."group"
HAVING AVG("s"."gpa") > 4

將s.group表達式放入GROUP BY部分，將條件avg(s.gpa)>4放入查詢的HAVING部分傲武。

有時蓉驹，應(yīng)該放在HAVING部分的條件中的條件包含一些非匯總列，這樣的列會被添加到GROUP BY部分揪利，因為根據(jù)SQL標(biāo)準(zhǔn)态兴，如果沒有添加到GROUP BY列表中，那么在HAVING部分內(nèi)禁止使用非匯總列疟位。

再舉一個例子：

select((item.order, item.order.total_price,
     sum(item.price * item.quantity))
     for item in OrderItem
     if item.order.total_price < sum(item.price * item.quantity))

這個查詢有如下條件： item.order.total_price < sum(item.price * item.quantity)瞻润，其中包含一個聚合函數(shù)，應(yīng)該添加到HAVING部分甜刻。

但是item.order.total_price這部分沒有聚合绍撞，因此，為了滿足SQL的要求得院，將其添加到GROUP BY部分中傻铣。

利用聚合函數(shù)排序

聚合函數(shù)可以在Query.order_by()函數(shù)內(nèi)部使用，下面是一個例子：

select((s.group, avg(s.gpa)) for s in Student).order_by(lambda s: desc(avg(s.gpa)))

另一種按聚合值排序的方法是在Query.order_by()方法中指定位置號祥绞。

select((s.group, avg(s.gpa)) for s in Student).order_by(-2)