StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The key elements to an elegant solution are <a href="http://www.postgresql.org/docs/current/interactive/functions-srf.html" rel="nofollow"><code>generate_series()</code></a>, <a href="http://www.postgresql.org/docs/current/interactive/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT" rel="nofollow"><code>date_part()</code></a>, a <a href="http://www.postgresql.org/docs/current/interactive/queries-with.html" rel="nofollow">CTE</a>, <a href="http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-GROUPBY" rel="nofollow"><code>GROUP BY</code></a> and <a href="http://www.postgresql.org/docs/current/interactive/functions-aggregate.html" rel="nofollow"><code>count(*)</code></a>, <a href="http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-FROM" rel="nofollow"><code>LEFT JOIN</code></a> and finally: the <a href="http://www.postgresql.org/docs/current/interactive/tablefunc.html" rel="nofollow"><code>crosstab()</code></a> function (with 1 parameter) from the additional module <a href="http://www.postgresql.org/docs/current/interactive/tablefunc.html" rel="nofollow">tablefunc</a>. To install it, run <em>once</em> per database:</p> <pre><code>CREATE EXTENSION tablefunc; </code></pre> <p>Query:</p> <pre><code>SELECT * FROM crosstab($x$ WITH x AS ( SELECT date_part('day', date_time_of_task)::int AS d ,date_part('hour', date_time_of_task)::int AS h ,count(*)::int AS ct FROM tasks GROUP BY 1,2 ) SELECT d, h, ct FROM (SELECT generate_series(1,31) AS d, generate_series(0,23) AS h) t LEFT JOIN x USING (d,h) ORDER BY 1,2 $x$) AS orders( day int ,h8 int, h9 int, h10 int, h11 int, h12 int, h13 int, h14 int, h15 int ,h16 int, h17 int, h18 int); </code></pre> <p>Produces a matrix of days and hours with the count of tasks in each field like you describe.</p> <p>BTW: I used this aux. function to generate the target definition list:</p> <pre><code>SELECT 'day int, ' || string_agg (x, ', ') FROM (SELECT ('h' || generate_series(8,18) || ' int') AS x) a </code></pre>

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