apache / apache/ossie

Add window function use cases to the TPC-DS example

Closed
#324 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
2.1k
Forks
267
Avg merge
4d 20h
Merged PRs (30d)
24

Description

## Problem

Window functions are already defined in both the [Expression Language proposal](https://docs.google.com/document/d/1Jn98kHWsnbvo1MycQBAnWirGTwRmw-DWaen7coNCYWA/edit?tab=t.0) and the [core expression language specification](https://github.com/apache/ossie/blob/main/core-spec/expression_language.md#window-functions). The specification covers ranking functions, offset functions, and window aggregations, but the canonical [`examples/tpcds_semantic_model.yaml`](https://github.com/apache/ossie/blob/main/examples/tpcds_semantic_model.yaml) currently demonstrates only regular aggregate metrics.

Without model-level examples, implementers and users do not have a concrete reference for expressing common analytical use cases, including ordering, partitioning, explicit window frames, field qualification across relationships, and the expected query grain.

## Proposed change

Add a small, representative set of validated ANSI SQL window-function use cases to `examples/tpcds_semantic_model.yaml`, using the existing TPC-DS datasets and relationships. Suggested cases:

- Running/cumulative sales through each calendar date (window aggregation)
- Sales rank by brand or store (ranking function)
- Sales change versus the previous period (offset function such as `LAG`)

For example, a cumulative sales metric could be expressed as:

```yaml
- name: cumulative_sales
expression:
dialects:
- dialect: ANSI_SQL
expression: SUM(store_sales.ss_ext_sales_price) OVER (ORDER BY date_dim.d_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
description: Running total of sales revenue through each calendar date
datatype: Decimal
```

Contributor guide

Open the contributing guide

Research direction

Start with examples/tpcds_semantic_model.yaml and compare its existing metrics with the window-functions sections in core-spec/expression_language.md. Use the existing TPC-DS datasets and relationships to add representative cumulative, ranking, and period-change cases covering ordering, partitioning, frames, and query grain. Done means the YAML contains validated ANSI_SQL examples with descriptions and datatypes.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
data
Issue type
Feature
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
70/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.