判断每个用户消费额的波峰跟波谷_Sql Server

判断每个用户消费额的波峰和波谷

本帖最后由 halfblood_prince 于 2014-07-04 14:06:03 编辑


IF OBJECT_ID('TEMPDB.DBO.#T1') IS NOT NULL DROP TABLE #T1
GO


CREATE TABLE #T1
(
UID VARCHAR(5),
DT DATE,
Expenditure FLOAT
)


Insert into #T1
VALUES('00001','2014-06-01',-20),
('00001','2014-06-02',50),
('00001','2014-06-04',15),
('00001','2014-06-05',13),
('00001','2014-06-06',100),
('00002','2014-06-02',33),
('00002','2014-06-05',66),
('00002','2014-06-06',101),
('00002','2014-06-07',26),
('00002','2014-06-08',30),
('00002','2014-06-10',43)

想要对每个用户的每次消费额判别出是否为波峰或波谷，最终要实现如下：
UID          DT                 Expenditure        Wave
00001 2014-06-01     -20                    波谷
00001 2014-06-02     50                     波峰
00001 2014-06-04     15
00001 2014-06-05     13                     波谷
00001 2014-06-06     100                  波峰
00002 2014-06-02     33                    波谷
00002 2014-06-05     66
00002 2014-06-06     101                  波峰
00002 2014-06-07     26                    波谷
00002 2014-06-08     30
00002 2014-06-10     43                    波峰

这个用SQL可以实现吗？
------解决方案--------------------

IF OBJECT_ID('TEMPDB.DBO.#T1') IS NOT NULL DROP TABLE #T1
GO 
CREATE TABLE #T1
(
UID VARCHAR(5),
DT DATE,
Expenditure FLOAT
)
Insert into #T1
VALUES('00001','2014-06-01',-20),
('00001','2014-06-02',50),
('00001','2014-06-04',15),
('00001','2014-06-05',13),
('00001','2014-06-06',100),
('00002','2014-06-02',33),
('00002','2014-06-05',66),
('00002','2014-06-06',101),
('00002','2014-06-07',26),
('00002','2014-06-08',30),
('00002','2014-06-10',43)
;
with t as (
select 
	*,ROW_NUMBER()over(partition by uid order by dt) rn
from #T1)
select 
	*
	,case 
		when (select t1.Expenditure from t t1 where t1.rn=t.rn-1 and t1.UID=t.UID)<t.Expenditure
		and  (select t1.Expenditure from t t1 where t1.rn=t.rn+1 and t1.UID=t.UID)<t.Expenditure
		then '波峰'
		when (select t1.Expenditure from t t1 where t1.rn=t.rn-1 and t1.UID=t.UID)>t.Expenditure
		and  (select t1.Expenditure from t t1 where t1.rn=t.rn+1 and t1.UID=t.UID)>t.Expenditure
		then '波谷'
		when ((t.rn=1 and (select t1.Expenditure from t t1 where t1.rn=t.rn+1 and t1.UID=t.UID)<t.Expenditure) 
			or t.rn=(select MAX(t1.rn)from t t1 where t.UID=t1.UID) and (select t1.Expenditure from t t1 where t1.rn=t.rn-1 and t1.UID=t.UID)<t.Expenditure)
		then '波峰'
		when ((t.rn=1 and (select t1.Expenditure from t t1 where t1.rn=t.rn+1 and t1.UID=t.UID)>t.Expenditure) 
			or t.rn=(select MAX(t1.rn)from t t1 where t.UID=t1.UID) and (select t1.Expenditure from t t1 where t1.rn=t.rn-1 and t1.UID=t.UID)>t.Expenditure)
		then '波谷'
		else '' end wave
from t

------解决方案--------------------


with t as
(select *,row_number() over(partition by UID order by DT) 'rn'
 from #T1)
select a.UID,a.DT,a.Expenditure,
       case when (b.rn is null and c.Expenditure>a.Expenditure)
                 or (c.rn is null and b.Expenditure>a.Expenditure)
                 or (b.Expenditure>a.Expenditure and c.Expenditure>a.Expenditure)
            then '波谷'
            when (b.rn is null and c.Expenditure<a.Expenditure)