1641836813 Datenreihe aus 2 Spalten erzeugen

Muschelpuster · 11 Januar 2022

Moin zusammen,

ich habe eine Tabelle mit einer Startzeit als Linux-Zeit und einer Dauer. Nun möchte ich zählen, wie viele Datensätze im gleichen Zeitraum relevant sind. Dazu muss ich ja zuerst einmal Start- und Endzeitpunkt als eine Reihe haben um dann zählen zu können. Geht das?

Code:

Datenbeispiel:
timestamp  | duration
-----------+-------------
1641836813 | 125
1641836937 | 43
1641883913 | 912
1641883998 | 13
1641884621 | 75

gewünschtes Teilergebnis:
timerow
---------------
1641836813
1641836937
1641836938
1641836980
1641883913
1641883998
1641884011
1641884621
1641884696
1641884825


gewünschtes Endergebnis:
timestamp  | counter
-----------+-------------
1641836813 | 1
1641836937 | 2
1641836938 | 1
1641836980 | 0
1641883913 | 1
1641883998 | 2
1641884011 | 1
1641884621 | 2
1641884696 | 1
1641884825 | 0

Das Zählen habe ich (eigentlich) im Griff. Ich habe das Endergebnis nur mit aufgenommen, falls der Ansatz von mir zu trivial gedacht ist und das am Ende viel einfacher und performanter geht.

Niels

castorp · 11 Januar 2022

Muschelpuster schrieb:
mit einer Startzeit als Linux-Zeit

Das ist wirklich eine ziemlich schlechte Design Entscheidung (Das hier interessiert Dich vielleicht)

Ich verstehe aber nicht so ganz was Du eigentlich abfragen willst. Du sprichst von Zeiträumen aber in dem Ergebnis sehe ich nur Zeitpunkte.
Welcher Zeitraum wird denn mit der Zahl 1641884825 definiert?

Dein Zwischenergebnis mit einer Liste von Zahlen, kannst Du natürlich so erzeugen:

SQL:

select "timestamp"
from the_table
union all
select "timestamp" + duration
from the_table
order by 1

akretschmer · 11 Januar 2022

mrh, nicht sicher, ob ich Dein Anligen richtig verstanden habe ...

Code:

edb=*# select * from muschelpupser ;
 timestamp  | duration 
------------+----------
 1641836813 |      125
 1641836937 |       43
 1641883913 |      912
 1641883998 |       13
 1641884621 |       75
(5 rows)

edb=*# with zeiten as (select timestamp as start, (timestamp + duration) stop from muschelpupser) select start, stop, count(*), array_agg(m.timestamp) from zeiten left join muschelpupser m on m.timestamp between zeiten.start and zeiten.stop group by start, stop order by start, stop;
   start    |    stop    | count |             array_agg              
------------+------------+-------+------------------------------------
 1641836813 | 1641836938 |     2 | {1641836813,1641836937}
 1641836937 | 1641836980 |     1 | {1641836937}
 1641883913 | 1641884825 |     3 | {1641883913,1641883998,1641884621}
 1641883998 | 1641884011 |     1 | {1641883998}
 1641884621 | 1641884696 |     1 | {1641884621}
(5 rows)

edb=*#

Muschelpuster · 11 Januar 2022

Vielen Dank für den Input.

castorp schrieb:
Das ist wirklich eine ziemlich schlechte Design Entscheidung (Das hier interessiert Dich vielleicht)

Danke - ich weiß. Das ist allerdings eine vorgegeben DB an der ich nichts drehen kann. Aber ja - ich habe gestern auch schon wieder etwas mit Timestamps getestet, wo ich es in der Hand hatte :mad:

castorp schrieb:
Ich verstehe aber nicht so ganz was Du eigentlich abfragen willst. Du sprichst von Zeiträumen aber in dem Ergebnis sehe ich nur Zeitpunkte.
Welcher Zeitraum wird denn mit der Zahl 1641884825 definiert?

Es geht um Telefonverbindungen. Ich will aus den bestehenden Daten eine Grafik bauen der anzeigt wann wie viele Verbindungen bestanden. Der Wert kann sich ja nur ändern wenn ein Gespräch beginnt oder wenn es endet. Daher ist 1641884825 das Ende der Verbindung mit dem Startzeitpunkt 1641883913 (+912).
Den Counter für den Zeitpunkt bekomme ich mit:

Code:

select count(*) from my_table
        where timestamp <= 1641883913  and timestamp + duration > 1641883913;

akretschmer schrieb:

Code:

with zeiten as (select timestamp as start, (timestamp + duration) stop from muschelpupser) select start, stop, count(*), array_agg(m.timestamp) from zeiten left join muschelpupser m on m.timestamp between zeiten.start and zeiten.stop group by start, stop order by start, stop;

Sieht cool aus, ich verstehe es aber (noch) nicht

Ich brauche auf jeden Fall auch die Endwerte wo der Zähler wieder auf 0 geht, sonst bleibt die Linie in der Grafik oben hängen.

Niels

Muschelpuster · 11 Januar 2022

So geht es:

Code:

select timestamp as count_time, 
    (select count(*) from "my_table"
        where timestamp <= Q1.timestamp
         and timestamp + duration > Q1.timestamp)
    from "my_table" AS Q1
