Re: Adding AVG to a JOIN

[Alexander Farber <alexander.farber@xxxxxxxxx>]

Thank you for the explanation. I have rearranged my query and it works now (surprisingly fast too) -

On Mon, Apr 23, 2018 at 9:58 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

                SELECT

                        u.elo,

                        AVG(c.played - c.prev_played) AS avg_time_per_move,

                        (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS score,

And I don't understand why adding a CTE has caused it, because without the CTE the GROUP BY u.elo was not required...

​Adding "AVG(c.played - c.prev_played)" directly to the top-level select statement​ column list is what turned it into a "GROUP BY" query.  When you embedded the "AVG(score)" in a subquery the GROUP BY was limited to just that subquery, and it had no other columns besides the aggregate and so didn't require a GROUP BY clause.

                WITH cte AS (
                        SELECT
                                m.gid,
                                m.uid,
                                m.played,
                                LAG(m.played) OVER (PARTITION BY m.gid ORDER BY played) AS prev_played
                        FROM words_moves m
                        JOIN words_games g ON (m.gid = g.gid AND m.uid in (g.player1, g.player2))
                        WHERE m.played > CURRENT_TIMESTAMP - interval '1 month'
                )
                SELECT
                        u.elo,
                        (SELECT TO_CHAR(AVG(played - prev_played), 'HH24:MI') FROM cte WHERE uid = u.uid) AS avg_time,
                        (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS avg_score,
                        s.given,
                        s.photo
                FROM words_users u
                JOIN words_social s USING (uid)
                WHERE u.elo > 1500
                -- take the most recent record from words_social
                AND NOT EXISTS (SELECT 1
                                FROM words_social x
                                WHERE s.uid = x.uid
                                AND x.stamp > s.stamp)
                -- only show players who where active in the last week
                AND EXISTS (SELECT 1
                            FROM words_moves
                            WHERE played > CURRENT_TIMESTAMP - INTERVAL '1 week'
                            AND action IN ('play', 'skip', 'swap', 'resign'))
                ORDER BY u.elo DESC
                LIMIT 10;

Best regards

Alex