StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>WITH q AS ( SELECT ( SELECT MIN(start_date) FROM mytable ) + level - 1 AS mydate FROM dual CONNECT BY level <= ( SELECT MAX(end_date) - MIN(start_date) FROM mytable ) ) SELECT group, mydate, ( SELECT COUNT(*) FROM mytable mi WHERE mi.group = mo.group AND q BETWEEN mi.start_date AND mi.end_date ) FROM q CROSS JOIN ( SELECT DISTINCT group FROM mytable ) mo </code></pre> <p><strong>Update:</strong></p> <p>A better and faster query making use of analytic functions.</p> <p>The main idea is that the number of ranges containing each date is the difference before the count of ranges started before that date and the count of ranges that ended before it.</p> <pre><code>SELECT cur_date, grouper, SUM(COALESCE(scnt, 0) - COALESCE(ecnt, 0)) OVER (PARTITION BY grouper ORDER BY cur_date) AS ranges FROM ( SELECT ( SELECT MIN(start_date) FROM t_range ) + level - 1 AS cur_date FROM dual CONNECT BY level <= ( SELECT MAX(end_date) FROM t_range ) - ( SELECT MIN(start_date) FROM t_range ) + 1 ) dates CROSS JOIN ( SELECT DISTINCT grouper AS grouper FROM t_range ) groups LEFT JOIN ( SELECT grouper AS sgrp, start_date, COUNT(*) AS scnt FROM t_range GROUP BY grouper, start_date ) starts ON sgrp = grouper AND start_date = cur_date LEFT JOIN ( SELECT grouper AS egrp, end_date, COUNT(*) AS ecnt FROM t_range GROUP BY grouper, end_date ) ends ON egrp = grouper AND end_date = cur_date - 1 ORDER BY grouper, cur_date </code></pre> <p>This query completes in <code>1</code> second on <code>1,000,000</code> rows.</p> <p>See this entry in my blog for more detail:</p> <ul> <li><a href="http://explainextended.com/2009/09/09/oracle-generating-a-list-of-dates-and-counting-ranges-for-each-date/" rel="nofollow noreferrer"><strong><code>Oracle</code>: generating a list of dates and counting ranges for each date</strong></a></li> </ul>

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