CCAM Database - Comprehensive Query Examples

This guide provides practical SQL queries for exploring and analyzing the CCAM database.

Table of Contents

1. Basic Queries
2. Procedure Search
3. Hierarchical Navigation
4. Pricing & Economics
5. Business Rules
6. Documentation & Glossary
7. Data Analysis
8. Advanced Queries

---

Basic Queries

Count records in all tables

-- Get row counts for all tables
SELECT
    schemaname,
    tablename,
    n_tup_ins - n_tup_del AS row_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
  AND tablename LIKE 'r_%'
ORDER BY n_tup_ins - n_tup_del DESC;

View database size

-- Overall database size
SELECT pg_size_pretty(pg_database_size('ccam')) AS database_size;

-- Size by table
SELECT
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS data_size,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname = 'public' AND tablename LIKE 'r_%'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

Inspect table structure

-- Get column information for a table
SELECT
    column_name,
    data_type,
    character_maximum_length,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'r_acte'
ORDER BY ordinal_position;

---

Procedure Search

Simple text search

-- Search by procedure code
SELECT cod_acte, nom_court, nom_long, dt_modif, dt_fin
FROM r_acte
WHERE cod_acte = 'GDFE005';

-- Search by name (case-insensitive)
SELECT cod_acte, nom_court, dt_fin
FROM r_acte
WHERE nom_court ILIKE '%échographie%'
  AND dt_fin IS NULL
LIMIT 20;

-- Search in long description
SELECT cod_acte, nom_court, nom_long
FROM r_acte
WHERE nom_long ILIKE '%cardiovasculaire%'
  AND dt_fin IS NULL
LIMIT 10;

Full-text search

-- French full-text search (with unaccent)
SELECT
    cod_acte,
    nom_court,
    ts_rank(nom_long_search, query) AS rank
FROM r_acte,
     to_tsquery('french_unaccent', 'chirurgie & cardiaque') AS query
WHERE nom_long_search @@ query
  AND dt_fin IS NULL
ORDER BY rank DESC
LIMIT 20;

-- Phrase search
SELECT cod_acte, nom_court
FROM r_acte
WHERE nom_long_search @@ phraseto_tsquery('french_unaccent', 'artère coronaire')
  AND dt_fin IS NULL
LIMIT 10;

Fuzzy search (trigrams)

-- Similar procedure names
SELECT
    cod_acte,
    nom_court,
    similarity(nom_court, 'ecographie') AS sim
FROM r_acte
WHERE nom_court % 'ecographie'  -- % operator uses trigram similarity
ORDER BY sim DESC
LIMIT 15;

-- Find typos or variations
SELECT cod_acte, nom_court
FROM r_acte
WHERE nom_court ILIKE '%cardio%'
ORDER BY similarity(nom_court, 'cardiovasculaire') DESC
LIMIT 10;

Active vs obsolete procedures

-- Only active procedures
SELECT COUNT(*) AS active_count
FROM r_acte
WHERE dt_fin IS NULL;

-- Obsolete procedures
SELECT cod_acte, nom_court, dt_modif, dt_fin
FROM r_acte
WHERE dt_fin IS NOT NULL
ORDER BY dt_fin DESC
LIMIT 20;

-- Procedures that became obsolete in 2023
SELECT cod_acte, nom_court, dt_fin
FROM r_acte
WHERE EXTRACT(YEAR FROM dt_fin) = 2023
ORDER BY dt_fin;

---

Hierarchical Navigation

Menu tree navigation

-- Get root menu nodes
SELECT cod_menu, libelle, cod_pere
FROM r_menu
WHERE cod_pere IS NULL
ORDER BY cod_menu;

-- Get children of a specific menu
SELECT cod_menu, libelle
FROM r_menu
WHERE cod_pere = 1
ORDER BY cod_menu;

