计算一下产品周期时,当遇到大数据量,就无法计算操作了,请问怎么解决?
当使用网友提供的解决方法,一万以内的数据还好解决,当数据量达到五百万以后,就基本没法使用了.
原始数据如下：
卡号 | 产品 | 动作 | 开始日期 | 结束日期
A001 | A产品 | 开通 | 2014-01-01 | 2014-05-30
A001 | A产品 | 续约 | 2014-06-01 | 2014-12-31
A001 | A产品 | 取消 | 2014-06-01 | 2014-12-31
A001 | A产品 | 重开 | 2014-06-08 | 2015-01-07
A001 | A产品 | 续约 | 2015-01-08 | 2015-06-07
A002 | B产品 | 开通 | 2014-01-01 | 2014-05-30
A002 | B产品 | 续约 | 2014-06-01 | 2014-12-31
A002 | B产品 | 续约 | 2015-01-01 | 2015-05-30
计算成以下数据：
卡号 | 产品 | 动作 | 开始日期 | 结束日期 | 产品周期
A001 | A产品 | 开通 | 2014-01-01 | 2014-05-30 | 1
A001 | A产品 | 续约 | 2014-06-01 | 2014-12-31 | 2
A001 | A产品 | 取消 | 2014-06-01 | 2014-12-31 | 0
A001 | A产品 | 重开 | 2014-06-08 | 2015-01-07 | 1
A001 | A产品 | 续约 | 2015-01-08 | 2015-06-07 | 2
A002 | B产品 | 开通 | 2014-01-01 | 2014-05-30 | 1
A002 | B产品 | 续约 | 2014-06-01 | 2014-12-31 | 2
A002 | B产品 | 续约 | 2015-01-01 | 2015-05-30 | 3
需要计算一下产品周期，计算方法是这样的，开通算1，续约加1，取消归0，取消后产品周期从1重新计算。
下面是网友提供的解决方法:
create table mytable(
卡号 varchar(5) ,产品 varchar(10) ,动作 varchar(10) ,开始日期 varchar(10) ,结束日期 varchar(10)
)
go
insert into mytable values
('A001','A产品','开通','2014-01-01','2014-05-30'),
('A001','A产品','续约','2014-06-01','2014-12-31'),
('A001','A产品','取消','2014-06-01','2014-12-31'),
('A001','A产品','重开','2014-06-08','2015-01-07'),
('A001','A产品','续约','2015-01-08','2015-06-07'),
('A002','B产品','开通','2014-01-01','2014-05-30'),
('A002','B产品','续约','2014-06-01','2014-12-31'),
('A002','B产品','续约','2015-01-01','2015-05-30')
go
with myrn as (
select * ,ROW_NUMBER() over(partition by 卡号 order by 卡号) as rn from mytable
) ,
mt as (
select * , 1 as 产品周期 from myrn where rn = 1
union all
select myrn.* , case when myrn.动作 = '续约' then mt.产品周期 + 1
when myrn.动作 = '取消' then 0
when myrn.动作 = '重开' then 1
end
from mt , myrn where myrn.rn = mt.rn + 1 and mt.卡号 = myrn.卡号
)
select * from mt order by 卡号 , rn
go
drop table mytable
go
(8 行受影响)
卡号 产品 动作 开始日期 结束日期 rn 产品周期
----- ---------- ---------- ---------- ---------- --- -----------
A001 A产品 开通 2014-01-01 2014-05-30 1 1
A001 A产品 续约 2014-06-01 2014-12-31 2 2
A001 A产品 取消 2014-06-01 2014-12-31 3 0
A001 A产品 重开 2014-06-08 2015-01-07 4 1
A001 A产品 续约 2015-01-08 2015-06-07 5 2
A002 B产品 开通 2014-01-01 2014-05-30 1 1
A002 B产品 续约 2014-06-01 2014-12-31 2 2
A002 B产品 续约 2015-01-01 2015-05-30 3 3
(8 行受影响)
我自己使用上面的语句,数据量选择一万或以上(我总数据量有五百五十万左右),就无法计算出来并插入到临时表了,等了半个钟或更长时间,一点反应都没有....
with myrn as (
select 编号,卡号,动作 ,ROW_NUMBER() over(partition by 卡号 order by 卡号) as rn
from 业务表
),
mt as (
select 1 as 产品周期,* from myrn where rn = 1
union all
select case when myrn.动作 = '续约' then mt.iActCount2 + 1
when myrn.动作 = '取消' then 0
when myrn.动作 = '重开' then 1
when myrn.动作 = '新开' then 1
end, myrn.*
from mt , myrn where myrn.rn = mt.rn + 1 and mt.卡号 = myrn.卡号
)
INSERT INTO 业务表临时表
(编号, 卡号, 产品周期, 动作
)
SELECT 编号, 卡号, 产品周期, 动作
FROM mt b
------解决思路----------------------
表数据太大，可以插入到临时表，然后 临时表关联字段加索引看看
------解决思路----------------------
將上面的sql的優化一下，將繁體改為簡體，試一下是否OK。
with cte as
(
select *,ROW_NUMBER() over(partition by 卡號,產品 order by 開始日期) as rn,1 as 生產週期
from mytable
),
cte1 as
(
select * from cte where rn=1
union all
select aaa.卡號,aaa.產品,aaa.動作,aaa.開始日期,aaa.结束日期,aaa.rn,
case when aaa.動作 = '續約' then bbb.生產週期+ 1
when aaa.動作 = '取消' then 0
when aaa.動作 = '銷約' then bbb.生產週期 - 1
when aaa.動作 = '停卡' then 0
when aaa.動作 = '重授' then bbb.生產週期
else aaa.生產週期
end as score
from cte as aaa,cte1 as bbb where aaa.卡號=bbb.卡號 and aaa.產品=bbb.產品 and aaa.rn=bbb.rn+1
)
select * from cte1 order by 卡號,產品,rn
------解决思路----------------------
上面的CTE是递归的，数据量太大的话，恐怕执行效率不会高。建议还是写存储过程吧，按卡号或者产品分批将数据读取到临时表中，计算出产品周期，再更新到原表，最后一起输出。