Aggregations and Query Helpers

def count(gen=None):
    """Count entities or values in query results.
    
    Args:
        gen: Generator expression or None for counting all results
        
    Returns:
        int: Count of matching entities/values
        
    Usage:
        count(p for p in Person if p.age > 18)
        count()  # Count all in current query context
    """

def sum(gen):
    """Calculate sum of numeric values.
    
    Args:
        gen: Generator expression yielding numeric values
        
    Returns:
        Numeric sum of values
        
    Usage:
        sum(o.total for o in Order if o.date >= start_date)
    """

def min(gen):
    """Find minimum value.
    
    Args:
        gen: Generator expression yielding comparable values
        
    Returns:
        Minimum value from results
        
    Usage:
        min(p.age for p in Person)
    """

def max(gen):
    """Find maximum value.
    
    Args:
        gen: Generator expression yielding comparable values
        
    Returns:
        Maximum value from results
        
    Usage:
        max(o.total for o in Order)
    """

def avg(gen):
    """Calculate average of numeric values.
    
    Args:
        gen: Generator expression yielding numeric values
        
    Returns:
        float: Average of values
        
    Usage:
        avg(p.age for p in Person if p.city == "NYC")
    """

def group_concat(gen, sep=','):
    """Concatenate grouped values into strings.
    
    Args:
        gen: Generator expression yielding string values
        sep: Separator string (default: ',')
        
    Returns:
        str: Concatenated string values
        
    Usage:
        group_concat(p.name for p in team.members, sep=', ')
    """

def distinct(gen):
    """Eliminate duplicate values from query results.
    
    Args:
        gen: Generator expression
        
    Returns:
        Query with distinct results only
        
    Usage:
        distinct(p.city for p in Person)
    """

def desc(attr):
    """Specify descending sort order for ORDER BY clauses.
    
    Args:
        attr: Entity attribute to sort by
        
    Returns:
        Descending sort specification
        
    Usage:
        select(p for p in Person).order_by(desc(Person.age))
    """

def JOIN(condition):
    """Explicit JOIN specification for complex queries.
    
    Args:
        condition: Join condition expression
        
    Returns:
        JOIN specification for query
        
    Usage:
        select((p, c) for p in Person 
               for c in Company if JOIN(p.company == c))
    """

def between(x, a, b):
    """BETWEEN operator for range queries.
    
    Args:
        x: Value to test
        a: Lower bound (inclusive)
        b: Upper bound (inclusive)
        
    Returns:
        bool: True if a <= x <= b
        
    Usage:
        select(p for p in Person if between(p.age, 18, 65))
    """

def concat(*args):
    """String concatenation function for queries.
    
    Args:
        *args: String expressions to concatenate
        
    Returns:
        str: Concatenated string
        
    Usage:
        select(concat(p.first_name, ' ', p.last_name) for p in Person)
    """

def coalesce(*args):
    """COALESCE function for NULL value handling.
    
    Args:
        *args: Expressions to evaluate (returns first non-NULL)
        
    Returns:
        First non-NULL argument value
        
    Usage:
        select(coalesce(p.nickname, p.first_name) for p in Person)
    """

from pony.orm import *

with db_session:
    # Count operations
    total_users = count(p for p in Person)
    adult_count = count(p for p in Person if p.age >= 18)
    
    # Sum operations
    total_revenue = sum(o.total for o in Order)
    monthly_sales = sum(o.total for o in Order 
                       if o.date.month == datetime.now().month)
    
    # Min/Max operations
    youngest_age = min(p.age for p in Person)
    oldest_age = max(p.age for p in Person)
    highest_order = max(o.total for o in Order)
    
    # Average calculations
    average_age = avg(p.age for p in Person)
    average_order_value = avg(o.total for o in Order if o.total > 0)
    
    print(f"Users: {total_users}, Adults: {adult_count}")
    print(f"Age range: {youngest_age} - {oldest_age}, Average: {average_age}")
    print(f"Revenue: ${total_revenue}, Average order: ${average_order_value}")

with db_session:
    # Group by with aggregations
    city_populations = select((p.city, count()) for p in Person).group_by(Person.city)
    
    # Multiple aggregations per group
    order_stats = select(
        (o.customer.name, count(), sum(o.total), avg(o.total))
        for o in Order
    ).group_by(Order.customer)
    
    # Group concat for string aggregation
    team_members = select(
        (t.name, group_concat(p.name, sep=', '))
        for t in Team for p in t.members
    ).group_by(Team.name)
    
    # Conditional aggregations
    sales_by_region = select(
        (s.region, 
         sum(s.amount for s in Sale if s.type == 'online'),
         sum(s.amount for s in Sale if s.type == 'retail'))
        for s in Sale
    ).group_by(Sale.region)

