SQL HowTo: création de chaînes à l'aide de fonctions de fenêtre

Parfois, lors de l'analyse des données, le problème se pose de distinguer des «chaînes» dans un échantillon - c'est-à-dire des séquences ordonnées d' enregistrements, pour chacune desquelles une certaine condition est remplie .

Cela peut être soit une condition sur les données de l'enregistrement lui-même, soit une expression complexe par rapport à un ou plusieurs enregistrements précédents - par exemple, la longueur de l'intervalle entre les échantillons de temps de fermeture.



Les solutions traditionnelles offrent différentes options de «self join», lorsque l'échantillon se connecte à lui-même, ou l'utilisation de certains faits «en dehors des données» - par exemple, que les enregistrements doivent avoir une étape strictement définie (N + 1, «pour chaque jour», ... )

La première option conduit souvent à la complexité quadratique de l' algorithme en termes de nombre d'enregistrements, ce qui est inacceptable dans les grands échantillons , et la seconde peut facilement «s'effondrer» s'il n'y a soudainement aucun échantillon dans les données source.

Mais cette tâche nous aidera à résoudre efficacement les fonctions de fenêtre dans PostgreSQL.

Tâche: compter l'argent des autres

Prenons le cas le plus simple d'une chaîne - lorsque la condition de continuité est déterminée par les données de l'enregistrement lui-même.

Toutes les autres opérations ne doivent pas être effectuées séparément. Mais dans un souci de clarté de l'algorithme, je vais le décomposer en étapes successives, et à la fin je montrerai quoi et comment optimiser .

Imaginons que nous ayons une petite banque qui gère les soldes des comptes clients dans le tableau. Dès que la réception et la transaction des dépenses ont lieu, cette date est utilisée pour enregistrer le montant total de la facture à la fin de la journée.

Après de longues vacances du Nouvel An, la banque a décidé de récompenser ses clients - et chaque personne qui a ouvert un compte cette année accumule en outre + 1% du solde quotidien moyen pour la plus longue période continue lorsque le compte n'a pas été «réinitialisé» .

Il s'agit ici de notre critère de continuité de la "chaîne". Eh bien, l'ordre des données sera déterminé par les dates des soldes.

Ils nous ont apporté un tel CSV et ont demandé de calculer rapidement qui et combien une telle générosité de la banque devrait obtenir:

date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 

Il suffit de noter quelques faits notables sur ces données:

• 01.01 était un jour férié, et la banque n'a pas fonctionné. Par conséquent, aucun des clients n'a enregistré de changements dans le solde ce jour-là, mais ils ont de l'argent dans leurs comptes. Autrement dit, les algorithmes de "force brute" qui itèrent le jour ne fonctionneront pas normalement.
• 04.01 Alice n'a effectué aucune opération, il n'y a donc pas d'entrée. Mais avant le 05.01, le montant sur son compte était non nul - cela devra être pris en compte dans l'analyse.
• Nous effectuons l'analyse le 01.01-12.01, mais le solde du compte d'Alice à la fin de cette période est non nul. Nous tenons également compte de la nécessité de limiter la période.

CSV à table

La meilleure façon d'importer depuis CSV est d' utiliser l'opérateur COPY . Mais nous allons essayer de le faire à travers des expressions régulières pour nous échauffer:

 CREATE TEMPORARY TABLE tbl AS SELECT to_date(prt[1], 'DD.MM.YYYY') dt , prt[2] client , prt[3]::numeric(32,2) balance FROM ( SELECT regexp_split_to_array(str, ';') prt FROM ( SELECT regexp_split_to_table( $$ date;client;balance 01.01.2020;;150 01.01.2020;;100 02.01.2020;;100 02.01.2020;;150 03.01.2020;;200 05.01.2020;;0 06.01.2020;;50 08.01.2020;;0 08.01.2020;;200 09.01.2020;;0 09.01.2020;;0 10.01.2020;;5 $$ , E'\\n') str ) T WHERE str <> '' OFFSET 1 ) T; 

