Feature extraction compresses many attributes into a few new ones, called features, that capture most of what makes rows different. Oracle Machine Learning builds such a model with algorithms like singular value decomposition, and FEATURE_ID, FEATURE_VALUE, and FEATURE_SET score rows: which feature describes a row best, and how strongly each feature applies.
Code for This Guide
The main examples are in the examples/machine-learning folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
feature_id(model using *) -- the strongest feature feature_value(model [, feature_id] using *) -- a feature's value for the row table(feature_set(model [, topn] using *)) -- all features with their values
The example summarizes each customer's bookings:
Setup:
create or replace view ml_customers as
select b.customer_id, count(*) as bookings, round(sum(b.total_amount)) as spent,
max(nvl(json_value(c.loyalty, '$.points' returning number), 0)) as points
from bookings b join customers c on c.customer_id = b.customer_id
group by b.customer_id;Two Features from Three Attributes
Example:
declare
v_settings dbms_data_mining.setting_list;
begin
v_settings('ALGO_NAME') := 'ALGO_SINGULAR_VALUE_DECOMP';
v_settings('FEAT_NUM_FEATURES') := '2';
v_settings('PREP_AUTO') := 'ON';
dbms_data_mining.create_model2('CUSTOMER_FEATURES', 'FEATURE_EXTRACTION',
'select * from ml_customers', v_settings, 'CUSTOMER_ID');
end;
/
select customer_id, bookings, spent, points,
feature_id(customer_features using *) as top_feature,
round(feature_value(customer_features, 1 using *), 3) as feature_1,
round(feature_value(customer_features, 2 using *), 3) as feature_2
from ml_customers
where customer_id in (1, 2, 50, 90);
select s.feature_id, round(s.value, 3) as value
from ml_customers m, table(feature_set(customer_features using *)) s
where m.customer_id = 1;Output:
PL/SQL procedure successfully completed.
CUSTOMER_ID BOOKINGS SPENT POINTS TOP_FEATURE FEATURE_1 FEATURE_2
______________ ___________ ________ _________ ______________ ____________ ____________
1 12 12025 3296 2 0.005 0.125
2 7 8316 7823 2 0.01 0.08
50 3 1161 7045 1 0.008 0.004
90 6 7321 0 2 0.001 0.079
FEATURE_ID VALUE
_____________ ________
2 0.125
1 0.005- The SVD model reduces bookings, spending, and points to two features.
- Customers 1, 2, and 90, frequent and high-spending travellers, are best described by feature 2; customer 50, with few bookings but many points, by feature 1.
- FEATURE_SET lists both features for customer 1, strongest first.
Things to Know
- Features are combinations of the original attributes; look at the model's details views to see what each one weighs.
- Use features as inputs to other models, or to group and compare rows with fewer columns.
- Drop the model with DBMS_DATA_MINING.DROP_MODEL('CUSTOMER_FEATURES'), then the view, when done.
Related Guides
- How to Explain Predictions with PREDICTION_DETAILS in Oracle
- How to Use Machine Learning on Embeddings in Oracle Database
Conclusion
Feature extraction models reduce many attributes to a few features, and FEATURE_ID, FEATURE_VALUE, and FEATURE_SET score each row against them. Use them to summarize rows compactly and to feed simpler inputs to other models.
