我有一个表datavalue
,其中每个client
的数据几乎为一年,间隔eventtime
为15分钟。我想创建另一个表datavalue_by_hour
,其中我将以1小时的间隔存储datavalue
的数据。为此,我需要:
- 获取
client
和eventhour
不同的值; - 计算上述结果每行
client
和eventhour
的avg(activepower)
; - 统计上述平均函数中使用的
activepower
值的个数
问题是,在mysql或postgres中,这可以很容易地用几乎一条指令完成。与卡桑德拉我猜唯一的方法是通过循环通过每个结果和应用另一个查询,直到最终结果实现…?这看起来效率很低,有没有其他更有效的方法来实现这样的结构?
AFH0AEE00A0BHC 2016-05-24 18:00:00+0000 0.067 4
AFH0AEE00AGCEC 2016-05-24 19:00:00+0000 0.081 4
AFH0ADE0ACDAAE 2016-05-24 20:00:00+0000 0.068 3
AFH0AEE00AGFEC 2016-05-24 21:00:00+0000 0.032 4
我的初始数据值表有这样的结构:
CREATE TABLE datavalue (
client text,
eventhour bigint,
eventtime timestamp,
activepower double,
activepowerclassification double,
dstoffset double,
PRIMARY KEY (( pt, eventhour ), eventtime));
和datavalue
表(+10000000行)中的数据集示例:
AFH0AEE00BFEFB 1473847200 2016-09-14 10:00:00+0000 0.042 0.0 1.0
AFH0AEE00BFEFB 1473847200 2016-09-14 10:15:00+0000 0.056 0.0 1.0
AFH0AEE00BFEFB 1473847200 2016-09-14 10:30:00+0000 0.075 0.0 1.0
AFH0AEE00BFEFB 1473847200 2016-09-14 10:45:00+0000 0.102 0.0 1.0
A0C0AEAFIFEAHE 1472882400 2016-09-03 06:00:00+0000 0.0 0.0 1.0
A0C0AEAFIFEAHE 1472882400 2016-09-03 06:15:00+0000 0.0 0.0 1.0
A0C0AEAFIFEAHE 1472882400 2016-09-03 06:30:00+0000 0.0 0.0 1.0
A0C0AEAFIFEAHE 1472882400 2016-09-03 06:45:00+0000 0.0 0.0 1.0
AFH0ACFB00CD0F 1452693600 2016-01-13 14:00:00+0000 0.244 0.0 0.0
AFH0ACFB00CD0F 1452693600 2016-01-13 14:15:00+0000 0.244 0.0 0.0
AFH0ACFB00CD0F 1452693600 2016-01-13 14:30:00+0000 0.242 0.0 0.0
AFH0ACFB00CD0F 1452693600 2016-01-13 14:45:00+0000 0.244 0.0 0.0
A0C0AEAFIEE0DC 1466319600 2016-06-19 07:00:00+0000 0.036 0.0 1.0
更新模式添加dstCount, dstSum和dstAvg:
CREATE TABLE datavalue (
client text,
eventhour bigint,
eventtime timestamp,
activepower double,
activepowerclassification double,
dstoffset double,
powerCount bigint static,
powerSum double static,
powerAvg double static,
PRIMARY KEY (( client, eventhour ), eventtime));
我使用了静态列,这里是文档
静态列是由同一分区的所有行共享的特殊列。让我们举一个例子:假设我们想要存储每个用户需要支付的账单,并保留每个用户未支付的余额。我们想要保持的不变量是余额总是所有未付账单的总和:
在插入新记录之前,使用此查询选择特定客户端和事件的当前powerCount和powerSum
让你插入记录
client eventhour eventtime activepower
AFH0AEE00BFEFB 1473847200 2016-09-14 10:00:00+0000 0.040
查询将是
SELECT powerCount,powerSum
FROM datavalue
WHERE client = 'AFH0AEE00BFEFB' AND eventhour = 1473847200 LIMIT 1;
现在你得到了当前的powerCount和powerSum让powerCount = 4 and powerSum = 0.275
更新后的数据
powerSum = powerSum + 0.040 = 0.315
powerCount = powerCount + 1 = 5
powerAvg = powerSum/powerCount = 0.063
现在插入记录与新的powerSum, powerCount和powerAvg