-- Recursive menu tree (full hierarchy)
WITH RECURSIVE menu_tree AS (
    -- Base case: root nodes
    SELECT
        cod_menu,
        cod_pere,
        libelle,
        0 AS level,
        ARRAY[cod_menu] AS path,
        cod_menu::TEXT AS path_string
    FROM r_menu
    WHERE cod_pere IS NULL

    UNION ALL

    -- Recursive case: children
    SELECT
        m.cod_menu,
        m.cod_pere,
        m.libelle,
        mt.level + 1,
        mt.path || m.cod_menu,
        mt.path_string || ' > ' || m.cod_menu
    FROM r_menu m
    JOIN menu_tree mt ON m.cod_pere = mt.cod_menu
    WHERE mt.level < 10  -- Prevent infinite loops
)
SELECT
    REPEAT('  ', level) || libelle AS menu_hierarchy,
    level,
    cod_menu,
    path_string
FROM menu_tree
ORDER BY path
LIMIT 100;

Procedures by menu

-- Count procedures per menu
SELECT
    m.cod_menu,
    m.libelle AS menu_name,
    COUNT(a.cod_acte) AS procedure_count
FROM r_menu m
LEFT JOIN r_acte a ON m.cod_menu = a.menu_cod AND a.dt_fin IS NULL
GROUP BY m.cod_menu, m.libelle
ORDER BY procedure_count DESC
LIMIT 30;

-- List procedures in a specific menu
SELECT
    a.cod_acte,
    a.nom_court,
    m.libelle AS menu
FROM r_acte a
JOIN r_menu m ON a.menu_cod = m.cod_menu
WHERE m.cod_menu = 2
  AND a.dt_fin IS NULL
ORDER BY a.nom_court
LIMIT 50;

Domain classification

-- Procedures by domain
SELECT
    d.libelle AS domain,
    COUNT(a.cod_acte) AS procedure_count
FROM r_domaine d
LEFT JOIN r_acte a ON d.cod_domai = a.domaine_cod AND a.dt_fin IS NULL
GROUP BY d.libelle
ORDER BY procedure_count DESC;

-- Procedures in a specific domain
SELECT
    a.cod_acte,
    a.nom_court,
    d.libelle AS domain
FROM r_acte a
JOIN r_domaine d ON a.domaine_cod = d.cod_domai
WHERE d.libelle ILIKE '%radiologie%'
  AND a.dt_fin IS NULL
LIMIT 30;

---

Pricing & Economics

Basic pricing lookup

-- Get pricing for a specific procedure code
SELECT
    p.cod_aap,
    p.aa_cod,
    p.coefficien,
    p.pu_base,
    p.supplement,
    (p.coefficien * COALESCE(p.pu_base, 0) + COALESCE(p.supplement, 0)) AS total_price
FROM r_acte_ivite_phase p
WHERE p.cod_aap LIKE 'GDFE005%'
ORDER BY p.cod_aap;

Multi-grid pricing

-- Get pricing across all grids for a procedure
SELECT
    pb.grille_cod,
    pb.code_pu,
    pb.montant_pu,
    pb.dt_debut,
    pb.dt_fin
FROM r_pu_base pb
WHERE pb.code_pu = 'ADC'
  AND pb.dt_fin IS NULL
ORDER BY pb.grille_cod;

-- Current pricing with grid information
SELECT
    ap.cod_aap,
    ap.coefficien,
    pb.grille_cod,
    pb.montant_pu,
    (ap.coefficien * pb.montant_pu) AS calculated_price
FROM r_acte_ivite_phase ap
JOIN r_pu_base pb ON ap.code_pu = pb.code_pu
WHERE ap.cod_aap = 'GDFE005100'
  AND pb.dt_fin IS NULL
ORDER BY pb.grille_cod;

Price evolution over time

-- Historical pricing for a code
SELECT
    code_pu,
    grille_cod,
    montant_pu,
    dt_debut,
    dt_fin
FROM r_pu_base
WHERE code_pu = 'ADC'
ORDER BY grille_cod, dt_debut DESC;

