Trying to fetch records only if preceded by at least another one

Поиск
Список
Период
Сортировка
От Alexander Farber
Тема Trying to fetch records only if preceded by at least another one
Дата
Msg-id CAADeyWg1RQcfzYzvAKC9kM=YKmNZeHq0vmy5OYvbyckembJY4Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Trying to fetch records only if preceded by at least another one
Список pgsql-general
Good evening,

for a word game hosted on PostgreSQL 10 I try to find interesting player moves (high score or played all 7 tiles) and generate a "puzzle" images out of them (example: https://imgur.com/a/StnXqoR )
The moves are stored in:
words_ru=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |                 Default                  
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null | nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 letters | text                     |           |          |
 hand    | text                     |           |          |
 puzzle  | boolean                  |           | not null | false
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_action_played_idx" btree (uid, action, played)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE

So I try to find 5 most old interesting moves and it works:

words_ru=> select  
m.mid,   -- interesting move id
m.gid,  
s.word,  
m.tiles
from words_moves m
left join words_scores s using (mid)                                                                                 
where m.action='play'                                                                  
and length(m.letters)=7                                                    
and length(m.hand)=7                                                     
and length(s.word)=7                                                                    
order by m.played asc                                                                                       
limit 5;

-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mid   | 77461
gid   | 1048
word  | СОПЕНИЕ
tiles | [{"col": 7, "row": 1, "value": 2, "letter": "С"}, {"col": 7, "row": 2, "value": 1, "letter": "О"}, {"col": 7, "row": 3, "value": 2, "letter": "П"}, {"col": 7, "row": 4, "value": 1, "letter": "Е"}, {"col": 7, "row": 5, "value": 1, "letter": "Н"}, {"col": 7, "row": 6, "value": 1, "letter": "И"}, {"col": 7, "row": 7, "value": 1, "letter": "Е"}]
-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
mid   | 78406
gid   | 5702
word  | СОПИЛКА
tiles | [{"col": 5, "row": 14, "value": 2, "letter": "С"}, {"col": 6, "row": 14, "value": 1, "letter": "О"}, {"col": 7, "row": 14, "value": 2, "letter": "П"}, {"col": 8, "row": 14, "value": 1, "letter": "И"}, {"col": 9, "row": 14, "value": 2, "letter": "Л"}, {"col": 10, "row": 14, "value": 2, "letter": "К"}, {"col": 11, "row": 14, "value": 1, "letter": "А"}]

However there is one problem: I only want to fetch those "interesting" moves which are preceded by at least another one move in the same game - because otherwise the game board is empty and the puzzle is boring.

So I have tried to add a JOIN LATERAL:

select                                                                                                                                             
m.mid,     -- interesting move id
m2.mid,   -- preceding move id in the same game id
m.gid,  
s.word,  
m.tiles                                                                                                                                              
from words_moves m                                                                                    
left join words_scores s using (mid)                                                                                                     
join lateral (SELECT * FROM words_moves WHERE gid=m.gid and mid < m.mid) AS m2 ON TRUE 
where m.action='play'   
and length(m.letters)=7                                                                           
and length(m.hand)=7                                                                      
and length(s.word)=7                                                                                   
order by m.played asc                                                                         
limit 5;

But it prints too many records: all moves cross-multiplied with each other.

As if I have forgotten to add 1 more condition to the JOIN LATERAL

Do you please have any ideas here? (I hope my context is not too confusing :-)

Regards
Alex

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: I think that my data is saved correctly, but when printing again, other data appears
Следующее
От: Jehan-Guillaume de Rorthais
Дата:
Сообщение: Re: logical replication - negative bitmapset member not allowed