在获取指定两行的差值,我以前都是两个表进行join,再相减,偶尔获得下面的sql,感觉很不错.
例如下面的sql,这里使用了sum, 其中case那里如果是指定是减数则为正数,指定被减数的使用负数,然后再进行sum,那么就是+负数也就是相减,也就获取到指定两行的差值,非常有创意
SELECT stat_name,
ROUND(sum(case
when snap_id = &END_SNAP_ID then
VALUE
else
-value
end) / &ELAPSED_TIME,2) ps,
ROUND( sum(case
when snap_id = &END_SNAP_ID then
VALUE
else
-value
end) / &TRANS,2) pt
FROM DBA_HIST_SYSSTAT
WHERE SNAP_ID in (&BEGIN_SNAP_ID, &END_SNAP_ID)
AND DBID = &DATABASE_ID
AND INSTANCE_NUMBER = 2
and stat_name in ('redo size',
'session logical reads',
'db block changes',
'physical reads',
'physical writes',
'user calls',
'parse count (total)',
'parse count (hard)',
'sorts (memory)',
'logons cumulative',
'execute count',
'user commits',
'user rollbacks')
group by STAT_NAME order by stat_name;