https://homeproj.cesnet.cz/
https://homeproj.cesnet.cz/httpauth-login/favicon.ico?1619448608
2020-07-07T12:30:32Z
Homeproj: Redmine for CESNET
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27300
2020-07-07T12:30:32Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>Precedes</strong> <i><a class="issue tracker-2 status-1 priority-4 priority-default overdue behind-schedule" href="/issues/6310">Feature #6310</a>: Implement result caching</i> added</li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27302
2020-07-07T12:30:41Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>Precedes</strong> <i><a class="issue tracker-2 status-4 priority-4 priority-default overdue behind-schedule" href="/issues/6332">Feature #6332</a>: Improve searching with caching and JavaScript</i> added</li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27304
2020-07-07T12:35:11Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-5 priority-4 priority-default closed" href="/issues/6308">Feature #6308</a>: Reimplement timeline calculations on database</i> added</li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27306
2020-07-07T12:35:32Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-5 priority-4 priority-default closed" href="/issues/6257">Feature #6257</a>: Review calculated statistics in timeline</i> added</li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27307
2020-07-07T13:01:24Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>File</strong> <a href="/attachments/3561">timeline_cte.txt</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/3561/timeline_cte.txt">timeline_cte.txt</a> added</li></ul><p>Using CTE, the basic query was extended as follows:</p>
<pre>
WITH
timeline AS (SELECT generate_series(\since, \until - INTERVAL '1ms', \interval) AS bucket),
total AS (SELECT COUNT(*) AS total FROM events WHERE "detecttime" >= \since AND "detecttime" < \until),
raw AS (SELECT \since + \interval * (width_bucket(detecttime, (SELECT array_agg(bucket) FROM timeline)) - 1) AS bucket, unnest(\set) AS set, COUNT(*) AS count FROM events WHERE "detecttime" >= \since AND "detecttime" < \until GROUP BY bucket, set),
sums AS (SELECT raw.set AS set, SUM(raw.count) AS sum FROM raw GROUP BY raw.set ORDER BY sum DESC)
(SELECT bucket, set, COALESCE(count, 0) AS count, NULL AS share FROM (timeline FULL JOIN sums ON TRUE) LEFT JOIN raw USING (bucket, set) ORDER BY bucket ASC, sums.sum DESC) UNION ALL SELECT NULL, sums.set, sums.sum, sums.sum/total.total::decimal FROM sums, total UNION ALL SELECT NULL, NULL, total.total, NULL FROM total;
</pre>
<p>with parameters <code>\since</code>, <code>\until</code>, <code>\interval</code> and <code>\set</code>, which generates all the data required for the specific view, with ARRAY and no LIMITing (e.g. category), zero-filled and sorted. See attachment <code>timeline_cte.txt</code> for example output. An optimization is possible for non-array column statistics and for LIMITing the query must be extended.<br />The runtime (on <code>mentat-alt</code>) is about 21.5s (median estimate) for 6 days and 1 hour interval as compared to the original query that took ~20.2s (median estimate).<br />Also an error is fixed here, where the interval upper bound was inclusive, resulting in several events getting in with <code>detecttime</code> = <code>\until</code>, quite commonly as some detectors report times with second granularity.</p>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27308
2020-07-07T13:25:24Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>Subject</strong> changed from <i>Autarchic DB queries for timeline</i> to <i>Autarkic DB queries for timeline</i></li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27309
2020-07-07T14:22:05Z
Radko Krkoš
krkos@cesnet.cz
<ul></ul><p>The total count query would be as follows:</p>
<pre>
WITH
timeline AS (SELECT generate_series(\since, \until - INTERVAL '1ms', \interval) AS bucket),
raw AS (SELECT \since + \interval * (width_bucket(detecttime, (SELECT array_agg(bucket) FROM timeline)) - 1) AS bucket, COUNT(*) AS count FROM events WHERE "detecttime" >= \since AND "detecttime" < \until GROUP BY bucket)
(SELECT bucket, COALESCE(count, 0) AS count FROM timeline LEFT JOIN raw USING (bucket) ORDER BY bucket ASC) UNION ALL SELECT NULL, SUM(count) FROM raw;
</pre>
<p>The run times are indistinguishable from the original version (both about 2s) on <code>mentat-alt</code>.</p>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=27311
2020-07-07T15:51:02Z
Radko Krkoš
krkos@cesnet.cz
<ul></ul><p>The limiting query should be as follows;</p>
<pre>
WITH
timeline AS (SELECT generate_series(\since, \until - INTERVAL '1ms', \interval) AS bucket),
toplist AS (SELECT unnest(\set) AS set, COUNT(*) AS sum FROM events WHERE "detecttime" >= \since AND "detecttime" < \until GROUP BY set ORDER BY sum DESC LIMIT \limit),
total AS (SELECT COUNT(*) AS total FROM events WHERE "detecttime" >= \since AND "detecttime" < \until),
raw AS (SELECT \since + \interval * (width_bucket(detecttime, (SELECT array_agg(bucket) FROM timeline)) - 1) AS bucket, unnest(\set) AS set, COUNT(*) AS count FROM events WHERE "detecttime" >= \since AND "detecttime" < \until GROUP BY bucket, set)
(SELECT bucket, set, COALESCE(count, 0) AS count, NULL AS share FROM (timeline FULL JOIN toplist ON TRUE) LEFT JOIN raw USING (bucket, set) ORDER BY bucket ASC, toplist.sum DESC) UNION ALL SELECT NULL, toplist.set, toplist.sum, toplist.sum/total.total::decimal FROM toplist, total UNION ALL SELECT NULL, NULL, total.total, NULL FROM total;
</pre>
<p>This adds the parameter <code>\limit</code>. The run time grew from about 46.5s to about 49s (median estimate) for 6 days, 1 hour intervals on <code>mentat-alt</code>.</p>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=29095
2020-11-05T10:29:15Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>Related to</strong> <i><a class="issue tracker-4 status-8 priority-4 priority-default" href="/issues/6251">Config #6251</a>: Rethink the indices and planner cost optimizations in light of the current usage patterns</i> added</li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=29102
2020-11-05T10:35:15Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>To be discussed</strong> changed from <i>Yes</i> to <i>No</i></li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=30045
2020-12-02T16:01:26Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Deferred</i></li><li><strong>Assignee</strong> deleted (<del><i>Radko Krkoš</i></del>)</li><li><strong>To be discussed</strong> deleted (<del><i>No</i></del>)</li></ul><p>The design is mostly done. This should be implemented one day, but currently there are more pressing issues.</p>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36405
2022-10-13T09:00:00Z
Pavel Kácha
ph@cesnet.cz
<ul><li><strong>Assignee</strong> set to <i>Jakub Maloštik</i></li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36425
2022-12-07T13:16:43Z
Jakub Maloštik
<ul><li><strong>Status</strong> changed from <i>Deferred</i> to <i>In Progress</i></li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36438
2022-12-15T14:20:00Z
Jakub Maloštik
<ul></ul><p>After updating the queries, with variables set as follows:<br /><pre><code class="sql syntaxhl"><span class="err">\</span><span class="k">set</span> <span class="n">since</span> <span class="s1">'</span><span class="se">\'</span><span class="s1">2022-12-01T00:00:00</span><span class="se">\'</span><span class="s1">::timestamp'</span>
<span class="err">\</span><span class="k">set</span> <span class="n">first_step</span> <span class="s1">'</span><span class="se">\'</span><span class="s1">2022-12-01T00:00:00</span><span class="se">\'</span><span class="s1">::timestamp'</span>
<span class="err">\</span><span class="k">set</span> <span class="k">until</span> <span class="s1">'</span><span class="se">\'</span><span class="s1">2022-12-07T00:00:00</span><span class="se">\'</span><span class="s1">::timestamp'</span>
<span class="err">\</span><span class="k">set</span> <span class="n">interval</span> <span class="s1">'INTERVAL </span><span class="se">\'</span><span class="s1">1h</span><span class="se">\'</span><span class="s1">'</span>
<span class="err">\</span><span class="k">set</span> <span class="k">set</span> <span class="s1">'"category"'</span>
<span class="err">\</span><span class="k">set</span> <span class="k">limit</span> <span class="mi">100</span>
</code></pre><br />The timings were:<br /><pre><code class="sql syntaxhl"><span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(:</span><span class="n">first_step</span><span class="p">,</span> <span class="p">:</span><span class="k">until</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="p">:</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">total</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(:</span><span class="n">since</span><span class="p">,</span> <span class="p">:</span><span class="n">first_step</span> <span class="o">+</span> <span class="p">:</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">timeline</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(:</span><span class="k">set</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">),</span>
<span class="n">sums</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="n">raw</span><span class="p">.</span><span class="k">count</span><span class="p">)</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span><span class="p">,</span> <span class="k">NULL</span> <span class="k">AS</span> <span class="k">share</span> <span class="k">FROM</span> <span class="p">(</span><span class="n">timeline</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="n">sums</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">raw</span> <span class="k">USING</span> <span class="p">(</span><span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="n">sums</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">sums</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">sums</span><span class="p">.</span><span class="k">sum</span><span class="p">,</span> <span class="n">sums</span><span class="p">.</span><span class="k">sum</span><span class="o">/</span><span class="n">total</span><span class="p">.</span><span class="n">total</span><span class="p">::</span><span class="nb">decimal</span> <span class="k">FROM</span> <span class="n">sums</span><span class="p">,</span> <span class="n">total</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span><span class="p">.</span><span class="n">total</span><span class="p">,</span> <span class="k">NULL</span> <span class="k">FROM</span> <span class="n">total</span><span class="p">;</span>
<span class="c1">-- ~7.9s</span>
<span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(:</span><span class="n">since</span><span class="p">,</span> <span class="p">:</span><span class="n">first_step</span> <span class="o">+</span> <span class="p">:</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">buckets</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(:</span><span class="n">first_step</span><span class="p">,</span> <span class="p">:</span><span class="k">until</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="p">:</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">buckets</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(:</span><span class="k">set</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">;</span>
<span class="c1">-- ~10.9s</span>
<span class="c1">--------------------------------</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(:</span><span class="n">first_step</span><span class="p">,</span> <span class="p">:</span><span class="k">until</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="p">:</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(:</span><span class="n">since</span><span class="p">,</span> <span class="p">:</span><span class="n">first_step</span> <span class="o">+</span> <span class="p">:</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">timeline</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">timeline</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">raw</span> <span class="k">USING</span> <span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="k">count</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">raw</span><span class="p">;</span>
<span class="c1">-- ~3.0s</span>
<span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(:</span><span class="n">since</span><span class="p">,</span> <span class="p">:</span><span class="n">first_step</span> <span class="o">+</span> <span class="p">:</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">buckets</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(:</span><span class="n">first_step</span><span class="p">,</span> <span class="p">:</span><span class="k">until</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="p">:</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">buckets</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">;</span>
<span class="c1">-- ~3.0s</span>
<span class="c1">--------------------------------</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(:</span><span class="n">first_step</span><span class="p">,</span> <span class="p">:</span><span class="k">until</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="p">:</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">toplist</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">unnest</span><span class="p">(:</span><span class="k">set</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="p">:</span><span class="k">limit</span><span class="p">),</span>
<span class="n">total</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(:</span><span class="n">since</span><span class="p">,</span> <span class="p">:</span><span class="n">first_step</span> <span class="o">+</span> <span class="p">:</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">timeline</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(:</span><span class="k">set</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span><span class="p">,</span> <span class="k">NULL</span> <span class="k">AS</span> <span class="k">share</span> <span class="k">FROM</span> <span class="p">(</span><span class="n">timeline</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="n">toplist</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">raw</span> <span class="k">USING</span> <span class="p">(</span><span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="n">toplist</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">toplist</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">toplist</span><span class="p">.</span><span class="k">sum</span><span class="p">,</span> <span class="n">toplist</span><span class="p">.</span><span class="k">sum</span><span class="o">/</span><span class="n">total</span><span class="p">.</span><span class="n">total</span><span class="p">::</span><span class="nb">decimal</span> <span class="k">FROM</span> <span class="n">toplist</span><span class="p">,</span> <span class="n">total</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span><span class="p">.</span><span class="n">total</span><span class="p">,</span> <span class="k">NULL</span> <span class="k">FROM</span> <span class="n">total</span><span class="p">;</span>
<span class="c1">-- ~10.9s</span>
<span class="k">SELECT</span> <span class="n">dist</span><span class="p">.</span><span class="n">bucket</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="n">dist</span><span class="p">.</span><span class="k">set</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="n">dist</span><span class="p">.</span><span class="k">count</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(:</span><span class="n">since</span><span class="p">,</span> <span class="p">:</span><span class="n">first_step</span> <span class="o">+</span> <span class="p">:</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">buckets</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(:</span><span class="n">first_step</span><span class="p">,</span> <span class="p">:</span><span class="k">until</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="p">:</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">buckets</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(:</span><span class="k">set</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">)</span> <span class="k">AS</span> <span class="n">dist</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">unnest</span><span class="p">(:</span><span class="k">set</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="p">:</span><span class="n">since</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="p">:</span><span class="k">until</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="p">:</span><span class="k">limit</span><span class="p">)</span> <span class="k">AS</span> <span class="n">toplist</span> <span class="k">USING</span> <span class="p">(</span><span class="k">set</span><span class="p">);</span>
<span class="c1">-- ~14.7s</span>
</code></pre></p>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36445
2022-12-20T10:58:12Z
Radko Krkoš
krkos@cesnet.cz
<ul><li><strong>To be discussed</strong> set to <i>Yes</i></li></ul><p>A follow-up from the VC:<br />Regarding the incompleteness of the query, that was my mistake, the relative counts are only included for the last few columns - only where needed for the current timeline output.<br />So, only the total times should be measured. Probably the complete Python code with DB calls versus sum of the new DB calls.</p>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36573
2023-04-13T12:45:46Z
Jakub Maloštik
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>Closed</i></li></ul><p>After couple of revisions, the queries are as follows:</p>
<pre><code class="SQL syntaxhl"><span class="c1">-- Only counts</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"events"</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"timeline"</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw"</span> <span class="k">ON</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="n">raw</span><span class="p">.</span><span class="n">bucket</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">FROM</span> <span class="nv">"raw"</span><span class="p">;</span>
<span class="c1">-- Simple grouping by set without toplist</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">total_events</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="nv">"eventclass"</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"events"</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">),</span>
<span class="n">sums</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span><span class="p">::</span><span class="nb">text</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="n">raw</span><span class="p">.</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="nv">"timeline"</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="nv">"sums"</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw"</span> <span class="k">ON</span> <span class="nv">"timeline"</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="nv">"raw"</span><span class="p">.</span><span class="n">bucket</span> <span class="k">AND</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">DISTINCT</span> <span class="k">FROM</span> <span class="nv">"raw"</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">FROM</span> <span class="nv">"sums"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="nv">"total_events"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">total_events</span><span class="p">;</span>
<span class="c1">-- Grouping of set that is an array without toplist</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">total</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">SUM</span><span class="p">(</span><span class="k">CARDINALITY</span><span class="p">(</span><span class="nv">"category"</span><span class="p">)),</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">total_events</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(</span><span class="nv">"category"</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"events"</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">),</span>
<span class="n">sums</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span><span class="p">::</span><span class="nb">text</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="n">raw</span><span class="p">.</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="nv">"timeline"</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="nv">"sums"</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw"</span> <span class="k">ON</span> <span class="nv">"timeline"</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="nv">"raw"</span><span class="p">.</span><span class="n">bucket</span> <span class="k">AND</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">DISTINCT</span> <span class="k">FROM</span> <span class="nv">"raw"</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">FROM</span> <span class="nv">"sums"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="nv">"total"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">total_events</span>
<span class="c1">-- Grouping of set that is an array with toplist</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">total</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">SUM</span><span class="p">(</span><span class="k">CARDINALITY</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">)),</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">total_events</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">toplist</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">unnest</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mi">100</span><span class="p">),</span>
<span class="n">toplist_with_rest</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">set</span><span class="p">::</span><span class="nb">text</span><span class="p">,</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">toplist</span> <span class="k">UNION</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'__REST__'</span> <span class="k">as</span> <span class="k">set</span><span class="p">,</span> <span class="n">total</span> <span class="o">-</span> <span class="k">SUM</span><span class="p">(</span><span class="k">sum</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">as</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">total</span><span class="p">,</span> <span class="n">toplist</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">total</span> <span class="k">HAVING</span> <span class="n">total</span> <span class="o">-</span> <span class="k">SUM</span><span class="p">(</span><span class="k">sum</span><span class="p">)::</span><span class="nb">bigint</span> <span class="o">></span> <span class="mi">0</span><span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">detecttime</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">)</span> <span class="n">top_events</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">toplist</span> <span class="k">USING</span> <span class="p">(</span><span class="k">set</span><span class="p">)</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">),</span>
<span class="n">raw_with_rest</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">::</span><span class="nb">text</span><span class="p">,</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="s1">'__REST__'</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="n">raw_totals</span><span class="p">.</span><span class="k">count</span> <span class="o">-</span> <span class="n">raw_sums</span><span class="p">.</span><span class="k">count</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="k">CARDINALITY</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">))</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">)</span> <span class="n">raw_totals</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">)</span> <span class="n">raw_sums</span> <span class="k">USING</span> <span class="p">(</span><span class="n">bucket</span><span class="p">))</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="nv">"timeline"</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="nv">"toplist_with_rest"</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw_with_rest"</span> <span class="k">ON</span> <span class="nv">"timeline"</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="nv">"raw_with_rest"</span><span class="p">.</span><span class="n">bucket</span> <span class="k">AND</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">set</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">DISTINCT</span> <span class="k">FROM</span> <span class="nv">"raw_with_rest"</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">sum</span> <span class="k">FROM</span> <span class="nv">"toplist_with_rest"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="nv">"total"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">total_events</span>
</code></pre>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36574
2023-04-13T12:48:17Z
Jakub Maloštik
<ul></ul><p>After couple of revisions, the queries are as follows:</p>
<pre><code class="SQL syntaxhl"><span class="c1">-- Only counts</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"events"</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"timeline"</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw"</span> <span class="k">ON</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="n">raw</span><span class="p">.</span><span class="n">bucket</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">FROM</span> <span class="nv">"raw"</span><span class="p">;</span>
<span class="c1">-- Simple grouping by set without toplist</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">total_events</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="nv">"eventclass"</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"events"</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">),</span>
<span class="n">sums</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span><span class="p">::</span><span class="nb">text</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="n">raw</span><span class="p">.</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="nv">"timeline"</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="nv">"sums"</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw"</span> <span class="k">ON</span> <span class="nv">"timeline"</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="nv">"raw"</span><span class="p">.</span><span class="n">bucket</span> <span class="k">AND</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">DISTINCT</span> <span class="k">FROM</span> <span class="nv">"raw"</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">FROM</span> <span class="nv">"sums"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="nv">"total_events"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">total_events</span><span class="p">;</span>
<span class="c1">-- Grouping of set that is an array without toplist</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">total</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">SUM</span><span class="p">(</span><span class="k">CARDINALITY</span><span class="p">(</span><span class="nv">"category"</span><span class="p">)),</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">total_events</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(</span><span class="nv">"category"</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="nv">"events"</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">),</span>
<span class="n">sums</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span><span class="p">::</span><span class="nb">text</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="n">raw</span><span class="p">.</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">raw</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="nv">"timeline"</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="nv">"sums"</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw"</span> <span class="k">ON</span> <span class="nv">"timeline"</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="nv">"raw"</span><span class="p">.</span><span class="n">bucket</span> <span class="k">AND</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">DISTINCT</span> <span class="k">FROM</span> <span class="nv">"raw"</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="nv">"sums"</span><span class="p">.</span><span class="k">sum</span> <span class="k">FROM</span> <span class="nv">"sums"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="nv">"total"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">total_events</span>
<span class="c1">-- Grouping of set that is an array with toplist</span>
<span class="k">WITH</span>
<span class="n">timeline</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AS</span> <span class="n">bucket</span> <span class="k">UNION</span> <span class="k">SELECT</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">),</span>
<span class="n">total</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">SUM</span><span class="p">(</span><span class="k">CARDINALITY</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">)),</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">total_events</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">),</span>
<span class="n">toplist</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">unnest</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span> <span class="k">LIMIT</span> <span class="mi">100</span><span class="p">),</span>
<span class="n">toplist_with_rest</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">set</span><span class="p">::</span><span class="nb">text</span><span class="p">,</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">toplist</span> <span class="k">UNION</span> <span class="p">(</span><span class="k">SELECT</span> <span class="s1">'__REST__'</span> <span class="k">as</span> <span class="k">set</span><span class="p">,</span> <span class="n">total</span> <span class="o">-</span> <span class="k">SUM</span><span class="p">(</span><span class="k">sum</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">as</span> <span class="k">sum</span> <span class="k">FROM</span> <span class="n">total</span><span class="p">,</span> <span class="n">toplist</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">total</span> <span class="k">HAVING</span> <span class="n">total</span> <span class="o">-</span> <span class="k">SUM</span><span class="p">(</span><span class="k">sum</span><span class="p">)::</span><span class="nb">bigint</span> <span class="o">></span> <span class="mi">0</span><span class="p">)</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="k">sum</span> <span class="k">DESC</span><span class="p">),</span>
<span class="n">raw</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">,</span> <span class="k">COUNT</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">detecttime</span><span class="p">,</span> <span class="k">unnest</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">)</span> <span class="k">AS</span> <span class="k">set</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">)</span> <span class="n">top_events</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">toplist</span> <span class="k">USING</span> <span class="p">(</span><span class="k">set</span><span class="p">)</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">),</span>
<span class="n">raw_with_rest</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">set</span><span class="p">::</span><span class="nb">text</span><span class="p">,</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="s1">'__REST__'</span> <span class="k">AS</span> <span class="k">set</span><span class="p">,</span> <span class="n">raw_totals</span><span class="p">.</span><span class="k">count</span> <span class="o">-</span> <span class="n">raw_sums</span><span class="p">.</span><span class="k">count</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">GREATEST</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">+</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span> <span class="o">*</span> <span class="p">(</span><span class="n">width_bucket</span><span class="p">(</span><span class="n">detecttime</span><span class="p">,</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">array_agg</span><span class="p">(</span><span class="n">bucket</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">generate_series</span><span class="p">(</span><span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span><span class="p">,</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="o">-</span> <span class="n">INTERVAL</span> <span class="s1">'1 microsecond'</span><span class="p">,</span> <span class="s1">'0 days 3600.000000 seconds'</span><span class="p">::</span><span class="n">interval</span><span class="p">)</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">))</span> <span class="o">-</span> <span class="mi">1</span><span class="p">))</span> <span class="k">AS</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="k">CARDINALITY</span><span class="p">(</span><span class="nv">"target_port"</span><span class="p">))</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">events</span> <span class="k">WHERE</span> <span class="nv">"detecttime"</span> <span class="o">>=</span> <span class="s1">'2023-04-01T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">AND</span> <span class="nv">"detecttime"</span> <span class="o"><</span> <span class="s1">'2023-04-07T00:00:00'</span><span class="p">::</span><span class="nb">timestamp</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">)</span> <span class="n">raw_totals</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">bucket</span><span class="p">,</span> <span class="k">SUM</span><span class="p">(</span><span class="k">count</span><span class="p">)::</span><span class="nb">bigint</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="n">raw</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">bucket</span><span class="p">)</span> <span class="n">raw_sums</span> <span class="k">USING</span> <span class="p">(</span><span class="n">bucket</span><span class="p">))</span>
<span class="p">(</span><span class="k">SELECT</span> <span class="n">timeline</span><span class="p">.</span><span class="n">bucket</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="n">COALESCE</span><span class="p">(</span><span class="k">count</span><span class="p">,</span> <span class="mi">0</span><span class="p">)</span> <span class="k">AS</span> <span class="k">count</span> <span class="k">FROM</span> <span class="p">(</span><span class="nv">"timeline"</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="nv">"toplist_with_rest"</span> <span class="k">ON</span> <span class="k">TRUE</span><span class="p">)</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="nv">"raw_with_rest"</span> <span class="k">ON</span> <span class="nv">"timeline"</span><span class="p">.</span><span class="n">bucket</span> <span class="o">=</span> <span class="nv">"raw_with_rest"</span><span class="p">.</span><span class="n">bucket</span> <span class="k">AND</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">set</span> <span class="k">IS</span> <span class="k">NOT</span> <span class="k">DISTINCT</span> <span class="k">FROM</span> <span class="nv">"raw_with_rest"</span><span class="p">.</span><span class="k">set</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">bucket</span> <span class="k">ASC</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">sum</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">set</span><span class="p">,</span> <span class="nv">"toplist_with_rest"</span><span class="p">.</span><span class="k">sum</span> <span class="k">FROM</span> <span class="nv">"toplist_with_rest"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="nv">"total"</span> <span class="k">UNION</span> <span class="k">ALL</span> <span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">total</span> <span class="k">FROM</span> <span class="n">total_events</span>
</code></pre>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36605
2023-04-17T13:23:34Z
Rajmund Hruška
<ul><li><strong>Status</strong> changed from <i>Closed</i> to <i>In Progress</i></li><li><strong>Target version</strong> changed from <i>Backlog</i> to <i>2.11</i></li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36606
2023-04-17T13:27:10Z
Rajmund Hruška
<ul><li><strong>Status</strong> changed from <i>In Progress</i> to <i>In Review</i></li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36615
2023-04-20T11:28:11Z
Rajmund Hruška
<ul><li><strong>To be discussed</strong> deleted (<del><i>Yes</i></del>)</li></ul>
Mentat - Feature #6413: Autarkic DB queries for timeline
https://homeproj.cesnet.cz/issues/6413?journal_id=36818
2023-06-29T14:31:48Z
Rajmund Hruška
<ul><li><strong>Status</strong> changed from <i>In Review</i> to <i>Closed</i></li></ul>