如何根据条件分配计数器 [英] How to assign the counter based on a condition
问题描述
源表DDL:-
CREATE TABLE temp (
REG_ID NUMBER(5) ,
Pkg_DES VARCHAR2(15),
PRD_DESC VARCHAR2(15)
EVENT_DATE DATE,
event_type_cd VARCHAR2(15)
)
我在下面也有插入语句-
I have insert statements too below -
insert into temp (REG_ID, Pkg_DES, PRD_DESC, EVENT_DATE, event_type_cd)
select 1 , 'CC' , 'trail' , to_date('12/12/2016', 'mm/dd/yyyy') , 'new sub' from dual union all
select 1 , 'CC' , 'trail' ,to_date('12/13/2016', 'mm/dd/yyyy') , 'exp' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/14/2016', 'mm/dd/yyyy') , 'upsell' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/15/2016', 'mm/dd/yyyy'), 'exp' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/16/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/17/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'aa' , 'trail' , to_date('12/12/2016', 'mm/dd/yyyy') , 'new sub' from dual union all
select 1 , 'aa' , 'trail' , to_date('12/13/2016', 'mm/dd/yyyy') , 'exp' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/14/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/15/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/16/2016', 'mm/dd/yyyy') , 'upsell' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/17/2016', 'mm/dd/yyyy') , 'renewal' from dual;
我在下面有这样的要求
REG_ID | Pkg_DES | PRD_DESC | EVENT_DATE | event_type_cd
-------+---------+----------+------------+--------------
1 | CC | trail | 12-12-2012 | new sub
1 | CC | trail | 12-13-2012 | exp
1 | CC | PAID | 12-14-2012 | upsell
1 | CC | PAID | 12-15-2012 | exp
1 | CC | PAID | 12-16-2012 | renewal
1 | CC | PAID | 12-17-2012 | renewal
1 | aa | trail | 12-12-2012 | new sub
1 | aa | trail | 12-13-2012 | exp
1 | aa | PAID | 12-14-2012 | renewal
1 | aa | PAID | 12-15-2012 | renewal
1 | aa | PAID | 12-16-2012 | upsell
1 | aa | PAID | 12-17-2012 | renewal
我需要的输出如下:
REG_ID | Pkg_DES | PRD_DESC | EVENT_DATE | event_type_cd | renewal_cnt | is_ren | is_conv
-------+---------+----------+------------+---------------+-------------+--------+--------
1 | CC | trail | 12-12-2012 | new sub | 0 | 0 | 0
1 | CC | trail | 12-13-2012 | exp | 0 | 0 | 0
1 | CC | PAID | 12-14-2012 | upsell | 0 | 0 | 1
1 | CC | PAID | 12-15-2012 | exp | 0 | 0 | 1
1 | CC | PAID | 12-16-2012 | renewal | 1 | 1 | 0
1 | CC | PAID | 12-17-2012 | renewal | 2 | 1 | 0
1 | aa | trail | 12-12-2012 | new sub | 0 | 0 | 0
1 | aa | trail | 12-13-2012 | exp | 0 | 0 | 0
1 | aa | PAID | 12-14-2012 | renewal | 0 | 0 | 1
1 | aa | PAID | 12-15-2012 | renewal | 1 | 1 | 0
1 | aa | PAID | 12-16-2012 | upsell | 2 | 1 | 0
1 | aa | PAID | 12-17-2012 | renewal | 3 | 1 | 0
- 转换逻辑:-如果PRD_DESC第一次从跟踪"更改为付费"，那么它被称为转换产品"
-
更新逻辑:-如果将付费产品从付费更新为付费，则称为RENEWAL产品
- Conversion Logic :- If a PRD_DESC is changed from Trail to Paid for the 1st time, then it is called CONVERSION product
Renewal Logic :- If a Paid Product is renewed from Paid to Paid, then it is called RENEWAL product
renewal_cnt应该仅从PAID to PAID产品开始.如果从PAID到PAID的event_type_cd为exp，则计数器不应递增.如果要付费的路径具有event_type_cd作为续订，则也应仅为零.该事件是按event_date
renewal_cnt should start only at the PAID to PAID product. If the PAID to PAID is having event_type_cd as exp then the counter shouldn't increment. If the trail to paid is having event_type_cd as renewal then also it should be zero only. the event's are ordered by event_date
is_conv应该设置为1.
is_conv should be set to 1 for the 1st Trail to Paid product.
is ren设置为1.
is ren should be set to 1 for the paid to paid product.
有人知道如何实现这一目标吗?
Does someone have the idea how to achieve this?
推荐答案
@PonderStibbons的解决方案很好，但是由于我不是基于递归而是自己创建的，因此也将其发布.请注意，其他数据集将有所不同.值得注意的是，该查询假定在相同
pkg_des记录的给定范围内，具有prd_descPAID 的记录组不会被非 PAID 值中断.这不是递归解决方案中的假设，这可能是丢弃我的解决方案的重要因素:The solution of @PonderStibbons is fine, but as I had made one myself, not based on recursion, I post it as well. Note that there will be differences for other data sets. Notably, this query assumes that within a given range of same
pkg_desrecords, the group of records withprd_descPAID are not interrupted by non-PAID values. This is not an assumption made in the recursive solution, which could be an important factor to discard my solution:select reg_id, pkg_des, prd_desc, event_date, event_type_cd, case when prd_desc = 'PAID' then greatest(0, -1+count(case when event_type_cd <> 'exp' then 1 end) over (partition by reg_id, pkg_des, prd_desc order by event_date asc rows between unbounded preceding and 0 preceding) ) else 0 end as renewal_cnt, case when lag(prd_desc) over (partition by reg_id, pkg_des order by event_date asc) = 'PAID' and prd_desc = 'PAID' and event_type_cd = 'renewal' then 1 else 0 end is_ren, case when lag(prd_desc) over (partition by reg_id, pkg_des order by event_date asc) = 'trail' and prd_desc = 'PAID' then 1 else 0 end is_conv from temp order by reg_id asc, pkg_des desc, event_date asc;对于给定的样本数据，输出是相同的:
Output is the same for the given sample data:
REG_ID | Pkg_DES | PRD_DESC | EVENT_DATE | event_type_cd | renewal_cnt | is_ren | is_conv -------+---------+----------+------------+---------------+-------------+--------+-------- 1 | CC | trail | 12-12-2012 | new sub | 0 | 0 | 0 1 | CC | trail | 12-13-2012 | exp | 0 | 0 | 0 1 | CC | PAID | 12-14-2012 | upsell | 0 | 0 | 1 1 | CC | PAID | 12-15-2012 | exp | 0 | 0 | 0* 1 | CC | PAID | 12-16-2012 | renewal | 1 | 1 | 0 1 | CC | PAID | 12-17-2012 | renewal | 2 | 1 | 0 1 | aa | trail | 12-12-2012 | new sub | 0 | 0 | 0 1 | aa | trail | 12-13-2012 | exp | 0 | 0 | 0 1 | aa | PAID | 12-14-2012 | renewal | 0 | 0 | 1 1 | aa | PAID | 12-15-2012 | renewal | 1 | 1 | 0 1 | aa | PAID | 12-16-2012 | upsell | 2 | 0* | 0 1 | aa | PAID | 12-17-2012 | renewal | 3 | 1 | 0我添加了一个星号，其输出与您在问题中列出的内容不同，但是上面是遵循字母规则的输出.
I added an asterisk where the output is different from what you listed in your question, but the above is the output when the rules are followed to the letter.
这篇关于如何根据条件分配计数器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
