Re: Problems with PARTITION BY with count() in window-func

På onsdag 08. november 2017 kl. 11:17:39, skrev Andreas Joseph Krogh <
andreas(at)visena(dot)com <mailto:andreas(at)visena(dot)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_window AS total_for_month FROM log_entry log
WHERE1 = 1 AND date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE)
BETWEEN'2017-01-01'::DATE AND '2017-05-01'::DATE GROUP BY month, log.activity_id
WINDOWtotal_for_month_window AS (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_id ASC ;
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 bydate_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_entry
logORDER 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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>