Audio Embedding Similarity

Objective

Use vector embeddings to find similar audio files. By generating embeddings from audio descriptions or transcripts, you can discover related content and build recommendation systems.

Step 1: Create Audio Embeddings Table

Create a table for audio with embeddings.

CREATE TABLE audio_embeddings (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    audio_file AUDIO(MP3),
    description TEXT,
    content_type VARCHAR(50),
    transcript TEXT,
    embedding VECTOR(384),
    duration_seconds INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Insert Sample Audio Content

Add sample audio with descriptions.

INSERT INTO audio_embeddings (id, title, description, content_type, duration_seconds) VALUES
    (1, 'Introduction to Python Programming', 'Learn the basics of Python programming language including variables, data types, and control structures', 'tutorial', 1800),
    (2, 'JavaScript Fundamentals', 'Essential JavaScript concepts for web development including DOM manipulation and events', 'tutorial', 2100),
    (3, 'Machine Learning Overview', 'Introduction to machine learning concepts, algorithms, and practical applications', 'lecture', 2700),
    (4, 'Deep Learning with Neural Networks', 'Advanced neural network architectures and deep learning techniques', 'lecture', 3000),
    (5, 'Data Analysis with Pandas', 'Using Python Pandas library for data manipulation and analysis', 'tutorial', 1500),
    (6, 'Web Development Best Practices', 'Modern web development patterns and coding standards', 'tutorial', 1200),
    (7, 'Cloud Computing Fundamentals', 'Introduction to cloud services, AWS, and deployment strategies', 'lecture', 2400),
    (8, 'Database Design Principles', 'Relational database design, normalization, and query optimization', 'tutorial', 1950),
    (9, 'Cybersecurity Essentials', 'Security best practices, threat prevention, and secure coding', 'lecture', 2200),
    (10, 'AI Ethics and Responsibility', 'Ethical considerations in artificial intelligence development', 'discussion', 1800);

Step 3: Generate Embeddings

Create embeddings from audio descriptions.

UPDATE audio_embeddings
SET embedding = EMBED(description)
WHERE embedding IS NULL;

Step 4: Find Similar Audio by Query

Search for audio similar to a text query.

SELECT
    title,
    content_type,
    description,
    COSINE_SIMILARITY(embedding, EMBED('programming and coding tutorials')) as similarity
FROM audio_embeddings
WHERE embedding IS NOT NULL
ORDER BY similarity DESC
LIMIT 5;

Step 5: Find Similar to Existing Audio

Find audio similar to a specific item.

SELECT
    b.title,
    b.content_type,
    COSINE_SIMILARITY(a.embedding, b.embedding) as similarity
FROM audio_embeddings a
CROSS JOIN audio_embeddings b
WHERE a.id = 3
  AND b.id != 3
  AND a.embedding IS NOT NULL
  AND b.embedding IS NOT NULL
ORDER BY similarity DESC
LIMIT 5;

Step 6: Create Similarity Pairs

Pre-compute similarity scores.

CREATE TABLE audio_similarity (
    id INTEGER PRIMARY KEY,
    audio_id_1 INTEGER NOT NULL,
    audio_id_2 INTEGER NOT NULL,
    similarity_score DECIMAL(6, 5),
    computed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO audio_similarity (id, audio_id_1, audio_id_2, similarity_score)
SELECT
    ROW_NUMBER() OVER (),
    a.id,
    b.id,
    COSINE_SIMILARITY(a.embedding, b.embedding)
FROM audio_embeddings a
CROSS JOIN audio_embeddings b
WHERE a.id < b.id
  AND a.embedding IS NOT NULL
  AND b.embedding IS NOT NULL;

Step 7: Find Related Content Clusters

Identify highly related content pairs.

SELECT
    ae1.title as audio_1,
    ae2.title as audio_2,
    asim.similarity_score
FROM audio_similarity asim
INNER JOIN audio_embeddings ae1 ON asim.audio_id_1 = ae1.id
INNER JOIN audio_embeddings ae2 ON asim.audio_id_2 = ae2.id
WHERE asim.similarity_score > 0.7
ORDER BY asim.similarity_score DESC;

Step 8: Content Type Similarity

Find similar audio within the same content type.

SELECT
    a.title as source,
    b.title as similar,
    a.content_type,
    COSINE_SIMILARITY(a.embedding, b.embedding) as similarity
FROM audio_embeddings a
INNER JOIN audio_embeddings b ON a.content_type = b.content_type AND a.id < b.id
WHERE a.embedding IS NOT NULL
  AND b.embedding IS NOT NULL
ORDER BY a.content_type, similarity DESC;

Step 9: Topic-Based Search

Find content matching specific topics.

SELECT
    title,
    content_type,
    duration_seconds / 60 as duration_minutes,
    COSINE_SIMILARITY(embedding, EMBED('artificial intelligence machine learning')) as relevance
FROM audio_embeddings
WHERE embedding IS NOT NULL
  AND COSINE_SIMILARITY(embedding, EMBED('artificial intelligence machine learning')) > 0.5
ORDER BY relevance DESC;

Step 10: Recommendation Query

Build recommendations for a listener.

SELECT
    'Based on: ' || a.title as recommendation_source,
    b.title as recommended,
    b.content_type,
    COSINE_SIMILARITY(a.embedding, b.embedding) as relevance
FROM audio_embeddings a
CROSS JOIN audio_embeddings b
WHERE a.id = 1
  AND b.id != a.id
  AND a.embedding IS NOT NULL
  AND b.embedding IS NOT NULL
ORDER BY relevance DESC
LIMIT 3;

Step 11: Similarity Distribution

Analyze similarity score distribution.

SELECT
    CASE
        WHEN similarity_score >= 0.8 THEN '0.8-1.0 (Very Similar)'
        WHEN similarity_score >= 0.6 THEN '0.6-0.8 (Similar)'
        WHEN similarity_score >= 0.4 THEN '0.4-0.6 (Related)'
        ELSE '< 0.4 (Different)'
    END as similarity_range,
    COUNT(*) as pair_count
FROM audio_similarity
GROUP BY
    CASE
        WHEN similarity_score >= 0.8 THEN '0.8-1.0 (Very Similar)'
        WHEN similarity_score >= 0.6 THEN '0.6-0.8 (Similar)'
        WHEN similarity_score >= 0.4 THEN '0.4-0.6 (Related)'
        ELSE '< 0.4 (Different)'
    END
ORDER BY similarity_range DESC;

Step 12: Content Gap Analysis

Find underrepresented topics.

SELECT
    content_type,
    COUNT(*) as audio_count,
    AVG(
        COSINE_SIMILARITY(embedding, EMBED('security and protection'))
    ) as security_relevance,
    AVG(
        COSINE_SIMILARITY(embedding, EMBED('data and analytics'))
    ) as data_relevance
FROM audio_embeddings
WHERE embedding IS NOT NULL
GROUP BY content_type;

Cleanup (Optional)

DROP TABLE IF EXISTS audio_similarity;
DROP TABLE IF EXISTS audio_embeddings;

Expected Outcomes

• Embeddings generated from descriptions
• Similarity search finds related audio
• Pre-computed pairs enable fast lookup
• Topic-based filtering works
• Recommendations generated from content

Similarity Score Guide

Key Concepts Learned

• Audio content embeddings
• COSINE_SIMILARITY for search
• Pre-computed similarity pairs
• Topic-based filtering
• Recommendation systems