Il s'agit d'une méthode «malhonnête» dans le sens où elle ne digère pas correctement, par exemple en protégeant un séparateur dans le corps d'un champ. Mais pour la plupart des applications simples - adaptées.

Étape 1: corriger la condition de l'application

Dans notre cas, la condition de continuité de chaîne est un équilibre non nul. Nous l'avons affiché dans un champ séparé, pour plus de clarté, en ordre chronologique par client:

 SELECT * , balance > 0 cond FROM tbl ORDER BY client, dt; 

 dt | client | balance | cond ------------------------------------ 2020-01-01 |  | 150.00 | t 2020-01-02 |  | 100.00 | t 2020-01-03 |  | 200.00 | t 2020-01-05 |  | 0.00 | f 2020-01-06 |  | 50.00 | t 2020-01-08 |  | 0.00 | f 2020-01-09 |  | 0.00 | f 2020-01-10 |  | 5.00 | t 2020-01-01 |  | 100.00 | t 2020-01-02 |  | 150.00 | t 2020-01-08 |  | 200.00 | t 2020-01-09 |  | 0.00 | f 

Étape 2: Calculez les disparus

Notez que le montant de Bob n'a pas changé du 02.01 au 08.01. Et selon les conditions du problème, nous devons calculer le reste quotidien moyen - c'est-à-dire que nous avons besoin d'informations sur ces jours «manqués». Ou au moins le nombre de jours où la valeur est restée la même:

 coalesce(lead(dt) OVER(PARTITION BY client ORDER BY dt), '2020-01-12') - dt days 

 dt | client | balance | cond | days ------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 2020-01-02 |  | 100.00 | t | 1 2020-01-03 |  | 200.00 | t | 2 2020-01-05 |  | 0.00 | f | 1 2020-01-06 |  | 50.00 | t | 2 2020-01-08 |  | 0.00 | f | 1 2020-01-09 |  | 0.00 | f | 1 2020-01-10 |  | 5.00 | t | 2 2020-01-01 |  | 100.00 | t | 1 2020-01-02 |  | 150.00 | t | 6 2020-01-08 |  | 200.00 | t | 1 2020-01-09 |  | 0.00 | f | 3 

En utilisant la fonction de fenêtre lead (), nous avons appris la date du prochain enregistrement dans l'ordre, et par fusion, nous avons limité l'intervalle pour le dernier. Dans le même temps, ils ont utilisé la propriété utile que la différence de deux dates dans PostgreSQL renvoie un nombre entier de jours entre elles.

En bonus presque gratuit, nous avons obtenu les mêmes informations pour les enregistrements avec un solde nul. Mais s'il y a beaucoup de lignes avec une condition non remplie qui ne nous intéressent pas, il est logique de conduire de tels calculs sous CASE afin d'économiser les ressources du serveur.

Étape 3: Trouvez les points d'arrêt

Le début de chaque chaîne qui nous intéresse est le point où la valeur de la condition précédemment calculée change par rapport à l'enregistrement précédent . Nous utiliserons la fonction lag () pour trouver de tels points:

 lag(cond) OVER(PARTITION BY client ORDER BY dt) IS DISTINCT FROM cond chain_start 

 dt | client | balance | cond | days | chain_start --------------------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 | t 2020-01-02 |  | 100.00 | t | 1 | f 2020-01-03 |  | 200.00 | t | 2 | f 2020-01-05 |  | 0.00 | f | 1 | t 2020-01-06 |  | 50.00 | t | 2 | t 2020-01-08 |  | 0.00 | f | 1 | t 2020-01-09 |  | 0.00 | f | 1 | f 2020-01-10 |  | 5.00 | t | 2 | t 2020-01-01 |  | 100.00 | t | 1 | t 2020-01-02 |  | 150.00 | t | 6 | f 2020-01-08 |  | 200.00 | t | 1 | f 2020-01-09 |  | 0.00 | f | 3 | t 

