agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: MS (direkt) <martin.stoecker@stb-datenservice.de>
To: pgsql-sql@postgresql.org
Subject: Re: Problems with PARTITION BY with count() in window-func
Date: Wed, 8 Nov 2017 15:51:05 +0100
Message-ID: <41eca9c2-e777-9084-8665-d9d6fda582a1@stb-datenservice.de> (raw)
In-Reply-To: <VisenaEmail.47.ec56caa56a402a62.15f9bdc4735@tc7-visena>
References: <VisenaEmail.47.ec56caa56a402a62.15f9bdc4735@tc7-visena>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
a solution without distinct:
select * from (
select
date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE)
AS month,
log.activity_id,
count(log.entity_id) over (partition by date_trunc('month',
log.start_date::TIMESTAMP WITHOUT TIME ZONE), activity_id),
count(log.entity_id) over (partition by date_trunc('month',
log.start_date::TIMESTAMP WITHOUT TIME ZONE))
FROM
log_entry log
order by 1,2) as x
group by 1,2,3,4
order by 1,2;
If one think of introducing new column i prefer using grouping sets and
rollup.
If you do so, an enclosing query will help to filter just the subtotals
you need.
Regards, Martin
Am 08.11.2017 um 14:50 schrieb Andreas Joseph Krogh:
> På onsdag 08. november 2017 kl. 11:17:39, skrev Andreas Joseph Krogh
> <andreas@visena.com <mailto:andreas@visena.com>>:
>
> Hi all.
> I'm trying to count() all log-entries per activity per month in a
> separate column using count() over(partition by ...) but get an
> error I don't understand.
> [snip]
>
> select date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE)AS month ,log.activity_id
> ,count(log.entity_id)AS num_logs
> ,count(log.entity_id)OVER total_for_month_windowAS total_for_month
> FROM log_entrylog WHERE 1 =1 AND date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE)BETWEEN '2017-01-01'::DATEAND '2017-05-01'::DATE
> GROUP BY month,log.activity_id
> WINDOW total_for_month_windowAS (PARTITION BY date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE))
>
> ORDER BY date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE)ASC ;
>
> Note the missing log.entity_id in GROUP BY.
> but this gives the error:
> [42803] column "log.entity_id" must appear in the GROUP BY clause
> or be used in an aggregate function
> Details
>
> This query gives the desired results:
> SELECT DISTINCT date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE)AS month ,log.activity_id
> ,count(log.entity_id)OVER(partition by date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE),log.activity_id)AS num_logs_per_activity
> ,count(log.entity_id)OVER (partition by date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE))AS total_for_month
> FROM log_entrylog ORDER BY date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE)ASC,log.activity_idASC ;
> month activity_id num_logs_per_activity total_for_month
> 2017-01-01 00:00:00.000000 1 2 4
> 2017-01-01 00:00:00.000000 2 2 4
> 2017-02-01 00:00:00.000000 1 5 6
> 2017-02-01 00:00:00.000000 2 1 6
>
> But I'd like a solution without the DISTINCT, if one exists?
> If I introduce a new column, logged_for, and want to list number of
> logged entries per person per activity I can use this:
> SELECT DISTINCT date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE)AS month ,log.logged_for
> ,log.activity_id
> ,count(log.entity_id)OVER(partition by date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE),log.logged_for,log.activity_id)AS num_logs_per_person_for_activity
> ,count(log.entity_id)OVER (partition by date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE),log.logged_for)AS total_for_person_for_month
> FROM log_entrylog ORDER BY date_trunc('month',log.start_date::TIMESTAMP WITHOUT TIME ZONE)ASC ;
> Which gives:
> month logged_for activity_id num_logs_per_person_for_activity
> total_for_person_for_month
> 2017-01-01 00:00:00.000000 5 1 2 4
> 2017-01-01 00:00:00.000000 5 2 2 4
> 2017-01-01 00:00:00.000000 6 1 2 4
> 2017-01-01 00:00:00.000000 6 2 2 4
> 2017-02-01 00:00:00.000000 5 1 5 6
> 2017-02-01 00:00:00.000000 5 2 1 6
> 2017-02-01 00:00:00.000000 6 1 5 6
> 2017-02-01 00:00:00.000000 6 2 1 6
>
> Is this the recommended way to do this, I mean - having "group by" in
> the "partition by" belonging to the OVER()-clause of the count-aggregates?
> Thanks.
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com;
> <https://www.visena.com;
--
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
Attachments:
[image/png] ihkhhplfhmpfbhho.png (1.9K, ../41eca9c2-e777-9084-8665-d9d6fda582a1@stb-datenservice.de/3-ihkhhplfhmpfbhho.png)
download | view image
view thread (3+ messages)
Message-ID: <41eca9c2-e777-9084-8665-d9d6fda582a1@stb-datenservice.de>
Permalink: ../41eca9c2-e777-9084-8665-d9d6fda582a1@stb-datenservice.de/
Also on: postgresql.org/message-id/41eca9c2-e777-9084-8665-d9d6fda582a1@stb-datenservice.de
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: martin.stoecker@stb-datenservice.de
Subject: Re: Problems with PARTITION BY with count() in window-func
In-Reply-To: <41eca9c2-e777-9084-8665-d9d6fda582a1@stb-datenservice.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox