递减问题处理

Pact_Alice 2015-10-13 04:22:35
--我想得到一列如
--第一行的Number不变
--第二行的Number是第二行的Number减去第一行的Number值
--第三行的Number 是第三行Number减去第二行的减值（也就是第二行的Number减去第一行的Number值）
--第四行的Number 是第四行的Number减去第三行的减值
--依次地推,,
--跨年分的话 如果年份变为12年 那么12年1月的数据就是用12年1月 减去11年12月的数据
--
--令一列就是即使跨年分了，那么12年一月的数据还是12年的数据，不用减去11年12月份的，12年2月的Number就是2月的数据减去12年1月的NUmber

--总体说我想得到两列 区别就在于跨年分的第一个月的计算是否包括上一年的最后一个月
;WITH cet AS (
select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all
select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000
select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200
select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000
select '1036000', 2011, 5, 354945.8000 union ALL --183491.4000
select '1036000', 2011, 6, 384152.5000 union ALL --200661.1000
select '1036000', 2011, 7, 353857.8900 union ALL --153196.7900
select '1036000', 2011, 8, 304991.9800 union ALL --151795.1900
select '1036000', 2011, 9, 233037.2000 union ALL --81242.0100
select '1036000', 2011, 10, 217856.1600 union ALL --136614.1500
select '1036000', 2011, 11, 141653.8400 union ALL --5039.6900
select '1036000', 2011, 12, 194081.8600 union ALL --189042.1700
select '1036000', 2012, 1, 181120.0100 union ALL ---7922.1600
select '1036000', 2012, 2, 211208.4000 --219130.5600
)
SELECT * FROM cet
...全文
179 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
道玄希言 2015-10-13
  • 打赏
  • 举报
 回复
上面条件范围可以写成

where [Year] >= DATEPART(YEAR,convert(date, @StartDate,120))
and [Year] <= DATEPART(YEAR,convert(date, @enddate,120))
and [Month]>= DATEPART(MONTH,convert(date, @StartDate,120))
and [Month]<= DATEPART(MONTH,convert(date, @enddate,120))

或
where convert(date, convert(nvarchar(4),[Year])+'-'+convert(nvarchar(2),[Month])+'-01', 120) >= convert(date, @StartDate,120)
and convert(date, convert(nvarchar(4),[Year])+'-'+convert(nvarchar(2),[Month])+'-01', 120) <= convert(date， @enddate,120)
道玄希言 2015-10-13
  • 打赏
  • 举报
 回复
引用 7 楼 Landa_Alice 的回复:
DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02-01'
SET @enddate='2011-05-04'
--用2011-05对应的NUmber减去2-4月NUmber的总和
;WITH cet AS (

select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all
select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000
select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200
select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000
select '1036000', 2011, 5, 354945.8000 --183491.4000

)
SELECT * FROM cet


;WITH cte AS (
select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all
select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000
select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200
select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000
select '1036000', 2011, 5, 354945.8000 union ALL --183491.4000
select '1036000', 2011, 6, 384152.5000 union ALL --200661.1000
select '1036000', 2011, 7, 353857.8900 union ALL --153196.7900
select '1036000', 2011, 8, 304991.9800 union ALL --151795.1900
select '1036000', 2011, 9, 233037.2000 union ALL --81242.0100
select '1036000', 2011, 10, 217856.1600 union ALL --136614.1500
select '1036000', 2011, 11, 141653.8400 union ALL --5039.6900
select '1036000', 2011, 12, 194081.8600 union ALL --189042.1700
select '1036000', 2012, 1, 181120.0100 union ALL ---7922.1600
select '1036000', 2012, 2, 211208.4000 --219130.5600
),
tb1 as
(
select
ROW_NUMBER() OVER(order by [Year], [Month]) as nid,
CustomerCode, [Year], [Month], [Number] from cte
where (时间范围)
)
select
t1.CustomerCode, t1.[Year], t1.[Month],
isnull(t1.[Number],0) as [本月],
isnull(t2.[Number],0) as [上月] ,
isnull(t2.[Num],0) as [之前月份和],
(isnull(t2.[Number],0) - isnull(t1.[Number],0)) as [本月减上月],
(case t1.[Month] when 1 then isnull(t1.[Number],0)
else isnull(t2.[Number],0) - isnull(t1.[Number],0) end) as [本月减上月不包年初],
(isnull(t1.[Number],0) - isnull(t2.[Num],0)) as [本月减之前月和]
from tb1 as t1
left join
(select nid, Number, (select sum(isnull(Number,0))
from tb1 where nid <= t.nid) as [Num] from tb1 as t)
as t2
on t1.nid = t2.nid + 1
Pact_Alice 2015-10-13
  • 打赏
  • 举报
 回复
