------=_Part_920_8821814.1210530972990
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
On Sun, May 11, 2008 at 11:47 PM, Craig Ringer
<craig@[EMAIL PROTECTED]
>
wrote:
> Mag Gam wrote:
>
> >
> > ts | size| Diff
> > -------------------+-----+------
> > 2002-03-16 | 11 | 0
> >
> > 2002-03-17 | 15 | 4
> > 2002-03-18 | 18 | 3
> > 2002-03-19 | 12 | -6
> >
> >
> > I need the first column to be 0, since it will be 11-11. The second
> > colum is 15-11. The third column is 18-15. The fourth column is 12-18.
> >
> > Any thoughts about this?
>
> Without making any comments on the advisability of the structure you're
> trying to use, here are a few ideas.
>
> The easy way is to use PL/PgSQL and FOR EACH .. SELECT . It's probably
> going to be rather fast too as it can use a single sequential scan.
>
> Otherwise (all examples use the following code):
>
> CREATE TABLE x (ts timestamp, size int);
> INSERT INTO x (ts, size) VALUES
> ('2002-03-16',11),
> ('2002-03-17',15),
> ('2002-03-18',18),
> ('2002-03-19',12);
>
> If you can assume that there is always exactly 1 day between entries
> then it's easy enough with a self join.
>
> If you cannot assume that, you can use a subquery with limit and order
> by to obtain the next record:
>
> SELECT
> a.ts,
> (SELECT b.size FROM x b WHERE b.ts > a.ts ORDER BY b.ts ASC LIMIT 1)
> - a.size AS difference
> FROM x a;
>
> ... but that'll be really slow for any significant number of entries.
not really... if you have an index on the TS column.
Best regards,
--
gurjeet[.singh]@[EMAIL PROTECTED]
gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
------=_Part_920_8821814.1210530972990
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
On Sun, May 11, 2008 at 11:47 PM, Craig Ringer <<a
href="mailto:craig@[EMAIL PROTECTED]
">craig@[EMAIL PROTECTED]
>>
wrote:<br><div class="gmail_quote"><blockquote class="gmail_quote"
style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt
0.8ex; padding-left: 1ex;">
<div class="Ih2E3d">Mag Gam wrote:<br>
<br>
><br>
> ts |
size| Diff<br>
> -------------------+-----+------<br>
> 2002-03-16 | 11 | 0<br>
><br>
> 2002-03-17 | 15 | 4<br>
> 2002-03-18 | 18 | 3<br>
> 2002-03-19 | 12 | -6<br>
><br>
><br>
> I need the first column to be 0, since it will be 11-11. The
second<br>
> colum is 15-11. The third column is 18-15. The fourth column is
12-18.<br>
><br>
> Any thoughts about this?<br>
<br>
</div>Without making any comments on the advisability of the structure
you're<br>
trying to use, here are a few ideas.<br>
<br>
The easy way is to use PL/PgSQL and FOR EACH .. SELECT . It's
probably<br>
going to be rather fast too as it can use a single sequential scan.<br>
<br>
Otherwise (all examples use the following code):<br>
<br>
CREATE TABLE x (ts timestamp, size int);<br>
INSERT INTO x (ts, size) VALUES<br>
('2002-03-16',11),<br>
('2002-03-17',15),<br>
('2002-03-18',18),<br>
('2002-03-19',12);<br>
<br>
If you can assume that there is always exactly 1 day between entries<br>
then it's easy enough with a self join.<br>
<br>
If you cannot assume that, you can use a subquery with limit and order<br>
by to obtain the next record:<br>
<br>
SELECT<br>
a.ts,<br>
(SELECT b.size FROM x b WHERE b.ts > a.ts ORDER BY b.ts ASC
LIMIT 1)<br>
- a.size AS difference<br>
FROM x a;<br>
<br>
.... but that'll be really slow for any significant number of
entries.</blockquote><div><br>not really... if you have an index on the TS
column.<br></div></div><br>Best regards,<br>--
<br>gurjeet[.singh]@[EMAIL PROTECTED]
>
singh.gurjeet@[EMAIL PROTECTED]
gmail | hotmail | indiatimes | yahoo
}.com<br><br>EnterpriseDB <a
href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br><br>Mail
sent from my BlackLaptop device
------=_Part_920_8821814.1210530972990--
|