En utilisant l'opérateur IS DISTINCT FROM au lieu de <>, nous avons évité les problèmes de comparaison avec NULL pour les premiers enregistrements de chaque client. En conséquence, toutes les lignes où la valeur TRUE est le début d'une nouvelle chaîne et FALSE est sa continuation.

Étape 4: enchaînez les liens

Pour regrouper des données au sein de chaque chaîne individuelle, il est plus facile d'attribuer le même identifiant à tous ses enregistrements. Le numéro de série de la chaîne elle-même est parfait pour elle. Et il est exactement égal au nombre de «débuts» de chaînes qui ont été trouvés plus haut dans l'échantillon.

Ils peuvent être calculés soit par la somme «fenêtre» de la somme des valeurs booléennes ({boolean} :: integer), soit en comptant le nombre d'enregistrements correspondant à la condition count (*) FILTER (WHERE {boolean}). Nous utiliserons la deuxième option:

 count(*) FILTER(WHERE chain_start) OVER(PARTITION BY client ORDER BY dt) grpid 

 dt | client | balance | cond | days | chain_start | grpid ----------------------------------------------------------------- 2020-01-01 |  | 150.00 | t | 1 | t | 1 2020-01-02 |  | 100.00 | t | 1 | f | 1 2020-01-03 |  | 200.00 | t | 2 | f | 1 2020-01-06 |  | 50.00 | t | 2 | t | 2 2020-01-10 |  | 5.00 | t | 2 | t | 3 2020-01-01 |  | 100.00 | t | 1 | t | 1 2020-01-02 |  | 150.00 | t | 6 | f | 1 2020-01-08 |  | 200.00 | t | 1 | f | 1 

À cette étape, nous connaissons déjà la longueur de tous les maillons de chaque chaîne, nous n'avons plus besoin d'enregistrements "sans intérêt", il suffit donc de les filtrer.

Étape 5: Mettre les chaînes

Pour calculer la moyenne de tous les jours d'une chaîne, nous avons besoin du nombre total de jours et du solde «intégral»:

 SELECT client , min(dt) chain_dt , sum(days * balance) balance , sum(days) days FROM ... GROUP BY 1, grpid ORDER BY 1, grpid; 

 client | chain_dt | balance | days -------------------------------------  | 2020-01-01 | 650.00 | 4  | 2020-01-06 | 100.00 | 2  | 2020-01-10 | 10.00 | 2  | 2020-01-01 | 1200.00 | 8 

Étape 6: recherche de sommets appliqués

En utilisant DISTINCT ON, nous laisserons un seul enregistrement (avec une valeur maximale de jours) pour chaque client:

 SELECT DISTINCT ON(client) * FROM ... ORDER BY client, days DESC; 

 client | chain_dt | balance | days -------------------------------------  | 2020-01-01 | 650.00 | 4  | 2020-01-01 | 1200.00 | 8 

En fait, c'est tout, tout ce qui reste est ...

Nous combinons et optimisons

Ici, nous avons combiné et optimisé les trois premières étapes:

• La sous-requête LATERAL nous a permis de calculer un champ supplémentaire sans passer par la sélection et de l'utiliser immédiatement dans la fonction
• la suppression d'une définition générale sous WINDOW aide PostgreSQL à ne pas faire de double tri pour former une «fenêtre» et calculer les deux fonctions dans un nœud WindowAgg
• Le calcul de la fonction «paresseux» sous CASE réduit le nombre d'opérations effectuées

De même, nous avons combiné les deux étapes suivantes. Mais l'ordre de la «fenêtre» pour le calcul des agrégats (client, grpid) et de l'uniformisation (client, somme (jours)) ne coïncidait pas, il y a donc toujours deux nœuds de tri dans le dernier bloc - avant WindowAgg et avant Unique.


[regardez expliquez.tensor.ru]

Je note que lors de la numérotation des chaînes , la condition WHERE est d'abord remplie , de sorte que les nombres générés par la fonction de fenêtre s'avèrent être séquentiels.