Início / dba / Perguntas / 151565
Accepted
Christian Schmitt
Asked: 2016-10-07 03:20:03 +0800 CST

postgresql GROUP BY e filtro de data mais recente por não agrupar por instrução?

  • 772

atualmente tenho a seguinte tabela:

CREATE TABLE demo (
  id SERIAL PRIMARY KEY,
  key TEXT NOT NULL,
  other_key TEXT NOT NULL,
  quantity BIGINT NOT NULL,
  date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

Agora eu quero agrupar pela consulta assim:

SELECT other_key, SUM(quantity) FROM demo GROUP BY other_key;

Isso funciona até agora tudo bem, porém agora eu quero filtrar pela chave e também imprimir o mais recente datepara a tabela, existe uma boa maneira de fazer isso?

Pseudo (falhará porque a chave não está agrupada por)

SELECT other_key, SUM(quantity), MAX(date) FROM demo GROUP BY other_key WHERE key = ?;

minha ideia inicial é uma subconsulta:

SELECT other_key, SUM(quantity), MAX(date) FROM (SELECT * FROM demo WHERE key = ?) GROUP BY other_key;

Existe uma maneira melhor de fazer isso? E qual seria um bom índice para a tabela?

Meu índice atual é:

CREATE INDEX demo_all_idx ON demo (key, other_key, quantity);

Bônus (editado):

  • Existe alguma maneira de classificar pelo MAX (data) então?

  • Existe uma maneira de criar uma função agregada que obtenha a data mais antiga em que a quantidade é maior que zero? ou seja, algum tipo de armazenamento de evento de inventário onde a data mais recente não deve ser a última entrada feita e, em vez disso, a mais recente entrada feita onde a quantidade não é subtraída / zero? como considere a seguinte tabela:

    id | key     | other_key | quantity | date
---+---------+-----------+----------+-----------------------
 6 | 0A19882 | 01/01     |      100 | 2016-08-30 00:00:00+02
 7 | 0A19882 | 01/02     |      -50 | 2016-09-01 00:00:00+02
 8 | 0A19882 | 01/01     |      100 | 2016-09-02 00:00:00+02
 9 | 0A19882 | 01/02     |      100 | 2016-08-31 00:00:00+02
11 | 0A19882 | 01/03     |      100 | 2016-08-31 00:00:00+02
12 | 0A19882 | 01/03     |     -100 | 2016-09-02 00:00:00+02
13 | 0A19882 | 01/03     |      100 | 2016-09-04 00:00:00+02

A data para 01/01 deveria ser 2016-08-30 00:00:00+02enquanto para 01/03 deveria ser 2016-09-04 00:00:00+02pois o evento com id 12 chegou a zero.

postgresql

1 respostas

  1. Best Answer

    A WHEREcláusula vai antes de GROUP BY:

    SELECT 
    other_key, 
    SUM(quantity) AS sum_quantity,
    MAX(date)     AS max_date 
FROM demo 
WHERE key = ?
GROUP BY other_key 
ORDER BY max_date ;

    A propósito, keyé uma palavra-chave reservada no SQL - embora não seja no Postgres. Seria melhor ser evitado como um nome de coluna ou tabela.

    Para a pergunta adicional, para calcular também as somas cumulativas (ordenadas por data) e depois encontrar a data (mais antiga) em que essas somas ficaram positivas e permaneceram positivas, é mais fácil fazer com algumas funções de janela:

    SELECT 
    other_key,  
    total_sum_quantity, max_date,
    CASE WHEN cumulative_sum > 0 THEN cumulative_sum END AS cumulative_sum,
    CASE WHEN cumulative_sum > 0 THEN date END AS oldest_positive_strike_date
FROM
  ( SELECT 
        *,
        ROW_NUMBER()
            OVER (PARTITION BY other_key
                  ORDER BY date DESC)  AS rn   
    FROM 
      ( SELECT 
            other_key, quantity, date,
            SUM(quantity) OVER (PARTITION BY key, other_key) AS total_sum_quantity,
            MAX(date) OVER (PARTITION BY key, other_key)     AS max_date,
            SUM(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS cumulative_sum,
            LAG(quantity) OVER (PARTITION BY key, other_key
                                ORDER BY date)               AS prev_quantity
        FROM demo 
        WHERE key = '0A19882'
      ) AS t
    WHERE (cumulative_sum  > 0 AND cumulative_sum-quantity <= 0)
       OR (cumulative_sum <= 0 AND cumulative_sum-quantity  > 0)
       OR (prev_quantity IS NULL)
  ) AS t2
WHERE rn = 1 ;

    Teste em rextester.com .

    Algumas notas:

    • O cumulative_sumretornado é a soma cumulativa no ponto do oldest_positive_strike_date. Se a soma cumulativa total não for positiva, ambas as colunas mostrarão NULL.
    • O PARTITION BY key, other_keypode ser substituído por PARTITION BY other_key. Deixei como está, caso precise rodar a consulta não só com um keyvalor e sim com mais, por exemplo. para toda a mesa ou com WHERE key IN (...).
    • O ORDER BY dateserá determinístico se (key, other_key, date)tiver uma UNIQUErestrição/índice. Se houver uma chance de você ter duas linhas com a mesma chave, other_key e date, substitua por algo que possa identificar uma linha, por exemplo. ORDER BY date, id.
    • O índice "óbvio" que beneficiará a consulta seria (key, other_key, date, quantity). O Postgres pode escolher um plano diferente, verificando a tabela ou usando o índice e também verificando os valores na tabela. isso depende de vários fatores. Experimente vários tamanhos de mesa e com a carga de trabalho que você espera.

    • Como a WHERE key = ?condição inicial restringirá as linhas a cerca de 100 (de uma tabela de 100 mil), pode ser mais eficiente usar um CTE que obtenha essas linhas primeiro, usando algo como o seguinte. Você pode se safar com um índice simples (key)e ter um bom desempenho:

      WITH a AS
  ( SELECT * 
    FROM demo
    WHERE key = ?
  ) 
SELECT ... ;          --- the query as it is, without the `WHERE`
    • 5

relate perguntas