-- Price changes in a date range
SELECT
    code_pu,
    grille_cod,
    montant_pu,
    dt_debut
FROM r_pu_base
WHERE dt_debut BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY dt_debut DESC, code_pu
LIMIT 50;

Pricing statistics

-- Average coefficient by activity type
SELECT
    ra.libelle AS activity_type,
    COUNT(*) AS count,
    AVG(ap.coefficien) AS avg_coefficient,
    MIN(ap.coefficien) AS min_coefficient,
    MAX(ap.coefficien) AS max_coefficient
FROM r_acte_ivite_phase ap
JOIN r_acte_ivite ai ON ap.aa_cod = ai.cod_aa
JOIN r_activite ra ON ai.activ_cod = ra.cod_activ
GROUP BY ra.libelle
ORDER BY avg_coefficient DESC;

-- Most expensive procedures (by coefficient)
SELECT
    a.cod_acte,
    a.nom_court,
    ap.coefficien,
    ap.pu_base
FROM r_acte a
JOIN r_acte_ivite ai ON a.cod_acte = ai.acte_cod
JOIN r_acte_ivite_phase ap ON ai.cod_aa = ap.aa_cod
WHERE a.dt_fin IS NULL
ORDER BY ap.coefficien DESC
LIMIT 30;

---

Business Rules

Procedure associations (allowed combinations)

-- Find procedures that can be combined with a specific procedure
SELECT
    a1.cod_acte AS procedure_1,
    a1.nom_court AS name_1,
    a2.cod_acte AS procedure_2,
    a2.nom_court AS name_2,
    tb.libelle AS rule,
    tb.coef AS coefficient_modifier
FROM r_associations assoc
JOIN r_acte_ivite ai1 ON assoc.aa_code1 = ai1.cod_aa
JOIN r_acte a1 ON ai1.acte_cod = a1.cod_acte
JOIN r_acte_ivite ai2 ON assoc.aa_code2 = ai2.cod_aa
JOIN r_acte a2 ON ai2.acte_cod = a2.cod_acte
JOIN r_tb03 tb ON assoc.regle_cod = tb.cod_regle AND assoc.grille_cod = tb.grille_cod
WHERE a1.cod_acte = 'GDFE005'
  AND a1.dt_fin IS NULL
  AND a2.dt_fin IS NULL
LIMIT 50;

-- Count associations per procedure
SELECT
    a.cod_acte,
    a.nom_court,
    COUNT(DISTINCT assoc.aa_code2) AS association_count
FROM r_acte a
JOIN r_acte_ivite ai ON a.cod_acte = ai.acte_cod
JOIN r_associations assoc ON ai.cod_aa = assoc.aa_code1
WHERE a.dt_fin IS NULL
GROUP BY a.cod_acte, a.nom_court
ORDER BY association_count DESC
LIMIT 30;

Incompatibilities (forbidden combinations)

-- Find incompatible procedures for a specific procedure
SELECT
    a1.cod_acte AS procedure_1,
    a1.nom_court AS name_1,
    a2.cod_acte AS procedure_2,
    a2.nom_court AS name_2,
    i.type_inco AS incompatibility_type
FROM r_incompatibilites i
JOIN r_acte_ivite ai1 ON i.aa_code1 = ai1.cod_aa
JOIN r_acte a1 ON ai1.acte_cod = a1.cod_acte
JOIN r_acte_ivite ai2 ON i.aa_code2 = ai2.cod_aa
JOIN r_acte a2 ON ai2.acte_cod = a2.cod_acte
WHERE a1.cod_acte = 'GDFE005'
  AND a1.dt_fin IS NULL
  AND a2.dt_fin IS NULL
LIMIT 50;

-- Procedures with most incompatibilities
SELECT
    a.cod_acte,
    a.nom_court,
    COUNT(*) AS incompatibility_count
