Metadata-Version: 2.4
Name: cwind-tableau
Version: 0.1.0
Summary: A Python pivot table 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 pivot table 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("销量").sum()
avg_sales = tab.col("销量").avg().alias("平均销量")

# Create a view with dimensions and aggregations
view = tab.view(
    table="销量表",
    dims=["产品", "地区"],
    aggs=[total_sales, avg_sales],
)

# Generate SQL
print(view.to_sql())
# SELECT 产品, 地区, SUM(销量) AS "SUM(销量)", AVG(销量) AS "平均销量"
# FROM 销量表
# GROUP BY 产品, 地区

# Execute with DuckDB
result = view.to_query()
```

## Features

### Column Reference

```python
tab.col("销量")  # Refer to a column
tab.col("订单日期")  # Column names support Chinese and any UTF-8 characters
```

### Built-in Aggregations

```python
tab.col("销量").sum()  # Sum
tab.col("销量").avg()  # Average
tab.col("销量").count()  # Count
tab.col("销量").count_distinct()  # Distinct count
tab.col("销量").min()  # Minimum
tab.col("销量").max()  # Maximum
tab.col("销量").median()  # Median
tab.col("销量").std()  # Standard deviation
tab.col("x").custom("MY_FUNC")  # Custom aggregation function
```

### Custom Aliases

```python
tab.col("销量").sum().alias("总销量")  # Aggregation alias
tab.col("订单日期").dt.year().alias("年")  # Dimension alias
```

### Date Extraction (`.dt` accessor)

```python
tab.col("订单日期").dt.year()  # Year
tab.col("订单日期").dt.month()  # Month
tab.col("订单日期").dt.day()  # Day
tab.col("订单日期").dt.quarter()  # Quarter
tab.col("订单日期").dt.week()  # Week number
tab.col("订单日期").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("订单日期").min().fixed("客户ID").alias("首单日期")

# Multi-column partition
tab.col("订单日期").min().fixed(["客户ID", "产品"])
```

### Arithmetic Expressions

```python
# Calculate amount = price × quantity, then sum
(tab.col("价格") * tab.col("数量")).sum()
```

## 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(销量)"`) 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