Обсуждение: Add calculated fields from one table to other table

>   I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>    
>   The time field in Timeseries table is different from time in tick table, its the timeseries
> for every minute. Now I want to calculate the average price & volume from tick table for each
> ticker and for every minute and add those fields to timeseries table. Can anyone please help me
> out with the sql query.
>    
>   Note: The ticker in the tick table also has duplicate values, so i am not able to create
> relation between two tables.

Here is my guess how it can be done:

insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...

where select .... would be

select    tick,          date_trunc('minute', time) as minute,         avg(price) as avg_price,         avg(volume) as
avg_volume
from      ticker
where  time between 'yourstartdate' and 'yourenddate'
group by tick, minute;

Regards,

Richard Broersma Jr.

Hi

 

I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries table has fields like ticker, time, avg_price, avg_volume.

 

The time field in Timeseries table is different from time in tick table, its the timeseries for every minute. Now I want to calculate the average price & volume from tick table for each ticker and for every minute and add those fields to timeseries table. Can anyone please help me out with the sql query.

 

Note: The ticker in the tick table also has duplicate values, so i am not able to create relation between two tables.

 

Thanks in advance

Roopa

---

> I have two tables. Tick table has fields like ticker, time, price & volume and Timeseries
> table has fields like ticker, time, avg_price, avg_volume.
>
> The time field in Timeseries table is different from time in tick table, its the timeseries
> for every minute. Now I want to calculate the average price & volume from tick table for each
> ticker and for every minute and add those fields to timeseries table. Can anyone please help me
> out with the sql query.
>
> Note: The ticker in the tick table also has duplicate values, so i am not able to create
> relation between two tables.

Here is my guess how it can be done:

insert into Timeseries ( tiker, time, avg_price, avg_volume ) select ...

where select .... would be

select tick,
date_trunc('minute', time) as minute,
avg(price) as avg_price,
avg(volume) as avg_volume
from ticker
where time between 'yourstartdate' and 'yourenddate'
group by tick, minute;

Regards,

Richard Broersma Jr.

>   Thanks a lot for your help. The query does work, but now I have a problem. The query goes like
> this: 
>    
>   select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),
> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) 
> from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and 
> tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,
> tk.ric order by tk.ric, tm.timeseries_time
>    
>   The problem is, if there is no row for certain minute, then I want the count to be displayed
> as zero and other coulmns like avg to be null. In this query, it just omits those minutes which
> doesnt have any row for a particular minute.

You have to use an outer join.  You will need a table or sequence that has every minute in a range
that you are interested in and outer join that to your actual table.  This will give you a count
of zero.

i.e.

select S.minute, count(W.minute) as minutecnt

from Series_of_Minutes S left join Working_table W

on S.minute = W.minute
;

hope this helps.

REgards,

Richard Broersma jr.

ps. sorry that my query suggestion didn't work :0)

> Thanks a lot for your help. The query does work, but now I have a problem. The query goes like
> this:
>
> select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),
> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)
> from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and
> tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,
> tk.ric order by tk.ric, tm.timeseries_time
>
> The problem is, if there is no row for certain minute, then I want the count to be displayed
> as zero and other coulmns like avg to be null. In this query, it just omits those minutes which
> doesnt have any row for a particular minute.

You have to use an outer join. You will need a table or sequence that has every minute in a range
that you are interested in and outer join that to your actual table. This will give you a count
of zero.

i.e.

select S.minute, count(W.minute) as minutecnt

from Series_of_Minutes S left join Working_table W

on S.minute = W.minute
;

hope this helps.

REgards,

Richard Broersma jr.

ps. sorry that my query suggestion didn't work :0)

Hi Roopa,

If your timeseries table has records for all minutes, then you should outer join both tables so as to get the desired results you are looking for... try doing this.

select tk.ric, tm.timeseries_time , count(tk.*), avg(tk.price),
sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)
from ticks tk right outer join timeseries tm
 where tk.tick_time >= tm.timeseries_time
   and tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time
group by tm.timeseries_time,tk.ric order by tk.ric, tm.timeseries_time

On 10/30/06, roopa perumalraja < roopabenzer@yahoo.com> wrote:

Hi

 

Thanks a lot for your immediate reply. I want to explain more about it. The ticks table has many rows in each minute and timeseries table has 1 minute increment data. And the query is as mentioned below, which just displays the result for the minutes in which the tick data exists. but i would like the result for the query to be like

 

ric | time | count | avg_price | avg_volume

