在两个月之前需要数据库小时数据 [英] need database hourly data before two months

问题描述

我有一个包含cpuload，freememory，diskspace，cpu利用率和主机名字的表格。我正在运行一个cron作业来获取所有主机中每10分钟的数据（例如：4台主机）。
现在我有一年的数据库数据。

我想将这些数据转换为小时平均数据。但我只需要两个月前的数据。最近两个月的数据不应该打扰。
我的数据是这样的

 主机名| cpuload | freedisk | freemem |时间戳
 localhost.localdomain | 0.15 | 136052 | 383660 | 2017-08-01 00:10:08 
 localhost.localdomain | 0.03 | 136492 | 383660 | 2017-08-01 00:20:08 
 localhost.localdomain | 0.01 | 133456 | 383660 | 2017-08-01 00:30:08 
 localhost.localdomain | 0.10 | 64544 | 383660 | 2017-08-01 00:40:08 
 localhost.localdomain | 0.01 | 68148 | 383660 | 2017-08-01 01:00:08 
  

每10分钟数据将添加至今天，即 2017-10-12 11:00:00

现在我想从两个月前的数据-12 11:00:00到2017-08-01 00:00:00数据我需要转换为小时数据

  SELECT Avg（CpuLoad）AS avgcpuload 
，Avg（FreeMemory）AS avgfreemem 
，Avg（FreeDisk）AS avgfreedisk 
，Avg（Cpu_util）AS avgcpuutil 
 FROM Dynamic_Host_Information; 
  

我得到了 avg 但我想要它仅两个月后才有数据。

请在这帮助我。
在此先感谢
sagar reddy

解决方案

一种方法可以是组通过您的数据由仅限日期和小时您的时间戳列的一部分以及其中子句将以下 2 个月的数据排除在外，以获得您想要的结果。

  SELECT t1.hostname 
，avg（cpuload）AS cpuload 
，avg（freedisk）AS freedisk 
，avg（freemem） AS freemem 
 FROM table1 t1 
 WHERE t1.TIMESTAMP< current_timestamp  - 间隔'42'天
 GROUP BY date_format（t1.TIMESTAMP，'％Y％m％d％H'）; 
  

INTERVAL'42'DAY 用于排除 10月和 9月数据。您可以相应地进行调整。

结果：

  hostname cpuload freedisk freemem 
 -------------------------------------- ----------------------- 
 localhost.localdomain 0.072500 117636.0000 383660.0000 
 localhost.localdomain 0.010000 68148.0000 383660.0000 
  

---

更新1：

如果您需要提及的时间段的数据。

  SELECT t1.hostname 
，avg（cpuload）AS cpuload 
，avg（freedisk）AS freedisk 
，avg（freemem）AS freemem 
 FROM table1 t1 
 WHERE t1.TIMESTAMP between'2017- 08-01 00:00:00'和'2017-08-12 11:00:00'
 GROUP BY date_format（t1.TIMESTAMP，'％Y％m％d％H'）; 
  

您可以查看演示 here

I have a table with cpuload,freememory,diskspace, cpu utilization and hostname feilds. i am running a cron job to get the data for every 10 mins in all hosts(ex: 4 hosts). Now i have one year data in database.

i want to convert that data into hourly average data. but i need only for the data which is before two months. last two months data should not disturb. my data is like this

 hostname              | cpuload | freedisk  | freemem |timestamp 
 localhost.localdomain | 0.15    | 136052    | 383660  | 2017-08-01 00:10:08 
 localhost.localdomain | 0.03    | 136492    | 383660  | 2017-08-01 00:20:08 
 localhost.localdomain | 0.01    | 133456    | 383660  | 2017-08-01 00:30:08 
 localhost.localdomain | 0.10    | 64544     | 383660  | 2017-08-01 00:40:08 
 localhost.localdomain | 0.01    | 68148     | 383660  | 2017-08-01 01:00:08 

every 10 mins data will add till today i.e 2017-10-12 11:00:00

now i want to take two months ago data i.e from 2017-08-12 11:00:00 to 2017-08-01 00:00:00 data i need to convert to hourly data

SELECT Avg(CpuLoad) AS avgcpuload
    ,Avg(FreeMemory) AS avgfreemem
    ,Avg(FreeDisk) AS avgfreedisk
    ,Avg(Cpu_util) AS avgcpuutil
FROM Dynamic_Host_Information;

i got avg but i want it for two months back data only.

please help me in this. thanks in advance sagar reddy

解决方案

One way could be to group by your data by date and hour only part of your timestamp column along with a where clause to exclude previous 2 months data as below to get your desired result.

SELECT t1.hostname
    ,avg(cpuload) AS cpuload
    ,avg(freedisk) AS freedisk
    ,avg(freemem) AS freemem
FROM table1 t1
WHERE t1.TIMESTAMP < current_timestamp - interval '42' day
GROUP BY date_format(t1.TIMESTAMP, '%Y%m%d%H');

INTERVAL '42' DAY is used to exclude Oct and Sep data. You can adjust it accordingly.

Result:

hostname                cpuload     freedisk      freemem
-------------------------------------------------------------
localhost.localdomain   0.072500    117636.0000   383660.0000
localhost.localdomain   0.010000    68148.0000    383660.0000

---

Update 1:

If you need data for the time period mentioned in question.

SELECT t1.hostname
    ,avg(cpuload) AS cpuload
    ,avg(freedisk) AS freedisk
    ,avg(freemem) AS freemem
FROM table1 t1
WHERE t1.TIMESTAMP between '2017-08-01 00:00:00' and '2017-08-12 11:00:00'
GROUP BY date_format(t1.TIMESTAMP, '%Y%m%d%H');

You can check the demo here

这篇关于在两个月之前需要数据库小时数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！