Início / dba / Perguntas / 192705
Accepted
Parker
Asked: 2017-12-09 06:30:05 +0800 CST

Como os valores DISTINCT fora de um GROUP BY podem ser contados sem usar subconsultas no PostgreSQL?

  • 772

Estou refatorando algumas lógicas de negócios que normalmente fazemos como processos noturnos em Java e agora estou tentando migrar para o PostgreSQL como um conjunto de visualizações materializadas. Reduzi o problema ao essencial, o que resulta nas três tabelas abaixo ( category_source~10 linhas, category~100k linhas, category_tags~10M linhas).

A lógica é bastante simples: para cada Source, some os valores de cada Tagno conjunto de Categoriesem that Sourcee divida cada uma dessas somas pelo número total de Categoriesem Source.

--DROP TABLE category_tags;
--DROP TABLE category;
--DROP TABLE category_source;

CREATE TABLE category_source
(
  id integer NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE category
(
  label text NOT NULL,
  source integer NOT NULL REFERENCES category_source(id),
  PRIMARY KEY (label)
);

CREATE TABLE category_tags
(
  category text NOT NULL REFERENCES category(label),
  tag text NOT NULL,
  rank real NOT NULL,
  PRIMARY KEY (category,tag)
);

-- Load sample data.
INSERT INTO category_source VALUES (1);
INSERT INTO category_source VALUES (2);

INSERT INTO category VALUES ('C3035240',1);
INSERT INTO category VALUES ('C3035245',1);
INSERT INTO category VALUES ('C3035250',2);

INSERT INTO category_tags VALUES ('C3035240','test',24.00);
INSERT INTO category_tags VALUES ('C3035240','sample',24.00);
INSERT INTO category_tags VALUES ('C3035240','method',20.00);
INSERT INTO category_tags VALUES ('C3035240','variety',18.00);
INSERT INTO category_tags VALUES ('C3035240','explanation',15.00);
INSERT INTO category_tags VALUES ('C3035245','test',20.00);
INSERT INTO category_tags VALUES ('C3035245','extra',21.00);
INSERT INTO category_tags VALUES ('C3035245','method',20.00);
INSERT INTO category_tags VALUES ('C3035245','sample',18.00);
INSERT INTO category_tags VALUES ('C3035245','question',15.00);
INSERT INTO category_tags VALUES ('C3035250','method',10.00);
INSERT INTO category_tags VALUES ('C3035250','explanation',8.00);
INSERT INTO category_tags VALUES ('C3035250','test',6.00);
INSERT INTO category_tags VALUES ('C3035250','question',5.00);
INSERT INTO category_tags VALUES ('C3035250','sample',2.00);
INSERT INTO category_tags VALUES ('C3035250','variety',4.00);

A consulta 1 aqui fica na maior parte do caminho, mas a source_category_countcoluna contém o número de Categoriesque Tagestá para o Source(varia), enquanto o que eu realmente quero é o número total de Categoriesno Source.

SELECT category_source.id,category_tags.tag,
       SUM(category_tags.rank) AS tag_total,
       COUNT(category.label) AS source_category_count,
       SUM(category_tags.rank)/COUNT(category.label) AS source_tag_rank
 FROM
  category_source,category,category_tags
 WHERE
  category_source.id=category.source
  AND category.label=category_tags.category
 GROUP BY category_source.id,category_tags.tag ORDER BY category_source.id,tag_total DESC;

 id |     tag     | tag_total | source_category_count | source_tag_rank
----+-------------+-----------+-----------------------+-----------------
  1 | test        |        44 |                     2 |              22
  1 | sample      |        42 |                     2 |              21
  1 | method      |        40 |                     2 |              20
  1 | extra       |        21 |                     1 |              21
  1 | variety     |        18 |                     1 |              18
  1 | explanation |        15 |                     1 |              15
  1 | question    |        15 |                     1 |              15
  2 | method      |        10 |                     1 |              10
  2 | explanation |         8 |                     1 |               8
  2 | test        |         6 |                     1 |               6
  2 | question    |         5 |                     1 |               5
  2 | variety     |         4 |                     1 |               4
  2 | sample      |         2 |                     1 |               2
(13 rows)

A consulta 2 abaixo produz os resultados que realmente estou procurando:

SELECT q1.*,q2.source_category_count,q1.tag_total/q2.source_category_count AS tag_source_rank FROM
(
  SELECT category_source.id AS source,category_tags.tag,SUM(category_tags.rank) AS tag_total
   FROM category_source
   INNER JOIN category ON (category_source.id=category.source)
   INNER JOIN category_tags ON (category.label=category_tags.category)
   GROUP BY category_source.id,category_tags.tag
) q1,
(
  SELECT source,COUNT(category) AS source_category_count FROM category GROUP BY source
) q2
 WHERE q1.source=q2.source
 ORDER BY source,tag_source_rank DESC
;

     source |     tag     | tag_total | source_category_count | tag_source_rank
    --------+-------------+-----------+-----------------------+-----------------
          1 | test        |        44 |                     2 |              22
          1 | sample      |        42 |                     2 |              21
          1 | method      |        40 |                     2 |              20
          1 | extra       |        21 |                     2 |            10.5
          1 | variety     |        18 |                     2 |               9
          1 | explanation |        15 |                     2 |             7.5
          1 | question    |        15 |                     2 |             7.5
          2 | method      |        10 |                     1 |              10
          2 | explanation |         8 |                     1 |               8
          2 | test        |         6 |                     1 |               6
          2 | question    |         5 |                     1 |               5
          2 | variety     |         4 |                     1 |               4
          2 | sample      |         2 |                     1 |               2
    (13 rows)

A consulta 3 produz resultados equivalentes usando WITH x () SELECT ...:

WITH category_counts AS
(
  SELECT source,COUNT(category) AS source_category_count FROM category GROUP BY source
) 
SELECT category_counts.source,category_tags.tag,
       SUM(category_tags.rank) AS tag_total,
       COUNT(category.label) AS source_category_freq,
       category_counts.source_category_count,
       SUM(category_tags.rank)/category_counts.source_category_count AS source_tag_rank
 FROM category_counts
 INNER JOIN category ON (category_counts.source=category.source)
 INNER JOIN category_tags ON (category.label=category_tags.category)
 GROUP BY category_counts.source,category_counts.source_category_count,category_tags.tag ORDER BY category_counts.source,tag_total DESC;

 source |     tag     | tag_total | source_category_freq | source_category_count | source_tag_rank
--------+-------------+-----------+----------------------+-----------------------+-----------------
      1 | test        |        44 |                    2 |                     2 |              22
      1 | sample      |        42 |                    2 |                     2 |              21
      1 | method      |        40 |                    2 |                     2 |              20
      1 | extra       |        21 |                    1 |                     2 |            10.5
      1 | variety     |        18 |                    1 |                     2 |               9
      1 | explanation |        15 |                    1 |                     2 |             7.5
      1 | question    |        15 |                    1 |                     2 |             7.5
      2 | method      |        10 |                    1 |                     1 |              10
      2 | explanation |         8 |                    1 |                     1 |               8
      2 | test        |         6 |                    1 |                     1 |               6
      2 | question    |         5 |                    1 |                     1 |               5
      2 | variety     |         4 |                    1 |                     1 |               4
      2 | sample      |         2 |                    1 |                     1 |               2
(13 rows)

Embora eu tenha duas consultas de trabalho que produzem os resultados que estou procurando, estou insatisfeito com o uso de duas subconsultas em tabelas desse tamanho (ainda não carreguei todos os meus dados ou fiz nenhum teste de desempenho, estou simplesmente trabalhando em este caso de teste no momento).

Eu sinto que o valor source_category_countque estou procurando está enterrado em algum lugar na Consulta 1 e simplesmente não sei como acessá-lo.

Outra alternativa que estou investigando é COUNT() OVER (PARTITION BY category_source), mas não tenho uma consulta funcional para esse método no momento.

Existe uma consulta mais simples que produzirá os mesmos resultados que a Consulta 2 ou a Consulta 3 (ou seja, uma modificação da Consulta 1 )?

Atualização: Adicionada uma segunda consulta de trabalho.

postgresql group-by

4 respostas

  1. enquanto o que eu realmente quero é o número total de categorias na fonte.

    Você está agrupando por category_source.id, category_tags.tag-- isso significa que você nunca pode dizer que quer " no " sem incluir esse grupo, que no seu caso inclui tags. As duas sub-seleções com diferentes GROUP BY's são o método aceito de fazer isso , mas existem outras opções para gerar os dados que você deseja, como GROUPING SETS; no entanto, o resultado não será o mesmo.

    SELECT
        c.label,
        tag,
        SUM(ct.rank) AS tag_total,
        COUNT(c.label) AS source_category_count
FROM category AS c
JOIN category_tags AS ct
        ON (ct.category=c.label)
GROUP BY GROUPING SETS ((c.label, ct.tag), (c.label))

;
  label   |     tag     | tag_total | source_category_count 
----------+-------------+-----------+-----------------------
 C3035240 | explanation |        15 |                     1
 C3035240 | method      |        20 |                     1
 C3035240 | sample      |        24 |                     1
 C3035240 | test        |        24 |                     1
 C3035240 | variety     |        18 |                     1
 C3035240 |             |       101 |                     5
 C3035245 | extra       |        21 |                     1
 C3035245 | method      |        20 |                     1
 C3035245 | question    |        15 |                     1
 C3035245 | sample      |        18 |                     1
 C3035245 | test        |        20 |                     1
 C3035245 |             |        94 |                     5
 C3035250 | explanation |         8 |                     1
 C3035250 | method      |        10 |                     1
 C3035250 | question    |         5 |                     1
 C3035250 | sample      |         2 |                     1
 C3035250 | test        |         6 |                     1
 C3035250 | variety     |         4 |                     1
 C3035250 |             |        35 |                     6
(19 rows)

    Você pode ver aqui que temos dois GROUP BYs no mesmo SELECTe você pode ver como o SQL exibe essa consulta.

    Como nota lateral, sugiro nunca usar junções SQL-89. Não há razão para isso. Grava suas junções explicitamente com [INNER] JOIN.

    • 1

  2. Apenas por diversão * , a consulta poderia ser feita sem subconsultas - se o Postgres tivesse implementado DISTINCTem agregados de janela:

    select distinct
    source,
    tag,
    sum(rank) over (partition by source, tag) as tag_total,
    count(*) over (partition by source, tag) as count,
    count(distinct category) over (partition by source) as count_category,
    sum(rank) over (partition by source, tag)
    / count(distinct category) over (partition by source) as avg_rank
from 
    category c 
    join category_tags ct
        on label = category
order by 
    source,
    tag_total desc ;

    Testado em dbfiddle.uk (Oracle) , simplesmente funciona.

    Em dbfiddle.uk (Postgres) , dá o erro:

    ERROR:  DISTINCT is not implemented for window functions  
LINE 6:     count(distinct category) over (partition by source) as c...  
            ^

    * Eu não recomendaria usar o método acima, mesmo que a sintaxe estivesse disponível. A necessidade de dois conjuntos de agregação diferentes no mesmo resultado requer o uso de duasOVER ()expressões diferentes e o uso deSELECT DISTINCT. Tudo incluído, é provavelmente uma receita para uma eficiência medíocre.

    Uma consulta com 2 tabelas derivadas e depois juntá-las provavelmente seria mais eficiente.

    • 1
  3. Best Answer

    Em vez de se juntar diretamente à categorytabela, você pode se juntar a uma subconsulta com uma função de janela. Você só consulta a tabela uma vez e também obtém seu resultado final.

    SELECT category_source.id AS source,
    category_tags.tag,
    SUM(category_tags.rank) AS tag_total,
    MAX(category_source_count) AS source_category_count,
    SUM(category_tags.rank)/MAX(category_source_count) AS source_tag_rank
FROM category_source
   INNER JOIN 
        (
        SELECT label, source, 
            COUNT(*) OVER (PARTITION BY source) category_source_count 
            FROM category
        ) AS category ON category_source.id=category.source
   INNER JOIN category_tags ON category.label=category_tags.category
GROUP BY category_source.id,category_tags.tag 
ORDER BY category_source.id,tag_total DESC;
    • 1

  4. Talvez um ponto de partida...

    select distinct
  CS.id
, CT.tag
, sum( CT.rank ) over ( partition by C.source, CT.tag order by C.source) as tag_total
, count( CT.category ) over ( partition by C.source, CT.tag order by C.source) as count
, ( sum( CT.rank ) over ( partition by C.source, CT.tag order by C.source) )
  / ( count( CT.category ) over ( partition by C.source, CT.tag order by C.source) 
  ) as tag_source_rank
from category_source CS
  join category C on CS.id = C.source
  join category_tags CT on C.label = CT.category
order by CS.id, tag_total desc
;

    -- Resultado

     id |     tag     | tag_total | count | tag_source_rank 
----+-------------+-----------+-------+-----------------
  1 | test        |        44 |     2 |              22
  1 | sample      |        42 |     2 |              21
  1 | method      |        40 |     2 |              20
  1 | extra       |        21 |     1 |              21
  1 | variety     |        18 |     1 |              18
  1 | explanation |        15 |     1 |              15
  1 | question    |        15 |     1 |              15
  2 | method      |        10 |     1 |              10
  2 | explanation |         8 |     1 |               8
  2 | test        |         6 |     1 |               6
  2 | question    |         5 |     1 |               5
  2 | variety     |         4 |     1 |               4
  2 | sample      |         2 |     1 |               2
(13 rows)

    Dbfiddle (Postgresql 9.5)

    • 0

relate perguntas