引用 5 楼 yangb0803 的回复:


;WITH cte AS (	

select '1036000' AS CustomerCode, 2011 AS [Year],	1 AS [Month],	146435.4600 AS [Number] union all

select '1036000', 2011,	2,	146905.9200 union ALL  --77179.0000	

select '1036000', 2011,	3,	224084.9200 union ALL  --146905.9200

select '1036000', 2011,	4,	318360.3200 union ALL  --171454.4000

select '1036000', 2011,	5,	354945.8000 union ALL  --183491.4000

select '1036000', 2011,	6,	384152.5000 union ALL  --200661.1000

select '1036000', 2011,	7,	353857.8900 union ALL  --153196.7900

select '1036000', 2011,	8,	304991.9800 union ALL  --151795.1900

select '1036000', 2011,	9,	233037.2000 union ALL  --81242.0100

select '1036000', 2011,	10,	217856.1600 union ALL  --136614.1500

select '1036000', 2011,	11,	141653.8400 union ALL  --5039.6900

select '1036000', 2011,	12,	194081.8600 union ALL  --189042.1700

select '1036000', 2012,	1,	181120.0100 union ALL  ---7922.1600

select '1036000', 2012,	2,	211208.4000	           --219130.5600

),

tb1 as

(

select 

    ROW_NUMBER() OVER(order by [Year], [Month]) as nid, 

    CustomerCode, [Year], [Month], [Number] from cte

)

select 

  t1.CustomerCode, t1.[Year], t1.[Month], t1.[Number], t2.[Number],

   (t2.[Number] - t1.[Number]) as n1,

  (case t1.[Month] when 1 then t1.[Number] 

    else t2.[Number] - t1.[Number] end) as n2

from tb1 as t1

left join tb1 as t2

on t1.nid = t2.nid + 1

DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02-01'
SET @enddate='2011-05-04'
--用2011-05对应的NUmber减去2-4月NUmber的总和
;WITH cet AS (

select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all
select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000
select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200
select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000
select '1036000', 2011, 5, 354945.8000 --183491.4000

)
SELECT * FROM cet
道玄希言 2015-10-13
  • 打赏
  • 举报
 回复 


;WITH cte AS (	

select '1036000' AS CustomerCode, 2011 AS [Year],	1 AS [Month],	146435.4600 AS [Number] union all

select '1036000', 2011,	2,	146905.9200 union ALL  --77179.0000	

select '1036000', 2011,	3,	224084.9200 union ALL  --146905.9200

select '1036000', 2011,	4,	318360.3200 union ALL  --171454.4000

select '1036000', 2011,	5,	354945.8000 union ALL  --183491.4000

select '1036000', 2011,	6,	384152.5000 union ALL  --200661.1000

select '1036000', 2011,	7,	353857.8900 union ALL  --153196.7900

select '1036000', 2011,	8,	304991.9800 union ALL  --151795.1900

select '1036000', 2011,	9,	233037.2000 union ALL  --81242.0100

select '1036000', 2011,	10,	217856.1600 union ALL  --136614.1500

select '1036000', 2011,	11,	141653.8400 union ALL  --5039.6900

select '1036000', 2011,	12,	194081.8600 union ALL  --189042.1700

select '1036000', 2012,	1,	181120.0100 union ALL  ---7922.1600

select '1036000', 2012,	2,	211208.4000	           --219130.5600

),

tb1 as

(

select 

    ROW_NUMBER() OVER(order by [Year], [Month]) as nid, 

    CustomerCode, [Year], [Month], [Number] from cte

)

select 

  t1.CustomerCode, t1.[Year], t1.[Month], t1.[Number], t2.[Number],

   (t2.[Number] - t1.[Number]) as n1,

  (case t1.[Month] when 1 then t1.[Number] 

    else t2.[Number] - t1.[Number] end) as n2

from tb1 as t1

left join tb1 as t2

on t1.nid = t2.nid + 1
Pact_Alice 2015-10-13
  • 打赏
  • 举报
 回复
