Question

Need some help in some SQL.

我有以下报告,我需要建设,但理想的情况是,我想要尝试和建设它,使用“快车道”,而不是需要一个每小时运行的程序。

The business problem we are solving is basically calculating the maximum amount of locations that are occupied in a given week by products, calculated hourly.

我知道,我可以通过每小时操作一个计算数额的程序,将其插入一个表格。然后,我就本周结束时的这个表提问,看看哪一天时间最多。

理想的情况是,我不想利用程序这样做。我写了这封信,可以告诉我任何时候的数字(星期一上午10时11分)。

Rather than copying and pasting this SQL script 24 x 7 times (1 for each hour of the day), is there something else I can do through SQL script here? Could I create a maintenance table that has every day, and time period listed (e.g. columns would be: day, hour_start, hour_end), join that onto my query and use a max function?

I m pretty sure that it can t be done through strait SQL but I m not a fan of time dependant procedures running (e.g. what if the server was to go offline).

任何建议都值得赞赏。

Answer 1

假设数据结构类似:

create table room_usage ( 
   roomnumber number(6),
   occupied_by varchar2(20),
   startdate date,
   enddate date
);

您可以询问每个小时的被占领房间数量:

with datgen as 
  (select to_date( 2008-09-19 , yyyy-mm-dd )+(rownum-1)/24 d 
     from dual
     connect by rownum<=168)
select d, (select count(*) from room_usage
             where startdate<=datgen.d
               and enddate>=datgen.d) occupied
  from datgen;

to_date (2008-09-19 , yyyy-mm-dd > is thestartdate for their query,168 你想要报告的时间数。

EDIT: 获取最大数目和最新日期,使用

with datgen as 
  (select to_date( 2008-09-19 , yyyy-mm-dd )+(rownum-1)/24 d 
     from dual
     connect by rownum<=168),
occ_count as (     
  select d, (select count(*) from room_usage
               where startdate<=datgen.d
                 and enddate>=datgen.d) occupied
    from datgen)
select d, occupied from (select * from occ_count order by occupied desc, d desc)
 where rownum=1;

友情链接