sesion_01_bigquery

gistfile1.txt

CREATE OR REPLACE TABLE area_comercial_ventas.tipo_documento_persona
/*CREATE TABLE IF NOT EXISTS*/
(
codigo_tipo_documento int64 NOT NULL options(description="Código del tipo de documento de la persona"),
tipo_documento string NOT NULL options(description="Tipo de documento de identidad de la persona"),
sistema_origen string NOT NULL options(description="Descripción del sistema origen"),
fecha_creacion timestamp  NOT NULL options(description="Fecha y hora de creación del registro"), 
usuario_creacion string NOT NULL options(description="Usuario que crea el registro")
)
options 
(
  description = "Catálogo de los tipos de documento de la persona"
);
---------------------------------------------------------
---------------------------------------------------------
--cambia una tabla particionada para que siempre se busque por particion
---------------------------------------------------------
ALTER TABLE mydataset.mypartitionedtable
SET OPTIONS (require_partition_filter=true)
---------------------------------------------------------
CREATE OR REPLACE VIEW area_comercial_ventas.v_persona AS
select 
pers.nro_documento,
pers.codigo_tipo_documento,
tip_pers.tipo_documento,
pers.nombres,
pers.apellidos,
pers.edad,
pers.genero,
pers.fecha_nacimiento,
pers.sistema_origen,
pers.fecha_creacion
from datacatalog.persona pers
left join datacatalog.tipo_documento_persona tip_pers on(pers.codigo_tipo_documento = tip_pers.codigo_tipo_documento);
------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

02

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
/*SELECT COLUMNAS*/
SELECT
date,
country,
fullvisitorid,
productsku,
productprice
FROM `data-to-insights.ecommerce.all_sessions_raw`;

/*SELECT **/
SELECT * FROM `data-to-insights.ecommerce.all_sessions_raw`;

/*LIMIT*/
SELECT fullvisitorid,country,city FROM `data-to-insights.ecommerce.all_sessions_raw` limit 5;


/*FROM **/
SELECT count(1) as cant,
  sentimentScore
FROM `data-to-insights.ecommerce.all_sessions_raw` session
JOIN `data-to-insights.ecommerce.products` product on(session.productSKU = product.SKU)
group by sentimentScore
order by 2 desc;

/*DISTINCT */
SELECT DISTINCT * FROM `data-to-insights.ecommerce.all_sessions_raw` ;

/*IFNULL */
SELECT 
fullvisitorid,
channelgrouping,
country,
city,
ifnull(pagetitle,'Por Definir') as pagetitle
FROM `data-to-insights.ecommerce.all_sessions_raw`
where pagetitle is null;


/*EXCEPT*/
SELECT * EXCEPT(fullvisitorid,chhanelgrouping)  FROM `data-to-insights.ecommerce.all_sessions_raw`;

/*INTERSECT */
SELECT fullVisitorId FROM `data-to-insights.ecommerce.all_sessions_raw` s1 where s1.country = 'United States'
INTERSECT DISTINCT
SELECT fullVisitorId FROM `data-to-insights.ecommerce.all_sessions_raw` s2 where s2.country = 'Canada';


/*REPLACE */
SELECT * replace("Perú" as country) FROM `data-to-insights.ecommerce.all_sessions_raw` where country ='Peru';

/*UNION ALL*/
SELECT fullvisitorid,country,'all_sessions' as fuente,date  FROM `data-to-insights.ecommerce.all_sessions` where fullvisitorid ='4801213629825609918'
UNION ALL
SELECT fullvisitorid,country,'all_sessions_raw' as fuente,date FROM `data-to-insights.ecommerce.all_sessions_raw` where fullvisitorid ='4801213629825609918' 

/*ALIAS COLUMNA Y TABLA*/
SELECT
  visitas.fullvisitorid AS codigo_visitante,
  visitas.country as pais,
  visitas.date as fecha_visita
FROM
  `data-to-insights.ecommerce.all_sessions_raw` as visitas
WHERE
  visitas.fullvisitorid ='4801213629825609918';

