Distribution Data Function/Solution

Database
Teradata Employee

Distribution Data Function/Solution

Dear members,

I have a requirement to distribute two channel equally whenever there is an add or deletion occurs. Is there any ways to allocate the relationship without randomly allocate if there is a change? Currently using the MOD function and its allocated randomly and cause a huge change of the data.

I have a sample for 3 scenarios and the expected outcome. Appreciate if anyone can share what is the function that can be used to cater such requirement.

3 REPLIES
Junior Contributor

Re: Distribution Data Function/Solution

Can you show how you currently do it?

Teradata Employee

Re: Distribution Data Function/Solution

I am using the current code as below. It closed and open the new records frequently as the party is randomly allocated.

INSERT INTO P1_TEMP.PARTY_CHANNEL_ALLOCATION_TASK(

CHANNEL_TYP_CD

,CHANNEL_ID

,PARTY_ID

,START_DT

,END_DT

,RECORD_DELETED_FLG

)

SEL

CRANK.CHANNEL_TYP_CD

,CRANK.CHANNEL_ID

,PRANK.PARTY_ID

,DATE AS START_DT

,NULL AS END_DT

,0 AS RECORD_DELETED_FLG

FROM

(SEL

CHANNEL_TYP_CD,

CHANNEL_ID,

ROW_NUMBER() OVER(

ORDER BY CHANNEL_ID)

FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP) CRANK (CHANNEL_TYP_CD,CHANNEL_ID,CHANNEL_RANK)

CROSS JOIN

(SEL COUNT(*)

FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP) CTOT(CHANNEL_TOTAL)

INNER JOIN

(

SELECT

PARTY.PARTY_ID,

ROW_NUMBER() OVER(

ORDER BY PARTY.PARTY_ID DESC)  

FROM P1_TEMP.C100_ACRM_PARTY_TEMP PARTY

GROUP BY 1) PRANK(PARTY_ID,PARTY_RANK)

 ON CRANK.CHANNEL_RANK=(

  CASE

   WHEN PRANK.PARTY_RANK MOD CTOT.CHANNEL_TOTAL=0

THEN CTOT.CHANNEL_TOTAL

ELSE PRANK.PARTY_RANK MOD CTOT.CHANNEL_TOTAL END);

Junior Contributor

Re: Distribution Data Function/Solution

Ouch, your current aproach will product join Party and Channel.

When you can apply QUANTILE logic to your problem there's no more product join and the mapping should be more stable. Try this (untested):

SEL
crank.channel_typ_cd
,crank.channel_id
,prank.party_id
,DATE AS start_dt
,NULL AS end_dt
,0 AS record_deleted_flg
FROM
(
SEL
channel_typ_cd
, channel_id
, ROW_NUMBER() OVER( ORDER BY channel_id)
FROM
P1_TEMP.C100_ACRM_CHANNEL_TEMP
) crank (channel_typ_cd,channel_id,channel_rank)
INNER JOIN
(
SELECT
party.party_id
, ((ROW_NUMBER() OVER( ORDER BY party.party_id DESC) -1)
* (SEL COUNT(*) FROM P1_TEMP.C100_ACRM_CHANNEL_TEMP)
/ COUNT(*) OVER()) + 1
FROM
P1_TEMP.C100_ACRM_PARTY_TEMP PARTY
GROUP BY
1
) prank(party_id,party_rank)
ON crank.channel_rank=prank.party_rank;