引用 4 楼 zbdzjx 的回复:
再试一下这个，看看对不对。
WITH cet AS (

select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all

select '1036000', 2011, 2,  146905.9200 union ALL --77179.0000

select '1036000', 2011, 3,  224084.9200 union ALL --146905.9200

select '1036000', 2011, 4,  318360.3200 union ALL --171454.4000

select '1036000', 2011, 5,  354945.8000 union ALL --183491.4000

select '1036000', 2011, 6,  384152.5000 union ALL --200661.1000

select '1036000', 2011, 7,  353857.8900 union ALL --153196.7900

select '1036000', 2011, 8,  304991.9800 union ALL --151795.1900

select '1036000', 2011, 9,  233037.2000 union ALL --81242.0100

select '1036000', 2011, 10, 217856.1600 union ALL --136614.1500

select '1036000', 2011, 11, 141653.8400 union ALL --5039.6900

select '1036000', 2011, 12, 194081.8600 union ALL --189042.1700

select '1036000', 2012, 1,  181120.0100 union ALL ---7922.1600

select '1036000', 2012, 2,  211208.4000 --219130.5600

)

,cet2 as

(

SELECT CustomerCode, [Year], [Month],[Number], 0 level, convert(decimal(18,4), isnull([Number],0)) [Number1] FROM cet WHERE [Year]=2011 and [Month]=1

UNION ALL

SELECT a.CustomerCode, a.[Year], a.[Month],a.[Number],b.level+1,CONVERT(decimal(18,4), isnull(a.[Number],0)-isnull(b.Number1,0)) FROM cet A,cet2 b

where a.Year*12+a.Month=b.Year*12+b.Month+1

)

select * from cet2

order by [YEAR],[MONTH]

天天说递归，为什么我想不起来递归处理呢，哎，没带脑子，
就是刚刚问您的问题请你帮我看看啊，就是定义两个参数的问题，
DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02-01'
SET @enddate='2011-05-04'
--用2011-05-04对应的NUmber减去2-4月NUmber的总和
;WITH cet AS (

select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all

select '1036000', 2011, 2, 146905.9200 union ALL --77179.0000

select '1036000', 2011, 3, 224084.9200 union ALL --146905.9200

select '1036000', 2011, 4, 318360.3200 union ALL --171454.4000

select '1036000', 2011, 5, 354945.8000 --183491.4000

)
SELECT * FROM cet


zbdzjx 2015-10-13
  • 打赏
  • 举报
 回复
再试一下这个，看看对不对。
WITH cet AS (

select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all

select '1036000', 2011, 2,  146905.9200 union ALL --77179.0000

select '1036000', 2011, 3,  224084.9200 union ALL --146905.9200

select '1036000', 2011, 4,  318360.3200 union ALL --171454.4000

select '1036000', 2011, 5,  354945.8000 union ALL --183491.4000

select '1036000', 2011, 6,  384152.5000 union ALL --200661.1000

select '1036000', 2011, 7,  353857.8900 union ALL --153196.7900

select '1036000', 2011, 8,  304991.9800 union ALL --151795.1900

select '1036000', 2011, 9,  233037.2000 union ALL --81242.0100

select '1036000', 2011, 10, 217856.1600 union ALL --136614.1500

select '1036000', 2011, 11, 141653.8400 union ALL --5039.6900

select '1036000', 2011, 12, 194081.8600 union ALL --189042.1700

select '1036000', 2012, 1,  181120.0100 union ALL ---7922.1600

select '1036000', 2012, 2,  211208.4000 --219130.5600

)

,cet2 as

(

SELECT CustomerCode, [Year], [Month],[Number], 0 level, convert(decimal(18,4), isnull([Number],0)) [Number1] FROM cet WHERE [Year]=2011 and [Month]=1

UNION ALL

SELECT a.CustomerCode, a.[Year], a.[Month],a.[Number],b.level+1,CONVERT(decimal(18,4), isnull(a.[Number],0)-isnull(b.Number1,0)) FROM cet A,cet2 b

where a.Year*12+a.Month=b.Year*12+b.Month+1

)

select * from cet2

order by [YEAR],[MONTH]
Pact_Alice 2015-10-13
  • 打赏
  • 举报
 回复
引用 2 楼 zbdzjx 的回复:
噢，内容看错了，上面的代码不对。

