Início / dba / Perguntas / 206055
Accepted
Vérace
Asked: 2018-05-08 07:37:25 +0800 CST

PostgreSQL - máximo de soma em diferentes meses com empates em vários anos

  • 772

Esta pergunta é a versão PostgreSQL de uma pergunta sobre o MySQL 5.6 aqui . Originalmente, era uma pergunta para ambos os RDBMSs, mas me foi sugerido que, dadas as diferentes capacidades dos dois sistemas, eu deveria dividir a pergunta - em particular, acho que os CTEs (cláusula WITH) devem tornar a consulta muito mais elegante e legível!

Suponha que eu tenha uma lista de tumores (esses dados são simulados a partir de dados reais):

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

Você quer descobrir qual tumor em particular foi mais comum em um determinado mês - até agora tudo bem!

Agora, você notará que para o mês 1 de 2017, há um empate - então não faz sentido escolher um aleatoriamente e dar isso como resposta - então os empates devem ser incluídos - isso torna o problema muito mais desafiador.

Eu tenho uma solução, mas é bastante complexa - gostaria de saber se minha solução é ótima ou não. O violino PostgreSQL está aqui ! A consulta no violino é muito complicada - vou dar uma olhada no uso de CTEs.

Minha primeira resposta (que funciona com PostgreSQL e MySQL) está incluída no violino, mas não a postarei aqui, pois acredito que será substituída pelos recursos superiores do PostgreSQL e seria apenas uma cópia da minha resposta para o MySQL pergunta!

postgresql cte

3 respostas

  1. Best Answer

    Para um determinado mês :

    SELECT tumour_count, illness
FROM (
   SELECT count(*) AS tumour_count, illness
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- given month: 2007-01
   AND    created_at  <  '2017-02-01'  -- optimized for index lookup
   GROUP  BY illness
   ) sub
WHERE  rnk = 1;

    Deve haver um índice em (created_at), ou talvez até mesmo (created_at, illness)para permitir varreduras somente de índice.

    As subconsultas são um pouco mais rápidas que as CTEs no Postgres. Portanto, use CTEs apenas onde você precisar deles ou quando o desempenho não for importante.

    Relacionado:

    Para qualquer período de tempo

    Conforme solicitado no comentário :

    SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, illness
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
ORDER  BY mon, illness;

    Cuidado se você tiver meses parciais adiantados ou pendentes , as contagens podem ser enganosas.

    Isso é funcionalmente equivalente ao que o ypercube já forneceu . Apenas algumas simplificações para ser um pouco mais curto/mais rápido. E o filtro adicionado por um determinado período de tempo .

    O suporte ao índice torna-se menos importante com a crescente parcela de linhas lidas na tabela - e deixa de ser útil por mais de aproximadamente 5%. (Exceções se aplicam, como para varreduras somente de índice.)

    Você ainda pode ter 1 linha por mês , agregando pares empatados. Curti:

    SELECT to_char(mon, 'YYYY-MM') AS month, tumour_count, string_agg(illness, ' | ')
FROM  (
   SELECT date_trunc('month', created_at) AS mon
        , illness
        , count(*) AS tumour_count
        , rank() OVER (PARTITION BY date_trunc('month', created_at)
                       ORDER BY count(*) DESC) AS rnk
   FROM   illness
   WHERE  created_at  >= '2017-01-01'  -- period from 2007-01 to 2019-01
   AND    created_at  <  '2019-02-01'
   GROUP  BY 1, 2
   ) sub
WHERE  rnk = 1
GROUP  BY mon, tumour_count
ORDER  BY mon;

    db<>fique aqui

    • 5

  2. Usando a RANK()função window and DATE_TRUNC()(para não usar EXTRACT()duas vezes):

      SELECT  
    EXTRACT(YEAR  FROM year_month) AS c_year,
    EXTRACT(MONTH FROM year_month) AS c_month,
    nature_of_illness,
    month_count
  FROM
    (
      SELECT 
        nature_of_illness,
        DATE_TRUNC('month', created_at) AS year_month,
        COUNT(*) AS month_count,
        RANK() OVER (PARTITION BY DATE_TRUNC('month', created_at) 
                     ORDER BY COUNT(*) DESC)
          AS rnk
      FROM illness
      GROUP BY 
        DATE_TRUNC('month', created_at),
        nature_of_illness
    ) AS t
  WHERE rnk = 1 
  ORDER BY 
    year_month, nature_of_illness ;

    Teste em dbfiddle.uk .

    • 4

  3. Eu dei uma olhada nisso e tive a mesma ideia geral que o ypercube. Eu acho que o dele é melhor, mas estou enviando este caso você ache interessante, e porque me diverti fazendo isso :)

    SELECT
    C_YEAR,
    C_MONTH,
    NATURE_OF_ILLNESS,
    MONTH_COUNT
FROM(
SELECT
    EXTRACT(YEAR FROM CREATED_AT) C_YEAR,
    EXTRACT(MONTH FROM CREATED_AT) C_MONTH,
    NATURE_OF_ILLNESS,
    COUNT(NATURE_OF_ILLNESS) MONTH_COUNT,
    MAX(COUNT(NATURE_OF_ILLNESS)) OVER (
        PARTITION BY EXTRACT(YEAR FROM CREATED_AT),  
        EXTRACT(MONTH FROM CREATED_AT)) MAX_MONTH_COUNT
FROM 
    ILLNESS
GROUP BY 
    EXTRACT(YEAR FROM CREATED_AT),
    EXTRACT(MONTH FROM CREATED_AT),
    NATURE_OF_ILLNESS
) AS SICKNESS

WHERE MONTH_COUNT = MAX_MONTH_COUNT
ORDER BY 
    C_YEAR ASC, 
    C_MONTH ASC
    • 2

relate perguntas