cwind-tableau 0.1.0

A Python pivot table DSL — build aggregate queries with a chainable API, generate and execute SQL via DuckDB

cwind-tableau

A Python pivot table DSL — build aggregate queries with a chainable API, generate and execute SQL.

English | 简体中文

---

Installation

pip install cwind-tableau

Requires Python >= 3.10.

Quick Start

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

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

Built-in Aggregations

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

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

Date Extraction (.dt accessor)

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:

# 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

# 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