Function get_sql_stats

marimo/_data/sql_summaries.py:10–129

Get stats of a column in a SQL table.

(
    table_name: str, column_name: str, column_type: DataType
)

def get_sql_stats(
    table_name: str, column_name: str, column_type: DataType
) -> ColumnStats:
    """
    Get stats of a column in a SQL table.
    """

    # Prepare the stats query based on the column type
    if column_type in ("integer", "number"):
        stats_query = f"""
        SELECT
            COUNT(*) as count,
            COUNT(DISTINCT "{column_name}") as unique,
            SUM(CASE WHEN "{column_name}" IS NULL THEN 1 ELSE 0 END) as null_count,
            MIN("{column_name}") as min,
            MAX("{column_name}") as max,
            AVG("{column_name}") as mean,
            PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY "{column_name}") as median,
            STDDEV("{column_name}") as std,
            PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY "{column_name}") as p5,
            PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY "{column_name}") as p25,
            PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY "{column_name}") as p75,
            PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY "{column_name}") as p95
        FROM {table_name}
        """
    elif (
        column_type == "date"
        or column_type == "datetime"
        or column_type == "time"
    ):
        stats_query = f"""
        SELECT
            COUNT(*) as count,
            COUNT(DISTINCT "{column_name}") as unique,
            SUM(CASE WHEN "{column_name}" IS NULL THEN 1 ELSE 0 END) as null_count,
            MIN("{column_name}") as min,
            MAX("{column_name}") as max
        FROM {table_name}
        """
    elif column_type == "boolean":
        stats_query = f"""
        SELECT
            COUNT(*) as count,
            COUNT(DISTINCT "{column_name}") as unique,
            SUM(CASE WHEN "{column_name}" IS NULL THEN 1 ELSE 0 END) as null_count,
            SUM(CASE WHEN "{column_name}" = TRUE THEN 1 ELSE 0 END) as true_count,
            SUM(CASE WHEN "{column_name}" = FALSE THEN 1 ELSE 0 END) as false_count
        FROM {table_name}
        """
    else:
        stats_query = f"""
        SELECT
            COUNT(*) as count,
            COUNT(DISTINCT "{column_name}") as unique,
            SUM(CASE WHEN "{column_name}" IS NULL THEN 1 ELSE 0 END) as null_count
        FROM {table_name}
        """