Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Unanswered: Months calculation

    Newbie question here.

    I'm trying to implement a calculation that deals with values that are archived on a monthly basis. I want to multiply X and Y, where X is an archived value for a particular month, and Y is a factor that depends how old the archived value is. For example, I might have the following values in the archive table:

    Month | Value
    --------------
    2008-12 | 10
    2008-11 | 50
    2008-10 | 25

    And in the table of factors, I have the following:

    Index | Factor
    --------------
    1 | 0.8
    2 | 0.6
    3 | 0.4

    My problem is in linking up the two records, to multiply the value from N months ago with the factor corresponding to index number N.

    I need to loop through as many as 24 months' records to perform this calculation, so extracting the month part of the date doesn't help me (unless I'm doing it wrong).

    Can someone give me a hand in putting an appropriate query together? I've been reading and re-reading the PostgreSQL documentation and various other sources for a couple of days and I'm still stumped.


    Many thanks,
    M@
    Last edited by mattbin; 01-05-09 at 12:00. Reason: Fix spacing

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by mattbin
    value from N months ago
    "ago" relative to what? The current date? A given date?

    Where does N come from? Is that some kind of "input" to the query?

  3. #3
    Join Date
    Jan 2009
    Posts
    5
    N is the number of months previous to the current date, i.e. FOR N IN 1..24 LOOP.

    My plan is to cycle through the records in the "Archive" table, comparing the most recent 24 months' records with the corresponding records 1 to 24 in the "Factors" table. For example, last month's "Archive" record corresponds to record 1 in the "Factors" table, the record from 2 months ago to record 2, and so on. So N is 1..24.

    The values in the "Factors" table don't typically change. A new record is added to the "Archive" table every month.

    The reason for all this, if this helps explain it, is to give more recent scores a different "weighting" from scores that were accrued further in the past.

    Hope that clarifies -- thanks.

  4. #4
    Join Date
    Jan 2009
    Posts
    5

    Solved!

    Well, I figured out how to extract the number of months between two dates, anyhow. The trick was that I could use date_part on the result of an age() expression.

    For anyone interested, it looks like this:

    SELECT (date_part('month', (age(current_date::timestamp, 'somedate'::timestamp))))
    + ((date_part('year', (age(current_date::timestamp, 'somedate'::timestamp)))) * 12);

    It returns the number of months in the age() result, plus twelve times the number of years in the age() result.

    In order to apply this to a list of values in the archive table, I suppose the approach is:

    1. Loop through the most recent 24 records in the Archive table.
    2. For each record, calculate its age in months.
    3. Find the corresponding record in the Factors table.
    4. Multiply the values from the two corresponding tables.
    5. End loop.

    I might add a conditional in the third step that ignores results that are more than 24 months old.

    If anyone wants to suggest improvements on this, of course, please do.


    Thx
    M@

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If you extend your factors table, adding rows for month ages 25-1200 (a hundred years or so), and have months 25-N each have a NULL factor, you could do it in one SQL statement, I would think...

    Remember, any value, multiplied by a null, results in a null. And, you can test for null within the SQL.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Jan 2009
    Posts
    5
    It's an interesting approach, Loquin, but I don't think it's workable in this case because our factors are rolling -- that is, factor #1 always applies to the archived value from one month ago, not to the archived value from the first month we recorded. And after an archive record is 24 months old, we want to ignore it completely (but we might want to change that in the future, say to 36 months or 18 months).

    Unless I've misunderstood what you're saying, I'm not sure how we could get this effect without a loop.

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I believe you do misunderstand.

    By extending the factors table, so that it would 'work' against dates MUCH further in the past that you have dates, or reasonably expect to have in the lifetime of your system, you should be able to do away with loops.

    Factor number 1 would ALWAYS apply to the most recent month. Factor #2 would always apply to the next most recent month, etc.

    It's just that, beginning with factor number 25 (25 months prior,) the factor value would be null.

    Now, aggregate functions default to not including records which have a null value. Single row functions pass the null value through, so, your query can include a "Where Factor is not Null" condition.

    Let's suppose that you currently have 10 years data in your system, and you extend the factors table to cover the last 1200 months (100 years.)

    You run a query which joins the factors table to the data, and which includes where factor_table.factor is not null...

    The query returns the most recent 24 months of data.

    Next month, run the same query again, and again, it only returns the most recent 24 months.

    Now, if you didn't include the where clause, the first query would return 120 months of records, with all but the most recent 24 months having a null factor value. The second run a month later would return 121 months, with all but the most recent 24 months containing a null factor value.
    Last edited by loquin; 02-10-09 at 12:13.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    OK. I experimented a little. First, I created two tables in the public schema:
    Code:
    CREATE TABLE public.archive
    (
      data_month character varying(7) NOT NULL, -- archive month for the data
      value integer, -- monthly data
      CONSTRAINT archive_pk PRIMARY KEY (data_month)
    ) 
    
    -- and
    
    CREATE TABLE public.factors
    (
      "index" integer NOT NULL, -- Months
      factor numeric,
      CONSTRAINT factor_pk PRIMARY KEY ("index") USING INDEX TABLESPACE pg_default
    )
    Next, I entered your data from above into the tables. (For testing, I assumed that this was a 3 month aging test.)

    Then, I worked on a query to join the two tables as you indicated. Essentially, you want to subtract factors.index (months) from the current_date, using this calculation result to join the two tables. The most difficult part of the whole exercise is getting everything cast to the same format (and on the same DAY of the month) on both the left and right hand side of the where clause.
    Code:
    select a.data_month , a.value * f.factor as "Calcs"
    from public.archive a, public.factors f
    where cast(cast(a.data_month || '-01' as date) as timestamp) = cast (extract(YEAR from current_date) || '-' || lpad(extract(MONTH from current_date),2,'0') || '-01' as date) - cast(index || ' month' as interval) 
    order by a.data_month desc
    returns the anticipated values, I believe.

    Finally, I added another value in the archive table for September 2008, and this value, since it was 'expired,' in our three-month window, did not appear in the result set.

    Note that, in your application, extending the factors table past the limit of months you want to use isn't required. But, if it is, you can still use it by checking for a null factor in the factors table.
    Last edited by loquin; 01-12-09 at 16:12.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •