# 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