J'ai besoin d'aide pour écrire / optimiser une requête pour récupérer la dernière version de chaque ligne par type et effectuer des calculs en fonction du type. Je pense que ce serait mieux si je l'illustre par un exemple.

Étant donné le jeu de données suivant:

+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| id    | event_type        | event_timestamp     | message_id  | sent_at             | status | rate     |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+
| 1     | create            | 2016-11-25 09:17:48 | 1           | 2016-11-25 09:17:48 | 0      | 0.500000 |
| 2     | status_update     | 2016-11-25 09:24:38 | 1           | 2016-11-25 09:28:49 | 1      | 0.500000 |
| 3     | create            | 2016-11-25 09:47:48 | 2           | 2016-11-25 09:47:48 | 0      | 0.500000 |
| 4     | status_update     | 2016-11-25 09:54:38 | 2           | 2016-11-25 09:48:49 | 1      | 0.500000 |
| 5     | rate_update       | 2016-11-25 09:55:07 | 2           | 2016-11-25 09:50:07 | 0      | 1.000000 |
| 6     | create            | 2016-11-26 09:17:48 | 3           | 2016-11-26 09:17:48 | 0      | 0.500000 |
| 7     | create            | 2016-11-27 09:17:48 | 4           | 2016-11-27 09:17:48 | 0      | 0.500000 |
| 8     | rate_update       | 2016-11-27 09:55:07 | 4           | 2016-11-27 09:50:07 | 0      | 2.000000 |
| 9     | rate_update       | 2016-11-27 09:55:07 | 2           | 2016-11-25 09:55:07 | 0      | 2.000000 |
+-------+-------------------+---------------------+-------------+---------------------+--------+----------+

Le résultat attendu devrait être:

+------------+--------------------+--------------------+-----------------------+
| sent_at    | sum(submitted_msg) | sum(delivered_msg) | sum(rate_total)       |
+------------+--------------------+--------------------+-----------------------+
| 2016-11-25 |                  2 |                  2 |              2.500000 |
| 2016-11-26 |                  1 |                  0 |              0.500000 |
| 2016-11-27 |                  1 |                  0 |              2.000000 |
+------------+--------------------+--------------------+-----------------------+

À la fin de l'article se trouve la requête utilisée pour obtenir ce résultat. Je suis prêt à parier qu'il devrait y avoir un moyen de l'optimiser, car il utilise des sous-requêtes avec des jointures, et d'après ce que j'ai lu sur BigQuery, il est préférable d'éviter les jointures. Mais d'abord un peu de contexte:

En substance, l'ensemble de données représente une table d'ajout uniquement, dans laquelle sont écrits les événements multipipes. La taille des données se chiffre à des centaines de millions et atteindra des milliards +. Étant donné que les mises à jour dans BigQuery ne sont pas pratiques et que les données sont diffusées vers BQ, j'ai besoin d'un moyen de récupérer le plus récent de chaque événement, d'effectuer des calculs en fonction de certaines conditions et de renvoyer un résultat précis. La requête est générée dynamiquement, en fonction des entrées de l'utilisateur, de sorte que plus de champs / calculs peuvent être inclus, mais ont été omis pour plus de simplicité.

  • Il n'y a qu'un seul événement create, mais n de tout autre type
  • Pour chaque groupe d'événements, seuls les plus récents doivent être pris en compte lors des calculs.
    • status_update - met à jour le statut
    • rate_update - met à jour le taux
    • create - explicite
  • Chaque événement qui n'est pas create peut ne pas porter le reste des informations de l'original / peut ne pas être précis (sauf pour message_id et le champ sur lequel l'événement fonctionne) (l'ensemble de données est simplifié, mais imaginez qu'il y a beaucoup plus de colonnes, et plus d'événements seront ajoutés plus tard)
    • Par exemple un rate_update peut avoir ou non le champ d'état défini, ou être une valeur qui n'est pas la finale, donc aucun calcul ne peut être effectué sur le champ d'état à partir d'un événement rate_update et il en va de même pour { {X3}}
  • On peut supposer que la table est partitionnée par date et chaque requête utilisera les partitions. Ces conditions ont été omises au profit de la simplicité pour le moment.

Je suppose donc que j'ai quelques questions:

  • Comment cette requête peut-elle être optimisée?
  • Sera-ce une meilleure idée de placer les événements, autres que create dans leurs propres tables, où les seuls champs disponibles seront ceux pertinents pour les événements, et nécessaires pour les jointures (message_id, event_timestamp)? Cela réduira-t-il la quantité de données traitées?
  • Quelle serait la manière la plus optimale d'ajouter plus d'événements à l'avenir, qui auront leurs propres conditions et calculs?

En fait, tout conseil sur la façon d'interroger cet ensemble de données de manière efficace et conviviale est plus que bienvenu! Je vous remercie! :)

La monstruosité que j'ai imaginée est la suivante. Les INNER JOINS sont utilisés pour récupérer la dernière version de chaque ligne, selon ceci ressource

    select
    sent_at as sent_at,
    sum(submitted_msg) as submitted,
    sum(delivered_msg) as delivered,
    sum(sales_rate_total) as sales_rate_total
    FROM (

      #DELIVERED
        SELECT 
            d.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            0 as submitted_msg,
            sum(if(status=1,1,0)) as delivered_msg,
            0 as sales_rate_total
        FROM `events` d
        INNER JOIN
                (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type = "status_update" 
                    group by 1
                    ) g on d.message_id = g.message_id and d.event_timestamp = g.ts
        GROUP BY 1,2

        UNION ALL

      #SALES RATE
        SELECT 
            s.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            0 as submitted_msg,
            0 as delivered_msg,
            sum(sales_rate) as sales_rate_total
        FROM `events` s
        INNER JOIN 
                    (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type in ("rate_update", "create")  
                    group by 1
                    ) f on s.message_id = f.message_id and s.event_timestamp = f.ts
        GROUP BY 1,2

        UNION ALL

      #SUBMITTED & REST
        SELECT 
            r.message_id,
            FORMAT_TIMESTAMP('%Y-%m-%d 00:00:00', sent_at) AS sent_at,
            sum(if(status=0,1,0)) as submitted_msg,
            0 as delivered_msg,
            0 as sales_rate_total
        FROM `events` r
        INNER JOIN
                (
                    select message_id, max(event_timestamp) as ts 
                    from `events` 
                    where event_type = "create" 
                    group by 1
                    ) e on r.message_id = e.message_id and r.event_timestamp = e.ts
        GROUP BY 1, 2

    ) k
    group by 1
