客户需求如下:N个月消费居前的用户的报表(每个月消费高于等于或者低于等于某一个值,连续多少个月)
SELECT *
FROM (SELECT COUNT(*) OVER(PARTITION BY cldjh, rn) cnt, t.*
FROM (SELECT add_months(rq,
-row_number()
OVER(PARTITION BY cldjh ORDER BY rq)) rn,
t.*
FROM tj_dl t
WHERE fl = 3
AND rq >= to_date('2014-01-01', 'yyyy-mm-dd')
AND rq <= to_date('2014-05-01', 'yyyy-mm-dd')
AND zxygz >= 100) t) tt
WHERE tt.cnt =
months_between(to_date('2014-05-01', 'yyyy-mm-dd'),
to_date('2014-01-01', 'yyyy-mm-dd')) + 1
统计连续5个月内,电量连续大于100的记录
SELECT *
FROM (SELECT COUNT(*) OVER(PARTITION BY cldjh, rn) cnt, t.*
FROM (SELECT add_months(rq,
-row_number()
OVER(PARTITION BY cldjh ORDER BY rq)) rn,
t.*
FROM tj_dl t
WHERE fl = 3
AND rq >= to_date('2014-01-01', 'yyyy-mm-dd')
AND rq <= to_date('2014-05-01', 'yyyy-mm-dd')
AND zxygz >= 100) t) tt
WHERE tt.cnt =
months_between(to_date('2014-05-01', 'yyyy-mm-dd'),
to_date('2014-01-01', 'yyyy-mm-dd')) + 1
统计连续5个月内,电量连续大于100的记录