Início / dba / Perguntas / 107485
Accepted
tufelkinder
Asked: 2015-07-20 18:51:07 +0800 CST

Soma/contagem/média contínua ao longo do intervalo de datas

  • 772

Em um banco de dados de transações abrangendo milhares de entidades ao longo de 18 meses, gostaria de executar uma consulta para agrupar todos os períodos possíveis de 30 dias entity_idcom uma SOMA de seus valores de transação e COUNT de suas transações nesse período de 30 dias e retornar os dados de uma maneira que eu possa consultar. Depois de muitos testes, este código realiza muito do que eu quero:

SELECT id, trans_ref_no, amount, trans_date, entity_id,
    SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
    COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
  FROM transactiondb;

E vou usar em uma query maior estruturada algo como:

SELECT * FROM (
  SELECT id, trans_ref_no, amount, trans_date, entity_id,
      SUM(amount) OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_total,
      COUNT(id)   OVER(PARTITION BY entity_id, date_trunc('month',trans_date) ORDER BY entity_id, trans_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS trans_count
    FROM transactiondb ) q
WHERE trans_count >= 4
AND trans_total >= 50000;

O caso que essa consulta não cobre é quando as contagens de transações abrangem vários meses, mas ainda estão dentro de 30 dias uma da outra. Esse tipo de consulta é possível com o Postgres? Se sim, aceito qualquer contribuição. Muitos dos outros tópicos discutem agregações " executando ", não rolando .

Atualizar

O CREATE TABLEroteiro:

CREATE TABLE transactiondb (
    id integer NOT NULL,
    trans_ref_no character varying(255),
    amount numeric(18,2),
    trans_date date,
    entity_id integer
);

Dados de exemplo podem ser encontrados aqui . Estou executando o PostgreSQL 9.1.16.

A saída ideal incluiria todas SUM(amount)as COUNT()transações em um período contínuo de 30 dias. Veja esta imagem, por exemplo:

Exemplo de linhas que idealmente seriam incluídas em um "conjunto", mas não são porque meu conjunto é estático por mês.

O destaque verde da data indica o que está sendo incluído na minha consulta. A linha amarela destacada indica o que eu gostaria que fizesse parte do conjunto.

Leitura anterior:

postgresql aggregate

1 respostas

  1. Best Answer

    A consulta que você tem

    Você pode simplificar sua consulta usando uma WINDOWcláusula, mas isso é apenas encurtar a sintaxe, não alterando o plano de consulta.

    SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date)
             ORDER BY trans_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
    • Também usando o um pouco mais rápido count(*), já que idcertamente está definido NOT NULL?
    • E você não precisa, ORDER BY entity_idjá que você jáPARTITION BY entity_id

    No entanto, você pode simplificar ainda mais:
    Não adicione nada ORDER BYà definição da janela, ela não é relevante para sua consulta. Então você não precisa definir um quadro de janela personalizado:

    SELECT id, trans_ref_no, amount, trans_date, entity_id
     , SUM(amount) OVER w AS trans_total
     , COUNT(*)    OVER w AS trans_count
FROM   transactiondb
WINDOW w AS (PARTITION BY entity_id, date_trunc('month',trans_date);

    Mais simples, mais rápido, mas ainda assim uma versão melhor do que você tem , com meses estáticos .

    A consulta que você pode querer

    ... não está claramente definido, então vou basear-me nestas suposições:

    Contar transações e valor para cada período de 30 dias dentro da primeira e última transação de qualquer entity_id. Exclua os períodos iniciais e finais sem atividade, mas inclua todos os possíveis períodos de 30 dias dentro desses limites externos.

    SELECT entity_id, trans_date
     , COALESCE(sum(daily_amount) OVER w, 0) AS trans_total
     , COALESCE(sum(daily_count)  OVER w, 0) AS trans_count
FROM  (
   SELECT entity_id
        , generate_series (min(trans_date)::timestamp
                         , GREATEST(min(trans_date), max(trans_date) - 29)::timestamp
                         , interval '1 day')::date AS trans_date
   FROM   transactiondb 
   GROUP  BY 1
   ) x
LEFT JOIN (
   SELECT entity_id, trans_date
        , sum(amount) AS daily_amount, count(*) AS daily_count
   FROM   transactiondb
   GROUP  BY 1, 2
   ) t USING (entity_id, trans_date)
WINDOW w AS (PARTITION BY entity_id ORDER BY trans_date
             ROWS BETWEEN CURRENT ROW AND 29 FOLLOWING);

    Isso lista todos os períodos de 30 dias para cada um entity_idcom seus agregados e trans_datesendo o primeiro dia (incl.) do período. Para obter valores para cada linha individual, junte-se à tabela base mais uma vez ...

    A dificuldade básica é a mesma discutida aqui:

    A definição do quadro de uma janela não pode depender dos valores da linha atual.

    E, em vez disso, chame generate_series()com timestampentrada:

    A consulta que você realmente deseja

    Após a atualização e discussão da pergunta:
    Acumule linhas do mesmo entity_idem uma janela de 30 dias começando em cada transação real.

    Como seus dados são distribuídos esparsamente, deve ser mais eficiente executar uma auto-junção com uma condição de intervalo , ainda mais porque o Postgres 9.1 ainda não possui LATERALjunções:

    SELECT t0.id, t0.amount, t0.trans_date, t0.entity_id
     , sum(t1.amount) AS trans_total, count(*) AS trans_count
FROM   transactiondb t0
JOIN   transactiondb t1 USING (entity_id)
WHERE  t1.trans_date >= t0.trans_date
AND    t1.trans_date <  t0.trans_date + 30  -- exclude upper bound
-- AND    t0.entity_id = 114284  -- or pick a single entity ...
GROUP  BY t0.id  -- is PK!
ORDER  BY t0.trans_date, t0.id

    violino SQL.

    Uma janela de rolagem só poderia fazer sentido (em relação ao desempenho) com dados para a maioria dos dias.

    Isso não agrega duplicatas (trans_date, entity_id)por dia, mas todas as linhas do mesmo dia são sempre incluídas na janela de 30 dias.

    Para uma mesa grande, um índice de cobertura como este pode ajudar bastante:

    CREATE INDEX transactiondb_foo_idx
ON transactiondb (entity_id, trans_date, amount);

    A última coluna amountsó é útil se você obtiver varreduras somente de índice dela. Senão, largue.

    Mas não será usado enquanto você seleciona a tabela inteira de qualquer maneira. Ele suportaria consultas para um pequeno subconjunto.

    • 32

relate perguntas