/*WITH*/
WITH base_visitas_20170801 AS 
(
  SELECT
    visitas.fullvisitorid AS codigo_visitante,
    visitas.country as pais,
    visitas.date as fecha_visita,
    visitas.productSKU as codigo_producto,
    visitas.v2ProductName as nombre_producto,
    visitas.productPrice as precio_producto,
    visitas.productquantity as cantidad_producto
  FROM `data-to-insights.ecommerce.all_sessions_raw` as visitas 
  WHERE visitas.date ='20170801'
)

SELECT * FROM base_visitas_20170801;


/*SUBCONSULTAS */
SELECT count(distinct fullVisitorId) FROM `data-to-insights.ecommerce.all_sessions_raw` s1 WHERE s1.country = 'United States'
AND EXISTS(
SELECT 1 FROM `data-to-insights.ecommerce.all_sessions_raw` s2 WHERE s2.country = 'Canada'
AND s1.fullVisitorId = s2.fullVisitorId);


/*ORDER BY POR NOMBRE COLUMNA Y POR POSICION*/
SELECT v2ProductCategory as categoria_producto,
       count(1) as cantidad_visitas
FROM `data-to-insights.ecommerce.all_sessions_raw`
group by v2ProductCategory
order by cantidad_visitas desc;


------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------

03

------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------
/*CASE expression*/
SELECT
  CASE country
    WHEN 'Chile' THEN 'America del Sur'
    WHEN 'Colombia' THEN 'America del Sur'
    WHEN 'Ecuador' THEN 'America del Sur'
    WHEN 'Panama' THEN 'America del Sur'
    WHEN 'Peru' THEN 'America del Sur'
    ELSE 'Resto del Mundo'
END
  AS pacifico_sur_comercial,
  country,
  count(1) as cantidad_visitas
FROM
  `data-to-insights.ecommerce.all_sessions_raw`
group by pacifico_sur_comercial,
country order by 2 asc
-----------------------------------------------------
/*CASE WHEN*/
SELECT
  CASE 
   WHEN country = 'Chile' THEN 'America del Sur'
   WHEN country =  'Colombia' THEN 'America del Sur'
   WHEN country = 'Ecuador' THEN 'America del Sur'
   WHEN country = 'Panama' THEN 'America del Sur'
   WHEN country = 'Peru' THEN 'America del Sur'
   ELSE 'Resto del Mundo'
END
  AS pacifico_sur_comercial,
  country,
  count(1) as cantidad_visitas
FROM
  `data-to-insights.ecommerce.all_sessions_raw`
group by pacifico_sur_comercial,
country order by 2 asc;
---------------------------------------------------------
/*IF*/
---------------------------------------------------------
SELECT 
if(products.stocklevel > 0 ,'Hay Stock','No hay Stock') as stock_condicion,
products.*
FROM `data-to-insights.ecommerce.products` products
---------------------------------------------------------
/*IFNULL*/
---------------------------------------------------------
SELECT count(1), 
       v2ProductCategory as session_categoria,
       categories.category as product_categoria,
        nullif(v2ProductCategory,categories.category) as comparacion_categoria
FROM `data-to-insights.ecommerce.all_sessions_raw`  session
join `data-to-insights.ecommerce.categories` categories on(session.productSKU = categories.productSKU)
group by v2ProductCategory,
       categories.category ,
       comparacion_categoria;
---------------------------------------------------------
/*NULLIF*/
---------------------------------------------------------
SELECT count(1), 
       v2ProductCategory as session_categoria,
       categories.category as product_categoria,
       nullif(v2ProductCategory,categories.category) as comparacion_categoria,
       IFNULL(nullif(v2ProductCategory,categories.category),'MATCH') as comparacion_categoria_2
FROM `data-to-insights.ecommerce.all_sessions_raw`  session
join `data-to-insights.ecommerce.categories` categories on(session.productSKU = categories.productSKU)
group by v2ProductCategory,
       categories.category ,
       comparacion_categoria,
       comparacion_categoria_2;
---------------------------------------------------------