FROM r_acte a
JOIN r_acte_ivite ai ON a.cod_acte = ai.acte_cod
JOIN r_incompatibilites i ON ai.cod_aa = i.aa_code1
WHERE a.dt_fin IS NULL
GROUP BY a.cod_acte, a.nom_court
ORDER BY incompatibility_count DESC
LIMIT 30;

Related procedures

-- Find related procedures
SELECT
    p.cod_proc,
    a1.cod_acte AS procedure_1,
    a1.nom_court AS name_1,
    a2.cod_acte AS procedure_2,
    a2.nom_court AS name_2,
    p.dt_effet
FROM r_procedure p
LEFT JOIN r_acte a1 ON p.acte_cod1 = a1.cod_acte
LEFT JOIN r_acte a2 ON p.acte_cod2 = a2.cod_acte
WHERE p.acte_cod1 = 'GDFE005' OR p.acte_cod2 = 'GDFE005'
ORDER BY p.dt_effet DESC
LIMIT 20;

---

Documentation & Glossary

Procedure notes

-- Get all notes for a procedure
SELECT
    n.note_num,
    n.texte_note
FROM r_note_acte n
JOIN r_acte a ON n.acte_cod = a.cod_acte
WHERE a.cod_acte = 'GDFE005'
ORDER BY n.note_num;

-- Count notes per procedure
SELECT
    a.cod_acte,
    a.nom_court,
    COUNT(n.note_num) AS note_count
FROM r_acte a
LEFT JOIN r_note_acte n ON a.cod_acte = n.acte_cod
WHERE a.dt_fin IS NULL
GROUP BY a.cod_acte, a.nom_court
HAVING COUNT(n.note_num) > 0
ORDER BY note_count DESC
LIMIT 30;

Glossary lookup

-- Search glossary
SELECT
    libelle,
    definition
FROM r_glossaire
WHERE libelle ILIKE '%anesthésie%'
ORDER BY libelle;

-- Get definition for specific term
SELECT definition
FROM r_glossaire
WHERE libelle = 'ACR';

-- Full-text search in definitions
SELECT
    libelle,
    LEFT(definition, 100) AS definition_preview
FROM r_glossaire
WHERE definition ILIKE '%chirurgie%'
LIMIT 20;

Menu notes

-- Get notes for a menu section
SELECT
    m.libelle AS menu,
    mn.texte_note
FROM r_note_menu mn
JOIN r_menu m ON mn.menu_cod = m.cod_menu
WHERE m.cod_menu = 1;

---

Data Analysis

Temporal analysis

-- Procedures modified in the last year
SELECT
    cod_acte,
    nom_court,
    dt_modif,
    dt_fin
FROM r_acte
WHERE dt_modif >= CURRENT_DATE - INTERVAL '1 year'
ORDER BY dt_modif DESC
LIMIT 50;

-- Procedures by creation year
SELECT
    EXTRACT(YEAR FROM dt_modif) AS year,
    COUNT(*) AS procedure_count
FROM r_acte
WHERE dt_fin IS NULL
GROUP BY EXTRACT(YEAR FROM dt_modif)
ORDER BY year DESC;

-- Procedures that became obsolete by year
SELECT
    EXTRACT(YEAR FROM dt_fin) AS year,
    COUNT(*) AS obsolete_count
FROM r_acte
WHERE dt_fin IS NOT NULL
GROUP BY EXTRACT(YEAR FROM dt_fin)
ORDER BY year DESC;

Activity type distribution

-- Procedures by activity type
SELECT
    ra.libelle AS activity_type,
    COUNT(DISTINCT ai.acte_cod) AS procedure_count
FROM r_acte_ivite ai
JOIN r_activite ra ON ai.activ_cod = ra.cod_activ
JOIN r_acte a ON ai.acte_cod = a.cod_acte
WHERE a.dt_fin IS NULL
GROUP BY ra.libelle
ORDER BY procedure_count DESC;

-- Activities per procedure (multi-activity procedures)
SELECT
    a.cod_acte,
    a.nom_court,
    COUNT(ai.activ_cod) AS activity_count,
    STRING_AGG(ra.libelle, ', ' ORDER BY ra.libelle) AS activities
