Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJkF0-0004jg-Vf for pgsql-sql@arkaria.postgresql.org; Tue, 28 Nov 2017 18:04:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eJkF0-0001eN-HY for pgsql-sql@arkaria.postgresql.org; Tue, 28 Nov 2017 18:04:18 +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 1eJkF0-0001eE-3d for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 18:04:18 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eJkEo-0002RL-99 for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 18:04:16 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=MoZv4NJmR9Ibjeukp0UMHMwA/tTwGk5HcIJXMwnmNeg=; b=L6P6XKaIfKB5DjlrpR0EJj3U5Mhc8wZghGESbcYx37aIdUOaot2DYmPqAp0uQQMlw6BM1EUWdnsS0BstsHlcEOjR16sibGXPTfrDIvfQw5SI45a9CEJk6llfWOGgLT9fhxCcJzRr8054+5ndjxYHNcrI2B6M4n2p6l47WFdbO6M=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1eJkEe-0008Rm-G8 for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 19:04:03 +0100 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.86_2) (envelope-from ) id 1eJkED-0005Z5-Rz for pgsql-sql@lists.postgresql.org; Tue, 28 Nov 2017 19:03:29 +0100 Date: Tue, 28 Nov 2017 19:03:29 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-sql@lists.postgresql.org Message-ID: In-Reply-To: Subject: Sv: Not counting duplicates of declared pratition in OVER()-clause MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_1000_776495068.1511892209706" X-Mailer: Visena Mail 2.1.0-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1,HTML_MESSAGE=0.001) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1000_776495068.1511892209706 Content-Type: multipart/related; boundary="----=_Part_1001_1038017085.1511892209715" ------=_Part_1001_1038017085.1511892209715 Content-Type: multipart/alternative; boundary="----=_Part_1002_36322694.1511892209737" ------=_Part_1002_36322694.1511892209737 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 tirsdag 28. november 2017 kl. 18:54:47, skrev Andreas Joseph Krogh = < andreas@visena.com >: Hi. =C2=A0 I'm trying to prevent duplicate values from being part of SUM(). =C2=A0 (complete schema with INSERTs below) =C2=A0 I have this query to count all log-entries per activity per month in a=20 sub-query, then adding a value from another table in the outer query, which= I'd=20 then like to sum but only count values from the same month once: =C2=A0 SELECT info.*, stuff.value + info.total_for_month AS new_value , SUM (stuff.value +info.total_for_month) OVER () AS total_new_value_sum FROM (= =20 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_tr= unc( 'month', log.start_date::TIMESTAMP WITHOUT TIME ZONE), log.activity_id) AS= =20 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 F= ROM=20 log_entrylog ORDER BY date_trunc('month', log.start_date::TIMESTAMP WITHOU= T=20 TIME ZONE) ASC, log.activity_id ASC ) AS info LEFT OUTER JOIN stuff ON inf= o .month =3D stuff.month ;=20 =C2=A0 It seems I forgot to turn my brain on, sorry. =C2=A0 This query gives me what I want (using row_number() and an outer query with= =20 FILTER on rownum=3D1): =C2=A0 SELECT q.* , SUM(q.new_value) FILTER (WHERE q.rownum =3D 1) OVER() AS=20 total_new_value_sumFROM ( SELECT info.*, stuff.value + info.total_for_month= AS=20 new_value ,row_number() OVER (partition by info.month) as rownum FROM ( SEL= ECT=20 DISTINCT date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIME ZONE) A= S=20 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= =20 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 F= ROM=20 log_entrylog ORDER BY date_trunc('month', log.start_date::TIMESTAMP WITHOU= T=20 TIME ZONE) ASC, log.activity_id ASC ) AS info LEFT OUTER JOIN stuff ON inf= o .month =3D stuff.month ) q ;=20 =C2=A0 Gives: month activity_id num_logs_per_activity total_for_month new_value rownum=20 total_new_value_sum 2017-01-01 00:00:00.000000 1 4 8 30 1 141 2017-01-01=20 00:00:00.000000 2 4 8 30 2 141 2017-02-01 00:00:00.000000 1 10 12 111 1 141= =20 2017-02-01 00:00:00.000000 2 2 12 111 2 141 2017-03-01 00:00:00.000000 1 1 = 1=20 NULL 1 141=20 =C2=A0 -- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andreas@visena.com www.visena.com =C2=A0 ------=_Part_1002_36322694.1511892209737 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 tirsdag 28. november 2017 kl. 18:54:47, skrev Andreas Joseph K= rogh <andreas@visena.com>:<= /div>
Hi.
=C2=A0
I'm trying to prevent duplicate values from being part of SUM().
=C2=A0
(complete schema with INSERTs below)
=C2=A0
I have this query to count all log-entries per activity per month in a= sub-query, then adding a value from another table in the outer query, whic= h I'd then like to sum but only count values from the same month once:
=C2=A0
SELECT info.*, stuff.value + AS new_value
    , SUM(stuff.value + info.total_for_mon=
th) OVER (=
) AS total_new_value_sum
FROM (
         SELECT D=
ISTINCT
          =
     date_trunc('month', log.start_date::TIMESTAMP WITHOUT TIM=
E ZONE) AS=
 month
          =
   , log.activity_id
             , c=
ount(log.entity_id) =
OVER(parti=
tion by date_trunc('month', log.start_date::<=
span style=3D"color: rgb(0, 0, 128); font-weight: bold;">TIMESTAMP WITHOUT =
TIME ZONE), AS num_logs_per_activity
             , c=
ount(log.entity_id) =
OVER (part=
ition by date_trunc('month', log.start_date::=
TIMESTAMP WITHOUT=
 TIME ZONE)) AS total_for_month
         FROM
          =
   log_entry log
         O=
RDER BY date_trunc('month', log.start_date::<=
span style=3D"color: rgb(0, 0, 128); font-weight: bold;">TIMESTAMP WITHOUT =
TIME ZONE) ASC, log<=
/span>.activity_id      ) AS info
    LEFT O=
UTER JOIN stuff ON info.month =3D stuff.month
;
=C2=A0
It seems I forgot to turn my brain on, sorry.
=C2=A0
This query gives me what I want (using row_number() and an outer query= with FILTER on rownum=3D1):
=C2=A0
SELECT q.*
    , SUM(q.new_value) FILTER (WHERE q.rownum =3D 1) OVER() AS total_new_va=
lue_sum
FROM (
SELECT info.*, stuff=
.value + info.total_=
for_month AS new_val=
ue
    , row_number() OVER (partition by info.month) as rownum
FROM (
         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 WITH=
OUT TIME ZONE), log<=
/span>.activity_id) AS num_logs_per_activity
             , count(log.entity_id) OVER (partition by date_trunc('month', log.start_date::TIMESTAMP WIT=
HOUT 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
     ) AS info
    LEFT OUTER JOIN =
stuff ON info=
.month =3D stuff.month

     ) q
;
=C2=A0
Gives:
=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09=09 =09=09 =09
monthactivity_idnum_logs_per_activitytotal_for_monthnew_valuerownumtotal_new_value_sum
2017-01-01 00:00:00.000000148301141
2017-01-01 00:00:00.000000248302141
2017-02-01 00:00:00.000000110121111141
2017-02-01 00:00:00.00000022121112141
2017-03-01 00:00:00.000000111NULL1141
=C2=A0
--
Andrea= s Joseph Krogh
CTO / Partner<= /span> - Visena AS
Mobile: +47 90= 9 56 963
=3D""
=C2=A0
------=_Part_1002_36322694.1511892209737-- ------=_Part_1001_1038017085.1511892209715 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= ------=_Part_1001_1038017085.1511892209715-- ------=_Part_1000_776495068.1511892209706--