Sorting groups by numerical value

[njcstreet]

Hi,

We have a requirement to sort groups of data by a numerical value. Take the example table defined below:

DROP TABLE IF EXISTS sales;
CREATE TABLE sales
(
       product String,
       sub_product String,
       sales_amount Float64
)
ENGINE = MergeTree
ORDER BY product;

INSERT INTO sales
VALUES
('Product1', 'SubProductA', 300001),
('Product1', 'SubProductB', 200000),
('Product1', 'SubProductC', 100000),
('Product1', 'SubProductD', 50000),
('Product2', 'SubProductE', 50000),
('Product2', 'SubProductF', 300000),
('Product2', 'SubProductG', 1000000),
('Product2', 'SubProductG', 200000),
('Product2', 'SubProductI', 100000),
('Product3', 'SubProductK', 400),
('Product3', 'SubProductL', 500)

What we need is a query which will place the product with the highest individual value at the top, followed by the sub-product with the highest value at the top, with all product / sub-product groups being kept together.

We would like to end up with a table like the one below, where:

• Product 2 comes first, since it contains the highest individual sales amount, followed by product 1 and then product 3. All elements of Product 2 are kept together at the top of the table, even though some items for Product 2 have a lower value than those of other products.
• SubProduct G comes first since it contains the highest sales amount. Again the two SubProductG records are kept together even though the second row has a lower value than some other sub-products - we want to maintain the grouping.

<style> </style>

product	sub_product	sales_amount
Product2	SubProductG	1,000,000
Product2	SubProductG	200,000
Product2	SubProductF	300,000
Product2	SubProductI	100,000
Product2	SubProductE	50,000
Product1	SubProductA	300,001
Product1	SubProductB	200,000
Product1	SubProductC	100,000
Product1	SubProductD	50,000
Product3	SubProductL	500
Product3	SubProductK	400

Thanks for any help you can provide.

[den-crane]

https://fiddle.clickhouse.com/83485ab6-6809-42eb-829f-fb33ab7afd0f

select product, sub_product, sales_amount
from  
  (
  select 
    product,
    arrayReverseSort(i->i.2, groupArray((sub_product, sales_amount))) ga,
    max(sales_amount) msa
  from sales
  group by product
  ) array join ga.1 as sub_product, 
               ga.2 as sales_amount, 
               arrayEnumerateDense(ga.1) as sub_product_rank 
order by msa desc, sub_product_rank, sales_amount desc
format PrettyCompactMonoBlock;

    +-product--+-sub_product-+-sales_amount-+
 1. | Product2 | SubProductG |      1000000 |
 2. | Product2 | SubProductG |       200000 |
 3. | Product2 | SubProductF |       300000 |
 4. | Product2 | SubProductI |       100000 |
 5. | Product2 | SubProductE |        50000 |
 6. | Product1 | SubProductA |       300001 |
 7. | Product1 | SubProductB |       200000 |
 8. | Product1 | SubProductC |       100000 |
 9. | Product1 | SubProductD |        50000 |
10. | Product3 | SubProductL |          500 |
11. | Product3 | SubProductK |          400 |
    +----------+-------------+--------------+

[den-crane]

gpt4 https://fiddle.clickhouse.com/1b477f44-cf43-492d-8cb3-e84344ea3552

Prompt: try window functions and rank

ChatGPT:

Using window functions and ranking is a great way to handle this task efficiently in ClickHouse. We can compute rankings based on the maximum sales amount for each product and sub-product, and then use these rankings to order the results. Here’s how you can structure the query using window functions:

SELECT 
    product,
    sub_product,
    sales_amount
FROM 
    (
        SELECT 
            product,
            sub_product,
            sales_amount,
            -- Calculate the rank of products by their maximum sales amount
            rank() OVER (ORDER BY max_sales_product DESC) AS product_rank,
            -- Calculate the rank of sub-products within each product
            rank() OVER (PARTITION BY product ORDER BY max_sales_subproduct DESC) AS sub_product_rank
        FROM 
            (
                SELECT 
                    product,
                    sub_product,
                    sales_amount,
                    -- Maximum sales amount for each product
                    max(sales_amount) OVER (PARTITION BY product) AS max_sales_product,
                    -- Maximum sales amount for each sub-product within each product
                    max(sales_amount) OVER (PARTITION BY product, sub_product) AS max_sales_subproduct
                FROM 
                    sales
            ) AS ranked_sales
    ) AS final_ranked_sales
ORDER BY 
    product_rank,
    sub_product_rank,
    sales_amount DESC;

Explanation:

Innermost Subquery: This subquery calculates the maximum sales for each product and for each sub-product within each product using the window function max() with appropriate PARTITION BY clauses.

Middle Subquery: Utilizing the results from the innermost subquery, it applies another layer of window functions to calculate rankings:
product_rank is calculated with a window function rank() that orders rows based on the maximum sales of each product (max_sales_product).
sub_product_rank is computed with rank() partitioned by product, thus maintaining product grouping and ordering sub-products within those groups based on their maximum sales.

Outer Query: Selects the desired columns and orders the results based on product_rank and sub_product_rank, ensuring that products and sub-products are sorted as specified. Additionally, sales_amount DESC ensures correct ordering for entries with the same sub-product.

This approach should generate the table as you described, correctly sorted by the sales amount criteria both at the product and sub-product levels, utilizing the power of window functions in ClickHouse.

https://chat.openai.com/share/d68b80b5-1a04-47a2-b588-9742c4f5213c

[njcstreet]

Thanks very much for this!