Início / dba / Perguntas / 173044
Accepted
Snowball
Asked: 2017-05-09 15:35:27 +0800 CST

Funções agregadas aninhadas em diferentes dimensões

  • 772

Eu tenho uma tabela onde gravo periodicamente a taxa de transferência de todas as interfaces de rede em um conjunto de servidores:

create table net (
    ts timestamptz not null,
    host text not null,
    interface text not null,
    recv_bytes bigint not null
);
create index on net (ts);
insert into net (ts, host, interface, recv_bytes) values
    ('2017-01-01 00:00:00+00', 'a', 'eth0',  500),
    ('2017-01-01 00:00:00+00', 'b', 'eth1', 2000),
    ('2017-01-01 00:00:01+00', 'b', 'eth0', 1000),  -- measurements arrive with some jitter, +1s here
    ('2017-01-01 00:00:02+00', 'c', 'eth0',  100),  -- only present in this interval
    ('2017-01-01 00:00:04+00', 'b', 'eth1', 1900),
    ('2017-01-01 00:00:05+00', 'a', 'eth0',  550),
    ('2017-01-01 00:00:05+00', 'b', 'eth0', 1200),

    ('2017-01-01 00:00:10+00', 'a', 'eth0',  600),
    ('2017-01-01 00:00:10+00', 'b', 'eth0', 1500),
    ('2017-01-01 00:00:11+00', 'b', 'eth1', 1900),
    ('2017-01-01 00:00:15+00', 'a', 'eth0',  600),
    ('2017-01-01 00:00:15+00', 'b', 'eth1', 1400),
    ('2017-01-01 00:00:16+00', 'b', 'eth0', 1400),
    ('2017-01-01 00:00:16+00', 'b', 'eth1', 1700);  -- (b,eth1) appears 3 times in this interval

Gostaria de encontrar a taxa de transferência total em todas as interfaces para um determinado intervalo de tempo, em média em intervalos de 10 segundos. Se um par (host, interface) estiver faltando dados para um determinado intervalo, ele pode simplesmente ser omitido. Isto é o que eu inventei:

select ts_interval, host, sum(recv_bytes) as recv_bytes
from (
    select
        to_timestamp(floor(extract(epoch from ts) / 10) * 10) as ts_interval,
        host,
        interface,
        avg(recv_bytes) as recv_bytes
    from net
    group by ts_interval, host, interface
) avg_net
where ts_interval >= '2017-01-01 00:00:00+00' and ts_interval < '2017-01-01 00:00:20+00'
group by ts_interval, host
order by ts_interval, host;

Resultado:

      ts_interval       | host |      recv_bytes
------------------------+------+-----------------------
 2017-01-01 00:00:00+00 | a    |  525.0000000000000000
 2017-01-01 00:00:00+00 | b    | 3050.0000000000000000
 2017-01-01 00:00:00+00 | c    |  100.0000000000000000
 2017-01-01 00:00:10+00 | a    |  600.0000000000000000
 2017-01-01 00:00:10+00 | b    | 3116.6666666666666667

Esta consulta parece excessivamente detalhada. A essência do que estou tentando fazer é somar em uma dimensão e fazer a média na outra. Em pseudo-SQL:

select
    to_timestamp(floor(extract(epoch from ts) / 10) * 10) as ts_interval,
    host,
    sum(avg(recv_bytes OVER ts_interval) OVER host) as recv_bytes
from net
where ts >= '2017-01-01 00:00:00+00' and ts < '2017-01-01 00:00:20+00'
group by ts_interval, host
order by ts_interval, host;

Existe uma maneira mais concisa de fazer isso?

postgresql aggregate

2 respostas

  1. Best Answer

    Consulta

    Você pode usar generate_series()para gerar um raster de tempo para ingressar em:

    SELECT g AS ts_interval, host, sum(recv_bytes) AS recv_bytes
FROM  (
   SELECT g, host, interface, avg(recv_bytes) AS recv_bytes
   FROM   generate_series(timestamptz '2017-01-01 00:00:00+00'
                        , timestamptz '2017-01-01 00:00:10+00'  -- 10 - only lower bound
                        , interval '10 sec') g
   JOIN   net n ON ts >= g
               AND ts <  g + interval '10 sec'
   GROUP  BY g, host, interface
   ) sub
GROUP  BY g, host
ORDER  BY g, host;

    Mesmo resultado. Não menos detalhado, mas muito mais rápido se você tiver um índice no (ts).

    Então, novamente, o ponto crucial é ter predicados " sargáveis ", que você também obtém sem generate_series()desta forma:

    SELECT ts_interval, host, sum(recv_bytes) as recv_bytes
FROM  (
   SELECT to_timestamp(trunc(extract(epoch from ts) / 10) * 10) AS ts_interval
        , host, interface, avg(recv_bytes) as recv_bytes
   FROM   net
   WHERE  ts >= '2017-01-01 00:00:00+00'
   AND    ts <  '2017-01-01 00:00:20+00' -- just make sure to match bounds
   GROUP  BY 1, 2, 3
   ) avg_net
GROUP  BY 1, 2
ORDER  BY 1, 2;

    Ponto menor: Com números exclusivamente positivos, trunc()é equivalente floor()e um pouco mais rápido.

    Estou usando números ordinais, já que você pediu um código "menos detalhado". Mas esse não é o cerne da questão aqui...

    Pergunta principal

    Você pode executar uma função de janela sobre uma função agregada em um nível de consulta (embora normalmente não seja mais rápido do que usar uma subconsulta).

    Mas não o contrário: você não pode executar uma função agregada sobre uma função de janela em um nível de consulta. Simplesmente não é possível. As funções de janela acontecem após as funções agregadas no SQL.
    Relacionado:

    • 3

  2. Se houver uma quantidade limitada de interfaces, pode ser

    select
  to_timestamp(floor(extract(epoch from ts) / 10) * 10) as ts_interval,
  host,
  coalesce(avg(recv_bytes) filter (where interface = 'eth0'), 0) + 
  coalesce(avg(recv_bytes) filter (where interface = 'eth1'), 0) +
  coalesce(avg(recv_bytes) filter (where interface = 'wlan0'), 0) -- etc
from
  net
group by 1,2
order by 1,2;

    Mas não tenho certeza se é realmente melhor do que a sua consulta original.

    • 2

relate perguntas