Início / dba / Perguntas / 332641
Accepted
nbon
Asked: 2023-10-30 21:33:12 +0800 CST

Limite os resultados de consultas complexas aos valores mais frequentes em determinado atributo

  • 772

Digamos que você tenha as seguintes tabelas para uma plataforma de várias lojas

CREATE TABLE orders (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  store_id BIGINT NOT NULL,
  ordered_at TIMESTAMPTZ NOT NULL
);

CREATE INDEX ON orders (store_id);

CREATE TABLE order_lines (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  order_id BIGINT NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
  item_id BIGINT NOT NULL REFERENCES items (id),
  quantity INT
);

CREATE INDEX ON order_lines (order_id);
CREATE INDEX ON order_lines (item_id);

CREATE TABLE items (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  store_id BIGINT NOT NULL,
  color VARCHAR(255) NOT NULL,
  size VARCHAR(255) NOT NULL,
  category VARCHAR(255)
);

CREATE TABLE returns (
  id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  order_line_id BIGINT NOT NULL REFERENCES order_lines (id) ON DELETE CASCADE,
  returned_at TIMESTAMPTZ NOT NULL,
  quantity INT NOT NULL,
  reason VARCHAR(255)
);

CREATE INDEX ON returns (order_line_id);

A partir disso, gostaria de obter uma lista de todos os pedidos durante um período e calcular certas métricas, como quantos itens foram comprados, quantos foram devolvidos, etc. Também gostaria de fazer isso para um subconjunto de itens com base na cor ou tamanho, mas também quero que sejam "classificados". Por exemplo, eu gostaria de mostrar essas métricas para os itens que tiveram uma cor que fez parte das cores mais retornadas, no geral.

O que descobri até agora é fazer isso em duas consultas. A primeira é passar por todas as devoluções e agrupá-las por cor e somar os itens, devolver, conforme abaixo:

select
  i.color,
  trunc(sum(r.quantity)::numeric / sum(ol.quantity)::numeric, 2) as return_rate 
from orders o
inner join
  order_lines ol on ol.order_id = o.id
inner join
  items i on i.id = ol.item_id
left outer join
  returns r on r.order_line_id = ol.id
group by i.color
order by return_rate desc nulls last 
limit 4;

    color     | return_rate 
--------------+-------------
 Black        |       0.43
 Blue         |       0.41
 White        |       0.40
 Yellow       |       0.39

Com base nessa consulta, eu faria uma nova que agruparia todos os pedidos por dia (data) e, em seguida, somaria a taxa de retorno total e a taxa de retorno das principais cores retornadas durante um intervalo de tempo. Eu também gostaria de poder filtrar isso por exemplo, tamanho, categoria, etc. nos itens. Ele será usado para um relatório dinâmico onde as pessoas podem ver o número total de pedidos, devoluções, taxa, bem como um gráfico de linhas da taxa média de devolução, principais cores retornadas em um intervalo de tempo selecionado.

Existe alguma maneira melhor de fazer isso? Parece um pouco errado percorrer e fazer todas as junções duas vezes. Leia um pouco sobre as funções da janela, mas não consegui descobrir se isso era aplicável aqui.

Comecei a mexer aqui com minha abordagem ingênua e também com CTE para tentar reutilizar os dados em massa. https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/10700

Obviamente, o desempenho é um pouco diferente localmente, com cerca de 100 mil linhas. Aqui está um plano de análise explicativo da consulta CTE sendo executada localmente.

