Langage de requête Beancount - Requêtes financières de type SQL

Beancount propose un langage de requête (BQL) puissant, de type SQL, qui vous permet de découper, d'analyser et d'explorer vos données financières avec précision. Que vous souhaitiez générer un rapport rapide, déboguer une entrée ou effectuer une analyse complexe, la maîtrise de BQL est essentielle pour libérer tout le potentiel de votre livre de comptes en texte brut. Ce guide vous présentera sa structure, ses fonctions et ses meilleures pratiques. 🔍

---

Structure et exécution des requêtes

Le cœur de BQL est sa syntaxe familière, inspirée de SQL. Les requêtes sont exécutées à l'aide de l'outil de ligne de commande bean-query, qui traite votre fichier de livre de comptes et renvoie les résultats directement dans votre terminal.

Format de requête de base

Une requête BQL est composée de trois clauses principales : SELECT, FROM et WHERE.

SELECT <cible1>, <cible2>, ...
FROM <expression-filtre-entrée>
WHERE <expression-filtre-écriture>;

• SELECT : Spécifie les colonnes de données que vous souhaitez extraire.
• FROM : Filtre les transactions entières avant qu'elles ne soient traitées.
• WHERE : Filtre les lignes d'écriture individuelles après que la transaction a été sélectionnée.

Système de filtrage à deux niveaux

Comprendre la différence entre les clauses FROM et WHERE est essentiel pour écrire des requêtes précises. BQL utilise un processus de filtrage à deux niveaux.

1. Niveau de transaction (FROM) Cette clause agit sur les transactions entières. Si une transaction correspond à la condition FROM, la transaction entière (y compris toutes ses écritures) est transmise à l'étape suivante. C'est le principal moyen de filtrer les données, car il préserve l'intégrité du système de comptabilité en partie double. Par exemple, filtrer FROM year = 2024 sélectionne toutes les transactions qui ont eu lieu en 2024.

2. Niveau d'écriture (WHERE) Cette clause filtre les écritures individuelles au sein des transactions sélectionnées par la clause FROM. Ceci est utile pour la présentation et pour se concentrer sur des branches spécifiques d'une transaction. Cependant, soyez conscient que le filtrage à ce niveau peut "rompre" l'intégrité d'une transaction dans la sortie, car vous ne pourriez voir qu'un seul côté d'une écriture. Par exemple, vous pourriez sélectionner toutes les écritures vers votre compte Expenses:Groceries.

---

Modèle de données

Pour interroger efficacement vos données, vous devez comprendre comment Beancount les structure. Un livre de comptes est une liste de directives, mais BQL se concentre principalement sur les entrées Transaction.

Structure de transaction

Chaque Transaction est un conteneur avec des attributs de niveau supérieur et une liste d'objets Posting.

Transaction
├── date
├── flag
├── payee
├── narration
├── tags
├── links
└── Postings[]
    ├── account
    ├── units
    ├── cost
    ├── price
    └── metadata

Types de colonnes disponibles

Vous pouvez SELECT n'importe lequel des attributs de la transaction ou de ses écritures.

1. Attributs de transaction Ces colonnes sont les mêmes pour chaque écriture au sein d'une même transaction.

   SELECT
       date,        -- La date de la transaction (datetime.date)
       year,        -- L'année de la transaction (int)
       month,       -- Le mois de la transaction (int)
       day,         -- Le jour de la transaction (int)
       flag,        -- L'indicateur de la transaction, par ex. "*" ou "!" (str)
       payee,       -- Le bénéficiaire (str)
       narration,   -- La description ou le mémo (str)
       tags,        -- Un ensemble d'étiquettes, par ex. #voyage-2024 (set[str])
       links        -- Un ensemble de liens, par ex. ^rapport-de-dépenses (set[str])

2. Attributs d'écriture Ces colonnes sont spécifiques à chaque ligne d'écriture individuelle.

   SELECT
       account,     -- Le nom du compte (str)
       position,    -- Le montant total, y compris les unités et le coût (Position)
       units,       -- Le nombre et la devise de l'écriture (Amount)
       cost,        -- La base de coût de l'écriture (Cost)
       price,       -- Le prix utilisé dans l'écriture (Amount)
       weight,      -- La position convertie en sa base de coût (Amount)
       balance      -- Le total cumulé des unités dans le compte (Inventory)

---

Fonctions de requête

BQL comprend une suite de fonctions pour l'agrégation et la transformation des données, tout comme SQL.

Fonctions d'agrégation

Les fonctions d'agrégation résument les données sur plusieurs lignes. Lorsqu'elles sont utilisées avec GROUP BY, elles fournissent des résumés groupés.

-- Compter le nombre d'écritures
SELECT COUNT(*)

-- Sommer la valeur de toutes les écritures (converties en une devise commune)
SELECT SUM(position)

-- Trouver la date de la première et de la dernière transaction
SELECT FIRST(date), LAST(date)

-- Trouver les valeurs de position minimales et maximales
SELECT MIN(position), MAX(position)

-- Grouper par compte pour obtenir une somme pour chacun
SELECT account, SUM(position) GROUP BY account

Fonctions Position/Inventory

