Re: Adding AVG to a JOIN - Mailing list pgsql-general
| From | Alexander Farber |
|---|---|
| Subject | Re: Adding AVG to a JOIN |
| Date | |
| Msg-id | CAADeyWinFoZkO3FzRDFyvyTbdqCdu11OkDTtzC8McAfJErjU0A@mail.gmail.com Whole thread Raw |
| In response to | Re: Adding AVG to a JOIN (Alexander Farber <alexander.farber@gmail.com>) |
| Responses |
Re: Adding AVG to a JOIN
|
| List | pgsql-general |
Good evening,
On Mon, Apr 23, 2018 at 12:56 PM, Alexander Farber <alexander.farber@gmail.com> wrote:
On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite <daniel@manitou-mail.org> wrote:
You may use a correlated subquery in the SELECT clause,
SELECT
u.elo,
(SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS 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
thank you all for helping me in adding an average score per move to my SQL JOIN.
Of course I would like to add yet another statistic and now am trying to add the average time per move to the same query, by prepending it a CTE with LAG():
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,
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,
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
JOIN cte c 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;
but this fails with
ERROR: 42803: column "u.elo" must appear in the GROUP BY clause or be used in an aggregate function
LINE 15: u.elo,
^
And I don't understand why adding a CTE has caused it, because without the CTE the GROUP BY u.elo was not required...
Regards
Alex
pgsql-general by date: