anofox_similarity

Downloads

294

GitHub-Sterne

Erweiterungs-Repository auf GitHub

Multimodale Produktsähnlichkeitssuche für die Lieferkettenplanung in der Fertigung. Finden Sie ähnliche Materialien anhand von Stücklisten-Überlappung (Jaccard), graphstruktureller Ähnlichkeit (Weisfeiler-Lehman-Kernel), Text-Embeddings und transaktionalen Mustern, inklusive Vorgängererkennung und Cold-Start-Analogabgleich für die Nachfrageprognose.

Maintainer: jrosskopf

Installation und Laden

INSTALL anofox_similarity FROM community;
LOAD anofox_similarity;

Beispiel

-- Components (BOM) for each product
CREATE TABLE bom_items (parent_id VARCHAR, child_id VARCHAR);
INSERT INTO bom_items VALUES
  ('PUMP-A', 'SEAL-001'), ('PUMP-A', 'BEARING-X'), ('PUMP-A', 'MOTOR-M1'),
  ('PUMP-B', 'SEAL-001'), ('PUMP-B', 'BEARING-X'), ('PUMP-B', 'MOTOR-M1'), ('PUMP-B', 'GASKET-Y'),
  ('PUMP-C', 'FILTER-F1'), ('PUMP-C', 'VALVE-V2');

-- Find the 3 materials most similar to PUMP-A by shared components
SELECT material_id, similarity, shared_components, total_components
FROM find_similar_materials_jaccard('PUMP-A', 3, bom_table := 'bom_items')
ORDER BY similarity DESC;
-- PUMP-B   0.75  3  4   (high overlap)
-- PUMP-C   0.0   0  5   (disjoint)

-- Structural similarity that also weighs BOM topology, not just component sets
SELECT wl_kernel_similarity('PUMP-A', 'PUMP-B', iterations := 3, bom_table := 'bom_items');

Über anofox_similarity

anofox_similarity hilft Herstellern, die Frage „Welchem bestehenden Produkt ähnelt dieses neue am meisten?“ direkt gegen ERP-Stammdaten (SAP, Microsoft Dynamics 365 oder eine einfache Tabelle bom_items) zu beantworten. Das ist die zentrale Eingabe für Cold-Start-Nachfrageprognosen, Vorgängererkennung und analogbasierte Planung bei Produkten ohne bisherige Verkaufshistorie.

Ähnlichkeitssignale setzen sich aus mehreren Tabellenfunktionen zusammen:

find_similar_materials_jaccard rangiert Materialien nach Überlappung der Stücklistenkomponenten.
wl_kernel_similarity / find_similar_materials_wl_kernel nutzen einen Weisfeiler-Lehman-Graph-Kernel, um die Stücklistentopologie zu erfassen: nicht nur, welche Komponenten gemeinsam sind, sondern wie sie strukturell angeordnet sind.
infer_predecessors erkennt Vorgänger-/Nachfolgerbeziehungen aus struktureller Ähnlichkeit plus zeitlicher Antikorrelation in der Warenbewegungs- historie (die Nutzung eines alten Teils sinkt, während die eines neuen steigt).
cold_start_analogs findet etablierte Produkte, um eine Prognose für ein brandneues Material ohne jede Historie zu initialisieren.
compute_fused_embeddings kombiniert strukturelle, transaktionale und Text- Embedding-Signale zu einem fusionierbaren Vektor, der sich mit der DuckDB-Erweiterung vss über die mitgelieferten HNSW-Index-Hilfen indizieren lässt.

Transformationsmakros für SAP (MARA/MAKT/MAST/STKO/STPO) und Dynamics 365 (sap_to_bom_items, sap_to_materials_with_desc, …) normalisieren gängige ERP-Exporte in das Stücklisten-/Materialschema, das der Rest der Erweiterung erwartet. Die Integration besteht daher meist aus SELECT * FROM sap_to_bom_items(...).

Alle Tabellenfunktionen akzeptieren eine vollständig qualifizierte, schreibgeschützte DuckDB-Datenbank und arbeiten eingeschränkt weiter, wenn optionale Begleiterweiterungen (anofox_forecast für Zeitreihenmerkmale, duckpgq für Property-Graph-Stücklistenabfragen) nicht geladen sind.

