yossico's questions

我有一个 SQLite DB，假设 id 可以被视为行的顺序，我想根据某些标准在表中查找重复序列。

考虑这个例子（http://sqlfiddle.com/#!5/dbdb0/1）

架构+数据：

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');

基本查询：

select * from eventLog
order by id asc

结果：

我突出显示了一些序列：如果我查看“详细信息”中的重复序列，结果应该是 ids：[1,2][5,6]，如果我查看“类型”列，结果应该是 [[ 1,2][5,6]]，[[3,4][6,7]]。

有没有一种方法无需外部编程即可查询重复序列 - 仅使用 SQL？

我有两张表，一张用于资源，另一张用于所需分配。

例如：

资源表告诉我从每个资源中得到了多少东西，所需表告诉我需要多少资源来分配这个项目。

在上面的例子中：

• 对于 X，我们需要分配 3 次 A 和 1 次 B。
• 对于 Y，我们需要分配 1 个时间 B 和 2 个时间 C。
• 对于 Z，我们需要分配 5 次 C。

我们将有一些备用的 C 项目...

目前，我尝试为两个表添加一个计算列（运行总计 - https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server）：

并且有一个表的笛卡尔乘法，但这是我的死胡同，我的主要问题是如何有一个公式知道将一个资源分成多个分配或从多个资源中分配一个分配。

有人可以提出线索吗？

编辑

SqlFiddle 链接：http ://sqlfiddle.com/#!18/de1417/3

架构：

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);

查询：

 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;

我的问题或多或少是理论上的——按时间间隔获取查询数据的最佳方法是什么。

情况

我有一个管理计数器的系统（数以千计的计数器 - 计算电力/水的消耗量等......），每 15 分钟对计数器进行采样并存储在数据库中。有一张表存储所有计数器数据 [id、counterID、Timestamp、CounterValue]。

现在，我想查询有关Interval（每小时/每天/每周/每月）消耗的数据。

间隔消耗定义为下一个间隔开始时的第一次测量（计数）减去当前间隔开始时的第一次测量。由于所有测量值都被聚合（计数器），因此两个计数器之间的增量表示间隔内的消耗。

因为有很多数据，我希望查询速度快，所以我需要一种更好的方法，然后只查询整个表......（我认为......）查询整个数据（即使数据量很小 - 这个查询是减缓...）：

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;

我想到了一些方法来做到这一点：

1. 在测量表中添加 [isHourly、isDaily、isWeekly...] 布尔列，当我存储数据时，我将更新这些标志。
2. 为每个间隔添加包含列 [id、date、counterId、counterDataRecord] 的表，这样就会有 tblDailyCounter、tblWeeklyCounter 等...

我还有其他想法吗？

这些方法的优点和缺点是什么？

最推荐/最佳实践是什么？