union all
    select timestamp + duration as count_time, 
        (select count(*) from "my_table"
               where timestamp <= Q1.timestamp + Q1. duration
             and timestamp + duration > Q1.timestamp + Q1. duration) as Call_Count 
        from "my_table" AS Q1
order by count_time;

Das ist aber von der Performance doch recht anspruchsvoll. Meine 10 Testdatensätze brauchten jetzt 128ms, wenn ich das auf die auch nicht hyperperfomante Appliance mit deutlich mehr Datensätzen los lasse geht diese mir entweder in die Knie oder ich muss sehr geduldig sein.
Ich hätte auch gerne im eingebundenen Query Q1.count_time verwendet um nicht mehrfach zu rechnen, aber Postgres fand das keine gute Idee.

Niels

akretschmer · 11 Januar 2022

du könntest mit EXPLAIN (analyse, buffers) <Abfrage> schauen, wo viel Zeit verloren geht. PostgreSQL kann funktionale Indexe, die könnten bei Abfragen ie "where ... and timestamp + duration > value" hilfreich sein. Demo:

Code:

edb=*# select * from muschelpupser ;
 timestamp  | duration 
------------+----------
 1641836813 |      125
 1641836937 |       43
 1641883913 |      912
 1641883998 |       13
 1641884621 |       75
(5 rows)

edb=*# create index idx_duration on muschelpupser ((timestamp + duration));
CREATE INDEX
edb=*# explain analyse select * from muschelpupser where timestamp + duration > 1641883998;
                                               QUERY PLAN                                               
--------------------------------------------------------------------------------------------------------
 Seq Scan on muschelpupser  (cost=0.00..1.07 rows=2 width=12) (actual time=0.014..0.016 rows=3 loops=1)
   Filter: (("timestamp" + duration) > 1641883998)
   Rows Removed by Filter: 2
 Planning Time: 0.329 ms
 Execution Time: 0.042 ms
(5 rows)

edb=*# set enable_seqscan to off;
SET
edb=*# explain analyse select * from muschelpupser where timestamp + duration > 1641883998;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Index Scan using idx_duration on muschelpupser  (cost=0.13..8.17 rows=2 width=12) (actual time=0.125..0.130 rows=3 loops=1)
   Index Cond: (("timestamp" + duration) > 1641883998)
 Planning Time: 0.104 ms
 Execution Time: 0.162 ms
(4 rows)

edb=*#

Aufgrund des Kostenmodells 'greift' der Index bei kleinen Tabellen noch nicht, daher der 'Trick' mit "set enable_seqscan to off;" um einen Indexscan zu forcieren. (man sieht an den cost= - Werten, daß Indexscan hier teuerer ist, und auch langsamer. Bei größeren Tabellen kippt dann der Plan)

Muschelpuster · 12 Januar 2022

Ich konnte das Ganze noch etwas optimieren:

Code:

WITH Q1 AS (SELECT timestamp FROM myTable
            UNION ALL
            SELECT timestamp + duration FROM myTable),
     Q2 AS (SELECT timestamp AS s, timestamp + duration AS e
            FROM myTable)
SELECT Q1.timestamp,
        (SELECT COUNT(*) FROM Q2 WHERE Q2.s <= Q1.timestamp AND Q2.e > Q1.timestamp) AS C
    FROM Q1;

So ist es immer noch keine Rakete, aber für meine Zwecke brauchbar.

Niels

ukulele · 13 Januar 2022

Ich würde sagen es geht zumindest eleganter aber vermutlich auch schneller:

Code:

WITH t([timestamp],duration) AS (
    SELECT 1641836813,125 UNION ALL
    SELECT 1641836937,43 UNION ALL
    SELECT 1641883913,912 UNION ALL
    SELECT 1641883998,13 UNION ALL
    SELECT 1641884621,75
    ), timeline([timestamp],[count]) AS (
    SELECT    [timestamp],1
    FROM    t
    UNION ALL
    SELECT    [timestamp] + duration,-1
    FROM    t
    )
SELECT    [timestamp],
        sum([count]) OVER (ORDER BY [timestamp]) AS [count]
FROM    timeline

Ist jetzt MSSQL aber sollte alles in PG kein Problem sein. Eventuell müssen die eckigen Klammern raus.

ukulele · 24 Januar 2022

Ergänzende Erklärung meiner Seits:

Code:

WITH t([timestamp],duration) AS (...

liefert nur Testdatensätze, die ganze Tabelle t musst du also mit deiner echten Datentabelle ersetzen. Das sähe dann ungefähr so aus:

Code:

WITH timeline([timestamp],[count]) AS (
    SELECT    [timestamp],1
    FROM   <deine_tabelle>
    UNION ALL
    SELECT    [timestamp] + duration,-1
    FROM    <deine_tabelle>
    )
SELECT    [timestamp],
        sum([count]) OVER (ORDER BY [timestamp]) AS [count]
FROM    timeline

Muschelpuster · 24 Januar 2022

Ah ja, teste ich!

Niels

1641836813 Datenreihe aus 2 Spalten erzeugen

Muschelpuster

Benutzer

castorp

Datenbank-Guru

akretschmer

Datenbank-Guru

Muschelpuster

Benutzer

Muschelpuster

Benutzer

akretschmer

Datenbank-Guru

Muschelpuster

Benutzer

ukulele

Datenbank-Guru

ukulele

Datenbank-Guru

Muschelpuster

Benutzer