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