How to create an arithmetic expression with lenght of items in each row of a column using PostgreSQL-CodePudding

I have a table like this:

CREATE TABLE my_ids_table (
  id VARCHAR NOT NULL,
  price DOUBLE PRECISION NOT NULL
);

INSERT INTO my_ids_table 
    (id, price) 
VALUES 
    ('id_01, id_02', 100),
    ('id_03', 200),
    ('id_04', 300),
    ('id_05', 400),
    ('id_06, id_07, id_08', 500);

Output:

Now I'd like to reach this result below, where I split each separated value by comma, and then I calculate each price divided by the length of id items in each individual row, creating the new_id and new_price columns like this:

Is it possible in SQL? What would it be the simplest solution for a begginer student like me? I have a SQL Fiddle created here: http://sqlfiddle.com/#!15/aaf730/1

CodePudding user response：

convert delimiter separated string into row then using cte for getting desired result.

-- PostgreSQL(v11)
WITH cte as (select unnest(string_to_array(id, ', ')) new_id , id, price
FROM my_ids_table)
SELECT c.id, c.price, c.new_id, t.new_price :: decimal(10, 2)
FROM cte c
INNER JOIN (SELECT id, MAX(price)/COUNT(id) new_price
            FROM cte
            GROUP BY id) t
        ON c.id = t.id;

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=a3d49af9342b55cfb780fa212ca1545f

CodePudding user response：

You can use regexp_split_to_table():

select *
from my_ids_table t cross join lateral
    regexp_split_to_table(t.id, ', ')

CodePudding user response：

I read too fast writing my solution. But for other readers using MSQL version 2016 , you can use the STRING_SPLIT-function like below

SELECT a.id, LTRIM(b.value), ROUND(price / COUNT(*) over(partition by a.id), 2)
FROM my_ids_table a
CROSS APPLY STRING_SPLIT(id, ',') b