# cwind-tableau **Repository Path**: carson_add/cwind-tableau ## Basic Information - **Project Name**: cwind-tableau - **Description**: No description available - **Primary Language**: Unknown - **License**: MIT - **Default Branch**: main - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 1 - **Created**: 2026-07-27 - **Last Updated**: 2026-08-17 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README # cwind-tableau
**A Python Tableau-style query DSL — build aggregate queries with a chainable API, generate and execute SQL.** English | [简体中文](./README.zh-CN.md)
--- ## Installation ```bash pip install cwind-tableau ``` Requires Python >= 3.10. ## Quick Start ```python import cwind_tableau as tab # Reference columns and apply aggregations total_sales = tab.col("amount").sum() avg_sales = tab.col("amount").avg().alias("avg_amount") # Create a view with dimensions and aggregations view = tab.view( table="sales_table", dims=["product", "region"], aggs=[total_sales, avg_sales], ) # Generate SQL print(view.to_sql()) # SELECT product, region, SUM(amount) AS "SUM(amount)", AVG(amount) AS "avg_amount" # FROM sales_table # GROUP BY product, region # Execute with DuckDB result = view.to_query() ``` ## Features ### Column Reference ```python tab.col("amount") # Refer to a column tab.col("order_date") # Refer to a date column ``` ### Built-in Aggregations ```python tab.col("amount").sum() # Sum tab.col("amount").avg() # Average tab.col("amount").count() # Count tab.col("amount").count_distinct() # Distinct count tab.col("amount").min() # Minimum tab.col("amount").max() # Maximum tab.col("amount").median() # Median tab.col("amount").std() # Standard deviation tab.col("x").custom("MY_FUNC") # Custom aggregation function (simple mode) tab.col("x").custom("MY_FUNC({col}, {b}, {c})", b=1, c='xxx') # Template mode: MY_FUNC(x, 1, 'xxx') ``` ### Custom Aliases ```python tab.col("amount").sum().alias("total_sales") # Aggregation alias tab.col("order_date").dt.year().alias("year") # Dimension alias ``` ### Date Extraction (`.dt` accessor) ```python tab.col("order_date").dt.year() # Year tab.col("order_date").dt.month() # Month tab.col("order_date").dt.day() # Day tab.col("order_date").dt.quarter() # Quarter tab.col("order_date").dt.week() # Week number tab.col("order_date").dt.month_name() # Month name (e.g. "January") ``` ### Window Dimensions (`.fixed()`) Turn an aggregation into a window function dimension: ```python # First order date per customer — used as a dimension, not an aggregation tab.col("order_date").min().fixed("customer_id").alias("first_order_date") # Multi-column partition tab.col("order_date").min().fixed(["customer_id", "product"]) ``` ### INCLUDE / EXCLUDE LOD Dimensions Create dimensions based on Level of Detail (LOD) expressions: ```python # INCLUDE: partition by view_dimensions ∪ product tab.col("amount").sum().include("product").alias("amount_by_product") # EXCLUDE: partition by view_dimensions − region tab.col("amount").sum().exclude("region").alias("amount_excl_region") # Multi-column # tab.col("amount").sum().include(["product", "city"]) # tab.col("amount").sum().exclude(["region", "category"]) ``` | Method | LOD Type | Partition Columns | |--------|----------|-------------------| | `.fixed(cols)` | FIXED | cols only, ignoring view dimensions | | `.include(cols)` | INCLUDE | view_dimensions ∪ cols | | `.exclude(cols)` | EXCLUDE | view_dimensions − cols | ### LOD as Measure Apply aggregations on LOD dimensions to create LOD measures (e.g. `SUM({FIXED ...})`): ```python # FIXED as measure — SUM({FIXED [product] : SUM([amount])}) tab.col("amount").sum().fixed("product").sum().alias("product_total") # INCLUDE as measure — AVG({INCLUDE [product] : SUM([amount])}) tab.col("amount").sum().include("product").avg().alias("avg_by_product") # EXCLUDE as measure — MAX({EXCLUDE [region] : SUM([amount])}) tab.col("amount").sum().exclude("region").max().alias("max_excl_region") # Use in a View view = tab.view( table="sales", dims=["region"], aggs=[ tab.col("amount").sum().alias("region_sales"), tab.col("amount").sum().fixed("product").sum().alias("product_total"), ], ) ``` ### Compound Measure (Arithmetic Between Aggregations) Perform arithmetic operations (`+`, `-`, `*`, `/`) between aggregation expressions. The engine automatically decomposes the expression into atoms, flattens them independently, and assembles the result via LEFT JOIN. ```python # Ratio: SUM(revenue) / SUM(cost) profit_ratio = (tab.col("revenue").sum() / tab.col("cost").sum()).alias("ratio") # Profit: SUM(revenue) - SUM(cost) profit = (tab.col("revenue").sum() - tab.col("cost").sum()).alias("profit") # Margin %: (SUM(revenue) - SUM(cost)) * 100 / SUM(revenue) margin = ( (tab.col("revenue").sum() - tab.col("cost").sum()) * 100 / tab.col("revenue").sum() ).alias("margin_pct") # With LOD measure: SUM(amount) / SUM({FIXED [product] : SUM(amount)}) ratio = ( tab.col("amount").sum() / tab.col("amount").sum().fixed("product").sum().alias("prod_total") ).alias("ratio") # Reverse arithmetic: scalar on the left remaining = (2000 - tab.col("amount").sum()).alias("remaining") # Use in a View view = tab.view( table="sales", dims=["region"], aggs=[profit_ratio, profit], ) ``` See [examples/10_compound_measures.py](./examples/10_compound_measures.py) for complete usage. ### Arithmetic Expressions on Columns ```python # Calculate amount = price × quantity, then sum (tab.col("price") * tab.col("quantity")).sum() ``` ### CASE WHEN Conditional Expressions Build `CASE WHEN` conditional expressions via `tab.if_()`, usable as dimensions: ```python # Simple classification c = tab.if_(tab.col("amount") > 500, "big").else_("small").alias("order_size") # Multi-branch classification c = ( tab.if_(tab.col("amount") > 500, "high value") .else_if_(tab.col("amount") > 200, "medium value") .else_("low value") .alias("value_category") ) # Use in a View view = tab.view( table="sales", dims=["region", c], aggs=[tab.col("amount").sum().alias("total")], ) # Combined with window dimensions (FixedDim) daily_profit = tab.col("profit").sum().fixed("order_date").alias("daily_profit") c2 = ( tab.if_(daily_profit > 2000, "highly profitable") .else_if_(daily_profit < 0, "unprofitable") .else_("profitable") .alias("daily_category") ) ``` Supported operators: `>`, `<`, `>=`, `<=`. Values support `int`, `float`, `str`, `Column`. ## API Reference ### `tab.col(name: str) -> Column` Create a column reference. Returns a `Column` object with aggregation and date extraction methods. ### `tab.view(table: str, dims: list, aggs: list) -> View` Create a pivot view. | Parameter | Type | Description | |-----------|------|-------------| | `table` | `str` | Source table name | | `dims` | `list[str \| Dimension]` | Dimension columns for GROUP BY | | `aggs` | `list[Aggregation]` | Aggregation expressions for SELECT | ### `View.to_sql() -> str` Generate the SQL string (DuckDB dialect). ### `View.to_query(con=None) -> duckdb.DuckDBPyResult` Execute the query via DuckDB. - Without `con`: uses `duckdb.query()` on the default connection - With `con`: uses `con.query()` on the provided connection ## Architecture ``` cwind-tableau/ ├── src/cwind_tableau/ │ ├── api/ # Facade: tab.col, tab.view │ ├── core/ # Orchestration: View, SQL generation │ └── models/ # Entities: Column, Aggregation, Dimension ├── tests/ ├── docs/ └── examples/ ``` ## Design Principles - **Reusable aggregations**: Aggregation expressions can be defined as variables and reused across queries - **Default aliases**: Auto-generated aliases (e.g. `"SUM(amount)"`) when `.alias()` is not used - **Chainable API**: All operations via method chaining — consistent and readable - **Expressions are objects**: Any intermediate result (column, aggregation, dimension) is a Python object that can be stored, passed, and composed ## Dependencies - **DuckDB** — SQL execution engine - **sqlglot** — SQL generation and future dialect transpilation ## License MIT