怎样对时间和地点进行维度建模?

Question

需求如下:
在一张地图上有几个点,不同的时间段上这些点的数值不一样.现在要统计出不同日期中这些点以15分钟为时段的数据.怎样设计这些表?
我最初设想是建一个时间表,一个地点表,一个数量的事实表.这些表的属性应该怎样建才好?

Answer 1

楼主分析问题应该从具体的业务入手，而不是首先设计数据库用表结构去套业务，首先把业务描述写明白，然后使用动词名词分析法，就很快能够找出一个对象的属性有哪些，动词去分析业务处理方式，问题就会迎刃而解，另外还要注意，多使用测试数据来表达自己的意思，然后别人才能提供有效的SQL语句来处理你的问题。

Answer 2

谢谢gaojier1000(高捷),我现在提供一些测试数据:
假设有三张表:
表一,地段时间表SegTime,起点startPoint,终点EndPoint,车辆类型,经过这两点的时间TimeSpan,
startPoint endPoint timeSpan(min) CarType
A           B         3            H1
B           D         5            H1
D           F         7            H1
A           B         4            H2
B           D         6            H2
D           F         8            H2
A           C         6            H1
C           M         8            H1
表二:
线路表Rout:routNo       rout
1            A-B-D-F
2            A-C-M表三：
出发时间表Start:
carNo  cartype routNo　startTime
aa      H1      1       10
bb      H2      2       12  
现在要根据表三得到这辆汽车经过不同点的时间：
carNo    carType   point    time
aa        H1       A         10
                   B         13
                   D         15
                   F         17  
请问怎样得到这些数据？
   

Answer 3

create  table SegTime(startPoint varchar(10),endPoint varchar(10),timeSpan int,CarType varchar(10))
insert SegTime
  select 'A','B',3,'H1' union
  select 'B','D',5,'H1' union
  select 'D','F',7,'H1' union
  select 'A','B',4,'H2' union
  select 'B','D',6,'H2' union
  select 'D','F',8,'H2' union
  select 'A','C',6,'H1' union
  select 'C','M',8,'H1'create  table Rout(routNo int,rout varchar(20))
insert Rout
  select 1,'A-B-D-F' union
  select 2,'A-C-M'create  table Start(carNo varchar(10),cartype varchar(10),routNo int,startTime int)
insert Start
  select 'aa','H1',1,10 union
  select 'bb','H2',2,12  
create function dbo.f_GetPoint
       (@routNo int)
returns @tb table (Point varchar(10),orderid int)
as
begin
  declare @s1 varchar(10)
  declare @pos int
  declare @rout varchar(20)
  declare @orderid int
  set @orderid=1
  select @rout = rout from Rout where routNo=@routNo
  select  @pos=PATINDEX('%-%',@rout)  while(@pos >0)
  begin
        set @s1 = substring(@rout,1,@pos-1)
        insert @tb select @s1,@orderid
 
        set @rout = right(@rout,len(@rout)-@pos)
        select  @pos=PATINDEX('%-%',@rout)
        select @orderid=@orderid+1
  end
  insert @tb select @rout,@orderid
  return
end
Gocreate function dbo.f_GetSeg
       (@routNo int)
returns @tb table (startPoint varchar(10),endPoint varchar(10))
as
begin
  declare @s1 varchar(10)
  declare @s2 varchar(10)
  declare @pos int
  declare @rout varchar(20)
  select @rout = rout from Rout where routNo=@routNo  select  @pos=PATINDEX('%-%',@rout)
  set @s1 = substring(@rout,1,@pos-1)
  set @rout = right(@rout,len(@rout)-@pos)
  select  @pos=PATINDEX('%-%',@rout)  while(@pos >0)
  begin
        set @s2 = substring(@rout,1,@pos-1)
        insert @tb select @s1,@s2
 
        set @rout = right(@rout,len(@rout)-@pos)
        set @s1 = @s2
        select  @pos=PATINDEX('%-%',@rout)
  end
  insert @tb select @s1,@rout
  return
end
Godeclare @routNo int
set @routNo=1
select carNo,Start.cartype,Point,time=case when orderid=1 then startTime else timeSpan end,orderid
  into #tmp
    from Start,Rout,SegTime,dbo.f_GetPoint(@routNo) b, dbo.f_GetSeg(@routNo) a
   where Start.routNo = @routNo
     and Start.routNo=Rout.routNo
     and Start.cartype=SegTime.cartype
     and a.startPoint=SegTime.startPoint
     and a.endPoint=SegTime.endPoint
     and (b.Point = a.endPoint or orderid=1 and b.Point = a.startPoint)
select * from #tmp
select a.carNo,a.cartype,a.Point,time=sum(b.time)
  from #tmp a,#tmp b
 where a.carNo=b.carNo
   and a.cartype=b.cartype
   and a.orderid >=b.orderid
  group by a.carNo,a.cartype,a.Point

Answer 4

