Psst.. new poll here.
[email protected] webmail now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!
Paste
Pasted as SQL by registered user vvillacorta ( 1 year ago )
DROP TABLE IF EXISTS d_mdl_vpc_disc.HM_ADEX CREATE TABLE d_mdl_vpc_disc.HM_ADEX
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_periodo'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/aceptacion/athena_2/HM_ADEX/'
)
AS (
SELECT CAST(periodo_val as varchar) periodo_adex, ruc_cd num_ruc,
substr(replace(substr(cast(date_add('month', +2, date_parse(CAST(periodo_val as varchar) ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6) periodo_ejecucion,
substr(replace(substr(cast(date_add('month', +3, date_parse(CAST(periodo_val as varchar) ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6) periodo_campania,
SUM(valor_mto) monto_adex,
SUM(CASE WHEN tipo_dsc = 'EXP' THEN valor_mto else 0 END) monto_exportacion,
SUM(CASE WHEN tipo_dsc = 'IMP' THEN valor_mto else 0 END) monto_importtacion,
SUM(CASE WHEN tipo_dsc = 'EXP' AND continente_dsc IN ('AFRICA', 'ASIA', 'OCEANIA', 'EUROPA') THEN valor_mto else 0 END) monto_exporttacion_otro_continente,
SUM(CASE WHEN tipo_dsc = 'IMP' AND continente_dsc IN ('AFRICA', 'ASIA', 'OCEANIA', 'EUROPA') THEN valor_mto else 0 END) monto_importtacion_otro_continente,
SUM(CASE WHEN tipo_dsc = 'EXP' AND continente_dsc LIKE '%AMERICA%' THEN valor_mto else 0 END) monto_exporttacion_america,
SUM(CASE WHEN tipo_dsc = 'IMP' AND continente_dsc LIKE '%AMERICA%' THEN valor_mto else 0 END) monto_importtacion_america,
periodo_val p_periodo
FROM e_perm_aws.mst_vpc_adex
GROUP BY periodo_val, ruc_cd
)
DROP TABLE IF EXISTS d_mdl_vpc_disc.STAGE_H... de Villacorta Plasencia, VictorVillacorta Plasencia, Victor09:32DROP TABLE IF EXISTS d_mdl_vpc_disc.STAGE_HM_ADEX_NUEVO_MES CREATE TABLE d_mdl_vpc_disc.STAGE_HM_ADEX_NUEVO_MES
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['p_periodo'],
external_location= 's3://sagemaker-us-east-1-058528764918/vpc/aceptacion/athena_nuevomes/STAGE_HM_ADEX_NUEVO_MES/'
)
AS (
SELECT AA.PERIODO_CAMPANIA, AA.PERIODO_EJECUCION, AA.NUM_RUC,
MAX(COALESCE(XX.monto_adex,0)) max_monto_adex_u12m,
MAX(COALESCE(XX.monto_exportacion,0)) max_monto_exportacion_u12m,
MAX(COALESCE(XX.monto_importtacion,0)) max_monto_importtacion_u12m,
MAX(COALESCE(XX.monto_exporttacion_otro_continente,0)) max_monto_exporttacion_otro_continente_u12m,
MAX(COALESCE(XX.monto_importtacion_otro_continente,0)) max_monto_importtacion_otro_continente_u12m,
MAX(COALESCE(XX.monto_exporttacion_america,0)) max_monto_exporttacion_america_u12m,
MAX(COALESCE(XX.monto_importtacion_america,0)) max_monto_importtacion_america_u12m,
sum(COALESCE(XX.monto_adex,0)) sum_monto_adex_u12m,
sum(COALESCE(XX.monto_exportacion,0)) sum_monto_exportacion_u12m,
sum(COALESCE(XX.monto_importtacion,0)) sum_monto_importtacion_u12m,
sum(COALESCE(XX.monto_exporttacion_otro_continente,0)) sum_monto_exporttacion_otro_continente_u12m,
sum(COALESCE(XX.monto_importtacion_otro_continente,0)) sum_monto_importtacion_otro_continente_u12m,
sum(COALESCE(XX.monto_exporttacion_america,0)) sum_monto_exporttacion_america_u12m,
sum(COALESCE(XX.monto_importtacion_america,0)) sum_monto_importtacion_america_u12m,
AA.periodo_ejecucion p_periodo
FROM d_mdl_vpc_disc.STAGE_HM_BASE_CAMPANIA_NUEVO_MES AA
LEFT JOIN d_mdl_vpc_disc.HM_ADEX XX
ON AA.NUM_RUC = XX.NUM_RUC AND AA.PERIODO_CAMPANIA >= XX.PERIODO_CAMPANIA
AND substr(replace(substr(cast(date_add('month', -12, date_parse(AA.periodo_campania ,'%Y%m')) as varchar), 1, 10), '-', ''), 1, 6) <= xx.PERIODO_CAMPANIA
WHERE 1 = 1
AND AA.PERIODO_EJECUCION >= '202111'
GROUP BY AA.PERIODO_CAMPANIA, AA.PERIODO_EJECUCION, AA.NUM_RUC
)
SELECT PERIODO_CAMPANIA, count(1), count(distinct(num_ruc))
FROM d_mdl_vpc_disc.STAGE_HM_ADEX_NUEVO_MES
GROUP BY PERIODO_CAMPANIA
ORDER BY PERIODO_CAMPANIA DESCtiene menú contextualRedactar
Revise this Paste