我有一个Oracle表，如下所示：
test_time test_name test_type test_location test_value
----------------- --------- --------- ------------- ----------
09/22/20 12:00:05 A RT Albany 200
09/22/20 12:00:05 A RT Chicago 500
09/22/20 12:00:05 B RT Albany 400
09/22/20 12:00:05 B RT Chicago 300
09/22/20 12:00:05 A WPL Albany 1500
09/22/20 12:00:05 A WPL Chicago 2300
09/22/20 12:00:05 B WPL Albany 2100
09/22/20 12:00:05 B WPL Chicago 1900
09/22/20 12:05:47 A RT Albany 300
09/22/20 12:05:47 A RT Chicago 400
09/22/20 12:05:47 B RT Albany 600
09/22/20 12:05:47 B RT Chicago 500
09/22/20 12:05:47 A WPL Albany 1700
09/22/20 12:05:47 A WPL Chicago 2000
09/22/20 12:05:47 B WPL Albany 1800
09/22/20 12:05:47 B WPL Chicago 2400
我想对此表运行SELECT，它将显示过去11分钟内特定测试类型(在本例中为"RT"(引用的每个位置的平均值，由test_name汇总"11分钟"；用于确保我将从每五分钟插入一次记录的脚本的至少两次迭代中检索行。
我希望针对该表的SELECT语句的结果如下所示：
test_name albany_avg_val chicago_avg_val
--------- -------------- ---------------
A 250 450
B 500 400
(注意：测试名称"A"的"albany_avg_val"反映了与在12:00和12:05运行的测试名称"A"/test_type"RT"/test_location"albany"的两次迭代相关联的"测试值"值的平均值(。
到目前为止，我构建的SELECT语句如下：
SELECT
test_name,
CASE test_location
WHEN 'Albany'
THEN ROUND(AVG( test_value ),0) albany_avg_val
WHEN 'Chicago'
THEN ROUND(AVG( test_value ),0) chicago_avg_val
END
FROM
test_table
WHERE
test_type = 'RT' AND test_time > sysdate - interval '11' minute;
但它并没有像预期的那样起作用。有人能帮我解决我可能缺少的东西吗？
我想你想要：
select
test_name,
round(avg(case when test_location = 'Albany' then test_value end)) albany_avg_val
round(avg(case when test_location = 'Chicago' then test_value end)) chicago_avg_val
from test_table
where
test_type = 'rt'
and test_location in ('Albany', 'Chicago')
and test_time > sysdate - 11 / 24 / 60
group by test_name
即：
使用
group by！在聚合函数
avg()中移动case表达式每列都应该分开-条件表达式不能生成两列
还有…：
where子句中的预过滤提高了查询的效率
使用"；数字"；针对
sysdate(其为date(的日期算法；如果您想要区间算法，请使用systimestamp而不是0是round()的默认精度
似乎需要条件聚合：
SELECT
test_name,
AVG(CASE
WHEN test_location='Albany'
THEN ROUND( test_value ) END) AS albany_avg_val,
AVG(WHEN test_location='Chicago'
THEN ROUND( test_value ) END) AS chicago_avg_val
FROM test_table
WHERE test_type = 'RT'
AND test_time > sysdate - interval '11' minute;
GROUP BY test_name
ROUND()函数的第二个参数(0(是多余的
请尝试类似的
SELECT
test_name,
ROUND(AVG(CASE when test_location='Albany'
THEN test_value
else null end),0) albany_avg_val,
ROUND(AVG(CASE when test_location='Chicago'
THEN test_value
else null end),0) Chicago_avg_val
FROM
test_table
WHERE
test_type = 'RT' AND test_time > sysdate - interval '11' minute
group by test_name; ```
pivot子句正是为这样的事情而设计的：以下查询聚合了所有test_type值：
select *
from (select test_name, test_location, test_type, test_value from test_table)
pivot(
avg(test_value)
for test_location in ('Albany ' as Albany,'Chicago' as Chicago)
);
结果：
TEST_NAME TEST_TYPE ALBANY CHICAGO
--------- --------- ---------- ----------
A RT 250 450
B RT 500 400
A WPL 1600 2150
B WPL 1950 2150
或者，如果只想过滤RT:
select *
from (select test_name, test_location, test_value from test_table where test_type='RT')
pivot(
avg(test_value)
for test_location in ('Albany ' as Albany,'Chicago' as Chicago)
);
结果：
TEST_NAME ALBANY CHICAGO
--------- ---------- ----------
B 500 400
A 250 450
带有样本数据的完整测试用例：
with test_table(test_time,test_name,test_type,test_location,test_value) as (
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'A', 'RT ', 'Albany ', 200 from dual union all
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'A', 'RT ', 'Chicago', 500 from dual union all
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'B', 'RT ', 'Albany ', 400 from dual union all
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'B', 'RT ', 'Chicago', 300 from dual union all
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'A', 'WPL', 'Albany ', 1500 from dual union all
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'A', 'WPL', 'Chicago', 2300 from dual union all
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'B', 'WPL', 'Albany ', 2100 from dual union all
select to_date('09/22/20 12:00:05','mm/dd/yy hh24:mi:ss'), 'B', 'WPL', 'Chicago', 1900 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'A', 'RT ', 'Albany ', 300 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'A', 'RT ', 'Chicago', 400 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'B', 'RT ', 'Albany ', 600 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'B', 'RT ', 'Chicago', 500 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'A', 'WPL', 'Albany ', 1700 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'A', 'WPL', 'Chicago', 2000 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'B', 'WPL', 'Albany ', 1800 from dual union all
select to_date('09/22/20 12:05:47','mm/dd/yy hh24:mi:ss'), 'B', 'WPL', 'Chicago', 2400 from dual
)
select *
from (select test_name, test_location, test_type, test_value from test_table)
pivot(
avg(test_value)
for test_location in ('Albany ' as Albany, 'Chicago' as Chicago)
);