SQL get average of a list in sql select

Question

We have this column in the table named "pricehistory"

1634913730;48.38,1634916509;48.38,1635162352;37.96,1635177904;49.14,1635337722;1219.98,1635340811;27.17

that is an example data.

first is the timestamp than after ; is the price at this timestamp

But i want the average price from every timestamp in a select... is that possible? I dont find any similiar examples somewhere and my tries to select doesnt work... i am not so good with sql

so i want average of all prices behind that ; and before , The , split the timestamp and prices

I think, the easiest way to do this is: create python/java/js script which download from database data, make calculations (using dictionary) and generate insert sql scripts, if you need, to put results into your database. — Yuri Molodyko, Oct 28 '21 at 18:28

score 1 · Answer 1 · answered Oct 28 '21 at 19:23

Some test data :

create table test ( id int not null, pricehistory text not null );
insert into test values ( 1, '1634913730;48.38,1634916509;48.38,1635162352;37.96,1635177904;49.14,1635337722;1219.98,1635340811;27.17' );
insert into test values ( 2, '1634913731;42.42,1634916609;21.21' );

If your RDBMS has some splitting function

Then it's quite easy, just split and use AVG. Here is an example using PostgreSQL :

SELECT id, AVG(SUBSTRING(v, 12, 42)::decimal) AS average
FROM test
INNER JOIN LATERAL regexp_split_to_table(pricehistory, E',') t(v) ON TRUE
GROUP BY id;

Then you get:

 id |       average        
----+----------------------
  2 |  31.8150000000000000
  1 | 238.5016666666666667
(2 rows)

Otherwise

You can use a CTE to split the values manually. This is a bit more involved. Here is an example using PostgreSQL again :

WITH RECURSIVE T AS (
  SELECT id,
    -- We get the last value ...
    SUBSTRING(pricehistory, LENGTH(pricehistory) - STRPOS(REVERSE(pricehistory), ',') + 2) AS oneprice,
    pricehistory AS remaining
  FROM test
  UNION ALL
  -- ... as we get the other values from the recursive CTE.
  SELECT id,
    LEFT(remaining, STRPOS(remaining, ',') - 1),
    SUBSTRING(remaining, STRPOS(remaining, ',') + 1)
  FROM T
  WHERE STRPOS(remaining, ',') > 0
)
SELECT id, AVG(SUBSTRING(oneprice, 12)::decimal) AS average
FROM T
GROUP BY id;

Then you get:

 id |       average        
----+----------------------
  2 |  31.8150000000000000
  1 | 238.5016666666666667
(2 rows)

I get that error in phpmyadmin with the first code... `Variable name was expected. (near ":" at position 36) An alias was previously found. (near "t" at position 140)` I just need change "test" to my tablename right? pricehistory is also name of my column — tevved, Oct 28 '21 at 19:28
My solution is about PostgreSQL, I did not know you were using MySQL. Let me check if I can find a solution for you :-) — Fabian Pijcke, Oct 28 '21 at 19:48
For MySQL there is nothing simple I'm afraid :-( I suggest you have a look at this SO question : https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows to split rows and use the 3 last lines of my query above to get the average. — Fabian Pijcke, Oct 28 '21 at 19:51

nachospiu · Answer 2 · 2021-10-29T00:17:20.350

MySql >= 8.0

I used Recursive Common Table Expressions (cte) to split pricehistory string by ','. Then I split price from timestamp by ';', cast price as decimal(10,2) and group by id to get average price by id.

WITH RECURSIVE
     cte AS (SELECT id, 
                    SUBSTRING_INDEX(pricehistory, ',', 1) AS price, 
                    CASE WHEN POSITION(',' IN pricehistory) > 0
                         THEN SUBSTR(pricehistory, POSITION(',' IN pricehistory) + 1)
                         ELSE NULL END AS rest
             FROM t
             
             UNION ALL
             
             SELECT id, 
                    SUBSTRING_INDEX(rest, ',', 1) AS price,
                    CASE WHEN POSITION(',' IN rest) > 0 
                         THEN SUBSTR(rest, POSITION(',' IN rest) + 1)
                         ELSE NULL END AS rest
             FROM cte
             WHERE rest IS NOT NULL)
             
SELECT id, AVG(CAST(SUBSTR(price, POSITION(';' IN price) + 1) AS decimal(10,2))) AS price_average
FROM cte
GROUP BY id;

A similar way to do the same (using regular expressions functions):

WITH RECURSIVE
     cte AS (SELECT Id, concat(pricehistory, ',') AS pricehistory FROM t),

     unnest AS (SELECT id, 
                       pricehistory,
                       1 AS i, 
                       REGEXP_SUBSTR(pricehistory, ';[0-9.]*,', 1, 1) AS price
                FROM cte
             
                UNION ALL
             
                SELECT id, 
                       pricehistory,
                       i + 1,
                       REGEXP_SUBSTR(pricehistory, ';[0-9.]*,', 1, i + 1)
                FROM unnest
                WHERE REGEXP_SUBSTR(pricehistory, ';[0-9.]*,', 1, i + 1) IS NOT NULL)
             
SELECT id, AVG(CAST(SUBSTR(price, 2, LENGTH(price) - 2) AS decimal(10,2))) AS price_average
FROM unnest
GROUP BY id;

score 0 · Answer 3 · answered Oct 28 '21 at 20:10

you don't write what DBMS you are using.
In MS SQL-SERVER you can write something like this.
Create a function to convert string to multiple rows, and then use that in the query.

CREATE or ALTER FUNCTION dbo.BreakStringIntoRows (@CommadelimitedString   varchar(1000), @Separator VARCHAR(1))
RETURNS   @Result TABLE (Column1   VARCHAR(max))
AS
BEGIN
        DECLARE @IntLocation INT
        WHILE (CHARINDEX(@Separator,    @CommadelimitedString, 0) > 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(@Separator,    @CommadelimitedString, 0)      
              INSERT INTO   @Result (Column1)
              --LTRIM and RTRIM to ensure blank spaces are   removed
              SELECT RTRIM(LTRIM(SUBSTRING(@CommadelimitedString,   0, @IntLocation)))   
              SET @CommadelimitedString = STUFF(@CommadelimitedString,   1, @IntLocation,   '') 
        END
        INSERT INTO   @Result (Column1)
        SELECT RTRIM(LTRIM(@CommadelimitedString))--LTRIM and RTRIM to ensure blank spaces are removed
        RETURN 
END

create table test1 ( id int not null, pricehistory varchar(max) not null );
insert into test1 values ( 1, '1634913730;48.38,1634916509;48.38,1635162352;37.96,1635177904;49.14,1635337722;1219.98,1635340811;27.17' );
insert into test1 values ( 2, '1634913731;42.42,1634916609;21.21' );

Select *,
(
  Select avg(CAST(RTRIM(LTRIM(SUBSTRING(column1,   0, CHARINDEX(';',    column1, 0)))) as decimal))  From dbo.BreakStringIntoRows(pricehistory, ',')
) as AVG
FRom test1

sample output:

SQL get average of a list in sql select

3 Answers3

If your RDBMS has some splitting function

Otherwise