Filtering on a chart which shows most items are frequently bought together
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?
My suggestion takes always all items from an order and not 2 or 3 or n ones in any combination which is much more complex. IMO my approach isn't not bad for the needed efforts and it provides already some insights.
I assume that your requirement will need n cartesian loop-logic - each item against each other item from an order to be able to apply such count-approach. Only aggregations regardless if in script or UI won't be enough.
If I had this task I would use Excel with this 6 orders to develop a working logic. If it worked I would extend it to n more orders as well as to a few dependencies / context-parts - to prove the working and to get an imagination to the needed efforts and performance-impact by transferring the logic into the script and/or the UI. I think it won't be trivial ...
It looked that there are a lot of items - therefore I suggest to consider to apply the logic against an upper categorization of the items and/or to restrict the bought together views on certain categories - because otherwise the cartesian data-set might become quite (too) large. In each case it would prove if the logic itself is working and provides the wanted insights.
