none
怎样和上期的数据对比 RRS feed

  • 问题

  •     租金表ContractTable,有7个字段,分别是:ContractNumber(合同号,主键,nvarchar)、City(所在城市,nvarchar),Road(所在路段,nvarchar),Address(门牌号,nvarchar),LeaseDateFrom(合同起始日,date),LeaseDateTo(合同终止日,date),MonthRent(月租金,decimal).

    -

        City、Road、Address是联合使用的。

    -

        现在把同一个City+Road+Address的值作为一个门店的完整地址,因为每隔一段时间这个门店会重新签合同,租金也会相应变化。

    -

        问题:我想求同一个门店现在的月租金(MonthRent)和上一次合同的月租金的差和比例该怎么求?

    (上一期的合同应该可以根据LeastDateFrom(合同起始日)来推断)

    例如:本期月租金为MonthRent2,上期月租金为MonthRent1,我要求它们的差,即:MonthRent2-MonthRent1;

    求他们的比例,即:(MonthRent2/MonthRent1)-1

    -

        有两点要注意的地方:1.同一个门店可能对应很多期的合同,我只是求最近的和上一期合同的差、比例。

        2.并不是单独的求差和比例,肯定是要把这些数据和其他数据放在一起的,那么其他数据该注意什么?

        3.显示的结果有两种情况,一种是把本期和上期的合同都显示出来;另一种是只显示本期合同,及本期合同和上期合同数据对比的结果,分别该怎么写呢???

     

        请大家帮忙看看!谢谢各位!


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!

    2011年9月25日 10:34

答案

  • 同一个问题要放在一起呀。

    首先你的这种表的设计就有问题:你应该建立一个客户表,里面可以有:ClientID, City, Road, Address .... 等,这样 ClientID 在这个表里就是唯一的了。

    针对你的具体问题,你的这个写法跟我教你的不一样,在哪里 "减一" 大有讲究哟. 你试试这个:

    ...

    SELECT px=Row_Number() OVER (Partition By City, Road, Address Order By LeasDate DESC) - 1

    ....

    然后把你的 Join 条件改成:

    LEFT JOIN f b ON a.px = b.px (不要再加 City, Address, Road 的比较了, px 已经是键了)

    2011年9月26日 19:14
    版主

全部回复

  • 算法很多。常用的有几种:Cursor, TempTable, Self Join, Common Table Expression, 甚至函数....

    总之就是‘当前’和 ‘上一个’相比,不知道你的表的具体结构,我用 AdventureWorksDW2008 中的一个表做例子,你可以参考这种算法,非常简单:

    WITH Table1 AS
    (
    SELECT ROW_NUMBER() OVER (Order by ProductKey)+1 AS ID, ProductKey,SalesAmount FROM dbo.FactResellerSales
    ),
    Table2 AS
    (
    SELECT ROW_NUMBER() OVER (Order by ProductKey) AS ID, ProductKey,SalesAmount FROM dbo.FactResellerSales
    )
    
    
    SELECT Table2.ID,Table2.SalesAmount,table2.SalesAmount - Table1.SalesAmount AS Diff FROM Table2
    LEFT JOIN Table1 ON Table2.ID = Table1.ID
    

    解释:

    • 生成两个完全相同的表,可以用 Temp Table, Self join, 这里我用 CTE;
    • ID 错开一个,在你的例子中可以使用 LeastDateFrom 作为参考字段;
    • 这种写法的好处就是容易理解,灵活,但性能不一定最好。


     

    2011年9月26日 1:30
    版主
  •     谢谢您的解答!但我是个初学者,您给的答案有很多看不懂的地方,比如开头的‘WITH’、‘(Order by ProductKey)+1'、‘CET’等等。

     

        而且我的所有字段没有int类型的啊!(Order by ProductKey)+1 应该实现不了吧?!

        能否就我所给出的只有7个字段的ContractTable表,把代码写出来呢?我会比较好理解!谢谢


    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!
    2011年9月26日 3:57
  •     租金表ContractTable,有7个字段,分别是:ContractNumber(合同号,主键,nvarchar)、City(所在城市,nvarchar),Road(所在路段,nvarchar),Address(门牌号,nvarchar),LeaseDateFrom(合同起始日,date),LeaseDateTo(合同终止日,date),MonthRent(月租金,decimal).

    -

        City、Road、Address是联合使用的。

    -

        现在把同一个City+Road+Address的值作为一个门店的完整地址,因为每隔一段时间这个门店会重新签合同,租金也会相应变化。

    -

        问题:我想求同一个门店现在的月租金(MonthRent)和上一次合同的月租金的差和比例该怎么求?

    (上一期的合同应该可以根据LeastDateFrom(合同起始日)来推断)

    例如:本期月租金为MonthRent2,上期月租金为MonthRent1,我要求它们的差,即:MonthRent2-MonthRent1;

        我做出了下列代码,但不完善,先发出来,但还有其他问题!

     

    ;with f as
    (
    select px=row_number()over(partition by city,road,address order by LeaseDateFrom),* from contracttable
    )
    select
     b.*,isnull(b.MonthRent,0)-isnull(a.MonthRent,0) as 租金差,
    (b.MonthRent-a.MonthRent)*1.0/a.MonthRent as 比例
    from
     f a 
    left join
     f b on a.city=b.city 
    and
     a.road=b.road and a.address=b.address and a.px=b.px-1
    

     


    代码得到的结果是下面的图例:

    出现的问题是:
      1.如果某个门店对应了几个合同(也就是一个当前合同和几个过期合同),就运行正常。但是,某个门店如果只有一个合同相对应,那就会出现大片大片的‘NULL’,要如何解决这种问题呢?我需要的是即使这个门店只有一个合同,也应该把它对应唯一的合同信息显示出来,该怎么写呢?

      2.这个只是ContractTable(合同表),实际上经常要联合其他表一起使用,例如PropertyTable(资产表),假如我要把PropertyTable中的BuildingArea(面积)加进来该怎么写呢?PropertyTable和ContractTable都包含了City、Road、Address这三个字段,并且利用这三个字段相加后的值做成外键关系。

    C# 菜鸟中的雏鸟!提的问题也许很幼稚,但我是认真的。希望看在党国的面子上拉兄弟一把!
    2011年9月26日 12:31
  • 同一个问题要放在一起呀。

    首先你的这种表的设计就有问题:你应该建立一个客户表,里面可以有:ClientID, City, Road, Address .... 等,这样 ClientID 在这个表里就是唯一的了。

    针对你的具体问题,你的这个写法跟我教你的不一样,在哪里 "减一" 大有讲究哟. 你试试这个:

    ...

    SELECT px=Row_Number() OVER (Partition By City, Road, Address Order By LeasDate DESC) - 1

    ....

    然后把你的 Join 条件改成:

    LEFT JOIN f b ON a.px = b.px (不要再加 City, Address, Road 的比较了, px 已经是键了)

    2011年9月26日 19:14
    版主