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

Question

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

Answer 1

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

Answer 2

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)
                 or (c.rn is null and b.Expenditure<a.Expenditure)
                 or (b.Expenditure<a.Expenditure and c.Expenditure<a.Expenditure)
            then '波峰' 
            else '' end 'Wave'
       
 from t a
 left join t b on a.UID=b.UID and a.rn=b.rn+1 
 left join t c on a.UID=c.UID and a.rn=c.rn-1 
 order by a.UID,a.rn/*
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                     波峰(11 row(s) affected)
*/

Answer 3

;WITH a1 AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY [UID] ORDER BY DT) re
FROM #t1 a
)
,a2 AS
(
SELECT *,ROW_NUMBER() OVER(PARTITION BY [UID] ORDER BY Expenditure) re2
FROM a1
)
,a3 AS
(
SELECT a.*,
CASE
WHEN a.re2>b.re2 AND (c.re2 IS NULL OR a.re2>c.re2) THEN 1 
WHEN (b.re2 IS NULL OR a.re2<b.re2) AND (c.re2 IS NULL OR a.re2>c.re2) THEN 2
WHEN a.re2 BETWEEN b.re2 AND c.re2 THEN 3
ELSE 0 
END re3
FROM a2 a
OUTER APPLY
(
SELECT re2 FROM a2 WHERE [UID]=a.[UID] AND re=a.re-1
) b
OUTER APPLY
(
SELECT re2 FROM a2 WHERE [UID]=a.[UID] AND re=a.re+1
) c
)
SELECT [UID],DT,Expenditure,CASE re3 WHEN 0 THEN '波谷' WHEN 1 THEN '波峰' ELSE '' END Wave
FROM a3
ORDER BY [UID],re