Dotaz: půlhodinové průměry

[jura]

Obracím se s prosbou o pomoc, jak vypočítat půhodinové pruměry.

příklad dat
2010-06-01 00:00:26 1.9138
2010-06-01 00:02:07 1.9145
2010-06-01 00:03:49 1.9150
2010-06-01 00:05:30 1.9141
2010-06-01 00:07:12 1.9123
2010-06-01 00:08:53 1.9154
2010-06-01 00:10:34 1.9132
2010-06-01 01:12:16 1.9118
2010-06-01 01:13:57 1.9119
2010-06-01 01:15:38 1.9123

Nemohu přijít na to jak šikovně grupovat data podle datumu a času.

Děkuji za pomoc

Jura

---

---

Odpovědi

[Ivan]

ema exponential moving average. Vice viz treba zdrojaky net-snmp.

[rADOn]

zgrupis podle pulhodin, nejak takhle:

select avg( hodnota ) from sometable group by floor(unix_timestamp( datum ) / 1800)

[Šangala]

Měl jsem to napsané ale neodeslané a je mi to líto smazat, toš:

Jednoduše to lze například na MySQL takto (je to zbytečně rozepsané):

SELECT FLOOR(UNIX_TIMESTAMP(datum)/(60*30)) AS cas,
       FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(datum)/(60*30))*(60*30)) AS cas_date,
       AVG(cislo) AS avg_cislo
         FROM test1 GROUP BY cas;

nad tabulkou:

CREATE TABLE `test1`(
 `datum` datetime NOT NULL , 
 `cislo` double,
  PRIMARY KEY  (`datum`)
);

s daty:

INSERT INTO test1 (datum, cislo) VALUES 
('2010-06-01 00:00:26', 1.9138),
('2010-06-01 00:02:07', 1.9145),
('2010-06-01 00:03:49', 1.9150),
('2010-06-01 00:05:30', 1.9141),
('2010-06-01 00:07:12', 1.9123),
('2010-06-01 00:08:53', 1.9154),
('2010-06-01 00:10:34', 1.9132),
('2010-06-01 01:12:16', 1.9118),
('2010-06-01 01:13:57', 1.9119),
('2010-06-01 01:15:38', 1.9123);

je výsledek:

+--------+---------------------+-----------------+
| cas    | cas_date            | avg_cislo       |
+--------+---------------------+-----------------+
| 708524 | 2010-06-01 00:00:00 | 1.9140428571429 |
| 708526 | 2010-06-01 01:00:00 |           1.912 |
+--------+---------------------+-----------------+

Ale pokud bych to chtěl tak nějak čistější:

SELECT DATE(datum) AS jen_datum, TIME(datum) AS jen_cas,
       FLOOR(TIME_TO_SEC(TIME(datum)) / (60*30)) AS filtr,
       AVG(cislo) AS avg_cislo
         FROM test1 GROUP BY jen_datum, filtr;

A tady je to pro hezký výpis:

SELECT jen_datum, CONCAT_WS('-',TIME_FORMAT(SEC_TO_TIME(filtr*(60*30)),'%H:%i'),TIME_FORMAT(SEC_TO_TIME((filtr+1)*(60*30)),'%H:%i')) AS usek, ROUND(avg_cislo,4) AS rndavg_cislo
 FROM (SELECT DATE(datum) AS jen_datum, TIME(datum) AS jen_cas,
         FLOOR(TIME_TO_SEC(TIME(datum)) / (60*30)) AS filtr,
         AVG(cislo) AS avg_cislo
           FROM test1 GROUP BY jen_datum, filtr) AS t1;

z výsledkem:

+------------+-------------+--------------+
| jen_datum  | usek        | rndavg_cislo |
+------------+-------------+--------------+
| 2010-06-01 | 00:00-00:30 |       1.9140 |
| 2010-06-01 | 01:00-01:30 |       1.9120 |
+------------+-------------+--------------+