A  | 12:00| 12 | 64.99 | 63.99

A | 12:01 | 0 | |

A | 12:02 | 5 | 36.99 | 32.99

 

but my query result is just

 

A  | 12:00| 12 | 64.99 | 63.99

A | 12:02 | 5 | 36.99 | 32.99

 

so can you help me out to modify the query to get the result what I expect

 

select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),
sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)
from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and
tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,
tk.ric order by tk.ric, tm.timeseries_time

Thanks a lot in advance

Roopa

 

ps. I wrote the query from your idea. so tanks a lot

Richard Broersma Jr <rabroersma@yahoo.com> wrote:

> Thanks a lot for your help. The query does work, but now I have a problem. The query goes like
> this:
>
> select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),
> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)
> from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and
> tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,
> tk.ric order by tk.ric, tm.timeseries_time
>
> The problem is, if there is no row for certain minute, then I want the count to be displayed
> as zero and other coulmns like avg to be null. In this query, it just omits those minutes which
> doesnt have any row for a particular minute.

You have to use an outer join. You will need a table or sequence that has every minute in a range
that you are interested in and outer join that to your actual table. This will give you a count
of zero.

i.e.

select S.minute, count(W.minute) as minutecnt

from Series_of_Minutes S left join Working_table W

on S.minute = W.minute
;

hope this helps.

REgards,

Richard Broersma jr.

ps. sorry that my query suggestion didn't work :0)

---

Want to start your own business? Learn how on Yahoo! Small Business.

> Thanks a lot for your help. The query does work, but now I have a problem. The query goes like
> this:
>
> select tk.ric, tm.timeseries_time, count(tk.*), avg(tk.price),
> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume)
> from ticks tk, timeseries tm where tk.tick_time >= tm.timeseries_time and
> tk.tick_time < (tm.timeseries_time + '1 minute' :: interval)::time group by tm.timeseries_time,
> tk.ric order by tk.ric, tm.timeseries_time
>
> The problem is, if there is no row for certain minute, then I want the count to be displayed
> as zero and other coulmns like avg to be null. In this query, it just omits those minutes which
> doesnt have any row for a particular minute.

You have to use an outer join. You will need a table or sequence that has every minute in a range
that you are interested in and outer join that to your actual table. This will give you a count
of zero.

i.e.

select S.minute, count(W.minute) as minutecnt

from Series_of_Minutes S left join Working_table W

on S.minute = W.minute
;

hope this helps.

REgards,

Richard Broersma jr.

ps. sorry that my query suggestion didn't work :0)

>   select foo.ric, tm.times_time, count(tk.*), avg(tk.price),
> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks) as
> foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and tk.tick_time <
> (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by tm.times_time,
> foo.ric order by tm.times_time;
>    
>   I get a error message like this:
>    
>   ERROR:  invalid reference to FROM-clause entry for table "foo"
> HINT:  There is an entry for table "foo", but it cannot be referenced from this part of the
> query.
>    
>     Can you help me with this?

I will try, but to start with, to help us, when you have a difficult query to solve, you should
simplify your query as much a possible.  This way we can more quickly see what you are intending
verses the problem you are having.

1 tip:  (select distinct ric from ticks)

I think that you will find that:(select ric from ticks group by ric)
is much faster than using the distinct.

The error in the query that I see is that you are using foo as a criteria in the ON syntax.  This
will not work. To illistrate:

A,B join C
ON  (B.id = C.id)  --ON syntax only works with joins
AND (B.id2 < C.id) --The And is still part of the ON syntax                  --you can not reference A since it is not
joined

WhereA.id = B.id       --you can only specify a non-joined tables contrainst
ANDA.id2 < C.id2
;                   --in the where clause

I hope this helps.

Regards,

Richard Broersma JR.

> select foo.ric, tm.times_time, count(tk.*), avg(tk.price),
> sum(tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select distinct ric from ticks) as
> foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and tk.tick_time <
> (tm.times_time + '1 minute' :: interval)::time and tk.ric = foo.ric group by tm.times_time,
> foo.ric order by tm.times_time;
>
> I get a error message like this:
>
> ERROR: invalid reference to FROM-clause entry for table "foo"
> HINT: There is an entry for table "foo", but it cannot be referenced from this part of the
> query.
>
> Can you help me with this?

I will try, but to start with, to help us, when you have a difficult query to solve, you should
simplify your query as much a possible. This way we can more quickly see what you are intending
verses the problem you are having.

1 tip: (select distinct ric from ticks)

I think that you will find that:
(select ric from ticks group by ric)
is much faster than using the distinct.

The error in the query that I see is that you are using foo as a criteria in the ON syntax. This
will not work. To illistrate:

A,B join C
ON (B.id = C.id) --ON syntax only works with joins
AND (B.id2 < C.id) --The And is still part of the ON syntax
--you can not reference A since it is not joined

Where
A.id = B.id --you can only specify a non-joined tables contrainst
AND
A.id2 < C.id2
; --in the where clause

I hope this helps.

Regards,

Richard Broersma JR.

>   Thanks for your help. That does make sense, but I am not able to get the result what I wanted
> exactly. Let me explain you.
>    
>   I have ticks table in which I have columns like ric, tick_time, price & volume. The times
> table has just one column with times_time which has time data for each minute ie.) 
>    
>   Ticks
>   ric | tick_time | price | volume
>   A | 12:00:01 | 23.00 | 12
>   A | 12:00:02 | 26.00 | 7
>   B | 12: 00:02 | 8.00 | 2
>   B | 12:01:01 | 45.00 | 6
>    
>   Times
>   times_time
>   12:00
>   12:01
>   12:02
>    
>   Now I want the timeseries for each minute for all ric in the tick table. So my query goes like
> this for a particular ric say for example ric 'A'
>    
>   select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum
> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from ticks where ric = 'A'
> group by ric) as foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and
> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'A' group by
> tm.times_time, foo.ric order by tm.times_time; 
> 
>   I get the result as I expect, but i am not able to derive a query for all rics in the tick
> table.
>    

