--时间显示函数
CREATE OR REPLACE FUNCTION MakeDateByMinute(minutes int)
RETURN varchar2
AS
day int;
hour int;
minute int;
BEGIN
day := Floor(minutes / 1440);
hour := Floor( ( minutes - (day * 1440) )/ 60 );
minute := Floor( minutes - day * 1440 - hour * 60 );
if minutes>0 then
RETURN to_char(day)|| '天'
||to_char(hour) || '小时'
||to_char(minute) ||'分钟';
else
return '小于1分钟';
end if;
END;
--租还车表 统计租车还车成一条记录sql
create or replace view bike_borrow_and_return_view as
select b.b_website_name,r.r_website_name,b.b_tenancy_date,r.r_tenancy_date,MakeDateByMinute(ROUND(TO_NUMBER(r.r_tenancy_date - b.b_tenancy_date) * 24*60)) date_discrepancy,b.bike_no,b.name,b.website_id,r.website_id r_website_id,decode(r.tenancy_price,null,'0.00',r.tenancy_price) tenancy_price from
(select ROW_NUMBER() OVER(PARTITION BY f.bike_rfid ORDER BY f.tenancy_date) as XH,s.website_name b_website_name,f.tenancy_date b_tenancy_date,t.bike_no,t.bike_rfid,c.name,s.website_id from fz_t_ref_card f
left join sm_t_website s on s.website_id=f.tenancy_website_id left join fz_t_bike_info t on t.bike_rfid=f.bike_rfid left join fz_t_associator c on c.card_id =f.card_id where f.ref_type=0
)b left join
(select ROW_NUMBER() OVER(PARTITION BY f.bike_rfid ORDER BY f.tenancy_date) as XH,s.website_name r_website_name,f.tenancy_date r_tenancy_date,f.bike_rfid,f.card_id,s.website_id,ltrim(to_char(f.tenancy_price / 100, '99999999990.99')) tenancy_price from fz_t_ref_card f
left join sm_t_website s on s.website_id=f.tenancy_website_id where f.ref_type=1
) r on b.XH=r.XH and b.bike_rfid=r.bike_rfid;
--网点流量统计sql
select t.b_website_name,
t.r_website_name,
count(*) bike_num,
t.b_pile,
decode(t.b_pile,0,'0%',round(count(*)/ t.b_pile, 4) * 100 || '%') b_v,
t.r_pile,
decode(t.r_pile,0,'0%',round(count(*) / t.r_pile, 4) * 100 || '%') r_v
from (select row_number() OVER(partition by bba.b_website_name order by bba.b_website_name) val,
bba.b_website_name,
bba.r_website_name,
bba.b_tenancy_date,
bba.r_tenancy_date,
(select count(m.bikepile_id)
from fz_t_bikepile_manager m
where m.website_id = bba.website_id) b_pile,
(select count(m.bikepile_id)
from fz_t_bikepile_manager m
where m.website_id = bba.r_website_id) r_pile
from bike_borrow_and_return_view bba
) t where 1=1
and to_char(t.b_tenancy_date, 'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd') and to_char(t.r_tenancy_date, 'yyyy-mm-dd') = to_char(sysdate,'yyyy-mm-dd')
group by b_website_name,r_website_name,t.b_pile,t.r_pile
--站点借还车率统计sql
select t.website_name,t.website_no,t.b_bike_num,t.r_bike_num,
decode(t.pile,0,'0%',round(t.b_bike_num/t.pile,4)*100 ||'%') b_bikerate,
decode(t.pile,0,'0%',round(t.r_bike_num/t.pile,4)*100 ||'%') r_bikerate,
t.b_bike_whenlong from(select tw.website_name,tw.website_no,
count(bb.website_id) b_bike_num,count(bb.r_website_id) r_bike_num,
(select count(m.bikepile_id) from fz_t_bikepile_manager m
where m.website_id = bb.website_id) pile,
MakeDateByMinute(sum(ROUND(TO_NUMBER(bb.r_tenancy_date -
bb.b_tenancy_date) * 24 * 60))) b_bike_whenlong
from bike_borrow_and_return_view bb
left join sm_t_website tw on bb.website_id = tw.website_id
--where ('#' is null or tw.area_id in (@))
group by tw.website_name,tw.website_no,bb.website_id ) t order by t.website_no