Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted as SQL by registered user vvillacorta ( 3 years 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