From: | Laura Smith <n5d9xq3ti233xiyif2vp(at)protonmail(dot)ch> |
---|---|
To: | postgre <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Conditional return of aggregated data |
Date: | 2019-12-02 11:10:44 |
Message-ID: | huts4iYHSGpV-pwvHgoN-X8_PwFIFr-j7wbWueEXrs2Du5bKHLE6lik-8y-s6DBKkhAZr-gGGdbadVf3g9uHTf3CB5qX4SJDfD8ezTKLfM8=@protonmail.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I have some aggregated statistics which are currently being queried as follows:
create view stats_age as
SELECT a.category,
a.statcount,
b.a_desc
FROM reg_aggregated_stats a,regs_select_age b where a.category='age' and a.datapoint::smallint=b.a_val order by a.datapoint asc;
However, as these relate to event registrations, a suggestion has been made that the database really should be returning nothing until a certain number of registrations has been met (in order to avoid privacy infringing inferrence from what should be an otherwise fully irreversibly aggregated dataset).
Now, the queries are wrapped in PL/PGSQL functions anyway, so I could make a second call to Postgres to find out sum(statcount) and then conditionally return based on that.
But is there a smarter way to do this out of a single SQL query ?
My initial idea was something along the lines of :
select (select sum(statcount) from stats_residence) as aggstat,statcount,short_name_en from stats_residence where aggstat>some_number;
But as I soon discovered that's not valid syntax! Hence ideas welcome from those smarter than me.
Thanks !
Laura
From | Date | Subject | |
---|---|---|---|
Next Message | Wim Bertels | 2019-12-02 11:27:42 | Re: Conditional return of aggregated data |
Previous Message | Ajay Pratap | 2019-12-02 06:52:13 | pgbackrest concerns and doubts. |