About Association
Identify the probability of co-occurring items in a collection using Association.
The relationships between co-occurring items are expressed as Association Rules.
Association Rules
Identify the probability of co-occurring items in a collection within the data.
The results of an association model are the rules that identify patterns of association within the data. Oracle Machine Learning for SQL does not support the scoring operation for association modeling.
Association rules can be applied as follows:
- Support: How often do these items occur together in the data?
- Confidence: How frequently the consequent occurs in transactions that contain the antecedent.
- Value: How much business value is connected to item associations
Market-Basket Analysis
Use association rules to analyze sales transactions, such as customers frequently buying cereal and milk together.
Association rules are often used to analyze sales transactions. For example, it is noted that customers who buy cereal at the grocery store often buy milk at the same time. In fact, association analysis find that 85% of the checkout sessions that include cereal also include milk. This relationship can be formulated as the following rule:
Cereal implies milk with 85% confidence
This application of association modeling is called market-basket analysis. It is valuable for direct marketing, sales promotions, and for discovering business trends. Market-basket analysis can also be used effectively for store layout, catalog design, and cross-sell.
Use Market Basket Data
Understand the use of association and Apriori for market basket analysis.
Market basket data identifies the items sold in a set of baskets or transactions. Oracle Machine Learning for SQL provides the association machine learning function for market basket analysis.
Association models use the Apriori algorithm to generate association rules that describe how items tend to be purchased in groups. For example, an association rule can assert that people who buy peanut butter are 80% likely to also buy jelly.
Market basket data is usually transactional. In transactional data, a case is a transaction and the data for a transaction is stored in multiple rows. OML4SQL association models can be built on transactional data or on single-record case data. The ODMS_ITEM_ID_COLUMN_NAME and ODMS_ITEM_VALUE_COLUMN_NAME settings specify whether the data for association rules is in transactional format.
Note:
Association models are the only type of model that can be built on native transactional data. For all other types of models, OML4SQL requires that the data be presented in single-record case format.
The Apriori algorithm assumes that the data is transactional and that it has many missing values. Apriori interprets all missing values as sparse data, and it has its own native mechanisms for handling sparse data.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information on the ODMS_ITEM_ID_COLUMN_NAME and ODMS_ITEM_VALUE_COLUMN_NAME settings.
Example: Creating a Nested Column for Market Basket Analysis
The example shows how to define a nested column for market basket analysis.
Association models can be built on native transactional data or on nested data. The following example shows how to define a nested column for market basket analysis.
The following SQL statement transforms this data to a column of type DM_NESTED_NUMERICALS in a view called SALES_TRANS_CUST_NESTED. This view can be used as a case table for machine learning.
CREATE VIEW sales_trans_cust_nested AS
SELECT trans_id,
CAST(COLLECT(DM_NESTED_NUMERICAL(
prod_name, 1))
AS DM_NESTED_NUMERICALS) custprods
FROM sales_trans_cust
GROUP BY trans_id;
This query returns two rows from the transformed data.
SELECT * FROM sales_trans_cust_nested
WHERE trans_id < 101000
AND trans_id > 100997;
The output is as follows:
TRANS_ID CUSTPRODS(ATTRIBUTE_NAME, VALUE)
------- ------------------------------------------------
100998 DM_NESTED_NUMERICALS
(DM_NESTED_NUMERICAL('O/S Documentation Set - English', 1)
100999 DM_NESTED_NUMERICALS
(DM_NESTED_NUMERICAL('CD-RW, High Speed Pack of 5', 1),
DM_NESTED_NUMERICAL('External 8X CD-ROM', 1),
DM_NESTED_NUMERICAL('SIMM- 16MB PCMCIAII card', 1))Example 7-1 Convert to a Nested Column
The view SALES_TRANS_CUST provides a list of transaction IDs to identify each market basket and a list of the products in each basket.
describe sales_trans_cust
The output is as follows:
Name Null? Type
----------------------------------------------------- -------- ----------------
TRANS_ID NOT NULL NUMBER
PROD_NAME NOT NULL VARCHAR2(50)
QUANTITY NUMBER
Related Topics
Association Rules and eCommerce
Apply association rules in eCommerce to personalize web pages by predicting user behavior based on page visits.
Association modeling has important applications in other domains as well. For example, in e-commerce applications, association rules may be used for Web page personalization. An association model might find that a user who visits pages A and B is 70% likely to also visit page C in the same session. Based on this rule, a dynamic link can be created for users who are likely to be interested in page C. The association rule is expressed as follows:
A and B imply C with 70% confidence
Related Topics
Use Retail Data for Analysis
Retail analysis often makes use of association rules and association models.
The association rules are enhanced to calculate aggregates along with rules or itemsets.
Related Topics
Example: Calculating Aggregates
This example shows how to calculate aggregates using the customer grocery purchase and profit data.
Calculating Aggregates for Grocery Store Data
Assume a grocery store has the following data:
Table 7-1 Grocery Store Data
| Customer | Item A | Item B | Item C | Item D |
|---|---|---|---|---|
| Customer 1 | Buys (Profit $5.00) | Buys (Profit $3.20) | Buys (Profit $12.00) | NA |
| Customer 2 | Buys (Profit $4.00) | NA | Buys (Profit $4.20) | NA |
| Customer 3 | Buys (Profit $3.00) | Buys (Profit $10.00) | Buys (Profit $14.00) | Buys (Profit $8.00) |
| Customer 4 | Buys (Profit $2.00) | NA | NA | Buys (Profit $1.00) |
The basket of each customer can be viewed as a transaction. The manager of the store is interested in not only the existence of certain association rules, but also in the aggregated profit if such rules exist.
In this example, one of the association rules can be (A, B)=>C for customer 1 and customer 3. Together with this rule, the store manager may want to know the following:
-
The total profit of item A appearing in this rule
-
The total profit of item B appearing in this rule
-
The total profit for consequent C appearing in this rule
-
The total profit of all items appearing in the rule
For this rule, the profit for item A is $5.00 + $3.00 = $8.00, for item B the profit is $3.20 + $10.00 = $13.20, for consequent C, the profit is $12.00 + $14.00 = $26.00, for the antecedent itemset (A, B) is $8.00 + $13.20 = $21.20. For the whole rule, the profit is $21.20 + $26.00 = $47.40.
Related Topics
Transactional Data
Understand transactional data, where a case includes a collection of items like a market basket at checkout.
Unlike other machine learning functions, association is transaction-based. In transaction processing, a case includes a collection of items such as the contents of a market basket at the checkout counter. The collection of items in the transaction is an attribute of the transaction. Other attributes might be a timestamp or user ID associated with the transaction.
Transactional data, also known as market-basket data, is said to be in multi-record case format because a set of records (rows) constitute a case. For example, in the following figure, case 11 is made up of three rows while cases 12 and 13 are each made up of four rows.
Non transactional data is said to be in a single-record case format because a single record (row) constitutes a case. In Oracle Machine Learning, association models can be built using either transactional or non transactional or two-dimensional data formats. If the data is non transactional, it is possible to transform to a nested column to make it transactional before association machine learning activities can be performed. Transactional format is the usual format but, the association rules model does accept two-dimensional input format. For non transactional input format, each distinct combination of the content in all columns other than the case ID column is treated as a unique item.