Good day. There is such a structure:

id | date 1 | 01.01.2017 2 | 01.01.2017 1 | 02.01.2017 3 | 02.01.2017 4 | 03.01.2017 1 | 03.01.2017 2 | 04.01.2017 5 | 04.01.2017 5 | 05.01.2017 2 | 05.01.2017 .......

It is necessary to obtain consolidated data with cumulative totals, namely:

 date | count 01.01.2017 | 2 02.01.2017 | 3 03.01.2017 | 4 04.01.2017 | 5 05.01.2017 | 5
T.e. for each unique date it is necessary to take the sum of unique ID with a cumulative result. I know how to do this by writing a function. But brute force to do this task is not true. How can I do this in one request?

    2 answers 2

    The variant with window functions, the estimated cost of postgresql 100 versus 20,000 in the variant with an explicit count of all lines from the beginning to each date:

     select date, max(cnt) from ( select A.date, count(B.id) over(order by A.date) cnt from (select distinct date from tdtab) A left join ( select id, min(date) date from tdtab group by id ) B on B.date=A.date ) X group by date

    The execution time of both variants is certainly worth comparing with real data.

    • Great, what you need. Thank you - User
     select d.date, count(distinct t.id) from (select distinct date date from t) d join t on t.date<=d.date group by d.date;
    • I have given in the description of the structure that must be obtained. It contains not only the number of elements, but also the date field. Your query returns only the number of elements, and without a cumulative total. - User
    • Cumulative total viewed, yes. The answer is updated. Well, the date could themselves have added;) - Zufir
    • The task does not match the example. First, there is a count in the subject, the field in the example is similarly named - but according to the text of the question "for each unique date you must take the sum of unique IDs". So quantity or amount? Secondly, for the date 04/01/2017, the example shows the desired total count = 5. There are two entries in the source data for this date, with id = 2 and id = 5. The resulting 5 can only be obtained by the requirement to return the maximum by id, which does not correspond to either count or amount. I think we need to check the wording of the question more carefully ... - Akina
    • 5 for 4.01 is the number of unique id up to this date inclusive. - Zufir
    • Wow, damn it ... but what could not immediately formulate that? - Akina