Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eCRiJ-0000Uf-CP for pgsql-sql@arkaria.postgresql.org; Wed, 08 Nov 2017 14:52:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1eCRiI-0002kp-U5 for pgsql-sql@arkaria.postgresql.org; Wed, 08 Nov 2017 14:52:22 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eCRhE-0000vz-KP for pgsql-sql@postgresql.org; Wed, 08 Nov 2017 14:51:16 +0000 Received: from mail.inqbus.de ([2a00:b6c0:1108::1c8c]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1eCRhA-0001cn-TT for pgsql-sql@postgresql.org; Wed, 08 Nov 2017 14:51:15 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=stb-datenservice.de; s=20160215; h=Content-Type:In-Reply-To:MIME-Version: Date:Message-ID:From:References:To:Subject:Sender:Reply-To:Cc: Content-Transfer-Encoding:Content-ID:Content-Description:Resent-Date: Resent-From:Resent-Sender:Resent-To:Resent-Cc:Resent-Message-ID:List-Id: List-Help:List-Unsubscribe:List-Subscribe:List-Post:List-Owner:List-Archive; bh=DMiqy+7Um7KgOQHTCWCVw+J6bRIDApD/BlpeV6U+hto=; b=GoddmHiRT+QgGpi4f6ZkcR+bR OJCL1V4M1FnKEbSxfBaavg487391qQuOYG5pm0ezO1JcXZ4XsCs7WPBPSYVig4UL9NrM+GOA3wsXK HSAZPCXWTbCC0Z7cYFNowdZFPmZAcXCU3poyHSj7hbvjQ2s2vw6wS5xDu1lR0spPQemA0=; Received: from ip9234fcfc.dynamic.kabel-deutschland.de ([146.52.252.252]:57762 helo=[192.168.178.254]) by mail.inqbus.de with esmtpa (Exim 4.88) (envelope-from ) id 1eCRh5-0004BP-7M for pgsql-sql@postgresql.org; Wed, 08 Nov 2017 15:51:09 +0100 Subject: Re: Problems with PARTITION BY with count() in window-func To: pgsql-sql@postgresql.org References: From: "MS (direkt)" Message-ID: <41eca9c2-e777-9084-8665-d9d6fda582a1@stb-datenservice.de> Date: Wed, 8 Nov 2017 15:51:05 +0100 User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101 Thunderbird/52.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/alternative; boundary="------------95E4FDC8DA47209E691C7454" Content-Language: de List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------95E4FDC8DA47209E691C7454 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit 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 > >: > > 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 > 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 --------------95E4FDC8DA47209E691C7454 Content-Type: multipart/related; boundary="------------FB2B5533C55E77A19E54BB19" --------------FB2B5533C55E77A19E54BB19 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit 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>:
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
WHERE 1 = 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
WINDOW total_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_entry log
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 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_entry log
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
 

-- 
Widdersdorfer Str. 415, 50933 Köln; Tel. +49 / 221 / 9544 010
HRB Köln HRB 75439, Geschäftsführer: S. Böhland, S. Rosenbauer
--------------FB2B5533C55E77A19E54BB19 Content-Type: image/png; name="ihkhhplfhmpfbhho.png" Content-Transfer-Encoding: base64 Content-ID: Content-Disposition: inline; filename="ihkhhplfhmpfbhho.png" iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJ REFUaEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFd Qc4VJP/HADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/ NPtk9uub7Gfdt5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3s aQaHt7fY70X949OnaS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8Wf Ci80Xgf9vVlrhg9yRONe/f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/ n7YPmuTETzIab4Y9ZWCrKexd8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde 7cXj7KRk4LsqDxQzCYeGsJNgaXZe+JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O 6x1IvtD40GWyJYYdkF0jIbYZlN16xygIgl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk7 9DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZkQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6 beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMHQPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ 46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aYhdiCnRC7jaj7eiK2ETInC5PRF6LY kSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhuEhRUJ2U2g6UlugyNb03Hl9ob HwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2QXI6C1sIHDUFhH4Hij4Ub Iev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8UBIYhBd6oSUgYMijO kecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6OkjzxJO5afGe6kmhB icWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBXQmTYZZNh 14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3kwM5 9oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcP c4rVNjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kS NtnKiKIlUfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4 cVmgzKt9j5gNJ/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCW qBQ9nCjJ5neivC82Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3 TpGRpk0Tzpj3ZEJXi/GRa6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqp HLu9jBrhUuhNwVGbpyZrzrl0HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtd Go+fVnzycUX5wmn6eCKPmWYJG2E/ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T 5j9EyxffCFOYi2wUlPyFFDQAhehESDjQGoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnH qcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB99CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9 ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXmfud08FiTeC+m2/o7ppo+PTS/NBK5ARpD G5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofssxBA1gj5NXGvfJu/Qh7HwQh/VDUE yUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+olK0A1EKRCE4snsh42w8Mv/Z hxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIzYDz+AbfD37GtbaoSKzgG t+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuuhuC4pCGGx+YD2fcc 5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3CHU2mTMwZ+B/ y8DfSMBLLOYXVuEAAAAASUVORK5CYII= --------------FB2B5533C55E77A19E54BB19-- --------------95E4FDC8DA47209E691C7454--