Tengo una tabla de ventas que se ve así:
store_id cust_id txn_id txn_date amt industry 200 1 1 20180101 21.01 1000 200 2 2 20200102 20.01 1000 200 2 3 20200103 19 1000 200 3 4 20180103 19 1000 200 4 5 20200103 21.01 1000 300 2 6 20200104 1.39 2000 300 1 7 20200105 12.24 2000 300 1 8 20200105 25.02 2000 400 2 9 20180106 103.1 1000 400 2 10 20200107 21.3 1000Aquí está el código para generar esta tabla de muestra:
CREATE TABLE sales( store_id INT, cust_id INT, txn_id INT, txn_date bigint, amt float, industry INT); INSERT INTO sales VALUES(200,1,1,20180101,21.01,1000); INSERT INTO sales VALUES(200,2,2,20200102,20.01,1000); INSERT INTO sales VALUES(200,2,3,20200103,19.00,1000); INSERT INTO sales VALUES(200,3,4,20180103,19.00,1000); INSERT INTO sales VALUES(200,4,5,20200103,21.01,1000); INSERT INTO sales VALUES(300,2,6,20200104,1.39,2000); INSERT INTO sales VALUES(300,1,7,20200105,12.24,2000); INSERT INTO sales VALUES(300,1,8,20200105,25.02,2000); INSERT INTO sales VALUES(400,2,9,20180106,103.1,1000); INSERT INTO sales VALUES(400,2,10,20200107,21.3,1000); Lo que me gustaría hacer es crear una nueva tabla de results que responda a la pregunta: qué porcentaje de mis clientes VIP, desde el 3 de enero de 2020, han comprado i) solo en mi tienda; ii) en mi tienda y en otras tiendas de la misma industria; iii) solo en otras tiendas de la misma industria? Defina un cliente VIP como alguien que ha comprado en una tienda determinada al menos una vez desde 2019.
Aquí está la tabla de salida de destino:
store industry pct_my_store_only pct_both pct_other_stores_only 200 1000 0.5 0.5 0.0 300 2000 0.5 0.5 0.0 400 1000 0.0 1.0 0.0Estoy tratando de usar funciones de ventana para lograr esto. Esto es lo que tengo hasta ahora:
CREATE TABLE results as SELECT s.store_id, s.industry, COUNT(DISTINCT (CASE WHEN s.txn_date>=20200103 THEN s.cust_id END)) * 1.0 / sum(count(DISTINCT (CASE WHEN s.txn_date>=20200103 THEN s.cust_id END))) OVER (PARTITION BY s.industry) AS pct_my_store_only ...AS pct_both ...AS pct_other_stores_only FROM sales s WHERE sales.txn_date>=20190101 GROUP BY s.store_id, s.industry;Lo anterior no parece ser correcto; ¿Cómo puedo corregir esto?
Una los distintos store_ids e industrias a los distintos store_ids e industrias concatenados para cada cliente y luego use la función de ventana avg() con la función find_in_set() para determinar si un cliente ha comprado o no en cada tienda:
with stores as ( select distinct store_id, industry from sales where txn_date >= 20190103 ), customers as ( select cust_id, group_concat(distinct store_id) stores, group_concat(distinct industry) industries from sales where txn_date >= 20190103 group by cust_id ), cte as ( select *, avg(concat(s.store_id) = concat(c.stores)) over (partition by s.store_id, s.industry) pct_my_store_only, avg(find_in_set(s.store_id, c.stores) = 0) over (partition by s.industry) pct_other_stores_only from stores s inner join customers c on find_in_set(s.industry, c.industries) and find_in_set(s.store_id, c.stores) ) select distinct store_id, industry, pct_my_store_only, 1 - pct_my_store_only - pct_other_stores_only pct_both, pct_other_stores_only from cte order by store_id, industry Ver la demostración .
Resultados:
> store_id | industry | pct_my_store_only | pct_both | pct_other_stores_only > -------: | -------: | ----------------: | -------: | --------------------: > 200 | 1000 | 0.5000 | 0.5000 | 0.0000 > 300 | 2000 | 0.5000 | 0.5000 | 0.0000 > 400 | 1000 | 0.0000 | 1.0000 | 0.0000