Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am trying to make a barchart to show which 2, 3 or 4 items are most frequently bought together.
My sales orders are stored in the table ps2_trans, the sales orders rows in ps2_rows where I have item_id and trans_id (id of the sales order).
This is my load script
Select
t.id AS trans_id,
r.id AS row_id,
GROUP_CONCAT(r.item_id ORDER BY r.item_id SEPARATOR ' == ') AS concat_item_ids,
GROUP_CONCAT(i.nm->>"$.bg" ORDER BY r.item_id SEPARATOR '; ') AS concat_items,
COUNT(r.id) AS rows_cnt,
r.item_id as item_id
FROM
ps2_trans t,
ps2_rows r,
ps2_items i
WHERE
t.id=r.trans_id AND
r.item_id=i.id
GROUP BY
t.id
HAVING
rows_cnt > 1;
this is my dimension: =IF(LEN(concat_items) - LEN(REPLACE(concat_items, ';', '')) = 1(2 or 3), concat_items)
this is the measure: COUNT(trans_id)
since i am using group by t.id in my query therefore i cannot apply any filters on my bar chart (brand, category, item)
can anybody help with a more intelligent way to solve this problem?
It will depend on the variance of working logic in dependency to further requirements which way would be most suitable in regard to the size of the data-set. I assume that incremental approaches are possible and then the overall size has not so much impact.
So whats the expected output from this dataa?
the expected output is a barchart where the dimensions are the item combinations and the measure how many times are bought together