3

Given the following table:

CREATE TABLE status_log
(
  entity_id bigint NOT NULL,
  user_id bigint NOT NULL,
  status character varying(128),
  "timestamp" timestamp(6) without time zone
)

I want to query number of entities in time window (grouped by granularity - hours, days, months), specifically number of entities in status before window, number that change into status during window and number that leave status before window ends. My faulty/unoptimized query:

WITH statuses AS (
    SELECT
        status,
        "timestamp",
        lag(status) OVER w AS previous_status,
        lag("timestamp") OVER w AS previous_timestamp,
        lead(status) OVER w AS next_status,
        lead("timestamp") OVER w AS next_timestamp
FROM status_log
WINDOW w AS (PARTITION BY entity_id ORDER BY "timestamp" ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER BY "timestamp" ASC
)
SELECT
    SUM(CASE WHEN previous_timestamp <= date_trunc('hours', statuses.timestamp)
AND previous_status='a' THEN 1 ELSE 0 END) AS entered,
    SUM(CASE WHEN status='a' THEN 1 ELSE 0 END) AS incoming,
    SUM(CASE WHEN status='a' AND next_status <> 'a'
AND next_timestamp <= date_trunc('hours', statuses.timestamp) + INTERVAL '1 hour'
THEN 1 ELSE 0 END) AS handled,
    date_trunc('hours', statuses.timestamp) AS interval
FROM statuses
WHERE statuses.timestamp BETWEEN '2016-07-24T00:00:00'::timestamp AND '2016-07-25T23:59:59.999999'::timestamp
GROUP BY date_trunc('hours', statuses.timestamp)
ORDER BY interval;

I think I have incoming and handled counts right. Is there any easier way than using window functions/precalculating entering entities?

UPDATE:

Trying a little bit different query now, still faulty and slow:

WITH predecesors AS (
    SELECT lag(status) OVER w = 'a' AS valid, lag("timestamp") OVER w AS change, status, "timestamp"
    FROM status_log
    WINDOW w AS (PARTITION BY entity_id ORDER BY "timestamp" ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
), succesors AS (
    SELECT status, "timestamp", lead(status) OVER w <> 'a' AS valid, lead("timestamp") OVER w AS change
    FROM status_log
    WINDOW w AS (PARTITION BY entity_id ORDER BY "timestamp" ASC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
)
SELECT 
    SUM(CASE WHEN predecesors.valid THEN 1 ELSE 0 END) AS enter,
    SUM(CASE WHEN status_log.status = 'a' THEN 1 ELSE 0 END) AS income,
    SUM(CASE WHEN succesors.valid AND
succesors.change < date_trunc('hours', status_log.timestamp) + INTERVAL '1 hour'
THEN 1 ELSE 0 END) AS handle,
    date_trunc('hours', status_log.timestamp) AS interval
FROM status_log
LEFT OUTER JOIN predecesors ON status_log.timestamp = predecesors.timestamp
RIGHT JOIN succesors ON status_log.timestamp = succesors.timestamp
WHERE status_log.timestamp BETWEEN '2016-06-25T00:00:00'::timestamp
AND '2016-07-25T23:59:59.999999'::timestamp
GROUP BY date_trunc('hours', status_log.timestamp)
ORDER BY interval;

UPDATE 2: Desired output:

.------------------.----------.----------.---------.
|      period      | begining | incoming | handled |
:------------------+----------+----------+---------:
| 01:00 01/07/2016 |      180 |       90 |      70 |
:------------------+----------+----------+---------:
| 02:00 01/07/2016 |      200 |       55 |      35 |
:------------------+----------+----------+---------:
| 03:00 01/07/2016 |      220 |       80 |     120 |
:------------------+----------+----------+---------:
| 04:00 01/07/2016 |      180 |       44 |      32 |
:------------------+----------+----------+---------:
| 05:00 01/07/2016 |      192 |       36 |      58 |
:------------------+----------+----------+---------:
| 06:00 01/07/2016 |      170 |       59 |      39 |
:------------------+----------+----------+---------:
| TOTAL            |      892 |      364 |     354 |
'------------------'----------'----------'---------'

Before first interval, there are 180 entities with status a as last status. 90 entities change into status a during the first interval and 70 change into a different status. I think the main problem would be first 'begining' number (then I can calculate the rest).

  • Your description of what you want is not very clear. For example, does "number of entities in status before window" mean they were in a given status immediately before the window, or at any time before the window? I would usually refer to your SQL to clarify what it is that you want, but since you say it is faulty I obviously cannot do that. Can you provide some sample input and sample desired output to clarify what it is you want? – jjanes Aug 1 '16 at 20:46
  • @jjanes Provided sample output with commentary. – sputnikus Aug 2 '16 at 10:12
  • It will more efficient if you use an enum data type for the status column instead. – 3manuek Aug 2 '16 at 17:18
  • by "faulty" do you mean it returns improper data? I also don't see any keys/indexes on status_log, which will certainly impact performance. – mpag Aug 3 '16 at 9:14
  • on first glance, it looks to me that your lead/lag usage isn't quite optimal, assuming that Postgres isn't able to magically treat your windowing function as a single optimized alias for lead and lag. Why not use ROWS 1 PRECEDING [implies between 1 preceding and current row] for the predecessors/lag and ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING for the lead? – mpag Aug 3 '16 at 9:34
3
+25

Get previous status by self join by Row_Number and group by date_part:

   WITH Prev_status AS (
   SELECT entity_id,
           user_id,
           status_id,
           row_number () over (entity_id, user_id
                               ORDER BY TIMESTAMP DESC) AS OrderNumber
   FROM Status_log
   WHERE status_log.timestamp BETWEEN '2016-06-25T00:00:00'::TIMESTAMP AND '2016-07-25T23:59:59.999999'::TIMESTAMP 
    ),

     Cur_status AS (
   SELECT entity_id,
           user_id,
           status_id,
           row_number () over (entity_id, user_id
                               ORDER BY TIMESTAMP DESC) NewOrderNumber
   FROM status_log sl
   WHERE status_log.timestamp BETWEEN '2016-06-25T00:00:00'::TIMESTAMP AND '2016-07-25T23:59:59.999999'::TIMESTAMP )


SELECT date_trunc('day',cs.timestamp) AS Date,
       date_part ('hour'cs.timestamp) AS HourInterval,
       SUM (CASE
                WHEN ps.status ='a' THEN 1
                ELSE 0
            END) ) AS Enter,
           SUM (CASE
                    WHEN cs.status ='a' THEN 1
                    ELSE 0
                END) ) AS Incoming,
               SUM (CASE WHEN ps.status = 'a'
                    AND cs.status <>'a' THEN 1 ELSE 0) AS Handeled

FROM Cur_status sc
JOIN Prev_status ps 
    ON cs.entity_id = ps.entity_id
        AND cs.user_id = cs.entity_id
        AND ps.OrderNumber = cs. NewOrdernumber +1

GROUP BY date_trunc('day',cs.timestamp) AS Date,
         date_part ('hour'cs.timestamp) AS HourInterval

This is core.
This script is prone to double or triple counting when same status for Entity/User posted several times during an hour. Also one time window treated on its own and not aware of status in previous time interval.

If you share example of source data I can update my answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.