pathwaycom / pathwaycom/pathway

Add syntax to be able to reduce over multiple windows

Open
#19 3 comments 0 reactions 1 assignee View on GitHub

@izulin is already working on this.

Since Mar 17, 2024.

enhancement
Dominant language
Python
Stars
62.3k
Forks
1.7k
PR merge metrics
No merged PRs in 30d

Description

I wonder if it's possible to have some additional syntax to make working with many windows possible?

For example, with ClickHouse SQL it's possible to work across many windows like this:

WITH sales AS (
    -- Your dataset source here
)
SELECT
    date,
    salesperson_id,
    region,
    amount,
    product_id,
    ROW_NUMBER() OVER w_region_amount AS row_number_region,
    RANK() OVER w_salesperson_amount AS rank_salesperson,
    DENSE_RANK() OVER w_product_amount AS dense_rank_product,
    SUM(amount) OVER w_region AS sum_sales_region,
    AVG(amount) OVER w_region AS avg_sales_region,
    MAX(amount) OVER w_salesperson AS max_sales_salesperson,
    MIN(amount) OVER w_product AS min_sales_product,
    LEAD(amount, 1) OVER w_salesperson_date AS lead_amount,
    LAG(amount, 1) OVER w_salesperson_date AS lag_amount,
    NTILE(10) OVER w_global_amount AS decile_rank_by_amount,
    FIRST_VALUE(salesperson_id) OVER w_region_amount AS top_salesperson_region,
    LAST_VALUE(salesperson_id) OVER w_region_amount_rows AS last_salesperson_region,
    COUNT(*) OVER w_region AS count_sales_region,
    PERCENT_RANK() OVER w_region_amount AS percent_rank_region,
    CUME_DIST() OVER w_region_amount AS cume_dist_region
FROM sales
WINDOW
    w_region AS (PARTITION BY region),
    w_salesperson AS (PARTITION BY salesperson_id),
    w_product AS (PARTITION BY product_id),
    w_region_amount AS (PARTITION BY region ORDER BY amount DESC),
    w_salesperson_amount AS (PARTITION BY salesperson_id ORDER BY amount DESC),
    w_product_amount AS (PARTITION BY product_id ORDER BY amount DESC),
    w_salesperson_date AS (PARTITION BY salesperson_id ORDER BY date),
    w_global_amount AS (ORDER BY amount DESC),
    w_region_amount_rows AS (PARTITION BY region ORDER BY amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY region, amount DESC;

With Pathway, I found myself having to define each window as a separate table, and then joining them back together. The resulting code was quite verbose.

Unless I am missing something and it's possible to do it succinctly with Pathway?

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.