Tengo una tabla que se ve así:
store_id cust_id amount indicator 1 1000 2.05 A 1 1000 3.10 A 1 2000 3.10 A 2 1000 5.10 B 2 2000 6.00 B 2 1000 1.05 ALo que estoy tratando de hacer es encontrar el porcentaje de ventas con los indicadores A, B para cada tienda al observar solo los ID de clientes únicos (es decir, las dos ventas al cliente 1000 en la tienda 1 solo contarían una vez). Algo como esto:
store_id pct_sales_A pct_sales_B pct_sales_AB 1 1.0 0.00 0.00 2 0.0 0.50 0.50Sé que puedo usar una subconsulta para encontrar los recuentos de cada tipo de transacción, pero tengo problemas para contar solo los distintos ID de cliente. Aquí hay un enfoque (incorrecto) para la columna pct_sales_A:
SELECT store_id, COUNT(DISTINCT(CASE WHEN txns_A>0 AND txns_B=0 THEN cust_ID ELSE NULL))/COUNT(*) AS pct_sales_A --this is wrong FROM (SELECT store_id, cust_id, COUNT(CASE WHEN indicator='A' THEN amount ELSE 0 END) as txns_A, COUNT(CASE WHEN indicator='B' THEN amount ELSE 0 END) as txns_B FROM t1 GROUP BY store_id, cust_id ) GROUP BY store_id;Puede usar la agregación condicional con COUNT(DISTINCT) :
SELECT store_id, COUNT(DISTINCT CASE WHEN indicator = 'A' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a, COUNT(DISTINCT CASE WHEN indicator = 'B' THEN cust_id END) * 1.0 / COUNT(DISTINCT cust_id) as ratio_a, FROM t1 GROUP BY store_id;Según su comentario, necesita dos niveles de agregación:
SELECT store_id, AVG(has_a) as ratio_a, AVG(has_b) as ratio_b, AVG(has_a * has_b) as ratio_ab FROM (SELECT store_id, cust_id, MAX(CASE WHEN indicator = 'A' THEN 1.0 ELSE 0 END) as has_a, MAX(CASE WHEN indicator = 'B' THEN 1.0 ELSE 0 END) as has_b FROM t1 GROUP BY store_id, cust_id ) sc GROUP BY store_id;Creo que quieres dos niveles de agregación condicional:
select store_id, avg(has_a = 1 and has_b = 0) pct_sales_a, avg(has_a = 0 and has_b = 1) pct_sales_b, avg(has_a + has_b = 2) pct_sales_ab from ( select store_id, cust_id, max(indicator = 'A') has_a, max(indicator = 'B') has_b from t1 group by store_id, cust_id ) t group by store_id
store_id | pct_ventas_a | pct_ventas_b | pct_sales_ab
-------: | ----------: | ----------: | -----------:
1 | 1.0000 | 0.0000 | 0.0000
2 | 0.0000 | 0.5000 | 0.5000