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',00301
On 2012-10-20, Berend Tober wrote:
> Thalis Kalfigkopoulos wrote:
>> On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell 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 successi
On 2012-10-20, Tom Lane wrote:
> "Raymond O'Donnell" 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
Thalis Kalfigkopoulos wrote:
On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell 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 think you can
Виктор Егоров wrote:
2012/10/20 Berend Tober :
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 meter.
That looks like
2012/10/20 Berend Tober :
> 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,
> lag(reading_date)
> OVER(
On 20/10/2012 17:50, Tom Lane wrote:
> "Raymond O'Donnell" 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 goin
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 this
"Raymond O'Donnell" 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, would
On Sun, Oct 21, 2012 at 3:29 AM, Raymond O'Donnell 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. If y
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 in
On 20/10/2012 17:23, Tom Lane wrote:
> Chris Angelico 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 anothe
Chris Angelico 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 reasonably s
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
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 t
> 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
On Sun, Oct 21, 2012 at 2:30 AM, Berend Tober 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 regular
> intervals:
> ..
Thalis Kalfigkopoulos wrote:
On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell 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 think you can
On Sat, Oct 20, 2012 at 8:02 AM, Raymond O'Donnell 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 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
20 matches
Mail list logo
