Re: an difficult SQL - Mailing list pgsql-general

Hi Thiemo,

Thank you for suggestions.

W dniu 5.11.2022 o 19:05, Thiemo Kellner pisze:
> Hi Rafal
> 
> You first could select the three users with the most recent entries with 
> a windowing function 
> (https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS) 

surely I'm missing something crucial here:
select row_number() over w,* from eventlog where row_number() over w < 5 
  window w as (partition by user);
ERROR:  window functions are not allowed in WHERE

So I'm unable to pick a limited number of rows within the user 
"group-window" ranges.

Without that, I cannot proceed.

Any suggestions?

-R
PS: regarding "my sets background", yes I do "think in sets" ... as 
opposed to thinking "in functions" (like iterating procedures). I do 
prefer solutions based on set definitions.

putting it into a with query 
(https://www.postgresql.org/docs/15/sql-select.html), in following with 
queries I would select 2.1 to 2.3 with each a constant column with each 
a different value you later sort by. In a next with query you can select 
all the rest (except all 
https://www.postgresql.org/docs/15/sql-select.html#SQL-EXCEPT) the 
results of 2.1 to 2.3 for 2.4 also with the notorious sort column. In a 
last with query you can put together the partial results for 2.1 to 2.4 
with a union all 
(https://www.postgresql.org/docs/15/sql-select.html#SQL-UNION) and 
selecting sort by the sort column and the timestamp in the final select.
> 
> I do not know your background, however, sql is about data sets end it is 
> not always easy to get ones head around thinking in sets. I hope you 
> could follow my suggestions. It might not be the most efficient way but 
> should work.
> 
> Kind regards
> 
> Thiemo
> 
> 
> Am 05.11.22 um 16:10 schrieb Rafal Pietrak:
>> Hi Everybody,
>>
>> I was wondering if anybody here could help me cook up a query:
>>
>> 1. against a list of events (like an activity log in the database). 
>> The list is a single table: create table events (tm timestamp, user 
>> int, description text).
>>
>> 2. of which the output would be sorted in such a way, that:
>> 2.1 most recent event would "select" most recent events of that same 
>> user, and displayed in a group (of say 10) of them (in "tm" order).
>>
>> 2.2 going through the events back in time, first event of ANOTHER user 
>> selects next group, where (say 10) most recent events of that OTHER 
>> user is presented.
>>
>> 2.3 next most recent event of yet another user selects yet another 
>> group to display and this selection process goes on, up to a maximum 
>> of (say 20) users/groups-of-their-events.
>>
>> 2.4 after that, all other events are selected in tm order.
>>
>> This is to present most recent telephone activities grouped by most 
>> recent subscribers so that the dashboard doesn't get cluttered with 
>> information but allows for an overview of other activity of most 
>> recent users.
>>
>> I tend to think, that it's a problem for a window function ... but 
>> I've stumbled on the problem how to limit the window "frame" to just a 
>> few (say 10) events within the "window" and have all the rest returned 
>> as "tail" of the query.
>>
>> BTW: the eventlog table is big. (and partitioned).
>>
>> Any help appreciated.
>>
>> -R
>>
>>