On 2012-10-20, Tom Lane t...@sss.pgh.pa.us wrote:
Raymond O'Donnell r...@iol.ie writes:
On 20/10/2012 17:23, Tom Lane wrote:
FWIW, Postgres is reasonably smart about the case of multiple window
functions with identical window definitions --- once you've got one
lag() in the query, adding more
On 2012-10-20, Berend Tober bto...@broadstripe.net wrote:
Thalis Kalfigkopoulos wrote:
On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell r...@iol.ie wrote:
On 20/10/2012 11:54, ochaussavoine wrote:
I have a table 'tmvt' with a field 'created' in the row, and would like to
compute the
Jasen Betts wrote:
electricity meter may bis a bad example as usage meters often have
fewer digits than are needed to track all historical usage
eg:
'2012-05-07',997743
'2012-06-06',999601
'2012-07-05',000338
'2012-08-06',001290
'2012-09-07',002158
'2012-10-05',003018
Hi,
I have a table 'tmvt' with a field 'created' in the row, and would like to
compute the difference between successive rows. The solution I found is:
It is complicate and very long to perform. The problem could be simply
solved with MySql by creating a new field and updating it using a
On 20/10/2012 11:54, ochaussavoine wrote:
Hi,
I have a table 'tmvt' with a field 'created' in the row, and would like to
compute the difference between successive rows. The solution I found is:
It is complicate and very long to perform. The problem could be simply
solved with MySql by
On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell r...@iol.ie wrote:
On 20/10/2012 11:54, ochaussavoine wrote:
Hi,
I have a table 'tmvt' with a field 'created' in the row, and would like to
compute the difference between successive rows. The solution I found is:
I think you can do it with
Thalis Kalfigkopoulos wrote:
On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell r...@iol.ie wrote:
On 20/10/2012 11:54, ochaussavoine wrote:
I have a table 'tmvt' with a field 'created' in the row, and would like to
compute the difference between successive rows. The solution I found is:
I
On Sun, Oct 21, 2012 at 2:30 AM, Berend Tober bto...@broadstripe.net wrote:
What about if there is more than one column you want the difference for (...
coincidentally I am writing a article on this topic right now! ...), say a
table which is used to record a metered quantity at not-quite
What about if there is more than one column you want the difference for (...
coincidentally I am writing a article on this topic right now! ...), say a
table which is used to record a metered quantity at not-quite regular
intervals:
CREATE TABLE electricity
(
current_reading_date date,
Thalis Kalfigkopoulos wrote:
SELECT current_meter_reading - lag(current_meter_reading) OVER(ORDER
BY current_reading_date) AS kWh_diff, extract('days' FROM
current_reading_date - lag(current_reading_date) OVER(ORDER BY
current_reading_date)) as num_service_days FROM mytable;
How would you get
On 20/10/2012 17:02, Berend Tober wrote:
Thalis Kalfigkopoulos wrote:
SELECT current_meter_reading - lag(current_meter_reading) OVER(ORDER
BY current_reading_date) AS kWh_diff, extract('days' FROM
current_reading_date - lag(current_reading_date) OVER(ORDER BY
current_reading_date)) as
Chris Angelico ros...@gmail.com writes:
To be quite honest, I would simply read the table directly and then do
the processing in an application language :) But two window functions
should do the trick. Whether or not it's actually more efficient that
way is another question.
FWIW, Postgres is
On 20/10/2012 17:23, Tom Lane wrote:
Chris Angelico ros...@gmail.com writes:
To be quite honest, I would simply read the table directly and then do
the processing in an application language :) But two window functions
should do the trick. Whether or not it's actually more efficient that
way
Raymond O'Donnell wrote:
On 20/10/2012 17:02, Berend Tober wrote:
Thalis Kalfigkopoulos wrote:
How would you get the previous reading (and perhaps the previous read
date) to also appear ...
Just include them in the SELECT:
Well, that is surprisingly easy!
How about this then: the table
On Sun, Oct 21, 2012 at 3:29 AM, Raymond O'Donnell r...@iol.ie wrote:
On 20/10/2012 17:23, Tom Lane wrote:
Having said that, they are pretty expensive. I tend to agree that doing
the processing on the application side might be faster --- but only if
you've got a place to put such code there.
Raymond O'Donnell r...@iol.ie writes:
On 20/10/2012 17:23, Tom Lane wrote:
FWIW, Postgres is reasonably smart about the case of multiple window
functions with identical window definitions --- once you've got one
lag() in the query, adding more isn't going to cost much.
Out of curiosity,
Berend Tober wrote:
Raymond O'Donnell wrote:
On 20/10/2012 17:02, Berend Tober wrote:
Thalis Kalfigkopoulos wrote:
How would you get the previous reading (and perhaps the
previous read
date) to also appear ...
Just include them in the SELECT:
Well, that is surprisingly easy!
How about
On 20/10/2012 17:50, Tom Lane wrote:
Raymond O'Donnell r...@iol.ie writes:
On 20/10/2012 17:23, Tom Lane wrote:
FWIW, Postgres is reasonably smart about the case of multiple window
functions with identical window definitions --- once you've got one
lag() in the query, adding more isn't going
2012/10/20 Berend Tober bto...@broadstripe.net:
Your suggestion almost worked as is for this, except that you have to note
that reading for meter #2 and meter #3 overlap (I briefly owned two houses),
and that seemed to confuse the lag() function:
SELECT
electric_meter_pk,
Виктор Егоров wrote:
2012/10/20 Berend Tober bto...@broadstripe.net:
Your suggestion almost worked as is for this, except that you have to note
that reading for meter #2 and meter #3 overlap ...
You can do
… OVER(PARTITION BY electric_meter_pk ORDER BY reading_date)
to split you data by
Thalis Kalfigkopoulos wrote:
On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell r...@iol.ie wrote:
On 20/10/2012 11:54, ochaussavoine wrote:
I have a table 'tmvt' with a field 'created' in the row, and would like to
compute the difference between successive rows. The solution I found is:
I
21 matches
Mail list logo