3
vulkoingim 16 janv. 2017 à 17:31

2 réponses

Meilleure réponse

Comment cette requête peut-elle être optimisée?

Essayez la version ci-dessous

#standardSQL
WITH types AS (
  SELECT 
    FORMAT_TIMESTAMP('%Y-%m-%d', sent_at) AS sent_at,
    message_id,
    FIRST_VALUE(status) OVER(PARTITION BY message_id ORDER BY (event_type = "create") DESC, event_timestamp DESC) AS submitted_status,
    FIRST_VALUE(status) OVER(PARTITION BY message_id ORDER BY (event_type = "status_update") DESC, event_timestamp DESC) AS delivered_status,
    FIRST_VALUE(rate) OVER(PARTITION BY message_id ORDER BY (event_type IN ("rate_update", "create")) DESC, event_timestamp DESC) AS sales_rate
  FROM events
), latest AS (
  SELECT 
    sent_at,
    message_id,
    ANY_VALUE(IF(submitted_status=0,1,0)) AS submitted,  
    ANY_VALUE(IF(delivered_status=1,1,0)) AS delivered,  
    ANY_VALUE(sales_rate) AS sales_rate
  FROM types
  GROUP BY 1, 2
)
SELECT   
  sent_at,
  SUM(submitted) AS submitted,  
  SUM(delivered) AS delivered,  
  SUM(sales_rate) AS sales_rate_total        
FROM latest
GROUP BY 1

Il est suffisamment compact pour être géré facilement, pas de redondance, pas de jointure du tout, etc.
Si votre table est partitionnée - vous pouvez facilement l'utiliser en ajustant la requête à un seul endroit

Vous pouvez utiliser les données factices ci-dessous si vous souhaitez d'abord vérifier la requête ci-dessus sur un faible volume

WITH events AS (
  SELECT 1 AS id, 'create' AS event_type, TIMESTAMP '2016-11-25 09:17:48' AS event_timestamp, 1 AS message_id, TIMESTAMP '2016-11-25 09:17:48' AS sent_at, 0 AS status, 0.500000 AS rate UNION ALL
  SELECT 2 AS id, 'status_update' AS event_type, TIMESTAMP '2016-11-25 09:24:38' AS event_timestamp, 1 AS message_id, TIMESTAMP '2016-11-25 09:28:49' AS sent_at, 1 AS status, 0.500000 AS rate UNION ALL
  SELECT 3 AS id, 'create' AS event_type, TIMESTAMP '2016-11-25 09:47:48' AS event_timestamp, 2 AS message_id, TIMESTAMP '2016-11-25 09:47:48' AS sent_at, 0 AS status, 0.500000 AS rate UNION ALL
  SELECT 4 AS id, 'status_update' AS event_type, TIMESTAMP '2016-11-25 09:54:38' AS event_timestamp, 2 AS message_id, TIMESTAMP '2016-11-25 09:48:49' AS sent_at, 1 AS status, 0.500000 AS rate UNION ALL
  SELECT 5 AS id, 'rate_update' AS event_type, TIMESTAMP '2016-11-25 09:55:07' AS event_timestamp, 2 AS message_id, TIMESTAMP '2016-11-25 09:50:07' AS sent_at, 0 AS status, 1.000000 AS rate UNION ALL
  SELECT 6 AS id, 'create' AS event_type, TIMESTAMP '2016-11-26 09:17:48' AS event_timestamp, 3 AS message_id, TIMESTAMP '2016-11-26 09:17:48' AS sent_at, 0 AS status, 0.500000 AS rate UNION ALL
  SELECT 7 AS id, 'create' AS event_type, TIMESTAMP '2016-11-27 09:17:48' AS event_timestamp, 4 AS message_id, TIMESTAMP '2016-11-27 09:17:48' AS sent_at, 0 AS status, 0.500000 AS rate UNION ALL
  SELECT 8 AS id, 'rate_update' AS event_type, TIMESTAMP '2016-11-27 09:55:07' AS event_timestamp, 4 AS message_id, TIMESTAMP '2016-11-27 09:50:07' AS sent_at, 0 AS status, 2.000000 AS rate UNION ALL
  SELECT 9 AS id, 'rate_update' AS event_type, TIMESTAMP '2016-11-27 09:55:07' AS event_timestamp, 2 AS message_id, TIMESTAMP '2016-11-25 09:55:07' AS sent_at, 0 AS status, 2.000000 AS rate 
)
4
Mikhail Berlyant 16 janv. 2017 à 20:46

Pour chaque table qui contient plusieurs événements et où nous devons choisir la dernière, nous avons une vue en place.

Afficher: user_profile_latest

SELECT * from (
  select rank() over (partition by user_id order by bq.created DESC, bq.insert_id  desc) as _rank,
*
FROM [user_profile_event]
) where _rank=1

Nous maintenons un enregistrement BQ avec created et insert_id à des fins de déduplication.

0
Pentium10 16 janv. 2017 à 14:46