Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE post
    (ID int, TAG_ID varchar2(8))
;

INSERT ALL 
    INTO post (ID, TAG_ID)
         VALUES (1, '1; 2;3')
    INTO post (ID, TAG_ID)
         VALUES (2, '1; 2')
    INTO post (ID, TAG_ID)
         VALUES (3, '3')
    INTO post (ID, TAG_ID)
         VALUES (4, '1; 3')
    INTO post (ID, TAG_ID)
         VALUES (5, '2; 3')
SELECT * FROM dual
;
5 rows affected

CREATE TABLE tag
    (ID int, NAME varchar2(5))
;

INSERT ALL 
    INTO tag (ID, NAME)
         VALUES (1, 'TAG_1')
    INTO tag (ID, NAME)
         VALUES (2, 'TAG_2')
    INTO tag (ID, NAME)
         VALUES (3, 'TAG_3')
SELECT * FROM dual
;
3 rows affected

    with t as  (
                select     regexp_substr(TAG_ID,'\d',1,level) as ID
                          ,count(*)                           as NUB_of_POSTS
                from       post  
                connect by ID = prior ID and level <= regexp_count(TAG_ID,'\d') and sys_guid() <> prior sys_guid()
                group by   regexp_substr(TAG_ID,'\d',1,level)
               )
    select     NAME
              ,NUB_of_POSTS
    from       t join tag using(ID)


NAME
NUB_OF_POSTS


TAG_1
3

TAG_2
3

TAG_3
4