Input
The InputTable has sales transaction data of an office supply chain store by different geographic regions and customer segments. The column product specifies the items that are purchased by a customer in a given transaction (column orderid).
| orderid | orderdate | orderqty | region | customer_segment | prd_category | product |
|---|---|---|---|---|---|---|
| 3 | 2010-10-13 00:00:00 | 6 | Nunavut | Small Business | Office Supplies | Storage & Organization |
| 293 | 2012-10-01 00:00:00 | 49 | Nunavut | Consumer | Office Supplies | Appliances |
| 293 | 2012-10-01 00:00:00 | 27 | Nunavut | Consumer | Office Supplies | Binders and Binder Accessories |
| 483 | 2011-07-10 00:00:00 | 30 | Nunavut | Corporate | Technology | Telephones and Communication |
| 515 | 2010-08-28 00:00:00 | 19 | Nunavut | Consumer | Office Supplies | Appliances |
| 515 | 2010-08-28 00:00:00 | 21 | Nunavut | Consumer | Furniture | Office Furnishings |
| 613 | 2011-06-17 00:00:00 | 12 | Nunavut | Corporate | Office Supplies | Binders and Binder Accessories |
| 613 | 2011-06-17 00:00:00 | 22 | Nunavut | Corporate | Office Supplies | Storage & Organization |
| 643 | 2011-03-24 00:00:00 | 21 | Nunavut | Corporate | Office Supplies | Storage & Organization |
| 678 | 2010-02-26 00:00:00 | 44 | Nunavut | Home Office | Office Supplies | Paper |
| 807 | 2010-11-23 00:00:00 | 45 | Nunavut | Home Office | Office Supplies | Paper |
| 807 | 2010-11-23 00:00:00 | 32 | Nunavut | Home Office | Office Supplies | Rubber Bands |
| 868 | 2012-06-08 00:00:00 | 32 | Nunavut | Home Office | Office Supplies | Appliances |
| ... | ... | ... | ... | ... | ... | ... |
SQL Call
SELECT * FROM FPGrowth (
ON sales_transaction AS InputTable
OUT TABLE OutputRuleTable (fpgrowth_out_rule)
OUT TABLE OutputPatternTable (fpgrowth_out_pattern)
USING
TargetColumns ('product')
TransactionIDColumns ('orderid')
PartitionColumns ('region')
MinSupport (0.01)
MinConfidence (0.0)
MaxPatternLength (4)
ConsequenceCountRange ('1-1')
PatternsOrRules ('both')
) AS dt;
Output
| output_information |
|---|
Patterns are kept in pattern table specified in the argument OutputPatternTable Rules are kept in rule table specified in the argument OutputRuleTable |
This query returns the following table:
SELECT * FROM fpgrowth_out_pattern ORDER BY region, pattern_product;
| region | pattern_product | length_of_pattern | count | support |
|---|---|---|---|---|
| Atlantic | Appliances, Chairs & Chairmats | 2 | 1 | 0.0555555555555556 |
| Atlantic | Chairs & Chairmats, Computer Peripherals | 2 | 1 | 0.0555555555555556 |
| Atlantic | Labels, Pens & Art Supplies | 2 | 1 | 0.0555555555555556 |
| Atlantic | Office Furnishings, Binder s and Binder Accessories | 2 | 1 | 0.0555555555555556 |
| Atlantic | Office Furnishings, Computer Peripherals | 2 | 1 | 0.0555555555555556 |
| Atlantic | Office Furnishings, Paper | 2 | 1 | 0.0555555555555556 |
| Atlantic | Office Furnishings, Paper, Storage & Organization | 3 | 1 | 0.0555555555555556 |
| Atlantic | Office Furnishings, Storage & Organization | 2 | 1 | 0.0555555555555556 |
| Atlantic | Paper, Labels | 2 | 1 | 0.0555555555555556 |
| Atlantic | Paper, Storage & Organization | 2 | 1 | 0.0555555555555556 |
| Northwest Territories | Binder s and Binder Accessories, Computer Peripherals | 2 | 3 | 0.0111524163568773 |
| Northwest Territories | Binder s and Binder Accessories, Office Furnishings | 2 | 4 | 0.0148698884758364 |
| Northwest Territories | Binder s and Binder Accessories, Office Machines | 2 | 3 | 0.0111524163568773 |
| Northwest Territories | Binder s and Binder Accessories, Rubber Bands | 2 | 3 | 0.0111524163568773 |
| Northwest Territories | Binder s and Binder Accessories, Telephones and Communication | 2 | 4 | 0.0148698884758364 |
| Northwest Territories | Computer Peripherals, Chairs & Chairmats | 2 | 3 | 0.0111524163568773 |
| ... | ... | ... | ... | ... |
This query returns the following table:
SELECT * FROM fpgrowth_out_rule ORDER BY region, score;
| region | antecedent_product | consequence_product | count_of_antecedent | count_of_consequence | cntb | cnt_antecedent | cnt_consequence | score | support | confidence | lift | conviction | leverage | coverage | chi_square | z_score |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Atlantic | Paper | Office Furnishings | 1 | 1 | 1 | 4 | 5 | 0.05 | 0.0555555555555556 | 0.25 | 0.9 | 0.962962962962963 | -0.00617283950617285 | 0.222222222222222 | 0.0197802197802198 | |
| Atlantic | Office Furnishings | Paper | 1 | 1 | 1 | 5 | 4 | 0.05 | 0.0555555555555556 | 0.2 | 0.9 | 0.972222222222222 | -0.00617283950617285 | 0.277777777777778 | 0.0197802197802198 | |
| Atlantic | Binders and Binder Accessories | Office Furnishings | 1 | 1 | 1 | 3 | 5 | 0.0666666666666667 | 0.0555555555555556 | 0.333333333333333 | 1.2 | 1.08333333333333 | 0.00925925925925925 | 0.166666666666667 | 0.0553846153846154 | |
| Atlantic | Office Furnishings | Binders and Binder Accessories | 1 | 1 | 1 | 5 | 3 | 0.0666666666666667 | 0.0555555555555556 | 0.2 | 1.2 | 1.04166666666667 | 0.00925925925925925 | 0.277777777777778 | 0.0553846153846154 | |
| Atlantic | Labels | Paper | 1 | 1 | 1 | 3 | 4 | 0.0833333333333333 | 0.0555555555555556 | 0.333333333333333 | 1.5 | 1.16666666666667 | 0.0185185185185185 | 0.166666666666667 | 0.257142857142857 |
The output tables contain only those rows that conform to MinSupport of 0.01. The rest of the rows that violate the condition are deleted.