Postgresql function for detecting alerts

postgresql

need help with PGSQL function for detecting temperature alerts.
Let's imagine we have tables

**measurment_tab**
id:int
room_id:int
measurment_date:timestamp
value:long

**alert_tab**
id:int
room_id:int
alert_value:long
date_add:timestamp
last_check_date:timestamp
alert_type:varchar [two possible values: below, over]

Last measurement value is:

10|2|2020-01-02 15:33:00|11.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

In this case function should detect alert when:

measurement value is over 12.7
each check I believe should updated last_check_date field to current value (now()) to limit the collection of values to check

I think this scenario is quite simple. Next one is more complicated.

Last measurement value is:

10|2|2020-01-02 15:33:00|13.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

As You can see current measurement value is over alert value. In this case alert shouldn't be triggered. Alert should be triggered when:

measurement value goes below alert value
after that measurement goes again over alert value

Thanks for advice.

Best Answer

If I'm understanding the question correctly?

I would run the alert code in a Insert Trigger, and I would add a column to measurement_tab to track which alerts matched the measurement.

alter table measurement_tab add column trigger_alert_id integer[];

CREATE TRIGGER alert
BEFORE INSERT
ON measurement_tab
FOR EACH ROW
EXECUTE PROCEDURE _alert();

CREATE FUNCTION _alert()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$

declare _r record;

begin

for _r in select id from alert_tab 
        where alert_tab.room_id = measurement_tab.room_id
        and value < alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

for _r in select id from alert_tab 
        where alert_tab.room_id = measurement_tab.room_id
        and value > alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

Run_alert_function(new.trigger_alert_id);  
         # this is where the other alert code 
         # that emails/sends out the alerts gets fired off; 
return new;
end;

$BODY$

If there is need to block alerts that have already been triggered. Meaning once alert has been set it must be reset before it can be fired off again, this can be done with additional logic in the loop, that looks for previous entries in measurement_tab that set this alert previously.

Related Solutions

Postgresql – Selecting the last record that meets a condition

I think LAG() cannot be used here, because with LAG you need to be specific about how many rows back you want to go. (It is 1 by default, but you can specify 3, 10 or any other number. The point, however, is that it must be a specific number.) In your situation, you do not know if the last matching value was on the previous row or on the row before it or even earlier.

So, a different approach would be to find the ID of the last row with the matching value, then look that ID up to get the value for the final output – something like this:

SELECT
  s.id,
  t.val
FROM
  (
    SELECT
      id,
      MAX(CASE WHEN val >=5 THEN id END) OVER (ORDER BY id ASC) AS last_id
    FROM
      test
  ) AS s
  INNER JOIN test AS t ON s.last_id = t.id
ORDER BY
  s.id ASC
;

Or you could use a correlated subquery to get the last value that is more than 5 in the subset from the lowest ID to the current ID:

SELECT
  id,
  (
    SELECT
      sub.val
    FROM
      test AS sub
    WHERE
      sub.id <= main.id
      AND sub.val >= 5
    ORDER BY
      id DESC
    LIMIT
      1
  ) AS val
FROM
  test AS main
ORDER BY
  id ASC
;

This would be similar to LAG() but more flexible (and likely less efficient) – a LAG() with a tweak, if you like.

Postgresql – Aggregate sales of the past 12 months for the current row date

Something like this should work..

-- IN A CTE
-- Grab the idclient, and the monthly range needed
-- We need the range because you can't sum over NULL (yet, afaik).
WITH idclient_month AS (
  SELECT idclient, month_transac
  FROM (
    SELECT idclient, min(month_transac), max(month_transac)
    FROM foo
    GROUP BY idclient
  ) AS t
  CROSS JOIN LATERAL generate_series(min::date, max::date, '1 month')
    AS gs(month_transac)
)
-- If we move this where clause down the rows get filtered /before/ the window function
SELECT *
FROM (

  SELECT
    idclient,
    month_transac,
    monthly_sales,
    sum(monthly_sales) OVER (
      PARTITION BY idclient
      ORDER BY month_transac
      ROWS 12 PRECEDING
    )
      - monthly_sales
      AS sales_ttm

  -- Here, we sum up the sales by idclient, and month
  -- We coalesce to 0 so we can use this in a window function
  FROM (
    SELECT idclient, month_transac, coalesce(sum(sales), 0) AS monthly_sales
    FROM foo
    RIGHT OUTER JOIN idclient_month
      USING (idclient,month_transac)
    GROUP BY idclient, month_transac
    ORDER BY idclient, month_transac
  ) AS t

) AS g
WHERE g.monthly_sales > 0;

Here we

Calculate the date-range for the idclient in a CTE.

SELECT idclient, month_transac
FROM (
  SELECT idclient, min(month_transac), max(month_transac)
  FROM foo
  GROUP BY idclient
) AS t
CROSS JOIN LATERAL generate_series(min::date, max::date, '1 month')
  AS gs(month_transac)
 idclient  |     month_transac      
-----------+------------------------
 511656A75 | 2010-06-01 00:00:00-05
 511656A75 | 2010-07-01 00:00:00-05
 511656A75 | 2010-08-01 00:00:00-05
 511656A75 | 2010-09-01 00:00:00-05
 511656A75 | 2010-10-01 00:00:00-05
 511656A75 | 2010-11-01 00:00:00-05
 511656A75 | 2010-12-01 00:00:00-06
 511656A75 | 2011-01-01 00:00:00-06
 [....]

RIGHT OUTER that CTE to a our sample dataset. We do this so we grow our sample dataset and we have entries with monthly_sales = 0 where needed.
Use a window function that uses windows over ROWS 12 PRECEDING. That's the key. That's the past 12 months. The window function can't operate on rows that are null, so we set them to 0 before we get to this step.
Select just the rows where monthly_sales > 0. We have to do this after the window function so as not to much with what is available for calculation (the window).

Output,

 idclient  |     month_transac      | monthly_sales | sales_ttm 
-----------+------------------------+---------------+-----------
 511656A75 | 2010-06-01 00:00:00-05 |         68.57 |      0.00
 511656A75 | 2010-07-01 00:00:00-05 |         88.63 |     68.57
 511656A75 | 2010-08-01 00:00:00-05 |         94.91 |    157.20
 511656A75 | 2010-09-01 00:00:00-05 |         70.66 |    252.11
 511656A75 | 2010-10-01 00:00:00-05 |         28.84 |    322.77
 511656A75 | 2015-10-01 00:00:00-05 |         85.00 |      0.00
 511656A75 | 2015-12-01 00:00:00-06 |        114.42 |     85.00
 511656A75 | 2016-01-01 00:00:00-06 |        137.08 |    199.42
 511656A75 | 2016-03-01 00:00:00-06 |        172.92 |    336.50
 511656A75 | 2016-04-01 00:00:00-05 |        125.00 |    509.42
 511656A75 | 2016-05-01 00:00:00-05 |        127.08 |    634.42
 511656A75 | 2016-06-01 00:00:00-05 |        104.17 |    761.50
 511656A75 | 2016-07-01 00:00:00-05 |         98.22 |    865.67
 511656A75 | 2016-08-01 00:00:00-05 |         37.08 |    963.89
 511656A75 | 2016-10-01 00:00:00-05 |        108.33 |   1000.97
 511656A75 | 2016-11-01 00:00:00-05 |        104.17 |   1024.30
 511656A75 | 2017-01-01 00:00:00-06 |        201.67 |   1014.05
(17 rows)

Best Answer

Related Solutions

Postgresql – Selecting the last record that meets a condition

Postgresql – Aggregate sales of the past 12 months for the current row date

Related Question