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

halfblood_prince 2014-07-04 02:04:45



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可以实现吗？

...全文

334 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

halfblood_prince 2014-07-04

打赏
举报

“lzw_0736”的效率最快。

lzw_0736 2014-07-04

打赏
举报


;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

唐诗三百首 2014-07-04

打赏
举报


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)
*/

spiritofdragon 2014-07-04

打赏
举报

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

halfblood_prince 2014-07-04