La colonne position est un objet composite. Ces fonctions vous permettent d'extraire des parties spécifiques de celui-ci ou de calculer sa valeur marchande.

-- Extraire simplement le nombre et la devise d'une position
SELECT UNITS(position)

-- Afficher le coût total d'une position
SELECT COST(position)

-- Afficher la position convertie en sa base de coût (utile pour les investissements)
SELECT WEIGHT(position)

-- Calculer la valeur marchande en utilisant les dernières données de prix
SELECT VALUE(position)

Vous pouvez les combiner pour des rapports puissants. Par exemple, pour voir le coût total et la valeur marchande actuelle de votre portefeuille d'investissement :

SELECT
    account,
    COST(SUM(position)) AS total_cost,
    VALUE(SUM(position)) AS market_value
FROM
    account ~ "Assets:Investments"
GROUP BY
    account

---

Fonctionnalités avancées

Au-delà des instructions SELECT de base, BQL offre des commandes spécialisées pour les rapports financiers courants.

Rapports de solde

L'instruction BALANCES génère un bilan ou un compte de résultat pour une période spécifique.

-- Générer un bilan simple au début de l'année 2024
BALANCES FROM close ON 2024-01-01
WHERE account ~ "^Assets|^Liabilities"

-- Générer un compte de résultat pour l'exercice 2024
BALANCES FROM
    OPEN ON 2024-01-01
    CLOSE ON 2024-12-31
WHERE account ~ "^Income|^Expenses"

Rapports de journal

L'instruction JOURNAL affiche l'activité détaillée d'un ou plusieurs comptes, de la même manière qu'une vue de grand livre traditionnelle.

-- Afficher toute l'activité de votre compte courant à son coût d'origine
JOURNAL "Assets:Checking" AT COST

-- Afficher toutes les transactions 401k, en affichant uniquement les unités (actions)
JOURNAL "Assets:.*:401k" AT UNITS

Opérations d'impression

L'instruction PRINT est un outil de débogage qui affiche les transactions complètes correspondantes dans leur format de fichier Beancount d'origine.

-- Imprimer toutes les transactions liées aux investissements de 2024
PRINT FROM year = 2024
WHERE account ~ "Assets:Investments"

-- Trouver une transaction par son ID unique (généré par certains outils)
PRINT FROM id = "8e7c47250d040ae2b85de580dd4f5c2a"

Expressions de filtrage

Vous pouvez créer des filtres sophistiqués à l'aide d'opérateurs logiques (AND, OR), d'expressions régulières (~) et de comparaisons.

-- Trouver toutes les dépenses de voyage du second semestre 2024
SELECT * FROM
    year = 2024 AND month >= 6
WHERE account ~ "Expenses:Travel"

-- Trouver toutes les transactions liées à des vacances ou à un voyage d'affaires
SELECT * FROM
    "vacation-2024" IN tags OR
    "business-trip" IN links

---

Considérations sur les performances ⚙️

bean-query est conçu pour être efficace, mais comprendre son flux de fonctionnement peut vous aider à écrire des requêtes plus rapides sur de grands livres de comptes.

1. Chargement des données : Beancount analyse d'abord l'ensemble de votre fichier de livre de comptes et trie toutes les transactions par ordre chronologique. L'ensemble de ces données est conservé en mémoire.
2. Optimisation des requêtes : Le moteur de requête applique des filtres dans un ordre spécifique pour une efficacité maximale : FROM (transactions) -> WHERE (écritures) -> Agrégations. Le filtrage au niveau FROM est le plus rapide car il réduit le jeu de données au plus tôt.
3. Utilisation de la mémoire : Toutes les opérations se déroulent en mémoire. Les objets Position et les agrégations Inventory sont optimisés, mais de très grands ensembles de résultats peuvent consommer une quantité importante de RAM. BQL n'utilise pas de stockage temporaire sur disque.

---

Bonnes pratiques

Suivez ces conseils pour écrire des requêtes propres, efficaces et faciles à maintenir.

1. Organisation des requêtes Formatez vos requêtes pour la lisibilité, en particulier les requêtes complexes. Utilisez des sauts de ligne et une indentation pour séparer les clauses.

   -- Une requête propre et lisible pour toutes les dépenses de 2024
   SELECT
       date,
       account,
       position
   FROM
       year = 2024
   WHERE
       account ~ "Expenses"
   ORDER BY
       date DESC;

2. Débogage Si une requête ne fonctionne pas comme prévu, utilisez EXPLAIN pour voir comment Beancount l'analyse. Pour tester un filtre, utilisez SELECT DISTINCT pour voir quelles valeurs uniques il correspond.

   -- Voir le plan de requête
   EXPLAIN SELECT date, account, position;
   
   -- Tester quels comptes correspondent à une expression régulière
   SELECT DISTINCT account
   WHERE account ~ "^Assets:.*";

3. Assertions de solde Vous pouvez utiliser BQL pour vérifier les assertions balance dans votre livre de comptes. Cette requête devrait renvoyer le montant exact spécifié dans votre dernier contrôle de solde pour ce compte.

   -- Vérifier le solde final de votre compte courant
   SELECT account, sum(position)
   FROM close ON 2025-01-01 -- Utiliser la date de votre directive de solde
   WHERE account = "Assets:Checking";