Vue matérialisée des dernières données avec regroupement + colonne d'horodatage

Question

Je modélise des traits (ou attributs) dans Bigquery. Voici un exemple de modèle

uid         string, link to owner id
uuid        string, unique among all rows
trait_name  string, name of the trait
trait_value string
added_at    timestamp, when the trait was added

J'essaie de construire une vue matérialisée qui contient le dernier trait pour chaque trait de chaque Uid. Je suis capable d'obtenir le résultat avec cette requête :

WITH traits AS (
  SELECT 'u1' uid, 'uu1' uuid, 't1' trait_name, 't1v1' trait_value, timestamp("2021-10-01 10:00:00") as added_at UNION ALL
  SELECT 'u1' uid, 'uu2' uuid, 't1' trait_name, 't1v2' trait_value, timestamp("2021-10-02 10:00:00") as added_at UNION ALL
  SELECT 'u1' uid, 'uu3' uuid, 't2' trait_name, 't2v1' trait_value, timestamp("2021-10-03 10:00:00") as added_at UNION ALL
  SELECT 'u1' uid, 'uu4' uuid, 't2' trait_name, 't2v2' trait_value, timestamp("2021-10-04 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu5' uuid, 't1' trait_name, 't1v1' trait_value, timestamp("2021-10-05 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu6' uuid, 't1' trait_name, 't1v2' trait_value, timestamp("2021-10-06 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu7' uuid, 't2' trait_name, 't2v1' trait_value, timestamp("2021-10-07 10:00:00") as added_at UNION ALL
  SELECT 'u2' uid, 'uu8' uuid, 't2' trait_name, 't2v2' trait_value, timestamp("2021-10-08 10:00:00") as added_at
)
SELECT * FROM (
  SELECT *,
  MAX(added_at) OVER (PARTITION BY uid, trait_name) as latest_added_at FROM traits
) WHERE latest_added_at = added_at

#  Row  uid uuid    trait_name  trait_value added_at                 latest_added_at    
#  1    u1  uu2     t1          t1v2        2021-10-02 10:00:00 UTC  2021-10-02 10:00:00 UTC
#  2    u1  uu4     t2          t2v2        2021-10-04 10:00:00 UTC  2021-10-04 10:00:00 UTC
#  3    u2  uu6     t1          t1v2        2021-10-06 10:00:00 UTC  2021-10-06 10:00:00 UTC
#  4    u2  uu8     t2          t2v2        2021-10-08 10:00:00 UTC  2021-10-08 10:00:00 UTC

Mais je ne peux pas l'utiliser pour les vues matérialisées car elles ne le supportent pas :

Les vues matérialisées ne supportent pas les fonctions analytiques ou WITH OFFSET.

J'ai également essayé d'utiliser des jointures

SELECT * FROM traits t
JOIN (
    SELECT
    uid,
    trait_name,
    MAX(added_at) AS max_added_at 
    FROM traits GROUP BY uid, trait_name
) grouped
ON t.uid = grouped.uid 
AND t.trait_name = grouped.trait_name
AND t.added_at = grouped.max_added_at

Mais ils ne sont pas non plus soutenus

Les requêtes de vues matérialisées ne peuvent pas référencer la même table plus d'une fois. Les traits de la table ont été vus plusieurs fois.

Y a-t-il un moyen de le faire en tant que vue matérialisée ?

Answer 1

Les vues matérialisées ont des limites qui peuvent rendre cela impossible à réaliser (cf. este documentation publique pour plus d'informations).

Actuellement, il semble que ce que vous essayez d'obtenir n'est pas entièrement pris en charge et l'utilisation de ARRAY_AGG semble être la plus proche possible, l'un des inconvénients étant que le résultat est de type tableau, et non la valeur elle-même.

J'ai réussi à obtenir les résultats attendus avec cette requête :

CREATE MATERIALIZED VIEW test_traits.mv_sample_traits as
SELECT uid,
       ARRAY_AGG(uuid IGNORE NULLS ORDER BY added_at DESC, uuid DESC, trait_value DESC LIMIT 1) as uuid,
       trait_name,
       ARRAY_AGG(trait_value IGNORE NULLS ORDER BY added_at DESC, uuid DESC, trait_value DESC LIMIT 1) as trait_value,
       ARRAY_AGG(added_at IGNORE NULLS ORDER BY added_at DESC, uuid DESC, trait_value DESC LIMIT 1) as added_at,
       MAX(added_at) AS latest_added_at,
FROM test_traits.traits
GROUP BY uid,trait_name

/*
SELECT * FROM test_traits.mv_sample_traits;
*/

Veuillez noter que le code est fourni à titre de référence et que vous devrez exécuter des tests pour vérifier s'il correspond à votre cas d'utilisation. Il n'y a aucune garantie que cela fonctionnera comme prévu et je ne peux donc pas le recommander pour une utilisation en production.

Answer 2

J'ai suivi la documentation google https://cloud.google.com/bigquery/docs/materialized-views pour créer une vue matérialisée pour votre besoin.

J'ai inséré les données dans un tableau comme ci-dessous

J'ai exécuté la requête suivante pour créer une vue matérialisée

CREATE MATERIALIZED VIEW  `graphical-reach-285218.test.traits1`
as select max(added_at) as latest_added_at,uid,trait_name from `graphical-reach-285218.test.traits`
group by uid, trait_name;

La vue matérialisée est créée comme suit

En interrogeant la vue matérialisée, voici les données que j'obtiens