adnanhamdussalam commented on issue #1867:
URL: https://github.com/apache/cloudberry/issues/1867#issuecomment-5103061332
Hi @liang8283,
PFB the required information:
CREATE OR REPLACE FUNCTION crm_lookup()
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $$
begin
-- pick records where atleast 1 relaz = row_id_asset
drop table if exists applied_ai_staging.crm_relaz;
create table applied_ai_staging.crm_relaz
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, sum(case when RELAZ_ABILITANTE_ABILITATO =
ROW_ID_ASSET then 1 else 0 end) IS_SAME
from
customer_base
where
RELAZ_ABILITANTE_ABILITATO is not null
group by
1; -- ~38s
--
drop table if exists crm_distinct_relaz;
create table crm_distinct_relaz
WITH (
appendonly=true,
compresstype=zstd
) as
select
*
from
crm_relaz
where
IS_SAME < 1; -- 2s
-- create new column, has values as original column for now
drop table if exists crm_crm_new__;
create table crm_crm_new__
WITH (
appendonly=true,
compresstype=zstd
) as
select
*, STATO_COMMERCIALE as STATO_COMMERCIALE_NEW
from
customer_base; -- 4m
-- update new column to cessato for relaz which don't have a signle
corresponding row_id_asset
update
crm_crm_new__
set
STATO_COMMERCIALE_NEW = 'CESSATO'
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_distinct_relaz where
RELAZ_ABILITANTE_ABILITATO is not null); -- 3m
-- -------------------------------
-- step 1: population intestatario
-- -------------------------------
-- create ref x table with original logic -> this would also be used in step
5 of btn population
drop table if exists ref_x;
create table ref_x
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO
from
crm_crm_new__
where
RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET and
RELAZ_ABILITANTE_ABILITATO is not null; -- 1m having count(distinct case when
tiid is pop then tiid end) = 1
-- relaz and asset same
drop table if exists crm_relaz_asset_same;
create table crm_relaz_asset_same
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO, TI_ID_INTESTATARIO
from
crm_crm_new__
where
(RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET
and TI_ID_INTESTATARIO != '' and TI_ID_INTESTATARIO is not null and
TI_ID_INTESTATARIO != 'NULL' and TI_ID_INTESTATARIO != 'none' and
TI_ID_INTESTATARIO != 'ND')
and END_DATE >= '2050-01-01'
group by
1, 2; -- 30s
drop table if exists crm_relaz_asset_same_copy;
create table crm_relaz_asset_same_copy
WITH (
appendonly=true,
compresstype=zstd
) as -- making a copy table for next insert
select
*
from
crm_relaz_asset_same; -- 2s
insert into crm_relaz_asset_same -- > case when ref_z holds true but tiid is
not popualted against record where relaz = row_id_asset
select distinct
RELAZ_ABILITANTE_ABILITATO, TI_ID_INTESTATARIO
from
crm_crm_new__
where
RELAZ_ABILITANTE_ABILITATO not in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_asset_same_copy where
RELAZ_ABILITANTE_ABILITATO is not null)
and RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not
null)
and TI_ID_INTESTATARIO != '' and TI_ID_INTESTATARIO is not null and
TI_ID_INTESTATARIO != 'NULL' and TI_ID_INTESTATARIO != 'none' and
TI_ID_INTESTATARIO != 'ND'; -- 2m
-- relaz asset diff
drop table if exists crm_relaz_asset_diff;
create table crm_relaz_asset_diff
WITH (
appendonly=true,
compresstype=zstd
) as -- relaz not present in previous table, dont have any row_id_asset
equal and are populated
select distinct
RELAZ_ABILITANTE_ABILITATO, max(TI_ID_INTESTATARIO) as
TI_ID_INTESTATARIO
from
crm_crm_new__
where
RELAZ_ABILITANTE_ABILITATO not in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not
null)
and TI_ID_INTESTATARIO != '' and TI_ID_INTESTATARIO is not null and
TI_ID_INTESTATARIO != 'NULL' and TI_ID_INTESTATARIO != 'none' and
TI_ID_INTESTATARIO != 'ND'
and END_DATE >= '2050-01-01'
group by
1; -- 2m
-- create tiid_new column
drop table if exists crm_crm_new_;
create table crm_crm_new_
WITH (
appendonly=true,
compresstype=zstd
) as
select
*, TI_ID_INTESTATARIO as TI_ID_INTESTATARIO_NEW
from
crm_crm_new__; -- 3m
-- update new tiid column with relaz = row_id_asset table
set optimizer = 'OFF';
update
crm_crm_new_
set
TI_ID_INTESTATARIO_NEW = crm_relaz_asset_same.TI_ID_INTESTATARIO
from
crm_relaz_asset_same
where
crm_crm_new_.RELAZ_ABILITANTE_ABILITATO =
crm_relaz_asset_same.RELAZ_ABILITANTE_ABILITATO
and (TI_ID_INTESTATARIO_NEW = '' or TI_ID_INTESTATARIO_NEW is null or
TI_ID_INTESTATARIO_NEW = 'NULL' or TI_ID_INTESTATARIO_NEW = 'none' or
TI_ID_INTESTATARIO_NEW = 'ND'); -- 5m
-- update new tiid column where relaz != row_id_asset table
update
crm_crm_new_
set
TI_ID_INTESTATARIO_NEW = crm_relaz_asset_diff.TI_ID_INTESTATARIO
from
crm_relaz_asset_diff
where
crm_crm_new_.RELAZ_ABILITANTE_ABILITATO =
crm_relaz_asset_diff.RELAZ_ABILITANTE_ABILITATO
and (TI_ID_INTESTATARIO_NEW = '' or TI_ID_INTESTATARIO_NEW is null or
TI_ID_INTESTATARIO_NEW = 'NULL' or TI_ID_INTESTATARIO_NEW = 'none' or
TI_ID_INTESTATARIO_NEW = 'ND'); -- 1m
set optimizer = 'ON';
-- ----------------------
-- step 2: create mapping
-- ----------------------
drop table if exists crm_crm_mapping_;
create table crm_crm_mapping_ as
select distinct
TI_ID_INTESTATARIO_NEW,
1 IS_MAIN_NUMBER,
max(case when AMBITO in ('Mobile', 'Mobile') then 1 else 0 end)
IS_Mobile,
max(case when AMBITO in ('Fisso', 'Fisso') then 1 else 0 end) IS_FIXED,
NUMERO_TELEFONO,
max(case when STATO_COMMERCIALE_NEW = 'ATTIVO' -- and
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from
ba_dev.crm_distinct_relaz where RELAZ_ABILITANTE_ABILITATO is not null)
then 1 else 0 end) IS_ACTIVE
from
crm_crm_new_
where
RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET and PRODOTTO_CLASS =
'SERVIZIO_ABILITANTE' and NUMERO_TELEFONO != '' and NUMERO_TELEFONO is not null
and NUMERO_TELEFONO != 'NULL'
and TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null
and TI_ID_INTESTATARIO_NEW != 'NULL'
group by
TI_ID_INTESTATARIO_NEW, NUMERO_TELEFONO
distributed by (NUMERO_TELEFONO, TI_ID_INTESTATARIO_NEW); -- 2m
ALTER TABLE crm_crm_mapping_ ADD PRIMARY KEY (NUMERO_TELEFONO,
TI_ID_INTESTATARIO_NEW);
-- prodotto class = elemento
INSERT INTO
crm_crm_mapping_
SELECT
crm_crm_new_.TI_ID_INTESTATARIO_NEW,
0 IS_MAIN_NUMBER,
max(case when AMBITO in ('Mobile', 'Mobile') then 1 else 0 end)
IS_Mobile,
max(case when AMBITO in ('Fisso', 'Fisso') then 1 else 0 end) IS_FIXED,
crm_crm_new_.NUMERO_TELEFONO,
max(case when STATO_COMMERCIALE_NEW = 'ATTIVO' -- and
RELAZ_ABILITANTE_ABILITATO in (select distinct RELAZ_ABILITANTE_ABILITATO from
ba_dev.crm_distinct_relaz where RELAZ_ABILITANTE_ABILITATO is not null)
then 1 else 0 end) IS_ACTIVE
from
crm_crm_new_
LEFT JOIN
crm_crm_mapping_
ON
crm_crm_new_.TI_ID_INTESTATARIO_NEW =
crm_crm_mapping_.TI_ID_INTESTATARIO_NEW
and
crm_crm_new_.NUMERO_TELEFONO = crm_crm_mapping_.NUMERO_TELEFONO
WHERE
crm_crm_mapping_.TI_ID_INTESTATARIO_NEW IS null
and
(crm_crm_new_.NUMERO_TELEFONO != '' and crm_crm_new_.NUMERO_TELEFONO is
not null and crm_crm_new_.NUMERO_TELEFONO != 'NULL'
and crm_crm_new_.TI_ID_INTESTATARIO_NEW != '' and
crm_crm_new_.TI_ID_INTESTATARIO_NEW is not null and
crm_crm_new_.TI_ID_INTESTATARIO_NEW != 'NULL')
group by
1, 5; -- 2m
-- adding associated ID
drop table if exists crm_crm_mapping_copy;
create table crm_crm_mapping_copy
WITH (
appendonly=true,
compresstype=zstd
) as
select
TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) IS_MAIN_NUMBER,
max(IS_Mobile) IS_Mobile,
max(IS_FIXED) IS_FIXED,
max(NUMERO_TELEFONO) NUMERO_TELEFONO,
max(IS_ACTIVE) IS_ACTIVE
from
crm_crm_mapping_
where
IS_MAIN_NUMBER = 1
group by
TI_ID_INTESTATARIO_NEW; -- 13s
--
drop table if exists crm_crm_mapping;
create table crm_crm_mapping as
select distinct
a.*,
(case when a.NUMERO_TELEFONO = b.NUMERO_TELEFONO and
a.TI_ID_INTESTATARIO_NEW != b.TI_ID_INTESTATARIO_NEW and a.IS_MAIN_NUMBER = 0
then b.TI_ID_INTESTATARIO_NEW else 'NA' end) ASSOCIATED_ID
from
crm_crm_mapping_ as a left join crm_crm_mapping_copy as b
on
a.NUMERO_TELEFONO = b.NUMERO_TELEFONO and a.TI_ID_INTESTATARIO_NEW !=
b.TI_ID_INTESTATARIO_NEW
distributed by (NUMERO_TELEFONO, TI_ID_INTESTATARIO_NEW); -- 2m
ALTER TABLE crm_crm_mapping ADD PRIMARY KEY (NUMERO_TELEFONO,
TI_ID_INTESTATARIO_NEW, ASSOCIATED_ID);
-- ---------------------------------
-- Step 3 num telefono column in crm
-- ---------------------------------
drop table if exists ref_x_new;
create table ref_x_new
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO, count(distinct case when
TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null then
TI_ID_INTESTATARIO_NEW end) TI_ID_INTESTATARIO_NEW
from
crm_crm_new_
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not
null) and RELAZ_ABILITANTE_ABILITATO is not null
group by
RELAZ_ABILITANTE_ABILITATO
having
count(distinct case when TI_ID_INTESTATARIO_NEW != '' and
TI_ID_INTESTATARIO_NEW is not null then TI_ID_INTESTATARIO_NEW end) = 1; -- 1m
having count(distinct case when tiid is pop then tiid end) = 1
drop table if exists crm_crm_btn_Mobile;
create table crm_crm_btn_Mobile
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, AMBITO, max(NUMERO_TELEFONO) as
NUMERO_TELEFONO_MAIN_Mobile
from
crm_crm_new_
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x_new where RELAZ_ABILITANTE_ABILITATO is
not null)
and PRODOTTO_CLASS = 'SERVIZIO_ABILITANTE' and AMBITO in ('Mobile',
'Fisso') and NUMERO_TELEFONO != '' and NUMERO_TELEFONO is not null and
NUMERO_TELEFONO != 'NULL'
group by
1, 2; -- 25s
--
drop table if exists crm_crm_new;
create table crm_crm_new
WITH (
appendonly=true,
compresstype=zstd
) as
select
*, NUMERO_TELEFONO as NUMERO_TELEFONO_MAIN
from
crm_crm_new_; -- 4 mins
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_crm_btn_Mobile.NUMERO_TELEFONO_MAIN_Mobile
from
crm_crm_btn_Mobile
where
crm_crm_new.RELAZ_ABILITANTE_ABILITATO =
crm_crm_btn_Mobile.RELAZ_ABILITANTE_ABILITATO
and crm_crm_new.AMBITO = crm_crm_btn_Mobile.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND'); --5m
-- pick relaz and tiid against which there are no btn
drop table if exists crm_relaz_no_btn;
create table crm_relaz_no_btn
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, TI_ID_INTESTATARIO_NEW -- sum(case when
NUMERO_TELEFONO_MAIN is not null then 1 else 0 end) IS_SAME
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x_new where RELAZ_ABILITANTE_ABILITATO is
not null) -- ref x holds true
and
RELAZ_ABILITANTE_ABILITATO is not null
and
TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null and
TI_ID_INTESTATARIO_NEW != 'NULL' and TI_ID_INTESTATARIO_NEW != 'none' and
TI_ID_INTESTATARIO_NEW != 'ND'
group by
1, 2
having
sum(case when NUMERO_TELEFONO_MAIN is not null and NUMERO_TELEFONO_MAIN
!= '' then 1 else 0 end) = 0; -- 45s
--
drop table if exists crm_crm_mapping_ambito;
create table crm_crm_mapping_ambito
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW,
IS_MAIN_NUMBER, IS_Mobile, IS_FIXED, NUMERO_TELEFONO, IS_ACTIVE, ASSOCIATED_ID,
(case when IS_Mobile = 1 then 'Mobile' when IS_FIXED = 1 then 'Fisso'
end) as AMBITO_MAPPING
from
crm_crm_mapping; -- 3s
--
drop table if exists crm_crm_mapping_ambito_no_btn;
create table crm_crm_mapping_ambito_no_btn
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) IS_MAIN_NUMBER,
max(IS_Mobile) IS_Mobile,
max(IS_FIXED) IS_FIXED,
max(NUMERO_TELEFONO) NUMERO_TELEFONO,
max(IS_ACTIVE) IS_ACTIVE,
max(ASSOCIATED_ID) ASSOCIATED_ID,
max(AMBITO_MAPPING) AMBITO_MAPPING
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab ->
confirm with AI
and IS_ACTIVE = 1 and IS_Mobile = 1 and IS_FIXED = 0-- this would
ignore all inactive records => need to figure how to give priority to active
records and in case none are present, pick inactive
group by
1; -- 4s
insert into
crm_crm_mapping_ambito_no_btn
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER),
max(IS_Mobile),
max(IS_FIXED),
max(NUMERO_TELEFONO),
max(IS_ACTIVE),
max(ASSOCIATED_ID),
max(AMBITO_MAPPING)
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab ->
confirm with AI
and IS_ACTIVE = 1 and IS_FIXED = 1 and IS_Mobile = 0-- this would
ignore all inactive records => need to figure how to give priority to active
records and in case none are present, pick inactive
group by
1;
drop table if exists crm_crm_mapping_ambito_no_btn_not_active;
create table crm_crm_mapping_ambito_no_btn_not_active
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') as TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) as IS_MAIN_NUMBER,
max(IS_Mobile) as IS_Mobile,
max(IS_FIXED) as IS_FIXED,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO,
max(IS_ACTIVE) as IS_ACTIVE,
max(ASSOCIATED_ID) as ASSOCIATED_ID,
max(AMBITO_MAPPING) as AMBITO_MAPPING
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab ->
confirm with AI
and IS_ACTIVE = 0 and IS_FIXED = 1 and IS_Mobile = 0 -- this would
ignore all inactive records => need to figure how to give priority to active
records and in case none are present, pick inactive
group by
1;
insert into
crm_crm_mapping_ambito_no_btn_not_active
select
nullif(TI_ID_INTESTATARIO_NEW, '') as TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) as IS_MAIN_NUMBER,
max(IS_Mobile) as IS_Mobile,
max(IS_FIXED) as IS_FIXED,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO,
max(IS_ACTIVE) as IS_ACTIVE,
max(ASSOCIATED_ID) as ASSOCIATED_ID,
max(AMBITO_MAPPING) as AMBITO_MAPPING
from
crm_crm_mapping_ambito
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab ->
confirm with AI
and IS_ACTIVE = 0 and IS_FIXED = 0 and IS_Mobile = 1 -- this would
ignore all inactive records => need to figure how to give priority to active
records and in case none are present, pick inactive
group by
1;
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_crm_mapping_ambito_no_btn.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn
where
crm_crm_new.TI_ID_INTESTATARIO_NEW =
crm_crm_mapping_ambito_no_btn.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO = crm_crm_mapping_ambito_no_btn.AMBITO_MAPPING
and crm_crm_mapping_ambito_no_btn.IS_MAIN_NUMBER = 1
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where
RELAZ_ABILITANTE_ABILITATO is not null); -- 2m
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN =
crm_crm_mapping_ambito_no_btn_not_active.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn_not_active
where
crm_crm_new.TI_ID_INTESTATARIO_NEW =
crm_crm_mapping_ambito_no_btn_not_active.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO =
crm_crm_mapping_ambito_no_btn_not_active.AMBITO_MAPPING
and crm_crm_mapping_ambito_no_btn_not_active.IS_MAIN_NUMBER = 1
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where
RELAZ_ABILITANTE_ABILITATO is not null);
drop table if exists crm_crm_mapping_ambito_no_btn_only_tiid;
create table crm_crm_mapping_ambito_no_btn_only_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) IS_MAIN_NUMBER,
max(IS_Mobile) IS_Mobile,
max(IS_FIXED) IS_FIXED,
max(NUMERO_TELEFONO) NUMERO_TELEFONO,
max(IS_ACTIVE) IS_ACTIVE,
max(ASSOCIATED_ID) ASSOCIATED_ID,
max(AMBITO_MAPPING) AMBITO_MAPPING
from
crm_crm_mapping_ambito_no_btn
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab ->
confirm with AI
-- and IS_ACTIVE = 1 and IS_FIXED = 1 and IS_Mobile = 0-- this would
ignore all inactive records => need to figure how to give priority to active
records and in case none are present, pick inactive
group by
1;
drop table if exists crm_crm_mapping_ambito_no_btn_not_active_only_tiid;
create table crm_crm_mapping_ambito_no_btn_not_active_only_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
nullif(TI_ID_INTESTATARIO_NEW, '') as TI_ID_INTESTATARIO_NEW,
max(IS_MAIN_NUMBER) as IS_MAIN_NUMBER,
max(IS_Mobile) as IS_Mobile,
max(IS_FIXED) as IS_FIXED,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO,
max(IS_ACTIVE) as IS_ACTIVE,
max(ASSOCIATED_ID) as ASSOCIATED_ID,
max(AMBITO_MAPPING) as AMBITO_MAPPING
from
crm_crm_mapping_ambito_no_btn_not_active
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_relaz_no_btn where TI_ID_INTESTATARIO_NEW is not null)
-- below condition and max conditions in select added by zaryab ->
confirm with AI
-- and IS_ACTIVE = 0 and IS_FIXED = 1 and IS_Mobile = 0 -- this would
ignore all inactive records => need to figure how to give priority to active
records and in case none are present, pick inactive
group by
1;
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN =
crm_crm_mapping_ambito_no_btn_only_tiid.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn_only_tiid
where
crm_crm_new.TI_ID_INTESTATARIO_NEW =
crm_crm_mapping_ambito_no_btn_only_tiid.TI_ID_INTESTATARIO_NEW
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where
RELAZ_ABILITANTE_ABILITATO is not null); -- 2m
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN =
crm_crm_mapping_ambito_no_btn_not_active_only_tiid.NUMERO_TELEFONO
from
crm_crm_mapping_ambito_no_btn_not_active_only_tiid
where
crm_crm_new.TI_ID_INTESTATARIO_NEW =
crm_crm_mapping_ambito_no_btn_not_active_only_tiid.TI_ID_INTESTATARIO_NEW
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_no_btn where
RELAZ_ABILITANTE_ABILITATO is not null);
--
drop table if exists crm_relaz_same_tiid;
create table crm_relaz_same_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, count(distinct TI_ID_INTESTATARIO_NEW)
TI_ID_INTESTATARIO_NEW
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO is not null
and
TI_ID_INTESTATARIO_NEW != '' and TI_ID_INTESTATARIO_NEW is not null and
TI_ID_INTESTATARIO_NEW != 'NULL' and TI_ID_INTESTATARIO_NEW != 'none' and
TI_ID_INTESTATARIO_NEW != 'ND'
group by
1
having
count(distinct TI_ID_INTESTATARIO_NEW) = 1; -- 40s
--
drop table if exists crm_relaz_elemento;
create table crm_relaz_elemento
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO,
max(AMBITO) AMBITO,
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_Fisso
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x_new where RELAZ_ABILITANTE_ABILITATO is
not null) -- ref x holds true
and PRODOTTO_CLASS = 'ELEMENTO_ABILITATO' and AMBITO in ('Fisso',
'Mobile') and NUMERO_TELEFONO_MAIN != '' and NUMERO_TELEFONO_MAIN is not null
and NUMERO_TELEFONO_MAIN != 'NULL'
and RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_same_tiid where
RELAZ_ABILITANTE_ABILITATO is not null)
-- group by added by zaryab along with above max statements, confirm from AI
group by
RELAZ_ABILITANTE_ABILITATO; -- 1m
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_relaz_elemento.NUMERO_TELEFONO_MAIN_Fisso
from
crm_relaz_elemento
where
crm_crm_new.RELAZ_ABILITANTE_ABILITATO =
crm_relaz_elemento.RELAZ_ABILITANTE_ABILITATO
and crm_crm_new.AMBITO = crm_relaz_elemento.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_same_tiid where
RELAZ_ABILITANTE_ABILITATO is not null); -- 2m
-- step 5
drop table if exists crm_relaz_multiple_tiid;
create table crm_relaz_multiple_tiid
WITH (
appendonly=true,
compresstype=zstd
) as
select
RELAZ_ABILITANTE_ABILITATO, count(distinct TI_ID_INTESTATARIO_NEW)
TI_ID_INTESTATARIO_NEW
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not
null) -- ref x holds true
and
RELAZ_ABILITANTE_ABILITATO is not null
group by
1
having
count(distinct TI_ID_INTESTATARIO_NEW) > 1; -- 1m
--
drop table if exists crm_relaz_multiple;
create table crm_relaz_multiple
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
RELAZ_ABILITANTE_ABILITATO,
max(TI_ID_INTESTATARIO_NEW) TI_ID_INTESTATARIO_NEW,
max(AMBITO) AMBITO,
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_Fisso
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not
null) -- ref x holds true
and RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET
and AMBITO in ('Fisso') and NUMERO_TELEFONO != '' and NUMERO_TELEFONO
is not null and NUMERO_TELEFONO != 'NULL'
and RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_multiple_tiid where
RELAZ_ABILITANTE_ABILITATO is not null)
group by -- group by and max added by zaryab
1; -- 35s
insert into
crm_relaz_multiple
select distinct
RELAZ_ABILITANTE_ABILITATO,
max(TI_ID_INTESTATARIO_NEW),
max(AMBITO),
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_Fisso
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not
null) -- ref x holds true
and RELAZ_ABILITANTE_ABILITATO = ROW_ID_ASSET
and AMBITO in ('Mobile') and NUMERO_TELEFONO != '' and NUMERO_TELEFONO
is not null and NUMERO_TELEFONO != 'NULL'
and RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_multiple_tiid where
RELAZ_ABILITANTE_ABILITATO is not null)
group by -- group by and max added by zaryab
1;
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_relaz_multiple.NUMERO_TELEFONO_MAIN_Fisso
from
crm_relaz_multiple
where
crm_crm_new.RELAZ_ABILITANTE_ABILITATO =
crm_relaz_multiple.RELAZ_ABILITANTE_ABILITATO
and crm_crm_new.TI_ID_INTESTATARIO_NEW =
crm_relaz_multiple.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO = crm_relaz_multiple.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND')
and crm_crm_new.RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from crm_relaz_multiple_tiid where
RELAZ_ABILITANTE_ABILITATO is not null); --2m
--
drop table if exists crm_tiid_not_in;
create table crm_tiid_not_in
WITH (
appendonly=true,
compresstype=zstd
) as
select distinct
TI_ID_INTESTATARIO_NEW
from
crm_crm_new
where
RELAZ_ABILITANTE_ABILITATO in (select distinct
RELAZ_ABILITANTE_ABILITATO from ref_x where RELAZ_ABILITANTE_ABILITATO is not
null) -- ref x holds true
and
TI_ID_INTESTATARIO_NEW not in (select distinct TI_ID_INTESTATARIO_NEW
from crm_relaz_multiple where TI_ID_INTESTATARIO_NEW is not null); -- 1m
drop table if exists crm_tiid_from_mapping;
create table crm_tiid_from_mapping
WITH (
appendonly=true,
compresstype=zstd
) as
select
TI_ID_INTESTATARIO_NEW,
max('Mobile') AMBITO,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO_MAPPING
from
crm_crm_mapping
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_tiid_not_in where TI_ID_INTESTATARIO_NEW is not null)
and IS_Mobile = 1
-- group by and max added by zaryab
group by
TI_ID_INTESTATARIO_NEW;
insert into
crm_tiid_from_mapping
select
TI_ID_INTESTATARIO_NEW,
max('Fisso') as AMBITO,
max(NUMERO_TELEFONO) as NUMERO_TELEFONO_MAPPING
from
crm_crm_mapping
where
TI_ID_INTESTATARIO_NEW in (select distinct TI_ID_INTESTATARIO_NEW from
crm_tiid_not_in where TI_ID_INTESTATARIO_NEW is not null)
and IS_FIXED = 1
-- group by and max added by zaryab
group by
TI_ID_INTESTATARIO_NEW;
--
update
crm_crm_new
set
NUMERO_TELEFONO_MAIN = crm_tiid_from_mapping.NUMERO_TELEFONO_MAPPING
from
crm_tiid_from_mapping
where
crm_crm_new.TI_ID_INTESTATARIO_NEW =
crm_tiid_from_mapping.TI_ID_INTESTATARIO_NEW
and crm_crm_new.AMBITO = crm_tiid_from_mapping.AMBITO
and (crm_crm_new.NUMERO_TELEFONO_MAIN is null or
crm_crm_new.NUMERO_TELEFONO_MAIN = '' or crm_crm_new.NUMERO_TELEFONO_MAIN =
'NULL' or crm_crm_new.NUMERO_TELEFONO_MAIN = 'none' or
crm_crm_new.NUMERO_TELEFONO_MAIN = 'ND'); -- 4m
-- step 4
drop table if exists crm_crm_final;
create table crm_crm_final
WITH (
appendonly=true,
compresstype=zstd,
orientation = column
) as
select
a.*,
(case when a.TI_ID_INTESTATARIO_NEW = b.TI_ID_INTESTATARIO_NEW and
a.AMBITO = (case when IS_Mobile = 1 then 'Mobile'end) then b.NUMERO_TELEFONO
end) MAIN_Mobile_NUMBER,
(case when a.TI_ID_INTESTATARIO_NEW = b.TI_ID_INTESTATARIO_NEW and
a.AMBITO = (case when IS_FIXED = 1 then 'Fisso'end) then b.NUMERO_TELEFONO end)
MAIN_Fisso_NUMBER
from
crm_crm_new as a left join crm_crm_mapping_ambito as b
on
a.TI_ID_INTESTATARIO_NEW = b.TI_ID_INTESTATARIO_NEW and a.AMBITO =
b.AMBITO_MAPPING and b.IS_MAIN_NUMBER = 1;
-- rollup btn ---ch
drop table if exists crm_crm2_scd_new_key1;
create table crm_crm2_scd_new_key1
WITH (
appendonly=true,
compresstype=zstd
) as
select
NUMERO_TELEFONO_MAIN as NUMERO_TELEFONO_MAIN_BTN,
max(TI_ID_INTESTATARIO_NEW) as TI_ID_INTESTATARIO_NEW_BTN,
-- group_concat(distinct CODICE_FISCALE order by CODICE_FISCALE)
CODICE_FISCALE_BTN,
string_agg(distinct case when CODICE_FISCALE<>'' then CODICE_FISCALE end
, ',') CODICE_FISCALE_BTN,
START_DATE,
LEAD(START_DATE,1) OVER (
partition by NUMERO_TELEFONO_MAIN
ORDER BY START_DATE
) END_DATE
from
crm_crm_final
group by
NUMERO_TELEFONO_MAIN, START_DATE; -- 2h,9m
delete from crm_crm2_scd_new_key1 where NUMERO_TELEFONO_MAIN_BTN = '';
update crm_crm2_scd_new_key1 set END_DATE = '2999-12-31' where END_DATE is
null;
-- rollup TIID
drop table if exists crm_crm1_scd_new_key1;
create table crm_crm1_scd_new_key1
WITH (
appendonly=true,
compresstype=zstd
) as
select
TI_ID_INTESTATARIO_NEW as TI_ID_INTESTATARIO_NEW_TIID,
max(NUMERO_TELEFONO_MAIN) as NUMERO_TELEFONO_MAIN_TIID,
-- group_concat(distinct CODICE_FISCALE order by CODICE_FISCALE)
CODICE_FISCALE_TIID,
string_agg(distinct case when CODICE_FISCALE<>'' then CODICE_FISCALE end
, ',') CODICE_FISCALE_TIID,
START_DATE,
LEAD(START_DATE,1) OVER (
partition by TI_ID_INTESTATARIO_NEW
ORDER BY START_DATE
) END_DATE
from
crm_crm_final
group by
TI_ID_INTESTATARIO_NEW, START_DATE; --1h
--
delete from crm_crm1_scd_new_key1 where TI_ID_INTESTATARIO_NEW_TIID = '';
update crm_crm1_scd_new_key1 set END_DATE = '2999-12-31' where END_DATE is
null;
--
truncate crm_crm1_scd_designgate3_red;
insert into crm_crm1_scd_designgate3_red
select * from crm_crm1_scd_new_key1;
truncate crm_crm2_scd_designgate3_red;
insert into crm_crm2_scd_designgate3_red
select * from crm_crm2_scd_new_key1;
-- for manual run
update file_load_log
set crm =1
where crm=0;
--
drop table if exists crm_relaz;
drop table if exists crm_distinct_relaz;
drop table if exists crm_crm_new__;
drop table if exists ref_x;
drop table if exists crm_relaz_asset_same;
drop table if exists crm_relaz_asset_same_copy;
drop table if exists crm_relaz_asset_diff;
drop table if exists crm_crm_new_;
drop table if exists crm_crm_mapping_;
drop table if exists crm_crm_mapping_copy;
drop table if exists crm_crm_mapping;
drop table if exists ref_x_new;
drop table if exists crm_crm_btn_Mobile;
drop table if exists crm_crm_new;
drop table if exists crm_relaz_no_btn;
drop table if exists crm_crm_mapping_ambito;
drop table if exists crm_crm_mapping_ambito_no_btn;
drop table if exists crm_crm_mapping_ambito_no_btn_not_active;
drop table if exists crm_crm_mapping_ambito_no_btn_only_tiid;
drop table if exists crm_crm_mapping_ambito_no_btn_not_active_only_tiid;
drop table if exists crm_relaz_same_tiid;
drop table if exists crm_relaz_elemento;
drop table if exists crm_relaz_multiple_tiid;
drop table if exists crm_relaz_multiple;
drop table if exists crm_tiid_not_in;
drop table if exists crm_tiid_from_mapping;
drop table if exists crm_crm_final;
drop table if exists crm_crm3_scd_temp;
drop table if exists crm_crm3_scd_temp_rno;
drop table if exists crm_crm1_scd_new_key1;
drop table if exists crm_crm2_scd_new_key1;
--
end;
################## Table DDL
######################################################################
customer_base
Column | Type | Collation |
Nullable | Default
---------------------------------+-----------------------------+-----------+----------+---------
row_id_asset | bigint | |
not null |
offer_name | character varying(255) | |
|
prodotto_class | character varying(35) | |
|
prodotto_subtype | character varying(255) | |
|
relaz_abilitante_abilitato | bigint | |
|
invariante | character varying(50) | |
|
invariante_ordine | character varying(50) | |
|
numero_telefono | character varying(120) | |
|
data_prima_attivazione | timestamp without time zone | |
|
data_attivazione | timestamp without time zone | |
|
data_cessazione_consistenza | timestamp without time zone | |
|
data_cessazione_prodotto | timestamp without time zone | |
|
ambito | character varying(120) | |
|
tipo_prodotto | character varying(300) | |
|
metodo_pagamento | character varying(200) | |
|
stato_commerciale | character varying(120) | |
|
flag_device | character varying(1) | |
|
tipo_pagamento | character varying(120) | |
|
id_cliente_mdc_org_ext | character varying(60) | |
|
codice_fiscale | character varying(200) | |
|
sesso | character varying(200) | |
|
segmento | character varying(120) | |
|
sottosegmento | character varying(120) | |
|
ti_id_intestatario | character varying(120) | |
|
ti_id_pagatore | character varying(120) | |
|
ti_id_utilizzatore | character varying(120) | |
|
churn_percentile | double precision | |
|
churn_probability | double precision | |
|
importo_fattura_fisso | double precision | |
|
nr_chiamate_settimanale | integer | |
|
data_ini_sett_chiamate | timestamp without time zone | |
|
data_fin_sett_chiamate | timestamp without time zone | |
|
quantita_byte_settimanale | bigint | |
|
data_ini_sett_bytes | timestamp without time zone | |
|
data_fin_sett_bytes | timestamp without time zone | |
|
internet_tier | bigint | |
|
num_tot_rate | character varying(300) | |
|
cap | character varying(50) | |
|
indirizzo | character varying(100) | |
|
data_creazione_tec_asset | timestamp without time zone | |
|
data_modifica_tec_asset | timestamp without time zone | |
|
hash_value | character varying(2000) | |
|
partition_date | date | |
|
flag_campione_controllo | character varying(50) | |
|
data_prevista_cessazione_sconto | character varying(100) | |
|
cic | character varying(50) | |
|
cus | character varying(100) | |
|
cproductid | character varying(255) | |
|
info_cliente | character varying(255) | |
|
start_date | date | |
|
end_date | date | |
|
filename | character varying(100) | |
|
Indexes:
"idx_crm_numero_telefono" btree (numero_telefono)
Distributed by: (row_id_asset, end_date)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