How about:

SELECT         foo.ric,        date_trunc('minute', tm.times_time) as minute,        count(tk.*),

> Thanks for your help. That does make sense, but I am not able to get the result what I wanted
> exactly. Let me explain you.
>
> I have ticks table in which I have columns like ric, tick_time, price & volume. The times
> table has just one column with times_time which has time data for each minute ie.)
>
> Ticks
> ric | tick_time | price | volume
> A | 12:00:01 | 23.00 | 12
> A | 12:00:02 | 26.00 | 7
> B | 12: 00:02 | 8.00 | 2
> B | 12:01:01 | 45.00 | 6
>
> Times
> times_time
> 12:00
> 12:01
> 12:02
>
> Now I want the timeseries for each minute for all ric in the tick table. So my query goes like
> this for a particular ric say for example ric 'A'
>
> select foo.ric, tm.times_time, count(tk.*), avg(tk.price), sum
> (tk.price*tk.volume)/sum(tk.volume), sum(tk.volume) from (select ric from ticks where ric = 'A'
> group by ric) as foo, times tm left join ticks tk on tk.tick_time >= tm.times_time and
> tk.tick_time < (tm.times_time + '1 minute' :: interval)::time and tk.ric = 'A' group by
> tm.times_time, foo.ric order by tm.times_time;
>
> I get the result as I expect, but i am not able to derive a query for all rics in the tick
> table.
>
> I really appreciate your help.

Sorry I prematurely sent my first email

Select foo.ric,
date_trunc("minute", tm.times_time) as time_tick,
count(tk.*) tickperminute,
avg(tk.price),
... --your other aggregate functions
from (
select ric
from ticks
where ric = 'A'
group by ric
) as foo
join ticks tk
on (tk.ric = foo.ric)
right join times tm
on (tk.tick_time >= tm.times_time)
and (tk.tick_time < (tm.times_time + '1 minute' :: interval)::time
and (tk.ric = 'A') -- this shouldn't be neccessary
-- if you restructor your join
-- since foo limits all ric to 'A'
-- but since it is on the wrong side
-- of an outer join it can't.
group by foo.ric,
time_tick

order by time_tick;

Regards,

Richard Broersma Jr.

> Hi Richard,
>    
>   Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I
> am able to get the result for a particular ric.
>    
>   Can you help me with getting the result for all the rics in the ticks table
>    
>   Thanks
>   Roopa

Could you send create table statements for the tables you are working on, and a few insert
statements for each table to have sample data. then show what you want the query results to look
like.

But from what you stated in your previous emails here is what I gather: maybe it might work?

select    tk.ric as ric,          tm.times_time as minute, --timestamps by minutes         count(tk.*) as ,
...
from      times tm
left join ticks tk
on (tm.times_time = date_trunc('minutes', tk.time))
group by  ric, minute
order by  minute;

> Hi Richard,
>
> Thanks a lot. I still am not able to get the result for all the rics in the ticks table but I
> am able to get the result for a particular ric.
>
> Can you help me with getting the result for all the rics in the ticks table
>
> Thanks
> Roopa

Could you send create table statements for the tables you are working on, and a few insert
statements for each table to have sample data. then show what you want the query results to look
like.

But from what you stated in your previous emails here is what I gather: maybe it might work?

select tk.ric as ric,
tm.times_time as minute, --timestamps by minutes
count(tk.*) as ,
...
from times tm
left join ticks tk
on (tm.times_time = date_trunc('minutes', tk.time))
group by ric, minute
order by minute;

>   I am sending you the create statement of tables & few insert statements as well. Hope this
> helps to solve the problem.

where are the insert statements?  ;)

