I have an analytical query with more than 1 million rows per day.
When I group the query fields by different customer, the query result does not change because each line becomes unique.
When I take the grouped query to quick sight, I cannot validate whether the number of different customers in the grouped query is the same as in the analytical query.
I notice that when I take the grouped query to quick sight, I cannot create calculated fields in an already aggregated field. I’m having difficulty validating whether the values are correct.
The query is a sales funnel. If the query is grouped, Quick Sight returns a different value compared to the same analytical query. For example, if I put the total_cupons field as sum, the value looks strange. If I add it in Quick Sight by count distinct, it always shows the value 1. I don’t know if it’s correct to group the data first or leave it to Quick Sight to do this.
Ex:
SELECT
COUNT(DISTINCT c.id) AS total_coupons,
COUNT(DISTINCT s.id) AS total_simulates,
COUNT(DISTINCT e.id) AS total_effective,
COUNT(DISTINCT customer_id) AS total_customers,
date,
category,
product
FROM coupon c
LEFT JOIN simulates s ON c.coupon_id = s.coupon_id
LEFT JOIN effective e ON s.simulate_id = e.simulate_id
GROUP BY date, category, product;
Hi @July
the SQL is you QS dataset definition?
Where is QS showing value 1?
Can you share the QS analysis?
I would leave the group to QS.
SELECT
c.id AS coupons,
s.id AS simulates,
e.id AS effective,
customer_id AS customers,
date,
category,
product
FROM coupon c
LEFT JOIN simulates s ON c.coupon_id = s.coupon_id
LEFT JOIN effective e ON s.simulate_id = e.simulate_id;
I just create a aggregate view with 500 rows and 3 distinct customers envolved, aggregate by date, product and category. When I try to visualize this field in Quick Sight, it returns 500 (if I aggregate by count) or 1 (if I aggregate by distinct count). I can’t visualize 3 value (customers)
I have three different customers, but when all these fields are aggregated by distinct customer (count distinct idt_customer) the qty_customers
and I take this base to Quick Sight, I can’t visualize the value of 3 different customers. For example, in a bar chart, if I add this field “qty_customers”, by sum, the value returns is the same number of rows as the base, if I add it by count distinct, the value gives 1, that is, I don’t I can see the value 3 for the different customer.