FROM r_acte a
JOIN r_acte_ivite ai ON a.cod_acte = ai.acte_cod
JOIN r_activite ra ON ai.activ_cod = ra.cod_activ
WHERE a.dt_fin IS NULL
GROUP BY a.cod_acte, a.nom_court
HAVING COUNT(ai.activ_cod) > 1
ORDER BY activity_count DESC
LIMIT 30;

Extension codes

-- Procedures with extensions
SELECT
    a.cod_acte,
    a.nom_court,
    e.cod_exten,
    re.libelle AS extension_type
FROM r_acte_extension e
JOIN r_acte a ON e.acte_cod = a.cod_acte
JOIN r_extension re ON e.cod_exten = re.cod_exten
WHERE a.dt_fin IS NULL
ORDER BY a.cod_acte, e.cod_exten
LIMIT 50;

-- Count extensions per procedure
SELECT
    a.cod_acte,
    a.nom_court,
    COUNT(e.cod_exten) AS extension_count
FROM r_acte a
LEFT JOIN r_acte_extension e ON a.cod_acte = e.acte_cod
WHERE a.dt_fin IS NULL
GROUP BY a.cod_acte, a.nom_court
HAVING COUNT(e.cod_exten) > 0
ORDER BY extension_count DESC
LIMIT 30;

---

Advanced Queries

Complete procedure information

-- Get comprehensive information for a procedure
SELECT
    a.cod_acte,
    a.nom_court,
    a.nom_long,
    a.dt_modif,
    a.dt_fin,
    d.libelle AS domain,
    m.libelle AS menu,
    ra.libelle AS activity_type,
    rph.libelle AS phase_type,
    ap.coefficien,
    ap.code_pu,
    ap.pu_base,
    (SELECT COUNT(*) FROM r_note_acte WHERE acte_cod = a.cod_acte) AS note_count,
    (SELECT COUNT(*) FROM r_acte_extension WHERE acte_cod = a.cod_acte) AS extension_count
FROM r_acte a
LEFT JOIN r_domaine d ON a.domaine_cod = d.cod_domai
LEFT JOIN r_menu m ON a.menu_cod = m.cod_menu
LEFT JOIN r_acte_ivite ai ON a.cod_acte = ai.acte_cod
LEFT JOIN r_activite ra ON ai.activ_cod = ra.cod_activ
LEFT JOIN r_acte_ivite_phase ap ON ai.cod_aa = ap.aa_cod
LEFT JOIN r_phase rph ON ap.phase_cod = rph.cod_phase
WHERE a.cod_acte = 'GDFE005';

Find similar procedures

-- Procedures with similar names
SELECT
    a1.cod_acte,
    a1.nom_court,
    a2.cod_acte AS similar_code,
    a2.nom_court AS similar_name,
    similarity(a1.nom_court, a2.nom_court) AS similarity_score
FROM r_acte a1
CROSS JOIN r_acte a2
WHERE a1.cod_acte < a2.cod_acte  -- Avoid duplicates
  AND a1.nom_court % a2.nom_court  -- Trigram similarity
  AND a1.dt_fin IS NULL
  AND a2.dt_fin IS NULL
ORDER BY similarity_score DESC
LIMIT 30;

Procedure complexity analysis

