Snowball's questions

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?

Estou tentando modelar a colocação de peças em uma placa de circuito. Sem nenhuma restrição significativa, meu esquema básico se parece com isso:

create table part (
    part_id bigserial primary key,
    name text not null,
    width double precision not null,
    height double precision not null
);
create table board (
    board_id bigserial primary key,
    width double precision not null,
    height double precision not null
);
create table board_part (
    board_id bigint not null references board,
    part_id bigint not null references part,
    position point not null
);

^{( SQL Fiddle , Visualização )}

Para be b2qualquer board_parts, desejo impor as seguintes restrições:

1. bencontra-se no tabuleiro:

   box(b.position, point(b.part.width,b.part.height))
       <@ box(point(0,0), point(b.board.width,b.board.height))
   

2. be b2não se sobreponham se estiverem no mesmo tabuleiro:

   b.board_id != b2.board_id or
   not (box(b.position, point(b.part.width,b.part.height))
           && box(b2.position, point(b2.part.width,b2.part.height)))
   

Como posso conseguir isso (sem muita duplicação de dados)? Mudar o esquema é bom.

Aqui está minha melhor tentativa (SQL Fiddle) , inspirando-se na resposta de Erwin à minha pergunta anterior . Ele impõe as restrições que eu queria, mas tem muitos dados duplicados na board_parttabela. Imagino que poderia escrever uma função para preencher os campos board_width, board_height, part_widthe part_heightautomaticamente, mas ainda parece errado ter tantos dados duplicados por aí. Além disso, digitar nos campos width/ heightparece um hack.

Tive alguns problemas ao modelar um esquema elétrico em SQL. A estrutura que eu gostaria de capturar é

  part ←────────── pin
   ↑                ↑
part_inst ←───── pin_inst

onde "inst" é a abreviação de "instância".

Por exemplo, eu poderia ter um partamplificador operacional LM358 com pins 1OUT, 1IN-, 1IN+, GND, 2IN+, 2IN-, 2OUT e V _CC . Eu poderia então colocar esta parte em um esquema, criando um part_inste 8 pin_insts.

Ignorando os campos de dados, minha tentativa inicial de um esquema foi

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial primary key,
    part_id bigint not null references parts
);
create table part_insts (
    part_inst_id bigserial primary key,
    part_id bigint not null references parts
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null references part_insts,
    pin_id bigint not null references pins
);

O principal problema com esse esquema é que a pin_instpode estar vinculado a part_instwith , part_id=1mas pintem part_id=2.

Eu gostaria de evitar esse problema no nível do banco de dados, e não no nível do aplicativo. Então, modifiquei minhas chaves primárias para impor isso. Marquei as linhas alteradas com --.

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial,                                          --
    part_id bigint not null references parts,
    primary key (pin_id, part_id)                              --
);
create table part_insts (
    part_inst_id bigserial,                                    --
    part_id bigint not null references parts,
    primary key (part_inst_id, part_id)                        --
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null,                              --
    pin_id bigint not null,                                    --
    part_id bigint not null references parts,                  --
    foreign key (part_inst_id, part_id) references part_insts, --
    foreign key (pin_id, part_id) references pins              --
);

Minha reclamação com esse método é que ele polui as chaves primárias: em todos os lugares em que me refiro a um part_inst, preciso acompanhar tanto o part_inst_idquanto o part_id. Existe outra maneira de aplicar a restrição pin_inst.part_inst.part_id = pin_inst.pin.part_idsem ser excessivamente prolixo?