agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@lists.postgresql.org
Subject: Sv: Not counting duplicates of declared pratition in OVER()-clause
Date: Tue, 28 Nov 2017 19:03:29 +0100 (CET)
Message-ID: <VisenaEmail.e2.91a23a9fffcf3cd3.16003ca4629@tc7-visena> (raw)
In-Reply-To: <VisenaEmail.e1.e756ac5c250cdc14.16003bb2e74@tc7-visena>
På tirsdag 28. november 2017 kl. 18:54:47, skrev Andreas Joseph Krogh <
andreas@visena.com <mailto:andreas@visena.com>>:
Hi.
I'm trying to prevent duplicate values from being part of SUM().
(complete schema with INSERTs below)
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, which I'd
then like to sum but only count values from the same month once:
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 (
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 ) AS info LEFT OUTER JOIN stuff ON info
.month = stuff.month ;
It seems I forgot to turn my brain on, sorry.
This query gives me what I want (using row_number() and an outer query with
FILTER on rownum=1):
SELECT q.* , SUM(q.new_value) FILTER (WHERE q.rownum = 1) OVER() AS
total_new_value_sumFROM ( SELECT info.*, stuff.value + info.total_for_month AS
new_value ,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 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 ) AS info LEFT OUTER JOIN stuff ON info
.month = stuff.month ) q ;
Gives:
month activity_id num_logs_per_activity total_for_month new_value rownum
total_new_value_sum 2017-01-01 00:00:00.000000 1 4 8 30 1 141 2017-01-01
00:00:00.000000 2 4 8 30 2 141 2017-02-01 00:00:00.000000 1 10 12 111 1 141
2017-02-01 00:00:00.000000 2 2 12 111 2 141 2017-03-01 00:00:00.000000 1 1 1
NULL 1 141
-- 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;
Message-ID: <VisenaEmail.e2.91a23a9fffcf3cd3.16003ca4629@tc7-visena>
Permalink: ../VisenaEmail.e2.91a23a9fffcf3cd3.16003ca4629@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.e2.91a23a9fffcf3cd3.16003ca4629@tc7-visena
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: andreas@visena.com, pgsql-sql@lists.postgresql.org
Subject: Re: Sv: Not counting duplicates of declared pratition in OVER()-clause
In-Reply-To: <VisenaEmail.e2.91a23a9fffcf3cd3.16003ca4629@tc7-visena>
* 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