with db_session:
    # Descending sort
    top_earners = select(e for e in Employee).order_by(desc(Employee.salary))
    latest_orders = select(o for o in Order).order_by(desc(Order.date))
    
    # Multiple sort criteria
    sorted_people = select(p for p in Person).order_by(
        Person.last_name, Person.first_name, desc(Person.age)
    )
    
    # Range queries with between
    millennials = select(p for p in Person 
                        if between(p.birth_year, 1981, 1996))
    
    moderate_orders = select(o for o in Order 
                           if between(o.total, 50.0, 500.0))
    
    # String concatenation in queries
    full_names = select(concat(p.first_name, ' ', p.last_name) 
                       for p in Person)
    
    formatted_addresses = select(
        concat(a.street, ', ', a.city, ', ', a.state, ' ', a.zip_code)
        for a in Address
    )
    
    # NULL handling with coalesce
    display_names = select(coalesce(p.nickname, p.first_name, 'Anonymous') 
                          for p in Person)
    
    contact_info = select(coalesce(p.mobile_phone, p.home_phone, p.work_phone)
                         for p in Person)

with db_session:
    # Statistical analysis
    age_stats = {
        'count': count(p for p in Person),
        'min': min(p.age for p in Person),
        'max': max(p.age for p in Person),
        'avg': avg(p.age for p in Person),
        'adults': count(p for p in Person if p.age >= 18)
    }
    
    # Sales analysis by period
    monthly_metrics = select(
        (o.date.month,
         count(),  # Order count
         sum(o.total),  # Total revenue
         avg(o.total),  # Average order value
         min(o.total),  # Smallest order
         max(o.total))  # Largest order
        for o in Order
        if o.date.year == 2023
    ).group_by(o.date.month)
    
    # Customer segmentation
    customer_segments = select(
        (c.id, c.name,
         count(c.orders),  # Order frequency
         sum(o.total for o in c.orders),  # Total spent
         avg(o.total for o in c.orders),  # Average order value
         max(o.date for o in c.orders))   # Last order date
        for c in Customer
    )
    
    # Product performance analysis
    product_performance = select(
        (p.category,
         count(distinct(oi.order_id for oi in p.order_items)),  # Orders with product
         sum(oi.quantity for oi in p.order_items),  # Total quantity sold
         sum(oi.price * oi.quantity for oi in p.order_items))  # Total revenue
        for p in Product
    ).group_by(Product.category)

with db_session:
    # Distinct values
    unique_cities = distinct(p.city for p in Person if p.city)
    unique_domains = distinct(p.email.split('@')[1] for p in Person)
    
    # Distinct with aggregations
    cities_with_counts = select(
        (city, count()) for city in distinct(p.city for p in Person)
    ).group_by(city)
    
    # Complex distinct operations
    active_customer_cities = distinct(
        c.address.city for c in Customer 
        if exists(o for o in c.orders if o.date >= recent_date)
    )
    
    # Distinct combinations
    name_age_combinations = distinct(
        (p.name, p.age) for p in Person
    )

with db_session:
    # Ranking within groups (using subqueries)
    top_salespeople_per_region = select(
        (s.name, s.region, s.total_sales)
        for s in Salesperson
        if s.total_sales == max(sp.total_sales for sp in Salesperson 
                               if sp.region == s.region)
    )
    
    # Running totals (simplified approach)
    cumulative_sales = []
    running_total = 0
    for order in select(o for o in Order).order_by(Order.date):
        running_total += order.total
        cumulative_sales.append((order.date, order.total, running_total))
    
    # Percentile calculations
    order_totals = list(select(o.total for o in Order).order_by(Order.total))
    median_order = order_totals[len(order_totals) // 2]
    q1_order = order_totals[len(order_totals) // 4]
    q3_order = order_totals[3 * len(order_totals) // 4]
    
    # Growth rate calculations
    monthly_growth = []
    monthly_revenues = list(select(
        (o.date.month, sum(o.total))
        for o in Order if o.date.year == 2023
    ).group_by(o.date.month).order_by(1))
    
    for i in range(1, len(monthly_revenues)):
        prev_month = monthly_revenues[i-1][1]
        curr_month = monthly_revenues[i][1]
        growth_rate = ((curr_month - prev_month) / prev_month) * 100
        monthly_growth.append((monthly_revenues[i][0], growth_rate))

with db_session:
    # Efficient counting vs fetching all
    # Good: Count without fetching
    user_count = count(p for p in Person if p.active)
    
    # Bad: Fetch all then count in Python
    # all_users = list(select(p for p in Person if p.active))
    # user_count = len(all_users)
    
    # Efficient aggregation queries
    summary_stats = select(
        (count(), sum(o.total), avg(o.total), min(o.total), max(o.total))
        for o in Order if o.date >= start_date
    ).get()  # Single query for multiple aggregations
    
    # Batch aggregations for different groups
    regional_stats = select(
        (c.region, count(), sum(o.total for o in c.orders))
        for c in Customer
    ).group_by(Customer.region)
    
    # Use EXISTS for existence checks instead of counting
    has_recent_orders = exists(o for o in Order 
                              if o.date >= recent_date)
    
    # Efficient distinct counting
    unique_customer_count = count(distinct(o.customer_id for o in Order))