yossico's questions

Eu tenho um banco de dados SQLite, vamos supor que o id possa ser considerado como a ordem das linhas, quero encontrar sequências repetidas na tabela de acordo com determinados critérios.

considere este exemplo ( http://sqlfiddle.com/#!5/dbdb0/1 )

esquema + dados:

CREATE TABLE eventLog 
    (
     id integer primary key autoincrement, 
     type varchar(20), 
     details varchar(30)
    );

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power on');

INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning started');

INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning finished');

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power off');

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power on');

INSERT INTO eventLog
(type, details)
VALUES
('cleaner', 'cleaning started');

INSERT INTO eventLog
(type, details)
VALUES
('power', 'power off');

Consulta básica:

select * from eventLog
order by id asc

resultado:

Destaquei algumas sequências: se eu olhar as sequências repetidas em "detalhes" o resultado deverá ser os ids: [1,2][5,6] e se eu olhar a coluna "tipo" o resultado deverá ser [[ 1,2][5,6]],[[3,4][6,7]].

Existe uma maneira de consultar sequências repetidas sem programação externa - usando apenas SQL?

Tenho duas tabelas, uma para recursos e outra para a alocação necessária.

Por exemplo:

A tabela de recursos informa quantas coisas eu tenho de cada recurso e a tabela necessária informa quantos recursos eu preciso para alocar esse item.

No exemplo acima:

• Para X, precisamos atribuir 3 vezes A e 1 vez B.
• Para Y, precisamos atribuir 1 vez B e 2 vezes C.
• Para Z, precisamos atribuir 5 vezes C.

Teremos alguns itens C de reposição…

Atualmente, tentei adicionar uma coluna calculada para ambas as tabelas (Running total - https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server ):

E ter uma multiplicação cartesiana das tabelas, mas esse é o meu beco sem saída, meu principal problema é como ter uma fórmula que saberá dividir um recurso em várias atribuições ou uma atribuição de vários recursos.

Alguém pode sugerir uma pista?

EDITAR

Link do SqlFiddle: http://sqlfiddle.com/#!18/de1417/3

Esquema:

CREATE TABLE Resources (
    ResID int primary key,
    ResName varchar(255),
    ResCnt int
);

CREATE TABLE Required (
    ReqID int primary key,
    ReqName varchar(255),
    ReqCnt int
);

insert into Resources values(1, 'A', 3);
insert into Resources values(2, 'B', 2);
insert into Resources values(3, 'C', 8);

insert into Required values(101, 'X', 4);
insert into Required values(102, 'Y', 3);
insert into Required values(103, 'Z', 5);

Consultas:

 SELECT ResName, ResCnt,
  SUM(ResCnt) OVER(ORDER BY ResName 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Resources;
  
  
  SELECT ReqName, ReqCnt,
  SUM(ReqCnt) OVER(ORDER BY ReqName 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS RunningTotal
  FROM Required;

Minha pergunta é mais ou menos teórica - qual é a melhor abordagem para obter dados de consulta por intervalos.

A situação

Eu tenho um sistema que gerencia contadores (milhares de contadores - conta consumo de eletricidade/água etc...), a cada 15 minutos o contador é amostrado e armazenado no banco de dados. existe uma tabela que armazena todos os dados dos contadores [id, counterID, Timestamp, CounterValue].

Agora, quero consultar os dados sobre o consumo do intervalo (horário/diário/semanal/mensal).

O consumo intervalado definido como a primeira medição (contagem) no início do próximo intervalo menos a primeira medição no início do intervalo atual. como todas as medições são agregadas (contadores) o delta entre dois contadores representa o consumo no intervalo.

Como há MUITOS dados e quero que as consultas sejam rápidas, preciso de uma maneira melhor do que apenas consultar a tabela inteira ... (eu acho ...) consultar todos os dados (mesmo com pequena quantidade de dados - essa consulta é lento...):

SELECT *, dateadd(hour,datediff(hour,0, dateOfData),0) as roundedTime
FROM (  SELECT *, ROW_NUMBER() OVER(PARTITION BY  counterId,datediff(hour, 0, dateOfData) ORDER BY dateOfData asc) row_number
        FROM counters_data) A
WHERE row_number = 1
Order by dateOfData;

Pensei em algumas maneiras de fazer isso:

1. Adicione colunas booleanas [isHourly,isDaily,isWeekly...] na tabela de medições e quando eu armazenar os dados, atualizarei esses sinalizadores.
2. Adicione tabelas para cada intervalo com colunas [id, date, counterId, counterDataRecord] para que haja tblDailyCounter, tblWeeklyCounter etc...

há alguma outra ideia que estou perdendo?

quais são os prós e contras dessas abordagens?

qual é a prática mais recomendada/melhor?