def filter_group() -> 'ModelSelect':
"""过滤分组.
SELECT vend_id, COUNT(*) AS num_prods
FROM product
GROUP BY vend_id
HAVING COUNT(*) > 2;
"""
return (Product.select(Product.vend_id,
count().alias('num_prods')).group_by(
Product.vend_id).having(count() > 2))
def filter_row_and_group() -> 'ModelSelect':
"""过滤行和分组.
SELECT vend_id, COUNT(*) AS num_prods
FROM product
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
"""
return (Product.select(
Product.vend_id,
count().alias('num_prods')).where(Product.prod_price >= 4).group_by(
Product.vend_id).having(count() >= 2))
def group_with_count() -> 'ModelSelect':
"""分组计算行数.
SELECT vend_id, COUNT(*) AS num_prods
FROM product
GROUP BY vend_id;
"""
return (Product.select(Product.vend_id,
count().alias('num_prods')).group_by(
Product.vend_id))
def calculation() -> 'ModelSelect':
"""子查询作为计算字段.
SELECT cust_id,
cust_name,
(SELECT COUNT(*)
FROM `order`
WHERE `order`.cust_id = customer.cust_id) AS orders
FROM customer;
"""
sub = (Order.select(count()).where(Order.cust_id == Customer.cust_id))
return (Customer.select(Customer.cust_id, Customer.cust_name,
sub.alias('orders')))
def function_count() -> Tuple['ModelSelect', 'ModelSelect']:
"""行数.
SELECT COUNT(*) AS num_cust
FROM customer;
SELECT COUNT(cust_email) AS num_cust
FROM customer;
"""
q1 = (Customer.select(count().alias('num_cust')))
c2 = fn.COUNT(Customer.cust_email).alias('num_cust')
q2 = (Customer.select(c2))
return q1, q2
def multi_reduce() -> 'ModelSelect':
"""多个聚集函数.
SELECT COUNT(*) AS count,
MIN(prod_price) AS min,
MAX(prod_price) AS max,
AVG(prod_price) AS avg,
SUM(prod_price) AS sum
FROM product;
"""
return (Product.select(count().alias('count'),
fn.MIN(Product.prod_price).alias('min'),
fn.MAX(Product.prod_price).alias('max'),
fn.AVG(Product.prod_price).alias('avg'),
fn.SUM(Product.prod_price).alias('sum')))