有如下表结构
create table A_STATISTICS_WAP_MODEL (
RecordDate date not null,//访问日期
ModelName varchar2(20) not null,//手机名称
TotalTimes NUMBER, //访问量
TotalUser NUMBER,
constraint PK_A_STATISTICS_WAP_MODEL primary key (RecordDate, ModelName)
);
问题:
当日手机访问量最高的前10名的信息
(当日访问量,前一天访问量,(当天比前一天的)日增长率,前三周[前三周的今天到今天]同期平均访问量 )4个信息
用一条SQL语句,或存储过程式.
************急等******************************
------解决方案--------------------
up
------解决方案--------------------
楼主的问题就是时间怎么表示
今天 RecordDate=to_char(sysdate, 'yyyy/mm/dd ')
前一天 RecordDate=to_char(sysdate-1, 'yyyy/mm/dd ')
前三周 RecordDate <=to_char(sysdate, 'yyyy/mm/dd ')
and RecordDate <=to_char(sysdate-21, 'yyyy/mm/dd ')
需要减多少天自己算!
------解决方案--------------------
select TotalTimes as "当日访问量 ",
lag(TotalTimes, 1, 0) over(order by RecordDate) as "前一天访问量 ",
case
when lag(TotalTimes, 1, 0) over(order by RecordDate) = 0 then
1.0
else
(TotalTimes - lag(TotalTimes, 1) over(order by RecordDate)) /
lag(TotalTimes, 1) over(order by RecordDate)
end "日增长率 "
from A_STATISTICS_WAP_MODEL
where ModelName in (select ModelName
from (select ModelName,
row_number() over(order by TotalTimes desc) as rn
from A_STATISTICS_WAP_MODEL a
where a.RecordDate = trunc(sysdate))
where rn <= 10)
前三周[前三周的今天到今天]同期平均访问量 )不清楚什么意思