0

My data-table has various assets along with the groups they belong to and the date of entry into that group/population/data-table.

Now I can calculate the average age of the assets but how would I actually go about calculating an average age over a period of time so for example for Group A, I can calculate it for Assets SN 001 and SN 004 but how do I go about getting a running average since Date of Entry and in a Year-on-Year format.

I apologise if I am unclear, I would be happy to elaborate.

Asset Group Date of Entry
SN 001 A 2011-01-01
SN 002 B 2014-06-01
SN 003 C 2015-01-01
SN 004 A 2018-06-01
SN 005 B 2019-01-01
SN 006 C 2021-06-01

Desired Result: Where # is the average age and changes based on the addition of Assets to each group and adjusts accordingly.

Date of Entry (Y) AVG_Group A AVG_Group B AVG_Group C
2011 # # #
2012 # # #
2013 # # #
2014 # # #
2015 # # #
2016 # # #
2017 # # #
2018 # # #
2019 # # #
2020 # # #
2021 # # #
Kamran
  • 1
  • 1

1 Answers1

0

You can try something like this :

SELECT extract(year from y.year)
     , avg(age(y.year, "Date of Entry")) FILTER (WHERE "Group" = 'A' AND "Date of Entry" <= y.year) AS "AVG_Group A"
     , avg(age(y.year, "Date of Entry")) FILTER (WHERE "Group" = 'B' AND "Date of Entry" <= y.year) AS "AVG_Group B"
     , avg(age(y.year, "Date of Entry")) FILTER (WHERE "Group" = 'C' AND "Date of Entry" <= y.year) AS "AVG_Group C"
  FROM (SELECT generate_series(min(date_trunc('year', "Date of Entry")), max(date_trunc('year', "Date of Entry")), interval '1 year') AS year
          FROM your_table
       ) AS y
 CROSS JOIN your_table AS t
 GROUP BY y.year
 ORDER BY y.year

see the test result in dbfiddle.

Edouard
  • 6,577
  • 1
  • 9
  • 20