Início / dba / Perguntas / 173226
Accepted
eComEvo
Asked: 2017-05-11 05:20:36 +0800 CST

Calculando o intervalo em dias com base na velocidade média

  • 772

Tenho uma orderstabela e preciso calcular quando há uma mudança na contagem média ao longo do tempo. Isso pode ser um aumento ou uma diminuição.

Por exemplo, se geralmente há 1 pedido a cada dois dias, preciso detectar se de repente aumentou para 3 pedidos todos os dias ou diminuiu para 1 pedido a cada 3 dias.

Exemplo de estrutura e dados da tabela:

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

No conjunto de dados acima, suponho que eu queria detectar que, ao longo da última semana, geralmente há apenas 1 pedido a cada 2 dias, mas nas últimas 24 horas esse padrão foi violado com 3 pedidos em um dia.

Resultado desejado:

  • Média de pedidos dos 7 dias anteriores por dia (não incluindo as últimas 24 horas): 0,43
  • Velocidade média do pedido dos 7 dias anteriores (não incluindo as últimas 24 horas): 1 pedido a cada 2 dias
  • Pedidos das últimas 24 horas: 3

Cenário alternativo:

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

Como alternativa, suponha que eu tenha o conjunto de dados acima. Normalmente, há aproximadamente 3 pedidos todos os dias na última semana, mas nas últimas 24 horas houve apenas 1 pedido.

Resultado desejado:

  • Média de pedidos dos 7 dias anteriores (não incluindo as últimas 24 horas): 2,57
  • Velocidade média dos pedidos dos 7 dias anteriores (não incluindo as últimas 24 horas): 3 pedidos a cada 1 dia
  • Pedidos das últimas 24 horas: 1

Como eu faria isso?

PostgreSQL 9.6

postgresql postgresql-9.6

1 respostas

  1. Best Answer

    Primeiro conte os pedidos agrupados por dia dos últimos 7 dias e, em seguida, calcule as médias

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

    Usando os primeiros dados de amostra:

    Pedidos agrupados por data:

criado_at | num_orders
:------------- | ---------:
05-05-2017 | 1
2017-05-07 | 1
2017-05-09 | 1

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

    dbfiddle aqui

    Usando dados de segunda amostra:

    Pedidos agrupados por data:

criado_at | num_orders
:------------- | ---------:
04-05-2017 | 3
05-05-2017 | 3
06-05-2017 | 3
2017-05-07 | 3
2017-05-08 | 3
2017-05-09 | 3

    AvgOrderVelocity | AvgLast7Days | Last24h
:--------------- | :----------- | ------:
 3.00            |  2.57        |       1

    dbfiddle aqui

    • 0

relate perguntas