Model / associate · All operations

Find items that occur together

Association rules, frequent itemsets, market basket.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result
a new grain: each row stands for a group of input rows
Level
several clauses of SQL, or a window

The DuckDB step

Pairs of products bought by the same customer

It reads the orders table the proofs share. Create it first:

SQL
CREATE TABLE orders AS SELECT * FROM (VALUES
  (1,  'Alice Ng',      'north', DATE '2026-01-03', 120.00, 'widget', 'a|b',   '{"channel":"web","items":[1,2]}'),
  (2,  'alice ng',      'north', DATE '2026-01-05',  80.50, 'gadget', 'b',     '{"channel":"shop","items":[3]}'),
  (3,  'Bob  Okafor',   'south', DATE '2026-01-05', 300.00, 'widget', 'a|c',   '{"channel":"web","items":[4,5,6]}'),
  (4,  'Carla Díaz',    'east',  DATE '2026-01-09',  NULL,  'gizmo',  NULL,    '{"channel":"web","items":[]}'),
  (5,  'Bob Okafor',    'south', DATE '2026-01-12',  45.25, 'gadget', 'c',     '{"channel":"phone","items":[7]}'),
  (6,  'Dev Patel',     'west',  DATE '2026-01-12', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (7,  'Carla Diaz',    'east',  DATE '2026-01-20',  99.99, 'gizmo',  'b|c',   '{"channel":"shop","items":[10]}'),
  (8,  'Dev Patel',     'west',  DATE '2026-02-02', 510.00, 'widget', 'a',     '{"channel":"web","items":[8,9]}'),
  (9,  'Eve Larsen',    NULL,    DATE '2026-02-03',  15.00, 'gadget', NULL,    '{"channel":"web","items":[11]}'),
  (10, 'Alice Ng',      'north', DATE '2026-02-10', 220.00, 'gizmo',  'a|b|c', '{"channel":"phone","items":[12,13]}')
) t(order_id, customer, region, order_date, amount, product, tags, meta);

Then run the statement:

SQL
CREATE TEMP TABLE result AS
WITH basket AS (SELECT DISTINCT lower(strip_accents(regexp_replace(customer, '\s+', ' ', 'g'))) AS who, product FROM orders)
SELECT a.product AS p1, b.product AS p2, count(*) AS together
FROM basket a JOIN basket b ON a.who = b.who AND a.product < b.product GROUP BY ALL ORDER BY together DESC;
SELECT * FROM result ORDER BY together DESC, p1, p2;
SELECT CASE WHEN ((SELECT list(p1 || '+' || p2 || '=' || together ORDER BY p1, p2) = ['gadget+gizmo=1', 'gadget+widget=2', 'gizmo+widget=1'] FROM result)) THEN 'as expected' ELSE error('the pairs bought together are not counted as expected') END AS verdict;
  • It needs nothing installed beyond DuckDB itself.
  • The statement checks its own result and raises an error when it is wrong: “the pairs bought together are not counted as expected”.
  • It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Find frequent item sets

Find the sets of items that occur together in many transactions, with their support.

Derive association rules

Turn frequent item sets into if-then rules scored by confidence, lift or similar measures.

Find frequent sequences

Find patterns of items that occur in the same order across many sequences of transactions.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (3)
  • MB Affinityread 2026-09-27

    Build an item-by-item affinity matrix from transaction data

  • MB Inspectread 2026-09-27

    List, filter and analyse the rules or itemsets produced by MB Rules

  • MB Rulesread 2026-09-27

    Mine association rules or frequent itemsets from transactions with Apriori or Eclat

KNIME Analytics Platform (3)
RapidMiner Studio (6)
  • Create Association Rulesread 2026-09-27

    Derive association rules from frequent item sets by confidence, lift or other criteria

  • FP-Growthread 2026-09-27

    Find frequent item sets with the FP-Growth algorithm

  • Generalized Sequential Patternsread 2026-09-27

    Find frequent sequential patterns in transactions with the GSP algorithm

  • Generate Item Set Indicatorsread 2026-09-27

    Add indicator columns flagging which frequent item sets each row contains

  • Item Sets to Dataread 2026-09-27

    Convert frequent item sets into a table with support, frequency and a score

  • Unify Item Setsread 2026-09-27

    Compare groups of frequent item sets and remove those that are not unique

Background

Association rule learning is a rule-based machine learning method for discovering interesting relations between variables in large databases.[1] An association rule such as {butter, bread} ⇒ {milk} means that if butter and bread are bought, customers also buy milk.[1] Beyond market basket analysis, association rules are employed in many areas, including web usage mining, intrusion detection and bioinformatics.[1]

When to use it

Every association rule is made of two sets of items, called itemsets: the antecedent, read as the if, and the consequent, read as the then.[1] The support of an itemset is the proportion of transactions in the dataset that contain it.[1][2] The confidence of a rule is the ratio of transactions containing both the antecedent and the consequent to the transactions containing the antecedent.[1]

Searching massive numbers of possible associations carries a large risk of finding spurious ones, items that co-occur with unexpected frequency but only by chance.[1] Statistically sound association discovery controls the risk of spurious associations, in most cases reducing the risk of finding any to a user-specified significance level.[1] The lift of a rule is the ratio of the observed support to that expected if the antecedent and the consequent were independent.[1][2] A lift of 1 implies that the antecedent and the consequent occur independently of each other, and no rule can be drawn involving them.[1]

The method

Rakesh Agrawal, Tomasz Imieliński and Arun Swami introduced association rules for discovering regularities between products in large-scale transaction data recorded by point-of-sale systems in supermarkets.[1] The concept of association rules was popularised particularly by the 1993 article of Agrawal and colleagues, but what is now called association rules was already introduced in a 1966 paper on a general data mining method called GUHA, developed by Petr Hájek and others.[1]

Association rule generation is usually split into two steps: a minimum support threshold finds all the frequent itemsets in the database, and a minimum confidence threshold then creates rules from them.[1] The set of possible itemsets is the power set of the items, less the empty set, so for n items it has size 2^n − 1 and grows exponentially in n.[1] An efficient search uses the downward-closure property of support, also called anti-monotonicity, which guarantees that all the subsets of a frequent itemset are also frequent.[1]

Apriori was given by R. Agrawal and R. Srikant in 1994, and identifies the frequent individual items in the database and extends them to larger itemsets as long as those itemsets appear sufficiently often.[1] Apriori uses a bottom-up approach, extending frequent subsets one item at a time and testing groups of candidates against the data, and terminates when no further successful extensions are found.[1] Equivalence Class Transformation (Eclat) is a backtracking algorithm that traverses the frequent itemset lattice depth first, and will almost certainly use less memory than the breadth-first search Apriori uses.[1] FP-growth, where FP stands for frequent pattern, inserts transactions into a tree structure called the FP-tree and grows frequent itemsets from it directly, instead of generating candidates and testing them against the entire database as Apriori does.[1] In RapidMiner, the FP-tree compresses the data so much that, in many cases, even large databases will fit into main memory.[3]

In RapidMiner, rules can be selected by a criterion such as confidence, lift or conviction.[2] In RapidMiner, conviction attempts to measure the degree of implication of a rule, and is sensitive to rule direction, so a rule and its reverse need not share it.[2]

Compared with the alternatives

In contrast with sequence mining, association rule learning typically does not consider the order of items either within a transaction or across transactions.[1] Sequential pattern mining discovers subsequences that are common to more than a user-set minimum support threshold of sequences in a sequence database, where a sequence is an ordered list of transactions.[1] Algorithms for sequence mining, among them the Generalized Sequential Pattern (GSP) algorithm, are mostly based on the level-wise approach of Apriori.[4] In RapidMiner's Generalized Sequential Patterns operator, a window size treats transactions close together in time as one transaction, so a customer who returns minutes later for a forgotten item can still support a pattern.[5] In RapidMiner's Generalized Sequential Patterns operator, max gap and min gap stop a customer's sequence supporting a pattern when the transactions containing it are too widely separated in time or too near.[5]

Sources

  1. [1]Association rule learning - Wikipediasecondary source
  2. [2]Create Association Rules - Rapidminer Documentationdocumentation
  3. [3]FP-Growth - Rapidminer Documentationdocumentation
  4. [4]GSP algorithm - Wikipediasecondary source
  5. [5]Generalized Sequential Patterns - Rapidminer Documentationdocumentation