QUERY PLAN                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=50730.74..50809.91 rows=2262 width=68) (actual time=350.787..351.912 rows=722 loops=1)
   Group Key: ((order_return_items.date)::date), order_return_items.color
   CTE order_return_items
     ->  HashAggregate  (cost=33884.69..39240.19 rows=226239 width=33) (actual time=211.390..258.041 rows=194309 loops=1)
           Group Key: o.ordered_at, i.color
           Planned Partitions: 4  Batches: 5  Memory Usage: 9265kB  Disk Usage: 7736kB
           ->  Hash Left Join  (cost=5740.52..14725.07 rows=226239 width=25) (actual time=44.080..158.610 rows=226902 loops=1)
                 Hash Cond: (ol.id = r.order_line_id)
                 ->  Hash Join  (cost=3754.69..9321.56 rows=226239 width=29) (actual time=31.592..107.385 rows=226239 loops=1)
                       Hash Cond: (ol.item_id = i.id)
                       ->  Hash Join  (cost=3572.53..8544.81 rows=226239 width=28) (actual time=29.966..79.287 rows=226239 loops=1)
                             Hash Cond: (ol.order_id = o.id)
                             ->  Seq Scan on order_lines ol  (cost=0.00..4378.39 rows=226239 width=28) (actual time=0.012..10.556 rows=226239 loops=1)
                             ->  Hash  (cost=2363.90..2363.90 rows=96690 width=16) (actual time=29.931..29.931 rows=96690 loops=1)
                                   Buckets: 131072  Batches: 1  Memory Usage: 5557kB
                                   ->  Seq Scan on orders o  (cost=0.00..2363.90 rows=96690 width=16) (actual time=0.005..13.289 rows=96690 loops=1)
                       ->  Hash  (cost=137.63..137.63 rows=3563 width=17) (actual time=1.620..1.621 rows=4462 loops=1)
                             Buckets: 8192 (originally 4096)  Batches: 1 (originally 1)  Memory Usage: 300kB
                             ->  Seq Scan on items i  (cost=0.00..137.63 rows=3563 width=17) (actual time=0.014..0.754 rows=4462 loops=1)
                 ->  Hash  (cost=1248.70..1248.70 rows=58970 width=12) (actual time=12.385..12.385 rows=61098 loops=1)
                       Buckets: 65536  Batches: 1  Memory Usage: 3376kB
                       ->  Seq Scan on returns r  (cost=0.00..1248.70 rows=58970 width=12) (actual time=0.007..5.392 rows=61098 loops=1)
   ->  Sort  (cost=11490.55..11496.20 rows=2262 width=52) (actual time=350.765..351.008 rows=6567 loops=1)
         Sort Key: ((order_return_items.date)::date), order_return_items.color
         Sort Method: quicksort  Memory: 626kB
         ->  Hash Join  (cost=6227.62..11364.52 rows=2262 width=52) (actual time=323.014..349.528 rows=6567 loops=1)
               Hash Cond: (order_return_items.color = most_returned_colors.color)
               ->  CTE Scan on order_return_items  (cost=0.00..4524.78 rows=226239 width=56) (actual time=211.393..223.913 rows=194309 loops=1)
               ->  Hash  (cost=6227.60..6227.60 rows=2 width=32) (actual time=111.569..111.571 rows=2 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     ->  Subquery Scan on most_returned_colors  (cost=6227.57..6227.60 rows=2 width=32) (actual time=111.564..111.566 rows=2 loops=1)
                           ->  Limit  (cost=6227.57..6227.58 rows=2 width=64) (actual time=111.564..111.565 rows=2 loops=1)
                                 ->  Sort  (cost=6227.57..6228.07 rows=200 width=64) (actual time=111.562..111.563 rows=2 loops=1)
                                       Sort Key: (trunc((sum(order_return_items_1.r_q) / sum(order_return_items_1.ol_q)), 3)) DESC NULLS LAST
                                       Sort Method: top-N heapsort  Memory: 25kB
                                       ->  HashAggregate  (cost=6221.57..6225.57 rows=200 width=64) (actual time=111.538..111.550 rows=44 loops=1)
                                             Group Key: order_return_items_1.color
                                             Batches: 1  Memory Usage: 48kB
                                             ->  CTE Scan on order_return_items order_return_items_1  (cost=0.00..4524.78 rows=226239 width=48) (actual time=0.000..85.098 rows=194309 loops=1)

Também apreciaríamos feedback sobre a estrutura geral, bem como os índices, se houver!

postgresql

1 respostas

  1. Best Answer

    Consulta

    Sua abordagem com CTEs parece boa para mim. Tenho algumas sugestões:

    WITH order_return_items AS (
   SELECT (o.ordered_at AT TIME ZONE 'Europe/Vienna')::date AS the_day  -- ① deterministic date 
        , i.color
        , sum(r.quantity)::int AS r_q    -- ② cast to int avoid escalation to numeric in next step
        , sum(ol.quantity)::int AS ol_q  -- cannot be 0 (right?!)
   FROM   orders       o
   JOIN   order_lines  ol ON ol.order_id = o.id
   JOIN   items        i  ON i.id = ol.item_id
   LEFT   JOIN returns r  ON r.order_line_id = ol.id
   GROUP  BY 1, 2
   )
, most_returned_colors AS (
   SELECT color  -- don't include total rate while not using it down the line
   FROM   order_return_items
   GROUP  BY 1
   ORDER  BY sum(r_q) * 10000 / sum(ol_q) DESC NULLS LAST   -- ③
           , color  -- ④ tiebreaker!
   LIMIT  5
   )
SELECT the_day, color
     , COALESCE(round((sum(r_q)::numeric / sum(ol_q)), 3), 0) AS rate  -- ⑤
FROM   most_returned_colors m
JOIN   order_return_items   o USING (color)  -- ⑥
GROUP  BY 1, 2
ORDER  BY 1, 2;

    violino

    ① Uma conversão simples de timestamptzpara dateé falha, pois depende da timezoneconfiguração da sua sessão. Pode levar a efeitos muito confusos. Defina o fuso horário para suas datas com um nome de fuso horário inequívoco . Um pouco mais caro, mas confiável. Ver:

    ② No Postgres, para evitar possíveis erros de overflow, sum(int)resulta em bigint, e sum(bigint)resulta em numeric. Mas os cálculos numericsão mais caros. Minha conversão integersó está correta se nunca houver um estouro de número inteiro (parece seguro assumir pedidos por dia e cor). Dessa forma, evitamos o escalonamento para numerica próxima etapa de agregação. Otimização secundária e opcional.

    ③ Minha expressão deve ser mais barata e precisa do que o cálculo em numerice então trunc(n, 3). A questão mais importante, porém: por que arredondar ou truncar para começar? Neste ponto é para classificação, não para exibição...

    ④ Adicionei colorcomo critério de desempate. Use o que achar mais apropriado, mas certifique-se de que a ordem de classificação seja determinística. Caso contrário, você poderá obter um resultado diferente para a mesma consulta e os mesmos dados em sua próxima chamada. E você terá dificuldade em descobrir o porquê.

    ⑤ Para exibição, round()parece fazer mais sentido do que trunc(). Erro menor. Além disso, tentei COALESCE()obter uma taxa de retorno de 0 em vez de nullonde nenhum retorno foi registrado.

    ⑥ A adesão é mais curta e barata. IN (SELECT ...)é potencialmente diferente (e mais caro), pois também dobra duplicatas no lado direito. Ambos são exatamente equivalentes aqui, pois m.colorsão únicos por definição.

    Índices

    Ao processar todas as linhas, você não precisará de nenhum índice além dos índices PK - e provavelmente nem mesmo desses. Na verdade, está tudo Seq Scanno seu plano de consulta. Depois de adicionar WHEREcondições, os índices correspondentes podem ajudar (muito).

    Estrutura geral

    returnsé uma palavra reservada no SQL padrão. O Postgres permite isso, mas eu os evitaria como identificador. Leva a erros confusos e mensagens de erro. Prefiro "the_day" a "date" como nome da coluna por motivos semelhantes.

    VARCHAR(255)normalmente indica um mal-entendido sobre os tipos de string do Postgres. Ver:

    Relacionado:

    • 1

relate perguntas