Having the following dataset. I need some help with a sql statement that would give me the latest row based on PING_DATE with unique PING_DESTINATION and PING_SOURCE with added column with the AVG of PING_AVG for all rows within the last 10 minutes.
拥有以下数据集。我需要一些sql语句的帮助,它会给我一个基于PING_DATE的最新行,其中包含唯一的PING_DESTINATION和PING_SOURCE,并且在过去10分钟内为所有行添加了带有PING_AVG AVG的列。
PING_DATE | PACKET_LOSS | PING_MIN | PING_AVG | PING_MAX | PING_SOURCE | PING_DESTINATION
-------------------------------------------------------------------------------------------------------
5/5/2015 12:58:18 PM | 0 | 68 | 68 | 72 | site1 | orange15
5/5/2015 12:58:43 PM | 0 | 68 | 71 | 76 | site1 | orange15
5/5/2015 12:59:11 PM | 0 | 68 | 68 | 72 | site1 | pear11
5/5/2015 1:09:47 PM | 0 | 68 | 70 | 76 | site1 | pear11
5/5/2015 1:43:59 PM | 0 | 68 | 69 | 72 | site1 | pear11
5/5/2015 1:45:41 PM | 0 | 68 | 69 | 72 | site1 | pear11
5/5/2015 2:03:43 PM | 0 | 68 | 68 | 72 | site1 | pear11
5/5/2015 3:01:53 PM | 0 | 68 | 68 | 72 | site1 | pear11
5/5/2015 3:02:05 PM | 0 | 68 | 69 | 72 | site1 | pear11
5/5/2015 3:00:59 PM | 20 | 68 | 68 | 68 | site1 | pear11
5/5/2015 3:01:07 PM | 0 | 68 | 68 | 72 | site1 | pear11
5/5/2015 3:01:14 PM | 0 | 68 | 70 | 72 | site1 | pear11
5/5/2015 12:46:55 PM | 3 | 3 | 3 | 3 | site1 | lemon1
Query Result:
PING_DATE | PACKET_LOSS | PING_MIN | PING_AVG | PING_MAX | PING_SOURCE | PING_DESTINATION | 10minavg
------------------------------------------------------------------------------------------------------------------
5/5/2015 12:58:43 PM | 0 | 68 | 71 | 76 | site1 | orange15 | 71
5/5/2015 3:01:14 PM | 0 | 68 | 70 | 72 | site1 | pear11 | 65
5/5/2015 12:46:55 PM | 3 | 3 | 3 | 3 | site1 | lemon1 | 3
3 个解决方案
#1
For "last 10 minutes average" being "last 10 minutes in each group" this is the query you are looking for:
对于“最后10分钟平均值”为“每组最后10分钟”,这是您要查找的查询:
with xyz as (
select X.*,
row_number() over (
partition by ping_destination, ping_source
order by ping_date desc
) as latest_row#,
avg(ping_avg) over (
partition by ping_destination, ping_source
order by ping_date asc
range between
interval '10' minute preceding
and current row
) as the_10_min_avg
from ping_table X
)
select *
from xyz
where latest_row# = 1
;
For "last 10 minutes average" being "from 10 minutes ago until now" this is the query you are looking for:
对于“从10分钟前到现在”的“最后10分钟平均值”,这是您要查找的查询:
with xyz as (
select X.*,
row_number() over (
partition by ping_destination, ping_source
order by ping_date desc
) as latest_row#,
avg(ping_avg) over (
partition by ping_destination, ping_source
) as the_10_min_avg
from ping_table X
where X.ping_date >= systimestamp - interval '10' minute
)
select *
from xyz
where latest_row# = 1
;
#2
Something like this:
像这样的东西:
SELECT DISTINCT ping_source,
first_value(ping_date) over (partition by ping_source order by ping_date desc),
first_value(packet_loss) over (partition by ping_source order by ping_date desc)
-- ...
FROM data
CROSS JOIN
select avg(ping_avg) from data
where (sysdate - ping_date) * 24 * 60 < 10;
#3
Here is a straight forward query based on the question.
这是基于该问题的直接查询。
Edited based on the sample output. For last 10 minutes from now, use systemtimestamp instead of i.latest_ping in the snippet "(i.latest_ping - interval '10' minute)". Use i.latest_ping for last 10 minutes from max_ping_time for that source-dest pair.
根据样本输出进行编辑。从现在开始的最后10分钟,在片段“(i.latest_ping - interval'10'分钟)”中使用systemtimestamp而不是i.latest_ping。对于该source-dest对,使用i.latest_ping从max_ping_time开始最后10分钟。
select
o.*,
(select avg(ping_avg) from ping_info a
where a.ping_source = i.ping_source
and a.ping_dest = i.ping_dest
and a.ping_date >= (systemtimestamp - interval '10' minute)
) last_10min_avg
from ping_info o,
(select ping_source, ping_dest, max(ping_date) latest_ping
from ping_info
group by ping_source, ping_dest) i
where o.ping_source = i.ping_source
and o.ping_dest = i.ping_dest
and o.ping_date = i.latest_ping;
#1
For "last 10 minutes average" being "last 10 minutes in each group" this is the query you are looking for:
对于“最后10分钟平均值”为“每组最后10分钟”,这是您要查找的查询:
with xyz as (
select X.*,
row_number() over (
partition by ping_destination, ping_source
order by ping_date desc
) as latest_row#,
avg(ping_avg) over (
partition by ping_destination, ping_source
order by ping_date asc
range between
interval '10' minute preceding
and current row
) as the_10_min_avg
from ping_table X
)
select *
from xyz
where latest_row# = 1
;
For "last 10 minutes average" being "from 10 minutes ago until now" this is the query you are looking for:
对于“从10分钟前到现在”的“最后10分钟平均值”,这是您要查找的查询:
with xyz as (
select X.*,
row_number() over (
partition by ping_destination, ping_source
order by ping_date desc
) as latest_row#,
avg(ping_avg) over (
partition by ping_destination, ping_source
) as the_10_min_avg
from ping_table X
where X.ping_date >= systimestamp - interval '10' minute
)
select *
from xyz
where latest_row# = 1
;
#2
Something like this:
像这样的东西:
SELECT DISTINCT ping_source,
first_value(ping_date) over (partition by ping_source order by ping_date desc),
first_value(packet_loss) over (partition by ping_source order by ping_date desc)
-- ...
FROM data
CROSS JOIN
select avg(ping_avg) from data
where (sysdate - ping_date) * 24 * 60 < 10;
#3
Here is a straight forward query based on the question.
这是基于该问题的直接查询。
Edited based on the sample output. For last 10 minutes from now, use systemtimestamp instead of i.latest_ping in the snippet "(i.latest_ping - interval '10' minute)". Use i.latest_ping for last 10 minutes from max_ping_time for that source-dest pair.
根据样本输出进行编辑。从现在开始的最后10分钟,在片段“(i.latest_ping - interval'10'分钟)”中使用systemtimestamp而不是i.latest_ping。对于该source-dest对,使用i.latest_ping从max_ping_time开始最后10分钟。
select
o.*,
(select avg(ping_avg) from ping_info a
where a.ping_source = i.ping_source
and a.ping_dest = i.ping_dest
and a.ping_date >= (systemtimestamp - interval '10' minute)
) last_10min_avg
from ping_info o,
(select ping_source, ping_dest, max(ping_date) latest_ping
from ping_info
group by ping_source, ping_dest) i
where o.ping_source = i.ping_source
and o.ping_dest = i.ping_dest
and o.ping_date = i.latest_ping;