feature: LIST_AGG

[bohutang]

Summary

Syntax

Aggregate function

LIST_AGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]

Window function

LIST_AGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Example

CREATE TABLE orders (
    o_orderstatus VARCHAR,
    o_clerk VARCHAR,
    o_totalprice DECIMAL(10, 2)
);

INSERT INTO orders (o_orderstatus, o_clerk, o_totalprice) VALUES
('F', 'Clerk#0001', 550000.00),
('O', 'Clerk#0002', 600000.00),
('F', 'Clerk#0003', 530000.00),
('O', 'Clerk#0004', 540000.00),
('P', 'Clerk#0005', 560000.00),
('F', 'Clerk#0006', 450000.00), 
('P', 'Clerk#0007', 700000.00),
('O', 'Clerk#0008', 800000.00),
('F', 'Clerk#0009', 520000.01);

SELECT
    o_orderstatus,
    LIST_AGG(o_clerk, ', ') WITHIN GROUP (ORDER BY o_totalprice DESC) AS clerks_list
FROM
    orders
WHERE
    o_totalprice > 520000
GROUP BY
    o_orderstatus;

[b41sh]

The string_agg function we have implemented is the same as the list_agg, we just need to support the WITHIN GROUP and window function, and the list_agg can be used as an alias for the string_agg.

[arkzuse]

i would like to take this issue

[arkzuse]

hi I have a doubt. is WITHIN GROUP clause implemented? I couldn't find it. If not where I should implement it?

for WITHIN GROUP changes have to be made in accumulate right?

[bohutang]

@arkzuse This is developed by @KKould on https://github.com/databendlabs/databend/pull/17519. Still thanks for your contribution.