计算许多行的平均数

Question

每一行都是一秒(时间戳增加了一秒)，我想要计算tel1和tel2每分钟的平均值。你能帮上忙吗？

CREATE TABLE "plik3" (
    "timestamp" REAL,
    "tel1"  REAL,
    "tel2"  REAL
);


1577833200.0    0.0         0.0
1577833201.0    0.0         0.0
1577833202.0    0.4000317   0.0
1577833203.0    0.80006206  0.80006146
1577833204.0    0.0         0.40002593
1577833205.0    0.400032    0.0
1577833206.0    0.800055    0.400024
1577833207.0    0.0         1.2000787
1577833208.0    0.40002337  0.8000576
1577833209.0    0.0         0.40002495
1577833210.0    0.0         0.0
1577833211.0    0.4000275   0.0
1577833212.0    0.400032    0.40003648
1577833213.0    0.0         1.2000787
1577833214.0    0.0         0.40003487
1577833215.0    0.8000595   0.0
1577833216.0    0.40002593  0.40003297

基地很大。13 046 098行

Answer 1

您可以对此使用算术和聚合：

select 
    cast(timestamp as int) / 60 * 60 timestamp_minute, 
    avg(tel1) avg_tel1, 
    avg(tel2) avg_tel2
from plik3
group by cast(timestamp as int) / 60 * 60

表达式cast(timestamp as int) / 60 * 60将unix时间戳转换为分钟。

如果要将(四舍五入)时间戳显示为日期时间，则可以在此基础上使用datetime()：

select 
    datetime(cast(timestamp as int) / 60 * 60, 'unixepoch') date_minute, 
    avg(tel1) avg_tel1, 
    avg(tel2) avg_tel2
from plik3
group by datetime(cast(timestamp as int) / 60 * 60, 'unixepoch')

Answer 2

你可以用这个策略。

1577833200.0

• Create
• 首先将起始时间戳用作常量：是一个子查询，其中包含发生操作：的常见分钟

((1577833200.0 - 1577833200.0) /60) = 0
((1577833201.0 - 1577833200.0) /60) = 0
((1577833202.0 - 1577833200.0) /60) = 0
...
...
((1577833261.0 - 1577833200.0) /60) = 1 

此计算将为发生在同一minute中的并发记录输出相同的值。

然后，您需要创建另一个按值分组的查询。

select minute, avg(tel1) as avg_tel1, avg(tel2) as avg_tel2 from
  (select ((`timestamp` - 1577833200.0) / 60) as minute, tel1, tel2 from plik3 as temp)
group by minute;