Início / dba / Perguntas / 213586
Accepted
AlexGordon
Asked: 2018-07-31 07:23:49 +0800 CST

Postgres 9.5 Materialized View de uma tabela com colunas jsonb

  • 772

Com o objetivo de melhorar o desempenho das consultas em uma tabela bastante grande (mais de 5 milhões de linhas) contendo jsonbcolunas, gostaria de criar visualizações materializadas com dados derivados dessas colunas. Segue uma versão simplificada do problema:

               Definição

CREATE TABLE minha_tabela (id int8, conteúdo jsonb);

               Conteúdo

identificação | contente
-------------------------------------------------- ---------------------------
 1 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 3}}
 2 | { "customer": "John Doe", "items": {"product": "Whiskey","qty": 4}}
 3 | { "customer": "Danny Boy", "items": {"product": "Cerveja","qty": 2}}

Agora eu gostaria de criar duas visualizações materializadas, uma conterá todos os nomes de clientes e a outra conterá a contagem de cada arquivo product.

Assim, o resultado final deve ser:

               Visão materializada 1 / os nomes dos clientes
nome do cliente
-------------
John Doe
Danny Boy

               Visão materializada 2 / a quantidade de todos os produtos

produto | quantidade
------------------
Cerveja | 2
Uísque | 7

Para a primeira visualização materializada, tentei o seguinte:

CRIAR VISTA MATERIALIZADA
  clientes AS
SELECIONAR DISTINTO
  content->'customer' AS customer_name FROM my_table;

Isso funciona bem, mas estou tendo problemas com a segunda visão materializada.

Como devo proceder para agregar as quantidades de cada produto de forma eficiente?

postgresql materialized-view

1 respostas

  1. Best Answer

    Você só precisa GROUP BYdo produto para construir sum()as quantidades dos produtos.

    CREATE MATERIALIZED VIEW products
AS
SELECT "content"->'items'->>'product' "product",
       sum(("content"->'items'->>'qty')::integer) "quantity"
       FROM "my_table"
       GROUP BY "content"->'items'->>'product';
    • 2

relate perguntas