分页查询
SELECT TB.EQUIPNAME,TA.equipid,TA.equipvoltage,
TA.equipelectricity,TA.equippower,TA.equipstatus
from (select ROWNUM RN,
A.equipid,A.equipvoltage,A.equipelectricity,A.equippower,A.equipstatus
from (select t.equipid,
t.equipvoltage,
t.equipelectricity,
t.equippower,
t.equipstatus
from L_OLI_ElectricalLoad_Data t
where equipAID = '8A9E426E1130'
and currenttime =
(select max(currenttime)
from L_OLI_ElectricalLoad_Data
where equipAID = '8A9E426E1130')
and ROWNUM <= 3) A) TA,
(select distinct B.equipType, B.EQUIPNAME
from L_OLI_Electrical_Archives B) TB
where TA.equipId = TB.equipType
and TA.RN >= 1
ORDER BY TA.equipId
按时间分组统计数据
--按小时统计
select b.dt xDate, nvl(a.ct, 0) xValue
from (select trunc(currenttime, 'hh24') dt, MAX(ANALYSERELECTRICITY) ct
from oli_monitor_data
group by trunc(currenttime, 'hh24')) a,
(select trunc(TO_DATE('2018-11-23', 'yyyy/MM/dd'), 'DD') + (level - 1) / 24 dt
from dual
connect by level < 25) b
where a.dt(+) = b.dt
order by b.dt
–按天统计
SELECT TO_CHAR(T.currenttime, 'YYYY-MM-DD') TIME, COUNT(*) COUNT
FROM yure_data T
GROUP BY TO_CHAR(T.currenttime, 'YYYY-MM-DD')
ORDER BY TO_CHAR(T.currenttime, 'YYYY-MM-DD') ASC NULLS LAST
–按自然周的日期统计
select to_char(next_day(t.currenttime+15/24 - 7,2),'YYYY-MM-DD') AS xdata,sum(1) as xvalue
from yure_data t
group by to_char(next_day(t.currenttime+15/24 - 7,2),'YYYY-MM-DD')
ORDER BY xdata;
–按自然周的第几周统计
SELECT TO_CHAR(T.currenttime, 'YYYY') YEAR,
TO_CHAR(T.currenttime, 'IW') TIME,
COUNT(*) COUNT
FROM yure_data T
GROUP BY TO_CHAR(T.currenttime, 'IW'), TO_CHAR(T.currenttime, 'YYYY')
ORDER BY TO_CHAR(T.currenttime, 'YYYY'),
TO_CHAR(T.currenttime, 'IW') ASC NULLS LAST
–按自然月统计
SELECT TO_CHAR(T.currenttime, 'YYYY-MM') TIME, COUNT(*) COUNT
FROM yure_data T
where currenttime between to_date('2018-01', 'yyyy-mm') and
to_date('2018-12', 'yyyy-mm')
GROUP BY TO_CHAR(T.currenttime, 'YYYY-MM')
ORDER BY TO_CHAR(T.currenttime, 'YYYY-MM') ASC NULLS LAST
–按季度统计
SELECT TO_CHAR(T.currenttime, 'YYYY') YEAR,
TO_CHAR(T.currenttime, 'Q') TIME,
COUNT(*) COUNT
FROM yure_data T
GROUP BY TO_CHAR(T.currenttime, 'Q'), TO_CHAR(T.currenttime, 'YYYY')
ORDER BY TO_CHAR(T.currenttime, 'YYYY'),
TO_CHAR(T.currenttime, 'Q') ASC NULLS LAST
–按年度统计
SELECT TO_CHAR(T.currenttime, 'YYYY') YEAR, COUNT(*) COUNT
FROM yure_data T
GROUP BY TO_CHAR(T.currenttime, 'YYYY')
ORDER BY TO_CHAR(T.currenttime, 'YYYY') ASC NULLS LAST
每隔15分钟统计一次
select to_char(currenttime, 'hh24')||':'||floor(to_char(currenttime, 'mi')/15)*15 tm, count(*)
from yure_data
where currenttime+0>to_date('20181125','yyyymmdd') and currenttime+0<to_date('20181126','yyyymmdd')
group by to_char(currenttime, 'hh24')||':'||floor(to_char(currenttime, 'mi')/15)*15
order by to_char(currenttime, 'hh24')||':'||floor(to_char(currenttime, 'mi')/15)*15 asc
按月分组统计数据,数据为空补零
select to_char(currenttime, 'yyyy-mm') xname, (max(t.cumulativeheat) - min(t.cumulativeheat)) / 9 xvalue
from S_WHR t
where TO_CHAR(t.currenttime, 'YYYY-MM-DD') like '2018-11%'
---where t.currenttime + 0 >= to_date('2018-11-01', 'yyyy-mm-dd')
---and t.currenttime - 1 < to_date('2018-11-30', 'yyyy-mm-dd')
group by to_char(currenttime, 'yyyy-mm')
order by to_char(currenttime, 'yyyy-mm');
第二种写法
select t2.datevalue xname,nvl(t1.tvalue, 0) xvalue
from (select (max(t.cumulativeheat) - min(t.cumulativeheat)) / 9 tvalue, TO_CHAR(t.currenttime, 'yyyy-mm') timevalue
from S_WHR t
where TO_CHAR(t.currenttime, 'YYYY-MM-DD') like '2018%'
group by TO_CHAR(t.currenttime, 'yyyy-mm')) t1,
(select '2018-' || lpad(level, 2, 0) datevalue
from dual
connect by level < 13) t2
where t1.timevalue(+) = t2.datevalue
order by t2.datevalue
两个日期相差的天时分秒
SELECT TD, TH, TM, ROUND((TT - TM) * 60) AS TS
FROM (SELECT ESTIME,
EETIME,
TD,
TH,
TRUNC((TT - TH) * 60) AS TM,
(TT - TH) * 60 AS TT
FROM (SELECT ESTIME,
EETIME,
TD,
TRUNC((TT - TD) * 24) AS TH,
(TT - TD) * 24 AS TT
FROM (SELECT TO_NUMBER(EETIME - ESTIME) AS TT,
TRUNC(TO_NUMBER(EETIME - ESTIME)) AS TD,
ESTIME,
EETIME
FROM (SELECT min(currenttime) + 0 AS ESTIME,
max(currenttime) + 0 AS EETIME
FROM yure_data))));
分组,sum,sum case when
select ta.currenttime,
sum(ta.wh) total,
sum(case
when ta.equipId = 'ammeter012' or ta.equipid = 'ammeter013' then
wh
end) vo,
(sum(ta.wh) / sum(case
when ta.equipId = 'ammeter012' or ta.equipid = 'ammeter013' then
wh
end)) xvalue
from (select currenttime, equipid, wh
from l_acc_ammeter t
where TO_CHAR(currenttime, 'YYYY-MM-DD') like '2019-01-22%'
group by t.currenttime, t.equipid, wh
order by currenttime asc) ta
group by ta.currenttime
order by ta.currenttime asc
分页查询和表关联
SELECT Tg.EQUIPNAME,Tg.equipid,Tg.equipvoltage,
Tg.equipelectricity,Tg.equippower,Tg.equipstatus
from (select ROWNUM RN,
tf.equipname,te.equipid,te.equipaid,te.equipvoltage,
te.equipelectricity,te.equippower,te.equipstatus,te.equipusepower
from l_oli_electricalload_data te,
(select tb.equipid, tc.equipname
from (select equipid, count(1)
from (select t.equipid,t.equipaid,t.equipvoltage,t.equipelectricity,
t.equippower,t.equipstatus,t.equipusepower, t.currenttime
from L_OLI_ElectricalLoad_Data t
where currenttime + 0 >=to_date('2019-02-28', 'yyyy-mm-dd')
and t.equipaid = '8A9E426E1120'
order by currenttime desc) ta
group by ta.equipid) tb,
(select distinct t.equiptype, t.equipname
from L_OLI_Electrical_Archives t
order by to_number(t.equiptype)) tc
where tb.equipid = tc.equiptype) tf
where te.equipid = tf.equipid
and currenttime =
(select max(currenttime) from l_oli_electricalload_data)
and te.equipaid = '8A9E426E1120'
and ROWNUM <= 3) Tg
where Tg.RN >= 1
ORDER BY to_number(Tg.equipId) asc