Die Erweiterung erfasst anonyme Nutzungstelemetrie (nur ein Hüllen-Ereignis extension_loaded; niemals Tabellennamen, Schlüssel oder SQL). Deaktivieren mit SET anofox_telemetry_enabled = false oder DATAZOO_DISABLE_TELEMETRY=1; siehe TELEMETRY.md im Repository.

Hinzugefügte Funktionen

function_name	function_type	description	comment	examples
aggregate_material_components	table_macro	NULL	NULL
bom_common_components	table_macro	NULL	NULL
bom_dfs_neighborhood	table_macro	NULL	NULL
bom_explosion_1level	table_macro	NULL	NULL
bom_explosion_multilevel	table_macro	NULL	NULL
bom_to_items	table_macro	NULL	NULL
bom_where_used	table_macro	NULL	NULL
check_anofox_forecast_available	macro	NULL	NULL
check_duckpgq_available	macro	NULL	NULL
check_statistics_freshness	table	Returns a single-row summary of the transactional_embedding_statistics table including stat_count, max_samples, current_version, last_updated, and is_fresh. is_fresh is TRUE when >= 30 statistics rows exist and last_updated is within 7 days.	NULL	[SELECT * FROM check_statistics_freshness();]
cold_start_analogs	table	Finds structural analogs for a cold-start material (one with little or no demand history). Returns established materials that are structurally similar and have at least min_history_months of goods movement data, enabling surrogate forecasting.	NULL	[SELECT * FROM cold_start_analogs(‘NEW-PART’, 5);, SELECT * FROM cold_start_analogs(‘NEW-PART’, 10, min_history_months := 12, min_similarity := 0.4);]
compute_domain_specific_statistics	table_macro	NULL	NULL
compute_fused_embeddings	table_macro	NULL	NULL
compute_jaccard_embeddings	table	Computes and RETURNS 128-dimensional MinHash embeddings (one row per material/seed) from BOM component sets; it does not write to material_embeddings — aggregate and upsert the result yourself. Each material’s embedding is derived from its set of direct child components. Requires bom_table with columns parent_id and child_id.	NULL	[SELECT * FROM compute_jaccard_embeddings();, SELECT * FROM compute_jaccard_embeddings(bom_table := ‘sap_stpo’);]
compute_textual_embeddings	table_macro	NULL	NULL
compute_transactional_embeddings	table	Computes 128-dimensional transactional embeddings from goods movement data and upserts them into material_embeddings. Features include demand velocity, seasonality, frequency, and quantity statistics normalized against transactional_embedding_statistics. Supports batched computation via batch_size and batch_offset.	NULL	[SELECT * FROM compute_transactional_embeddings();, SELECT * FROM compute_transactional_embeddings(movements_table := ‘sap_mseg’, time_window_days := 365);, SELECT * FROM compute_transactional_embeddings(batch_size := 1000, batch_offset := 0);]
create_bom_property_graph	table_macro	NULL	NULL
dynamics365_to_bom_component	table_macro	NULL	NULL
dynamics365_to_bom_header	table_macro	NULL	NULL
dynamics365_to_materials	table_macro	NULL	NULL
embed_text	macro	NULL	NULL
embedding_backend	scalar	Generates a 384-dimensional text embedding (FLOAT[384]) for the given text. provider selects the backend and provider_config is a JSON string of provider-specific options (api_key, endpoint). NOTE: unless the extension is built with a local OpenVINO model or a real API backend is configured, all providers return a deterministic, hash-based PLACEHOLDER embedding (useful for wiring and tests, not semantic search).	NULL	[SELECT embedding_backend(‘diesel pump’, ‘gemma-local’, ‘{}’);, SELECT embedding_backend(‘valve seat’, ‘gemini-api’, ‘{“api_key”: “…”}’);]
extract_material_descriptions	table_macro	NULL	NULL
extract_ts_features	table_macro	NULL	NULL
filter_recent_movements	table_macro	NULL	NULL
find_similar_materials_jaccard	table	Finds the k most structurally similar materials to a query material using exact Jaccard similarity on BOM component sets. Returns material_id, similarity, shared_components, and total_components. Filtered by min_similarity threshold.	NULL	[SELECT * FROM find_similar_materials_jaccard(‘MAT-001’, 10);, SELECT * FROM find_similar_materials_jaccard(‘MAT-001’, 20, min_similarity := 0.3);]
find_similar_materials_wl_kernel	table	Finds the k most structurally similar materials using the Weisfeiler-Lehman graph kernel, which captures multi-hop BOM structure beyond direct component overlap. iterations controls the number of WL refinement rounds (default 3).	NULL	[SELECT * FROM find_similar_materials_wl_kernel(‘MAT-001’, 10);, SELECT * FROM find_similar_materials_wl_kernel(‘MAT-001’, 10, iterations := 3, min_similarity := 0.2);]
fuse_embeddings	scalar	Fuses structural (FLOAT[256]), textual (FLOAT[384]), and transactional (FLOAT[128]) embeddings into a single 768-dimensional combined embedding using weighted concatenation. Each component is scaled by sqrt(weight). Weights do not need to sum to 1.0.	NULL	[SELECT fuse_embeddings(se, te, xe, {‘structural’:0.5,‘textual’:0.5,‘transactional’:0.0}) FROM material_embeddings;]
hnsw_compact_index	pragma	NULL	NULL
hnsw_index_scan	table	NULL	NULL
infer_predecessors	table	Identifies predecessor (superseded) materials for a given successor by combining BOM structural similarity with anti-correlated consumption patterns. Returns predecessor candidates ranked by confidence score along with correlation, similarity, and temporal overlap data. Useful for lifecycle transition planning and inventory run-down.	NULL	[SELECT * FROM infer_predecessors(‘NEW-PART’);, SELECT * FROM infer_predecessors(‘NEW-PART’, lookback_months := 24, min_similarity := 0.5, min_confidence := 0.6, lag_weeks := 4);]
items_to_bom	table_macro	NULL	NULL
jaccard_similarity	scalar	Computes the Jaccard similarity coefficient between two lists treated as sets. Returns a value in [0.0, 1.0] equal to \|intersection\| / \|union\|. Duplicates within each list are deduplicated before comparison. Returns NULL if either argument is NULL; returns 0.0 if both lists are empty.	NULL	[SELECT jaccard_similarity([‘A’,‘B’,‘C’], [‘B’,‘C’,‘D’]); – 0.5, SELECT jaccard_similarity([1,2,3,4], [3,4,5,6]); – 0.3333]
pragma_hnsw_index_info	table	NULL	NULL
recompute_embedding_statistics	table_macro	NULL	NULL
sap_to_bom_items	table_macro	NULL	NULL
sap_to_materials	table_macro	NULL	NULL
sap_to_materials_with_desc	table_macro	NULL	NULL
vss_join	table_macro	NULL	NULL
vss_match	table_macro	NULL	NULL
wl_fingerprint	table_macro	NULL	NULL
wl_kernel_similarity	macro	NULL	NULL

Überladene Funktionen

Diese Erweiterung fügt keine Funktionsüberladungen hinzu.

Hinzugefügte Typen

Diese Erweiterung fügt keine Typen hinzu.

Hinzugefügte Einstellungen

name	description	input_type	scope	aliases
anofox_telemetry_enabled	Enable or disable anonymous usage telemetry	BOOLEAN	GLOBAL	[]
anofox_telemetry_key	PostHog API key for telemetry	VARCHAR	GLOBAL	[]
datazoo_banner	Show the DataZoo feedback banner when an extension is loaded in an interactive terminal (at most once a day per extension).	BOOLEAN	GLOBAL	[]
hnsw_ef_search	experimental: override the ef_search parameter when scanning HNSW indexes	BIGINT	GLOBAL	[]
hnsw_enable_experimental_persistence	experimental: enable creating HNSW indexes in persistent databases	BOOLEAN	GLOBAL	[]