-- Procedures ranked by complexity (associations + incompatibilities + phases)
WITH complexity AS (
    SELECT
        a.cod_acte,
        a.nom_court,
        COUNT(DISTINCT ai.cod_aa) AS activity_count,
        COUNT(DISTINCT ap.cod_aap) AS phase_count,
        (SELECT COUNT(*) FROM r_associations assoc
         JOIN r_acte_ivite ai2 ON assoc.aa_code1 = ai2.cod_aa
         WHERE ai2.acte_cod = a.cod_acte) AS association_count,
        (SELECT COUNT(*) FROM r_incompatibilites inc
         JOIN r_acte_ivite ai2 ON inc.aa_code1 = ai2.cod_aa
         WHERE ai2.acte_cod = a.cod_acte) AS incompatibility_count
    FROM r_acte a
    LEFT JOIN r_acte_ivite ai ON a.cod_acte = ai.acte_cod
    LEFT JOIN r_acte_ivite_phase ap ON ai.cod_aa = ap.aa_cod
    WHERE a.dt_fin IS NULL
    GROUP BY a.cod_acte, a.nom_court
)
SELECT
    cod_acte,
    nom_court,
    activity_count,
    phase_count,
    association_count,
    incompatibility_count,
    (activity_count + phase_count + association_count + incompatibility_count) AS complexity_score
FROM complexity
ORDER BY complexity_score DESC
LIMIT 50;

Cross-table data quality check

-- Find orphaned records (procedures without activities)
SELECT a.cod_acte, a.nom_court
FROM r_acte a
LEFT JOIN r_acte_ivite ai ON a.cod_acte = ai.acte_cod
WHERE ai.cod_aa IS NULL
  AND a.dt_fin IS NULL
LIMIT 20;

-- Activities without pricing
SELECT
    ai.cod_aa,
    a.cod_acte,
    a.nom_court
FROM r_acte_ivite ai
JOIN r_acte a ON ai.acte_cod = a.cod_acte
LEFT JOIN r_acte_ivite_phase ap ON ai.cod_aa = ap.aa_cod
WHERE ap.cod_aap IS NULL
  AND a.dt_fin IS NULL
LIMIT 20;

-- Check foreign key integrity
SELECT
    'r_acte missing menu' AS issue,
    COUNT(*) AS count
FROM r_acte a
LEFT JOIN r_menu m ON a.menu_cod = m.cod_menu
WHERE m.cod_menu IS NULL

UNION ALL

SELECT
    'r_acte missing domain' AS issue,
    COUNT(*) AS count
FROM r_acte a
LEFT JOIN r_domaine d ON a.domaine_cod = d.cod_domai
WHERE d.cod_domai IS NULL;

---

Performance Tips

Using materialized views

-- Use pre-computed view for active procedures
SELECT * FROM v_acte_current
WHERE nom_court ILIKE '%échographie%'
LIMIT 20;

-- Use pricing view
SELECT * FROM v_acte_with_pricing
WHERE cod_acte = 'GDFE005';

-- Refresh views after data updates
REFRESH MATERIALIZED VIEW v_acte_current;
REFRESH MATERIALIZED VIEW v_acte_with_pricing;

Efficient searching

-- Use indexes efficiently
EXPLAIN ANALYZE
SELECT cod_acte, nom_court
FROM r_acte
WHERE cod_acte = 'GDFE005';  -- Uses index

-- Full-text search is faster than ILIKE for complex patterns
EXPLAIN ANALYZE
SELECT cod_acte, nom_court
FROM r_acte
WHERE nom_long_search @@ to_tsquery('french_unaccent', 'chirurgie');

---

Exporting Results

Export to CSV

-- Export query results
COPY (
    SELECT cod_acte, nom_court, nom_long
    FROM r_acte
    WHERE dt_fin IS NULL
    ORDER BY cod_acte
) TO '/tmp/active_procedures.csv' WITH CSV HEADER;

Export from psql

# Export to CSV from command line
psql -U ccam_user -d ccam -c "SELECT * FROM r_acte WHERE dt_fin IS NULL" --csv > procedures.csv

# Export with custom delimiter
psql -U ccam_user -d ccam -c "SELECT * FROM r_acte WHERE dt_fin IS NULL" -F '|' > procedures.txt

---

Next Steps

For API access to these queries: - See README.md for FastAPI development plans - Consider adding indexes for frequently queried fields - Create stored procedures for complex business logic

For more information: - Database structure: CCAM_DATABASE_STRUCTURE.md - Quick start guide: QUICKSTART.md