2

I have an orders table and need to calculate when there is a change in the average count across time. This could be either an increase or decrease.

For instance, if there is usually 1 order every two days, I need to detect if that suddenly increased to 3 orders every day or decreased to 1 order every 3 days.

Example table structure and data:

CREATE TABLE "public"."orders" (
    "id" SERIAL,
    "created_at" timestamp NOT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '1 hours'),
(now() - interval '6 hours'),
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '4 days'),
(now() - interval '6 days'),
(now() - interval '9 days');

In the data set above, supposed I wanted to detect that over the course of the last week there is usually only 1 order every 2 days, but over the last 24 hours that pattern has been violated with 3 orders in one day.

Desired result:

  • Previous 7 day's average orders per day (not including last 24 hours): 0.43
  • Previous 7 day's average order velocity (not including last 24 hours): 1 order every 2 days
  • Last 24 hour's orders: 3

Alternate scenario:

INSERT INTO "orders" ("created_at")
VALUES
(now() - interval '12 hours'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '2 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '3 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '4 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '5 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '6 days'),
(now() - interval '7 days'),
(now() - interval '7 days'),
(now() - interval '7 days');

Alternatively, suppose I have the above data set. Normally there are approximately 3 orders every day for the past week, but over the last 24 hours there was only 1 order.

Desired result:

  • Previous 7 day's average orders (not including last 24 hours): 2.57
  • Previous 7 day's average order velocity (not including last 24 hours): 3 orders every 1 days
  • Last 24 hour's orders: 1

How would I do this?

PostgreSQL 9.6

  • 1
    Please add the desired result. – McNets May 10 '17 at 14:09
  • @McNets I've updated the question to be more descriptive and include data for each scenario. – eComEvo May 10 '17 at 20:50
  • Yes, but could you add a desired result? how many days should be considered? every 2 days? by week? – McNets May 10 '17 at 20:53
  • To calculate a tendency I need a period of time between samples. – McNets May 10 '17 at 21:00
  • @McNets question updated. Let me know if this is what you need. – eComEvo May 10 '17 at 21:18
0

First count orders grouped by day of last 7 days, and then calculate averages

--= Count orders grouping by date
WITH cn AS
(
    SELECT created_at::date, COUNT(*) num_orders
    FROM   orders
    WHERE  created_at <  (now() - interval '1 days')
    AND    created_at >= (now() - interval '8 days')
    GROUP BY created_at::date
)
SELECT to_char(AVG(num_orders), '9.99') AS "AvgOrderVelocity",
       to_char(SUM(num_orders) / 7::decimal(18,2), '0.99')  AS "AvgLast7Days",
       (SELECT COUNT(*)
        FROM   orders
        WHERE  created_at > (now() - interval '1 days')) "Last24h"
FROM   cn;

Using first sample data:

Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-05 |          1
2017-05-07 |          1
2017-05-09 |          1
AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 1.00            |  0.43        |       3

dbfiddle here

Using second sample data:

Orders grouped by date:

created_at | num_orders
:--------- | ---------:
2017-05-04 |          3
2017-05-05 |          3
2017-05-06 |          3
2017-05-07 |          3
2017-05-08 |          3
2017-05-09 |          3
AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 3.00            |  2.57        |       1

dbfiddle here

|improve this answer|||||
  • When I run this on my database, I get an accurate number for Last24h but I get #.## in both the AvgOrderVelocity and AvgLast7Days. Any idea why that is? – eComEvo May 16 '17 at 0:11
  • Ahh, I see the problem. The numbers I get on my dataset are so large that '9.99' in the char cast is insufficient. Changing to '999.99' solved the issue. However, I have no idea just how many digits a given result will have. Is there any reason not to just do round(AVG(num_orders), 2)? – eComEvo May 16 '17 at 0:45
  • No there isn't, I've just used this format to fit your desired result. – McNets May 16 '17 at 7:40

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.