主页 / dba / 问题 / 173226
Accepted
eComEvo
Asked: 2017-05-11 05:20:36 +0800 CST

根据平均速度计算以天为单位的间隔

  • 772

我有一张orders表，需要计算平均计数随时间变化的时间。这可能是增加也可能是减少。

比如平时两天1单，我需要检测是突然增加到每天3单，还是突然减少到每3天1单。

示例表结构和数据：

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');

在上面的数据集中，假设我想检测在过去一周内通常每 2 天只有 1 个订单，但在过去 24 小时内一天内有 3 个订单违反了该模式。

期望的结果：

  • 前 7 天的平均每天订单数（不包括最近 24 小时）：0.43
  • 前 7 天的平均订单速度（不包括最近 24 小时）：每 2 天 1 个订单
  • 过去 24 小时的订单数：3

替代方案：

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');

或者，假设我有上述数据集。通常过去一周每天大约有 3 个订单，但在过去 24 小时内只有 1 个订单。

期望的结果：

  • 前 7 天的平均订单数（不包括最近 24 小时）：2.57
  • 前 7 天的平均订单速度（不包括最近 24 小时）：每 1 天 3 个订单
  • 最近 24 小时的订单：1

我该怎么做？

PostgreSQL 9.6

postgresql postgresql-9.6

1 个回答

  1. Best Answer

    先统计最近7天按天分组的订单，然后计算平均值

    --= 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;

    使用第一个样本数据：

    按日期分组的订单：

创建时间 | num_orders
:-------- | ----------:
2017-05-05 | 1个
2017-05-07 | 1个
2017-05-09 | 1个

    AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 1.00            |  0.43        |       3

    dbfiddle在这里

    使用第二个示例数据：

    按日期分组的订单：

创建时间 | 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在这里

    • 0

相关问题