postgresql while/loop function

Question

is there a way to replace/simplified the following code with while/loop function? I need to loop it while items < specific number or variable. Thank you!

select 
count(distinct wallet) filter (where items >= 3 and items < 6) + 
count(distinct wallet) filter (where items >= 6 and items < 9) + 
count(distinct wallet) filter (where items >= 9 and items < 12) +
count(distinct wallet) filter (where items >= 12 and items < 15) as total_items

from 
agg
where items > 0

Doesn't this just simplify to `select count(distinct wallet) filter (where items >= 3 and items < 15) as total_items`? — Bergi, Aug 11 '21 at 20:56
Oh, right, you need the `distinct` per group, which would sum to a different count than counting all wallets that fall anywhere in the range. — Bergi, Aug 11 '21 at 21:12

score 0 · Accepted Answer · answered Aug 11 '21 at 21:18

0

No, there is no way to loop this. (Well, there is, but it's overcomplicated and probably inefficient).

You can however just use maths to reduce the items to one canonical element per group, and then count the distinct tuples consisting of the wallet and the respective canonical element:

select count(distinct (wallet, items/3)) as total_items
from agg
where items >= 3 and items < 15

answered Aug 11 '21 at 21:18

Bergi

630,263
148
957
1,375

Thank you! That worked exactly as I need! – Mikhail Ryabov Aug 12 '21 at 12:41

score 0 · Answer 2 · answered Aug 11 '21 at 23:04

You can write a function for this as below:

create or replace function dist_items(threshold integer, init_val integer, val_diff integer) returns integer as $$
        DECLARE
            start_pos integer;
            end_pos integer;
            terminator integer;
            temp integer default 0;
            res integer default 0;
        BEGIN
                IF init_val > threshold
                THEN
                    RETURN 0;
                END IF;
                terminator:= 0;
                start_pos:= init_val;
                end_pos := start_pos + val_diff;
                LOOP
                EXIT WHEN terminator = 1;                
                    SELECT count(distinct wallet) filter (where items >= start_pos and items < end_pos) into temp from agg where items > 0;
                    res := res + temp;
                    start_pos := start_pos + val_diff;
                    end_pos := start_pos + val_diff;
                    IF (start_pos >= threshold)
                    THEN
                        terminator:= 1;
                    END IF;
                    IF (end_pos > threshold)
                    THEN
                        end_pos:= threshold;
                    END IF;
                END LOOP;
                RETURN res;
        END;
$$ LANGUAGE plpgsql;

postgresql while/loop function

2 Answers2

Linked