PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create a graph for the previous weeks using postgres

I have tables of assets(say 3 assets) which has following columns

  1. assetID
  2. status
  3. updated_date

Now every week there will be a change in status of the few assets. So only changed ones will be populated into this table. So if the status of the 2 assets has changed, then only 2 records will be added.

So after 2nd week, the table will have 5 records.

In 3rd week , the status of one of the assets is changed . The table will 3+2+1 = 6 records

After some weeks, I would like to create a bar chart to show the change of the status for all these assets week wise.

But not sure, how I can achieve this using postgres sql to get correct value for the 3 assets week wise.

Example:
This is how my table looks like

In Week 2024-01-10 – got only 2 records were inserted because there is no change in the status of the A3

In Week 2024-01-17 – got only 1 record was inserted because there is no change in the status of the A2 & A1 from Week 2024-01-10

assetID status updated_date
A1 For Exit 2024-01-03
A2 In Progress 2024-01-03
A3 Gap 2024-01-03
A1 Completed 2024-01-10
A2 For Exit 2024-01-10
A3 In Progress 2024-01-17

Now, if I have to put a graph which shows the status of all the assets each week. How I can achieve this.

So the stacked bar chart x-axis should have 3 values i.e. 3 weeks
And y-axis, the count of assets with stacks of status.

Its easy to achieve this if I had my table like this. But its dynamic table, only assets data which has changed status is populated each week

assetID status updated_date
A1 For Exit 2024-01-03
A2 In Progress 2024-01-03
A3 Gap 2024-01-03
A1 Completed 2024-01-10
A2 For Exit 2024-01-10
A3 Gap 2024-01-10
A1 Completed 2024-01-17
A2 For Exit 2024-01-17
A3 In Progress 2024-01-17
Tags:

2

Answers


  1. 0

    Assuming that you actually want the number of assets that changed in each week, then this is trivial. It is the the number of records added each week. So for each week, count the records with an updated_date in that week.

    If you want something more complex than this you need to edit your question so it explains what it is you do want.

    Login or Signup to reply.
  2. 0

    Asuming the original table is named "table1" the final table is created by

    1. Getting a cross join of all possible assets and updated_dates.
    2. For each of those combinations fetch the possible statusses (status update_date <= date of cross join)
    3. Take a "distinct on" sorted on date desc to only get the latest valid status_change for each combi in cross join

    In SQL this would be:

    SELECT DISTINCT ON (a."assetID", b.updated_date) a."assetid",
                                                 b.updated_date,
                                                 c.status
FROM   (SELECT DISTINCT "assetid"
        FROM   table1) a
       cross join (SELECT DISTINCT updated_date
                   FROM   table1) b
                  left join (SELECT *
                             FROM   table1) c
                         ON a."assetid" = c."assetid"
                            AND b.updated_date >= c.updated_date
ORDER  BY a."assetid",
          b.updated_date,
          c.updated_date DESC

    results look like:

    Result of query

    In your code it might be better to replace table a and table b with other tables where you define all assets and all update_dates (or generateseries if no table of this exists), so there will also be records for assets without updates, and for update_dates without any updates on any asset.

    Then the code would be something like:

    SELECT DISTINCT ON (a."assetID", b.updated_date) a."assetid",
                                                 b.updated_date,
                                                 c.status
FROM   (SELECT DISTINCT "assetid"
        FROM   tbl_assets) a
       cross join (SELECT DISTINCT updated_date
                   FROM   tbl_update_dates) b      -- b can also be defined by a generate_series
                  left join (SELECT *
                             FROM   table1) c
                         ON a."assetid" = c."assetid"
                            AND b.updated_date >= c.updated_date
ORDER  BY a."assetid",
          b.updated_date,
          c.updated_date DESC
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search