StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The correct way would be to use a window over <code>RANGE (INTERVAL '6' MONTH) PRECEDING</code> but unfortunately PostgreSQL doesn't support bounded <code>RANGE</code> windows yet so the query will fail:</p> <pre><code>regress=> SELECT month_d, user_id, SUM(cnt) OVER (PARTITION BY user_id ORDER BY month_d RANGE INTERVAL '6' MONTH PRECEDING) FROM cnts ORDER BY 2,1; ERROR: RANGE PRECEDING is only supported with UNBOUNDED LINE 1: ...(cnt) OVER (PARTITION BY user_id ORDER BY month_d RANGE INTE... </code></pre> <p>Without that you're going to be back with a join over <code>generate_series</code>, and doing that over multiple user ids cumbersome. I suspect your self-join approach is significantly preferable to attempting to do this with a <code>ROWS</code> based window over <code>sum</code>. You'd have to cross join a <code>generate_series</code> of the entire date range with the set of all distinct uids, then left outer join that against the <code>cnts</code> table, process that with <code>sum</code> over a window, and then filter out rows with null counts. Needless to say, this is a more tortured way of doing things than a simple self-join.</p> <hr> <p>For your sample data the following query will produce the same result you have shown above:</p> <pre><code>-- This query is totally wrong and only works because of overly simple sample data SELECT month_d, user_id, SUM(cnt) OVER (PARTITION BY user_id ORDER BY month_d ROWS 1 PRECEDING) FROM cnts ORDER BY 2,1; </code></pre> <p>However, it's totally wrong. I'm showing it primarily to illustrate that the sample data isn't good enough for solid testing, since the results match essentially by sheer luck. None of your samples have more than two samples within a six month range. Sample data is great, but you need to think about the corner cases, just like when you're writing unit tests. You should have uids that don't start and stop at the same dates, with different counts, etc.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload