Est-il possible d'améliorer l'écriture de cette requête group + sum pour qu'elle soit plus rapide ?

Question

Voici les données :

me  friend  game    status  count
1   2       gem     done    10
2   1       gem     done    5
1   3       gem     done    4
3   1       gem     done    6

Voici ma question :

WITH
  -- outgoing for all
    outgoing_for_all AS
  (SELECT
     me,
     sum(count) AS sum
   FROM game_totals
   WHERE status IN ('pending', 'done')
   GROUP BY me),

  -- incoming for all
    incoming_for_all AS
  (SELECT
     friend,
     sum(count) AS sum
   FROM game_totals
   WHERE status IN ('pending', 'done')
   GROUP BY friend)

SELECT
  me,
  outgoing_for_all.sum AS outgoing,
  incoming_for_all.sum AS incoming,
  outgoing_for_all.sum - incoming_for_all.sum AS score
FROM outgoing_for_all
  FULL OUTER JOIN incoming_for_all ON outgoing_for_all.me = incoming_for_all.friend

Voici le résultat :

me  outgoing    incoming    score
1   14          11          3
2   5           10          -5
3   6           4           2

Est-il possible d'écrire la requête ci-dessus de manière à ce qu'elle soit plus rapide ? Je pense qu'il y a une possibilité de faire la somme avec un seul SELECT . Le problème, c'est que je ne sais pas comment faire pour GROUP BY de manière à ce que je puisse résumer count de deux rangs en un seul.

Nous vous remercions.

Answer 1

Oui, vous pouvez obtenir la somme des deux en une seule requête, en utilisant fonctions de la fenêtre .

SELECT
 me,
 sum(count) AS sum over(partition by me) AS outgoing,
 sum(count) AS sum over(partition by friend) AS incoming
FROM game_totals
WHERE status IN ('pending', 'done')

Answer 2

Le problème, c'est que je ne sais pas comment faire pour GROUP BY de manière à ce que je puisse résumer count de deux rangs en un seul.

Vous avez deviné : parce que vous voulez une seule ligne (un count ) à compter deux fois (une fois pour me 's outgoing et un pour friend 's incoming ), vous devrez doubler tous vos rangs. De plus, ces lignes doublées devront être groupées par une colonne différente. L'approche traditionnelle consiste généralement à utiliser UNION :

SELECT me,
       SUM(count) FILTER (WHERE mul = 1) outgoing,
       SUM(count) FILTER (WHERE mul = -1) incoming,
       SUM(mul * count) score
FROM (
  SELECT me, 1 mul, count
  FROM   game_totals
  WHERE  status IN ('pending', 'done')
  UNION ALL
  SELECT friend, -1, count
  FROM   game_totals
  WHERE  status IN ('pending', 'done')
) t
GROUP BY me;

Ou, parce que nous savons exactement que chaque ligne doit être comptée deux fois, vous pouvez utiliser une fonction CROSS JOIN aussi :

SELECT   CASE mul WHEN 1 THEN me ELSE friend END me,
         SUM(count) FILTER (WHERE mul = 1) outgoing,
         SUM(count) FILTER (WHERE mul = -1) incoming,
         SUM(mul * count) score
FROM     game_totals, (VALUES (1), (-1)) m(mul)
WHERE    status IN ('pending', 'done')
GROUP BY CASE mul WHEN 1 THEN me ELSE friend END

MAIS Ils sont tout simplement plus lisibles. Ils sont en fait plus lents que votre variante. (Je pense que vous avez déjà trouvé la solution la plus rapide. Cependant, vous devriez penser à utiliser des index (peut-être plus, ou d'autres index, si vous les utilisez déjà). Par exemple, cet index pourrait vous aider beaucoup :

CREATE INDEX idx_game_totals_me_friend_count
  ON game_totals(me, friend, count)
  WHERE status IN ('pending', 'done');

http://rextester.com/NGAHW3672