Postgresql - How to remove last one from array_agg() in one select query?

Question

I have a special need with below table

              Table "public.skill_name"
  Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
 position | integer |           | not null |
 value    | text    |           | not null |
 id       | text    |           | not null |
 skill    | text    |           |          |
Indexes:
    "skill_name.id" UNIQUE, btree (id)
Foreign-key constraints:
    "skill_name_skill_fkey" FOREIGN KEY (skill) REFERENCES skill(id) ON DELETE SET NULL

and some sample data like below:

 position |                                         value                                         |             id              |            skill
----------+---------------------------------------------------------------------------------------+-----------------------------+-----------------------------
     1000 | Python                                                                                | ck5bxmk67101790acuf05cikujw | ck5bxmk62101789acuf7pj1qmj6
     2000 | Python Language                                                                       | ck5bxmk69101791acufih7mc6u6 | ck5bxmk62101789acuf7pj1qmj6
     3000 | Stdlib                                                                                | ck5bxmk6c101792acuflzcu2avg | ck5bxmk62101789acuf7pj1qmj6
     4000 | functools                                                                             | ck5bxmk6e101793acuf42ih0evn | ck5bxmk62101789acuf7pj1qmj6
     5000 | lru_cache                                                                             | ck5bxmk6g101794acuf690rjgzp | ck5bxmk62101789acuf7pj1qmj6
     1000 | Python                                                                                | ck5bxysvp102005acuf6unt4cb7 | ck2wk5gba044342xbyaulv17i
     2000 | Python Language                                                                       | ck5bxysvs102012acuf5862l0gx | ck2wk5gba044342xbyaulv17i
     3000 | Python Syntax                                                                         | ck5bxysvu102021acufjcmxi1ij | ck2wk5gba044342xbyaulv17i
     4000 | Classes                                                                               | ck5bxysvx102030acufbaz3kml3 | ck2wk5gba044342xbyaulv17i
     5000 | metaclasses                                                                           | ck5bxysvz102037acufa5lmbuhj | ck2wk5gba044342xbyaulv17i

The requirement is to generate a result like below(NOTE: The last one group by skill been excluded in column path)

            skill            |                                         path
-----------------------------+---------------------------------------------------------------------------------------
 ck5bxmk62101789acuf7pj1qmj6 | Python,Python Language,Stdlib,functools
 ck2wk5gba044342xbyaulv17i   | Python,Python Language,Python Syntax,Classes

I have below sql but it does not work, it complains more than one row returned by a subquery used as an expression

SELECT
    skill,
    ARRAY_REMOVE(
        ARRAY_AGG(value), 
        (
            SELECT
                skill_name.value
                FROM (
                    SELECT
                        *,
                        skill AS skill_id,
                        LAST_VALUE(position) OVER (
                        PARTITION BY skill
                        ORDER BY position
                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                        ) AS last_pos
                    FROM skill_name
                ) skill_name
                WHERE last_pos=position
                GROUP BY skill_name.value
        )
    ) as path
FROM skill_name
GROUP BY skill;

I do not know how to fix that, could any one help?

GMB · Accepted Answer · 2020-01-22 23:18:18Z

2

You could use row_number() to locate and eliminate the last value from the resultset before aggregating:

select skill, array_agg(value order by position) path
from (
    select t.*, row_number() over(partition by skill order by position desc) rn
    from mytable t
) t
where rn > 1
group by skill

answered Jan 22, 2020 at 23:18

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-01-23 03:09:02Z

1

Postgres has pretty sophisticated array functions. You don't need a subquery to do this:

select skill,
       (array_agg(value order by position))[1:count(*) - 1] as path
from t
group by skill

answered Jan 23, 2020 at 3:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

1 Comment

Keannylen Over a year ago

it's not unaccepted your answer, as I already accepted the one above about 4 hours ago, I did accepted your answer but I realized that result in unaccepted the one I already accepted, it's not fair to do that so I have to revert it back. I REALLY REALLY want to give both of you guys ACCEPTED, but I can't. It's really hard :(

Collectives™ on Stack Overflow

Postgresql - How to remove last one from array_agg() in one select query?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related