>    
>   CREATE TABLE ticks
> (
>   tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass),
>   ric varchar(30) NOT NULL,
>   tick_date date NOT NULL,
>   tick_time time NOT NULL,
>   price float8,
>   volume int4,
>   CONSTRAINT ticks_pkey PRIMARY KEY (tick_id),
> ) 
> WITHOUT OIDS;
>    
>   CREATE TABLE times
> (
>   times_time time NOT NULL,
>   count int4,
>   CONSTRAINT times_pkey PRIMARY KEY (times_time)
> )
>    
>   selct statement of ticks table
>    ric | tick_date  |  tick_time   | price 
> -----+------------+--------------+-------
>  A   | 2006-04-04 | 00:00:55.023 | 4.05   
>  AA  | 2006-04-04 | 00:00:55.023 | 9.05 
>  A   | 2006-04-04 | 00:00:59.023 | 6.05    
>  A   | 2006-04-04 | 00:01:00.023 | 5.05 
>  ABC | 2006-04-04 | 00:01:00.509 |12.00      
>  ABI | 2006-04-04 | 00:01:03.511 |13.00      
>  AA  | 2006-04-04 | 00:01:08.023 | 6.05      
>  ABT | 2006-04-04 | 00:01:08.518 | 3.06 
>  ABT | 2006-04-04 | 00:01:09.518 | 7.06    
>  
> select statement of times table
>   times_time 
> -----------
>  00:00:00        
>  00:01:00        
>  00:02:00        
>         
>   
> I want the query result to look 
>   ric | times_time | count | avg_price
> ----+------------+-------+-----------
>  A  | 00:00:00   | 2  | 5.05
>  AA | 00:00:00   | 1     | 9.05
> ABC | 00:00:00   | 0     | 
> ABI | 00:00:00   | 0     | 
> ABT | 00:00:00   | 0     | 
>  A  | 00:01:00   | 1  | 5.05
>  AA | 00:01:00   | 1     | 6.05
> ABC | 00:01:00   | 1     |12.00 
> ABI | 00:01:00   | 1     |13.00 
> ABT | 00:01:00   | 2     | 5.06


Here is what I got:ric |  minute  | count |    avg_price
-----+----------+-------+------------------ABC | 00:00:00 |     0 |                0ABT | 00:00:00 |     0 |
   0AA  | 00:00:00 |     2 |             9.05ABI | 00:00:00 |     0 |                0A   | 00:00:00 |     6 |
  5.05A   | 00:01:00 |     3 |             5.05ABI | 00:01:00 |     1 |               13AA  | 00:01:00 |     2 |
    6.05ABT | 00:01:00 |     9 | 5.72666666666667ABC | 00:01:00 |     1 |               12A   | 00:02:00 |     0 |
         0AA  | 00:02:00 |     0 |                0ABI | 00:02:00 |     0 |                0ABC | 00:02:00 |     0 |
           0ABT | 00:02:00 |     0 |                0
 
(15 rows)


And here is how I got it:

SELECT               A.ric,               A.minute,               count(B.*) as count,
COALESCE(avg(B.price),0)as avg_price
 
FROM       (       SELECT  T.ric,               M.times_time as minute       FROM               ticks T       CROSS
JOIN              times M       WHERE               M.times_time       BETWEEN               '00:00:00'
AND              '00:03:00'       ) A
 
LEFT JOIN       ticks B
ON       A.ric = B.ric       AND       A.minute = date_trunc('minute', B.tick_time)
GROUP BY       A.ric,       A.minute
ORDER BY       A.minute
;


Hope this is what you were looking for.  This is the first time I've ever had to employ a cross
join get what I wanted.  Just realize that this query will explode with a very large number to
records returned as the times table grows.  You should expect a quantity of results like (total
ticks * total times)

Regards,

Richard Broersma Jr.