Блог

Есть ли способ в mysql8.0 установить ограничение в JSON_ARRAYAGG or GROUP_CONCAT ? Например:

 WITH season AS (
    select 'DAL'  as team, 4 as wins, 2000 as season UNION
    select 'DAL'  as team, 10 as wins, 2001 as season UNION
    select 'DAL'  as team, 9 as wins, 2002 as season UNION
    select 'GB'  as team, 2 as wins, 2000 as season UNION
    select 'GB'  as team, 3 as wins, 2001 as season UNION
    select 'GB'  as team, 4 as wins, 2002 as season
) SELECT
    team,
    GROUP_CONCAT(wins order by season desc separator ' ') wins_str,
    JSON_ARRAYAGG(wins) wins_arr
FROM season
GROUP BY team;
 

Например, как я мог бы получить только первые 5 wins в двух вышеупомянутых полях? Например, я хочу сделать что-то вроде того, что вы можете сделать в BigQuery STRING_AGG , где он принимает a LIMIT .

Самый простой метод — это оконные функции:

 SELECT
    team,
    GROUP_CONCAT(wins ORDER BY season DESC SEPARATOR ' ') wins,
    JSON_ARRAYAGG(wins)
FROM (
    SELECT s.*,
    ROW_NUMBER() OVER (
        PARTITION BY team ORDER BY seasons DESC
    ) AS seqnum
    FROM seasons s
) s
WHERE seqnum <= 5
GROUP BY team