Is it possible to create a custom order by function in postgres that alternate group of rows of specified dimensions?

I have this table “clients” with (id, name, offer_type) where offer_type can be 0, 1, 2 or 3. Wrokin’ with Rails, I’m trying to transport an algorithm from ruby to Postgres SQL ( due to performance reasons ) that first shuffles the clients and then orders them based on offer_type in this order:
4 clients with offer_type = 1;
3 clients with offer_type = 2;
2 clients with offer_type = 3;
1 clients with offer_type = 0;
4 clients with offer_type = 1;
3 clients with offer_type = 2;
2 clients with offer_type = 3;
1 clients with offer_type = 0;
4 clients with offer_type = 1;
3 clients with offer_type = 2;
…
And so on until i’ve ended every record.
Huge amount of rows to perform well in both memory and execution time.
I’m honestly having kinda a hard time tryin’ to figure it out, for the “shuffle” part I used:
SELECT * FROM clients ORDER BY random();
So about that I’m covered… For the actual ordering by offer_type I’m off sea, I’ve tried something found on this page https://www.gab.lc/articles/order_by_custom_list/ but it didn’t actually help,’cause I ended having them sorted by blocks of clients with the same offer_type.
I was hoping someone could direct me to a page where it’s explained something similar or if someone has some suggestion.

Answer

I think that you can do this with row_number() and integer division:

select c.*
from clients c
inner join (values(1, 4), (2, 3), (3, 2), (0, 1)) m(offer_type, seq)
    on m.offer_type = c.offer_type
order by
    (row_number() over(partition by c.offer_type order by random()) - 1) / m.seq,
    m.seq desc

Advertisement

Answer