Search Postgresql Archives

Advice on efficient approach to aggregation statistics

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi -

    I have a table for which I want to fit an exponential curve to values in one column of the table and compute a correlation statistic on the fit of the values to the exponential curve. The table consists of the following three columns: {"ID","Date","Value"}. The desired result of the exercise is to have a list of "ID"s, with the associated correlation statistic (based on the "Value" column) for a subset of "Date"s. The "fitted" exponential curve is not a true exponential regression for performance reasons. It uses the exponential form: y = a * exp(b * x), but selects the constant values 'a' & 'b' using only the first and last "Values" (as sorted by "Date") -- so it's really a best-fit exponential curve which is assured to pass through the first and last sorted points. The following is a simplified example (assuming the best practice solution involves the development of an aggregate function):

Table (primary key is {"ID","Date"}):
    ID |     Date     | Value
    ----------------------------------
    A | 2010-11-12 | 48.0000
    A | 2010-11-11 | 24.0000
    A | 2010-11-10 | 12.0000
    A | 2010-11-09 |  6.0000
    A | 2010-11-08 |  3.0000
    A | 2010-11-07 |  1.5000
    B | 2010-11-12 | 33.1479
    B | 2010-11-11 | 19.5030
    B | 2010-11-10 | 11.4749
    B | 2010-11-09 |  6.7514
    B | 2010-11-08 |  3.9723
    B | 2010-11-07 |  2.3371

Hypothetical query:
    SELECT "ID", correl_exp("Value") AS "Corr"
    FROM Table
    WHERE "Date" >= '2010-11-09'
    GROUP BY "ID";

Expected output:
    ID | Corr
    ---------------
    A | 1.0000
    B | 0.8690

If attempting to reconstruct the simple example, the following are the values for constants 'a' and 'b' for "ID"s A & B (also note that values for dates 11/7 and 11/8 are ignored given the hypothetical query):
    A: a = 3.0000, b =  0.6931
    B: a = 1.4283, b =  0.9513


Questions:
    1) What is the best approach to accomplish the expected result?
        I would think the creation of an aggregate function would be the "best" option (i.e. highest performance with little ongoing database maintenence), but thought a nested SQL statement may also work.
    2) If the creation of an aggregate function is the best option,
        A) What is the highest performance language (or does it even matter)? PL/C, PL/R, PL/pgSQL, other?  Can one use pre-complied shared libraries?
        B) What is the best state function used to construct an array of "Values", sorted by "Date" (as both the first and the last value need to be known to compute the exponential curve)? I have heard array_append has performance issues.

Many thanks -
Will

PostgreSQL v. 8.3.8 (expecting to upgrade to v.9.0.1 soon -- so best practice solution should be compatable with v9.0.1 -- if best solution takes advantage of improvements in v9.0.1 and is incompatable with v8.3.8, please note and I can implement the solution after upgrade)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux