数据库的创建语句如下:
DROP TABLE "PERSON" CASCADE CONSTRAINTS;/*==============================================================*/
/* Table: "PERSON" */
/*==============================================================*/
CREATE TABLE "PERSON" (
"ID" INT not null,
"USERNAME" varchar2(20) not null,
"PASSWORD" varchar2(20) not null,
"REGISTERTIME" DATE not null,
constraint PK_PERSON primary key ("ID")
);
插入测试数据:
begin
for k in 1..100 loop
insert into person(id,username,password,registertime) values('10010'||k,'abc'||k,'000000',to_date('2008-07-17 11:00:00','YYYY-MM-DD HH24:MI:SS'));
end loop;
end;
/
begin
for k in 1..100 loop
insert into person(id,username,password,registertime) values('10011'||k,'abc'||k,'000000',to_date('2008-07-17 12:00:00','YYYY-MM-DD HH24:MI:SS'));
end loop;
end;根据里面的数据,要做如下统计:
1.查询统计出每个月的每一天的每个时间段的注册人数(现定为每隔一小时统计一次);
2.动态输入时间段长度统计。
写入相应的SQL语句,谢谢!
DROP TABLE "PERSON" CASCADE CONSTRAINTS;/*==============================================================*/
/* Table: "PERSON" */
/*==============================================================*/
CREATE TABLE "PERSON" (
"ID" INT not null,
"USERNAME" varchar2(20) not null,
"PASSWORD" varchar2(20) not null,
"REGISTERTIME" DATE not null,
constraint PK_PERSON primary key ("ID")
);
插入测试数据:
begin
for k in 1..100 loop
insert into person(id,username,password,registertime) values('10010'||k,'abc'||k,'000000',to_date('2008-07-17 11:00:00','YYYY-MM-DD HH24:MI:SS'));
end loop;
end;
/
begin
for k in 1..100 loop
insert into person(id,username,password,registertime) values('10011'||k,'abc'||k,'000000',to_date('2008-07-17 12:00:00','YYYY-MM-DD HH24:MI:SS'));
end loop;
end;根据里面的数据,要做如下统计:
1.查询统计出每个月的每一天的每个时间段的注册人数(现定为每隔一小时统计一次);
2.动态输入时间段长度统计。
写入相应的SQL语句,谢谢!
from PERSON
where REGISTERTIME between :begdate and :enddate
group by trunc(REGISTERTIME),substr(to_char(REGISTERTIME, 'yyyy-mm-dd hh'),12,2);
那需要这么麻烦啊to_char(REGISTERTIME,'HH24')
就是小时数
就是两小时一次
from PERSON
where REGISTERTIME between to_date('2008-07-17 10:00:00','YYYY-MM-DD HH24:MI:SS')
and to_date('2008-07-19 16:00:00','YYYY-MM-DD HH24:MI:SS')
group by trunc(REGISTERTIME),substr(to_char(REGISTERTIME, 'yyyy-mm-dd hh'),12,2);
刚才试了一下,只能是每小时统计一次,我现在需要自定义一个时间,能否统计出来呢?又该怎么写呢
TRUNC (TO_NUMBER (TO_CHAR (registertime, 'HH24')) / 2) HOUR,
COUNT (*) 注册人数
FROM person
WHERE registertime BETWEEN :begdate AND :enddate
GROUP BY TRUNC (registertime),
TRUNC (TO_NUMBER (TO_CHAR (registertime, 'HH24')) / 2);
这个函数是取出DATE型数据的小时部分
后面参数hh24就是小时
如果改成yyyy就是年
mm月
DD日
MI分钟
SS秒取出小时后,用to_number转换成数字
然后除2,再用trunc取整
比如0点,出来就是0
1点,出来也是0
2点,出来是1
3点出来是1
(1/2=0.5,trunc后就是0)