谢谢你的回答，没事，我是实在没有思路才问各位朋友们的，
上两个问题是我自己想写出来的，最急最主要的问题就是
DECLARE @StartDate DATETIME,@enddate DATETIME
SET @StartDate='2011-02'
SET @enddate='2011-05'
--如果我定义两个参数，开始时间结束时间
--我想用结束时间的Number 减去开始时间到结束前时间的月NUmber
--比如开始时间是2011-02 结束时间是2011-05
--那么得到的结果就是用 2011-05的NUmber值减去（2011-02的值+2011-03的值+2011-04的值）
--354945.8000-(146905.9200+224084.9200+318360.3200)
这个问题比较的急，可以先帮我看看吗，上面的两个问题不太急，给自己做练习的
zbdzjx 2015-10-13
  • 打赏
  • 举报
 回复
噢，内容看错了，上面的代码不对。
zbdzjx 2015-10-13
  • 打赏
  • 举报
 回复
这样？？
WITH cet AS (

select '1036000' AS CustomerCode, 2011 AS [Year], 1 AS [Month], 146435.4600 AS [Number] union all

select '1036000',      2011, 2, 146905.9200 union ALL  --77179.0000

select '1036000',      2011, 3, 224084.9200 union ALL  --146905.9200

select '1036000',      2011, 4, 318360.3200 union ALL  --171454.4000

select '1036000',      2011, 5, 354945.8000 union ALL  --183491.4000

select '1036000',      2011, 6, 384152.5000 union ALL  --200661.1000

select '1036000',      2011, 7, 353857.8900 union ALL  --153196.7900

select '1036000',      2011, 8, 304991.9800 union ALL  --151795.1900

select '1036000',      2011, 9, 233037.2000 union ALL  --81242.0100

select '1036000',      2011, 10, 217856.1600 union ALL  --136614.1500

select '1036000',      2011, 11, 141653.8400 union ALL  --5039.6900

select '1036000',      2011, 12, 194081.8600 union ALL  --189042.1700

select '1036000',      2012, 1, 181120.0100 union ALL  ---7922.1600

select '1036000',      2012, 2, 211208.4000            --219130.5600

)

SELECT a.*

, b.Number 

, c.Number

FROM cet a 

left join cet b on a.Year*12+a.Month=b.Year*12+b.Month-1

left join cet c on a.Year=c.Year and a.Month=c.Month-1
美国大数据工程师面试宝典
美国大数据面试宝典,非常有用,董飞的,讲得很不错
模型 边际效应递减法则
边际效用递减法则(The law of diminishing marginal utility，也称边际效益递减法则、边际贡献递减)，边际效用递减是经济学的一个基本概念，是指在其他条件不变的情况下，如果一种投入要素连续地等量增加，增加到一定产值后，所提供的产品的增量就会下降，即可变要素的边际产量会递减。当消费者消费某一物品的总数量越来越多时，其新增加的最后一单位物品的消费所获得的效用（即边际效用）通常会呈现越来越少的现象（递减diminishing），称之边际效用递减法则。也叫做戈森第一法则。
OJ 系列之最大递减数
1、问题描述给出一个非负整数，找到这个非负整数中包含的最大递减数。一个数字的递减数是指相邻的数位从大到小排列的数字。 如： 95345323，递减数有：953,95,53,53,532,32, 那么最大的递减数为953。 如果输入的数字为负数，返回-1。2、解题思路 1、首先将非负整数每位数字压入vector数组中，其顺序和原数字从左到右一致； 2、找出递减数（从大到小排列）； 3、从所有的递减
计算机科学中的“边际效益递减”理论
计算机科学中的“边际效益递减”理论 边际效益递减，不仅仅是一个经济学词汇，在计算机硬件领域，也是这样的。 在经济学中，边际效益递减受人的心理影响，当消费某种物品时，开始的刺激一定大，从而人的满足程度就高，但不断的消费同一种物品，即同一刺激不断反复时，人在心理上的兴奋程度或满足必然减少。 在计算机科学中，不管是单机还是分布式集群，都存在一个问题：规模越大，维护成本越高。 在经济学中，导致这个结果的原因是人的心理。在计算机科学中，导致这个结果的原因是一些瓶颈。 经济学中的“不断消费同一种物品”，是物品的增加。对
Redis递增递减功能
递增指令：incr（默认从0开始） 递减指令：decr（默认从0开始，递减会出现负数，这点跟memcache不一样,mc到0） 如下： 附上shardedJedisPool和JedisCluster的两种实现方式： shardedJedisPool： @Override public Long decr(String key) { ShardedJedis jedis = null; Long result = 0l; try {
MS-SQL Server

34,838

社区成员

254,632

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章