1. 獲取某段時(shí)間中的含有數(shù)據(jù)的日期集合
select substr(day_hhmmss,0,8) 列別名 from 數(shù)據(jù)表名 t // t為數(shù)據(jù)表名的別名
where (day_hhmmss between '20160228' and '20160311235959')
group by substr(day_hhmmss,0,8) //group by 用于分組
//此處也可以添加 order by 列別名 來(lái)排序
結(jié)果為：
20160305
20160306
20160307
20160311
20160304
20160308
20160309
20160310
注意：between...and...需格外注意
day_hhmmss between '20160228' and '20160311235959'
//時(shí)間段為（2016年2月28日零點(diǎn)）到（2016年3月11日23點(diǎn)59分59秒）間的所有時(shí)間點(diǎn)
day_hhmmss between '20160228' and '20160311'
//時(shí)間段為（2016年2月28日零點(diǎn)）到（2016年3月11日零點(diǎn)）間的所有時(shí)間點(diǎn)，但不包括29號(hào)當(dāng)天的所有數(shù)據(jù)
2.獲取某段時(shí)間中的所有日期集合
獲取20160228到20160311間的所有日期集合
select to_char(to_date('20160228','yyyyMMdd') + rownum - 1, 'yyyyMMdd') 列別名
from dual
connect by rownum <= trunc(to_date('20160311','yyyyMMdd') - to_date('20160228', 'yyyyMMdd')) + 1
結(jié)果為：
20160228
20160229
20160301
20160302
20160303
20160304
20160305
20160306
20160307
20160308
20160309
20160310
20160311
3.獲取某段時(shí)間中沒(méi)有數(shù)據(jù)的日期集合
數(shù)據(jù)庫(kù)中minus用于兩個(gè)集合做減法
select to_char(to_date('20160228','yyyyMMdd') + rownum - 1, 'yyyyMMdd') 列別名
from dual
connect by rownum <= trunc(to_date('20160311','yyyyMMdd') - to_date('20160228', 'yyyyMMdd')) + 1
minus
(select substr(day_hhmmss,0,8) 列別名
from 數(shù)據(jù)表名 t
where (day_hhmmss between '20160228' and '20160311235959')
group by substr(day_hhmmss,0,8))
結(jié)果為：
20160228
20160229
20160301
20160302
20160303
4.合并列：行變列
使用wm_concat() 函數(shù)
select wm_concat(列別名) 新列別名from(
select to_char(to_date('20160228','yyyyMMdd') + rownum - 1, 'yyyyMMdd') 列別名
from dual
connect by rownum <= trunc(to_date('20160311','yyyyMMdd') - to_date('20160228', 'yyyyMMdd')) + 1
minus
(select substr(day_hhmmss,0,8) 列別名
from 數(shù)據(jù)表名 t
where (day_hhmmss between '20160228' and '20160311235959')
group by substr(day_hhmmss,0,8))
)
結(jié)果為：
20160228,20160229,20160301,20160302,20160303
5.統(tǒng)計(jì)數(shù)據(jù)表中各相同數(shù)據(jù)的數(shù)據(jù)量
select count(*) as num,t.時(shí)間列名 as 時(shí)間 from 數(shù)據(jù)庫(kù) t where t.時(shí)間列名 between '起始時(shí)間' and '結(jié)束時(shí)間' group by t.時(shí)間列名
結(jié)果為：
num 時(shí)間
20 201608081255
52 201608081256
87 201608081257
24 201608081258
15 201608081259
6.獲取當(dāng)前系統(tǒng)時(shí)間
select sysdate as 當(dāng)前系統(tǒng)時(shí)間 from dual
結(jié)果為：
2016/8/10 10:33:05
7.統(tǒng)計(jì)某段時(shí)間內(nèi)每十五分鐘獲取到的數(shù)據(jù)量
select to_char(to_date(t.時(shí)間列名, 'yyyymmddhh24miss'), 'yyyy-mm-dd hh24')||':'||floor(to_char(to_date(t.t.時(shí)間列名, 'yyyymmddhh24mi'), 'mi')/15)*15 時(shí)間,
count(*) num
from 數(shù)據(jù)庫(kù) t where t.時(shí)間列名 between '起始時(shí)間' and '結(jié)束時(shí)間'
group by to_char(to_date(t.時(shí)間列名, 'yyyymmddhh24mi'), 'yyyy-mm-dd hh24')||':'||floor(to_char(to_date(t.時(shí)間列名, 'yyyymmddhh24mi'), 'mi')/15)*15
order by to_char(to_date(t.時(shí)間列名, 'yyyymmddhh24mi'), 'yyyy-mm-dd hh24')||':'||floor(to_char(to_date(t.t.時(shí)間列名, 'yyyymmddhh24mi'), 'mi')/15)*15
結(jié)果為：
時(shí)間 num
2016-08-09 00:0 1526
2016-08-09 00:15 1856
2016-08-09 00:30 16
2016-08-09 00:30 26
2016-08-09 00:45 152
2016-08-09 01:0 15
2016-08-09 01:15 1526
2016-08-09 01:30 156
2016-08-09 01:30 26
2016-08-09 01:45 126
8.統(tǒng)計(jì)某段時(shí)間內(nèi)存在數(shù)據(jù)量的十五分鐘的數(shù)目
select count(distinct to_char(to_date(t.時(shí)間列名, 'yyyymmddhh24miss'),'yyyymmddhh24')||floor(to_char(to_date(t.時(shí)間列名, 'yyyymmddhh24miss'),'mi')/15)*15) num
from 數(shù)據(jù)表 t where t.時(shí)間列名 between '起始時(shí)間' and '結(jié)束時(shí)間'
結(jié)果是：
num
96
