Re: an difficult SQL

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
>>
>>