谢谢各位！特别是Jane_64().还有一个问题，根据上面的模型，如何求得某一时间段此区域内
的所有车辆数量，某一点某一时间段内通过这一点的车辆数量?

Answer 5

create  table SegTime(startPoint varchar(10),endPoint varchar(10),timeSpan int,CarType varchar(10))
insert SegTime
  select 'A','B',3,'H1' union
  select 'B','D',5,'H1' union
  select 'D','F',7,'H1' union
  select 'A','B',4,'H2' union
  select 'B','D',6,'H2' union
  select 'D','F',8,'H2' union
  select 'A','C',6,'H2' union
  select 'C','M',8,'H2'create  table Rout(routNo int,rout varchar(20))
insert Rout
  select 1,'A-B-D-F' union
  select 2,'A-C-M'create  table Start(carNo varchar(10),cartype varchar(10),routNo int,startTime int)
insert Start
  select 'aa','H1',1,10 union
  select 'bb','H2',2,12  
create function dbo.f_GetPoint
       (@routNo int)
returns @tb table (Point varchar(10),orderid int)
as
begin
  declare @s1 varchar(10)
  declare @pos int
  declare @rout varchar(20)
  declare @orderid int
  set @orderid=1
  select @rout = rout from Rout where routNo=@routNo
  select  @pos=PATINDEX('%-%',@rout)  while(@pos >0)
  begin
        set @s1 = substring(@rout,1,@pos-1)
        insert @tb select @s1,@orderid
 
        set @rout = right(@rout,len(@rout)-@pos)
        select  @pos=PATINDEX('%-%',@rout)
        select @orderid=@orderid+1
  end
  insert @tb select @rout,@orderid
  return
end
Gocreate function dbo.f_GetSeg
       (@routNo int)
returns @tb table (startPoint varchar(10),endPoint varchar(10))
as
begin
  declare @s1 varchar(10)
  declare @s2 varchar(10)
  declare @pos int
  declare @rout varchar(20)
  select @rout = rout from Rout where routNo=@routNo  select  @pos=PATINDEX('%-%',@rout)
  set @s1 = substring(@rout,1,@pos-1)
  set @rout = right(@rout,len(@rout)-@pos)
  select  @pos=PATINDEX('%-%',@rout)  while(@pos >0)
  begin
        set @s2 = substring(@rout,1,@pos-1)
        insert @tb select @s1,@s2
 
        set @rout = right(@rout,len(@rout)-@pos)
        set @s1 = @s2
        select  @pos=PATINDEX('%-%',@rout)
  end
  insert @tb select @s1,@rout
  return
end
Gocreate function dbo.f_GetCarPointTime()
returns @tb table (carNo varchar(10),cartype varchar(10),Point varchar(10),time int,orderid int)
as
begin
  declare @routNo int
  declare c_Rout cursor for select routNo from Rout
  open c_Rout
  fetch next from c_Rout into @routNo
  WHILE @@FETCH_STATUS = 0
  BEGIN
    insert @tb
      select carNo,Start.cartype,Point,time=case when orderid=1 then startTime else timeSpan end,orderid
        from Start,Rout,SegTime,dbo.f_GetPoint(@routNo) b, dbo.f_GetSeg(@routNo) a
       where Start.routNo = @routNo
         and Start.routNo=Rout.routNo
         and Start.cartype=SegTime.cartype
         and a.startPoint=SegTime.startPoint
         and a.endPoint=SegTime.endPoint
         and (b.Point = a.endPoint or orderid=1 and b.Point = a.startPoint)
    fetch next from c_Rout into @routNo
  end
  close c_Rout 
  DEALLOCATE c_Rout
  return
end
Godeclare @begTime int,@endTime int
set @begTime=12
set @endTime=18/*某一时间段此区域内的所有车辆数量*/
declare @startPoint varchar(10),@endPoint varchar(10)
set @startPoint='A'
set @endPoint='C'
select 车辆数量=count(distinct carNo)  
  from (select a.carNo,a.cartype,a.Point,time=sum(b.time)
          from dbo.f_GetCarPointTime() a,dbo.f_GetCarPointTime() b
         where a.carNo=b.carNo
           and a.cartype=b.cartype
           and a.orderid >=b.orderid
         group by a.carNo,a.cartype,a.Point) c
 where time between @begTime  and @endTime
  and point between @startPoint and @endPoint
/*某一点某一时间段内通过这一点的车辆数量*/
declare @Point varchar(10)
set @Point='A'
select 车辆数量=count(distinct carNo)  
  from (select a.carNo,a.cartype,a.Point,time=sum(b.time)
          from dbo.f_GetCarPointTime() a,dbo.f_GetCarPointTime() b
         where a.carNo=b.carNo
           and a.cartype=b.cartype
           and a.orderid >=b.orderid
         group by a.carNo,a.cartype,a.Point) c
 where time between @begTime  and @endTime
  and point=@Point