How to reverse a DolphinDB table aggregated by group by + toArray back to its original form?

Question

I have an in - memory DolphinDB table created as follows:

ticker = `AAPL`IBM`IBM`AAPL`AMZN`AAPL`AMZN`IBM`AMZN
volume = 106 115 121 90 130 150 145 123 155;
t = table(ticker, volume);
t;

The output of the table t is:

ticker  volume
-----   ----
AAPL    106
IBM     115
IBM     121
AAPL    90
AMZN    130
AAPL    150
AMZN    145
IBM     123
AMZN    155

I used the toArray function along with group by to aggregate the grouped data into array vectors in a single row, which makes it easier to view all data under each group:

t1 = select toArray(volume) as volume_all from t group by ticker;
t1;

The output of the table t1 is:

ticker   volume_all
------   ----------
AAPL     [106,90,150]
AMZN     [130,145,155]
IBM      [115,121,123]

Now, I want to reverse t1 back to the original table t. The reason for this requirement is that sometimes when I group by price, the time columns may become non - unique. To restore a unique time column, I first use the toArray approach.

I attempted the following method:

select flatten(volume_all) as volume from t1

The output is:

volume
-----
106
90
150
130
145
155
115
121
123

However, this only gives me the volumes without the corresponding tickers.

My questions are:

Is there a built - in function or syntax in DolphinDB that can expand both the grouped keys (in this case, ticker) and their associated array elements (volume_all) simultaneously?
Does DolphinDB provide any documented patterns for reversing aggregation operations like this? Any help or guidance would be greatly appreciated.

d r · Accepted Answer · 2025-10-13 11:27:52Z

I'm not familiar with dolphinDB, but if it supports recursive CTE queries than you could get what you want with some basicstring fuctions (Substr(), Instr(), Replace()).
Oracle sintax:

WITH
  t1 AS      --  your sample data
    ( Select 'AAPL' as ticker, '[106,90,150]' as volume_all From Dual Union All
      Select 'AMZN', '[130,145,155]' From Dual Union All
      Select 'IBM',  '[115,121,123]' From Dual 
  ), 
--  Recursive CTE 
 grid (pass, ticker, volume_all, volume) AS
   ( Select  1 as pass, ticker, 
             Replace(volume_all, ']', ',') as vočume_all, 
             Substr(volume_all, 2, Instr(volume_all, ',', 1, 1) - 2) as volume
     From   t1
    UNION ALL
     Select pass + 1 as pass, 
            ticker, 
            volume_all, 
            Substr(volume_all, Instr(volume_all, ',', 1, pass) + 1, Instr(volume_all, ',', 1, pass + 1) - Instr(volume_all, ',', 1, pass) - 1 ) as volume
     From   grid
     Where  pass < Length(volume_all) - Length(Replace(volume_all, ',', ''))
   )
--  Main SQL
Select ticker, volume 
From grid

TICKER	VOLUME
AAPL	106
AMZN	130
IBM	115
AAPL	90
AMZN	145
IBM	121
AAPL	150
AMZN	155
IBM	123

fiddle

Collectives™ on Stack Overflow

How to reverse a DolphinDB table aggregated by group by + toArray back to its original form?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related