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
