Metadata-Version: 2.4
Name: cwind-tableau
Version: 0.2.0
Summary: A Python Tableau-style query DSL — build aggregate queries with a chainable API, generate and execute SQL via DuckDB
Author: CrystalWindSnake
Author-email: CrystalWindSnake <568166495@qq.com>
License-Expression: MIT
License-File: LICENSE
Requires-Dist: duckdb>=1.5.5
Requires-Dist: sqlglot>=30.13.0
Requires-Python: >=3.10
Description-Content-Type: text/markdown

# cwind-tableau

<div align="center">

**A Python Tableau-style query DSL — build aggregate queries with a chainable API, generate and execute SQL.**

English | [简体中文](./README.zh-CN.md)

</div>

---

## 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"),
    ],
)
```

### Arithmetic Expressions

```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