Обсуждение: aggregate functions are not allowed in UPDATE
Good evening,
I have prepared a simple test case for my question: https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/0
There I create 3 tables:
CREATE TABLE users (
uid SERIAL PRIMARY KEY,
avg_time TEXT
);
CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
player2 INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE CHECK (player1 <> player2)
);
CREATE TABLE moves (
mid BIGSERIAL PRIMARY KEY,
played timestamptz NOT NULL,
gid INTEGER NOT NULL REFERENCES games(gid) ON DELETE CASCADE,
uid INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE
);
And then I fill them with 3 test players and 2 test games (1 vs. 3 and 1 vs. 2):
INSERT INTO users (uid) VALUES
(1),
(2),
(3);
INSERT INTO games (gid, player1, player2) VALUES
(1, 2, 3),
(2, 1, 3),
(3, 1, 2);
INSERT INTO moves (played, gid, uid) VALUES
(NOW() + INTERVAL '1 hour', 2, 1),
(NOW() + INTERVAL '2 hour', 2, 3),
(NOW() + INTERVAL '3 hour', 2, 1), -- +1 hour
(NOW() + INTERVAL '4 hour', 2, 3),
(NOW() + INTERVAL '5 hour', 2, 1), -- +1 hour
(NOW() + INTERVAL '6 hour', 2, 3),
(NOW() + INTERVAL '7 hour', 2, 1), -- +1 hour
(NOW() + INTERVAL '8 hour', 2, 3),
(NOW() + INTERVAL '10 hour', 3, 1),
(NOW() + INTERVAL '20 hour', 3, 2),
(NOW() + INTERVAL '30 hour', 3, 1), -- +10 hours
(NOW() + INTERVAL '40 hour', 3, 2),
(NOW() + INTERVAL '50 hour', 3, 1), -- +10 hours
(NOW() + INTERVAL '60 hour', 3, 2),
(NOW() + INTERVAL '70 hour', 3, 1), -- +10 hours
(NOW() + INTERVAL '80 hour', 3, 2);
After that I am able to calculate the average time that the player 1 needs to perform a move (11 hours):
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
WHERE uid = 1
)
SELECT
uid, AVG(diff)
FROM diffs
GROUP BY uid;
But how to take that calculated value and store it in the "avg_time" text column of the users table?
When I am trying
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
WHERE uid = 1
)
UPDATE users SET
avg_time = TO_CHAR(AVG(diff), 'HH24:MI')
FROM diffs;
the syntax error is unfortunately printed by PostgreSQL 10:
aggregate functions are not allowed in UPDATE
Regards
Alex
I have prepared a simple test case for my question: https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/0
There I create 3 tables:
CREATE TABLE users (
uid SERIAL PRIMARY KEY,
avg_time TEXT
);
CREATE TABLE games (
gid SERIAL PRIMARY KEY,
player1 INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE,
player2 INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE CHECK (player1 <> player2)
);
CREATE TABLE moves (
mid BIGSERIAL PRIMARY KEY,
played timestamptz NOT NULL,
gid INTEGER NOT NULL REFERENCES games(gid) ON DELETE CASCADE,
uid INTEGER NOT NULL REFERENCES users(uid) ON DELETE CASCADE
);
And then I fill them with 3 test players and 2 test games (1 vs. 3 and 1 vs. 2):
INSERT INTO users (uid) VALUES
(1),
(2),
(3);
INSERT INTO games (gid, player1, player2) VALUES
(1, 2, 3),
(2, 1, 3),
(3, 1, 2);
INSERT INTO moves (played, gid, uid) VALUES
(NOW() + INTERVAL '1 hour', 2, 1),
(NOW() + INTERVAL '2 hour', 2, 3),
(NOW() + INTERVAL '3 hour', 2, 1), -- +1 hour
(NOW() + INTERVAL '4 hour', 2, 3),
(NOW() + INTERVAL '5 hour', 2, 1), -- +1 hour
(NOW() + INTERVAL '6 hour', 2, 3),
(NOW() + INTERVAL '7 hour', 2, 1), -- +1 hour
(NOW() + INTERVAL '8 hour', 2, 3),
(NOW() + INTERVAL '10 hour', 3, 1),
(NOW() + INTERVAL '20 hour', 3, 2),
(NOW() + INTERVAL '30 hour', 3, 1), -- +10 hours
(NOW() + INTERVAL '40 hour', 3, 2),
(NOW() + INTERVAL '50 hour', 3, 1), -- +10 hours
(NOW() + INTERVAL '60 hour', 3, 2),
(NOW() + INTERVAL '70 hour', 3, 1), -- +10 hours
(NOW() + INTERVAL '80 hour', 3, 2);
After that I am able to calculate the average time that the player 1 needs to perform a move (11 hours):
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
WHERE uid = 1
)
SELECT
uid, AVG(diff)
FROM diffs
GROUP BY uid;
But how to take that calculated value and store it in the "avg_time" text column of the users table?
When I am trying
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
WHERE uid = 1
)
UPDATE users SET
avg_time = TO_CHAR(AVG(diff), 'HH24:MI')
FROM diffs;
the syntax error is unfortunately printed by PostgreSQL 10:
aggregate functions are not allowed in UPDATE
Regards
Alex
On Tue, Jan 15, 2019 at 9:42 AM Alexander Farber <alexander.farber@gmail.com> wrote: > When I am trying > > WITH diffs AS ( > SELECT > gid, > uid, > played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff > FROM moves > WHERE uid = 1 > ) > UPDATE users SET > avg_time = TO_CHAR(AVG(diff), 'HH24:MI') > FROM diffs; > > the syntax error is unfortunately printed by PostgreSQL 10: > > aggregate functions are not allowed in UPDATE So calculate the average somewhere else, put the result in a column, and then reference that column in the SET clause. Its also unusual to have FROM with UPDATE but no WHERE clause... David J.
Unfortunately, I don't understand your advice, David -
On Tue, Jan 15, 2019 at 5:46 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 15, 2019 at 9:42 AM Alexander Farber
<alexander.farber@gmail.com> wrote:
> When I am trying
>
> WITH diffs AS (
> SELECT
> gid,
> uid,
> played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
> FROM moves
> WHERE uid = 1
> )
> UPDATE users SET
> avg_time = TO_CHAR(AVG(diff), 'HH24:MI')
> FROM diffs;
>
> the syntax error is unfortunately printed by PostgreSQL 10:
>
> aggregate functions are not allowed in UPDATE
So calculate the average somewhere else, put the result in a column,
and then reference that column in the SET clause.
do you suggest to add a second CTE?
Regards
Alex
On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber <alexander.farber@gmail.com> wrote: >> So calculate the average somewhere else, put the result in a column, >> and then reference that column in the SET clause. >> > > do you suggest to add a second CTE? That would qualify as "somewhere else" - as would a simple subquery in FROM. David J.
Ahh, the subqueries -
On Tue, Jan 15, 2019 at 5:59 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber
<alexander.farber@gmail.com> wrote:
>> So calculate the average somewhere else, put the result in a column,
>> and then reference that column in the SET clause.
>
> do you suggest to add a second CTE?
That would qualify as "somewhere else" - as would a simple subquery in FROM.
they escaped my mind for some reason! :-)
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
WHERE uid = 1
)
UPDATE users SET avg_time =
(
SELECT
AVG(diff)
FROM diffs
GROUP BY uid
)
WHERE uid = 1;
seems to work, thank you
And I should better change the avg_time column from TEXT to TIMESTAMPTZ (and use TO_CHAR on it later down the road) so that I can compare my players
Regards
Alex
Last question please - how to run the query for all users?
I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL way?
How to refer to the outside "uid" from inside the CTE in the query below?
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
WHERE uid = how_to_refer_to_the_outside_uid
)
UPDATE users SET avg_time =
(
SELECT
AVG(diff)
FROM diffs
GROUP BY uid
)
WHERE uid IN (SELECT uid FROM users);
Regards
Alex
On Tue, Jan 15, 2019 at 12:42 PM Alexander Farber <alexander.farber@gmail.com> wrote: > > Last question please - how to run the query for all users? > > I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL way? > > How to refer to the outside "uid" from inside the CTE in the query below? > > WITH diffs AS ( > SELECT > gid, > uid, > played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff > FROM moves > WHERE uid = how_to_refer_to_the_outside_uid > ) > UPDATE users SET avg_time = > ( > SELECT > AVG(diff) > FROM diffs > GROUP BY uid > ) > WHERE uid IN (SELECT uid FROM users); UPDATE users SET avg_time = diffs.average_time_for_the_grouped_by_user FROM diffs WHERE users.uid = diffs.uid --< the missing "where" I commented about earlier But you need to construct the "diffs" CTE/subquery so that it Group[s] By uid David J.
Thank you, the following seems to have worked -
On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> UPDATE users
> SET avg_time = diffs.average_time_for_the_grouped_by_user
> FROM diffs
> WHERE users.uid = diffs.uid --< the missing "where" I commented about earlier
>
> But you need to construct the "diffs" CTE/subquery so that it Group[s] By uid
>
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
),
avg_diffs AS (
SELECT uid, AVG(diff) as avg_diff FROM diffs GROUP BY uid
)
UPDATE users SET avg_time = avg_diff
FROM avg_diffs
WHERE users.uid = avg_diffs.uid;
https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/9
Or did you mean something else?
Regards
Alex
On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
>
> UPDATE users
> SET avg_time = diffs.average_time_for_the_grouped_by_user
> FROM diffs
> WHERE users.uid = diffs.uid --< the missing "where" I commented about earlier
>
> But you need to construct the "diffs" CTE/subquery so that it Group[s] By uid
>
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
),
avg_diffs AS (
SELECT uid, AVG(diff) as avg_diff FROM diffs GROUP BY uid
)
UPDATE users SET avg_time = avg_diff
FROM avg_diffs
WHERE users.uid = avg_diffs.uid;
https://www.db-fiddle.com/f/w1AYGpoZiWW9bLCYjHDk7H/9
Or did you mean something else?
Regards
Alex