2
\$\begingroup\$

My Data consists of rows containing a Length/Duration in Minutes, a Begin & EndTime. The precision is 1min (or larger).

the table looks something like that:

CREATE TABLE public."TimeSeries" (
    "Id" uuid NOT NULL,
    "BeginTime" timestamptz NOT NULL,
    "EndTime" timestamptz NOT NULL DEFAULT '0001-01-01 00:00:00' :: timestamp without time zone,
    "LengthMin" int4 NOT NULL
);

INSERT INTO public."TimeSeries" ("Id","BeginTime","LengthMin","EndTime") VALUES
     ('ecabcd8d-3129-4128-b126-a4e1abcd49c9'::uuid,'2021-11-16 11:45:00.000',1,'2021-11-16 11:46:00.000'),
     ('e5abcd70-5125-412b-9128-93ecabcdcba9'::uuid,'2021-11-16 11:46:00.000',1,'2021-11-16 11:47:00.000'),
     ('03abcdb8-8129-4124-b126-f5bbabcd7157'::uuid,'2021-11-16 11:47:00.000',1,'2021-11-16 11:48:00.000'),
     ('54abcdcc-6129-4126-912b-34b2abcde19a'::uuid,'2021-11-16 11:50:00.000',1,'2021-11-16 11:51:00.000'),
     ('77abcd28-212f-4122-912f-5060abcd2aa0'::uuid,'2021-11-16 11:51:00.000',1,'2021-11-16 11:52:00.000'),
     ('8babcdd1-f12a-4124-9128-2529abcda136'::uuid,'2021-11-16 11:52:00.000',1,'2021-11-16 11:53:00.000'),
     ('8aabcd94-9129-4121-b12c-1ebfabcd06b8'::uuid,'2021-11-16 12:35:00.000',1,'2021-11-16 12:36:00.000'),
     ('96abcd04-b12e-4122-b12b-4dc2abcdcee4'::uuid,'2021-11-16 12:40:00.000',1,'2021-11-16 12:41:00.000'),
     ('42abcd4a-f129-412c-9124-41b3abcd3ca3'::uuid,'2021-11-16 12:44:00.000',1,'2021-11-16 12:45:00.000'),
     ('beabcd8f-c12f-4126-a12b-0a37abcdd0bb'::uuid,'2021-11-16 12:49:00.000',1,'2021-11-16 12:50:00.000'),
     ('b8abcd79-d12c-4120-912f-754fabcdd220'::uuid,'2021-11-16 12:50:00.000',1,'2021-11-16 12:51:00.000'),
     ('c3abcd08-e121-4127-b125-4e70abcd5756'::uuid,'2021-11-16 12:59:00.000',1,'2021-11-16 13:00:00.000'),
     ('65abcdbe-7121-412a-a12c-1f68abcd94eb'::uuid,'2021-11-16 13:00:00.000',1,'2021-11-16 13:01:00.000'),
     ('f5abcd38-9122-412c-b12f-957fabcd79b5'::uuid,'2021-11-16 13:05:00.000',1,'2021-11-16 13:06:00.000');

The code I came up with looks something like that: ( http://www.sqlfiddle.com/#!17/8a517/2 )

SELECT
    u."SeriesBegin",
    COUNT(u."BeginTime") as cnt,
    min(u."BeginTime") as min,
    max(u."EndTime") as max,
    max(u."EndTime") - min(u."BeginTime") as delta
FROM
    (
        SELECT
            v."BeginTime",
            v."EndTime",
            v."isInSeries",
            v."n_in_series",
            lag("BeginTime", v."n_in_series" :: int) OVER (
                ORDER BY
                    "BeginTime"
            ) AS "SeriesBegin",
            grp
        FROM
            (
                SELECT
                    t.*,
                    row_number() OVER (
                        partition BY t."BeginTime" - grp * make_interval(mins => t."LengthMin")
                        ORDER BY
                            t."BeginTime"
                    ) - 1 as n_in_series
                FROM
                    (
                        SELECT
                            row_number() OVER (
                                ORDER BY
                                    "BeginTime"
                            ) AS grp,
                            "BeginTime" = lag("EndTime") OVER (
                                ORDER BY
                                    "BeginTime"
                            ) AS "isInSeries",
                            *
                        FROM
                            "TimeSeries"
                        WHERE
                            "LengthMin" = 1
                    ) t
                ORDER BY
                    t."BeginTime"
            ) AS v
    ) u
GROUP BY
    "SeriesBegin"
ORDER BY
    "SeriesBegin" ASC

The result:

    |          SeriesBegin | cnt |                  min |                  max |                                          delta |
    |----------------------|-----|----------------------|----------------------|------------------------------------------------|
    | 2021-11-16T11:45:00Z |   3 | 2021-11-16T11:45:00Z | 2021-11-16T11:48:00Z | 0 years 0 mons 0 days 0 hours 3 mins 0.00 secs |
    | 2021-11-16T11:50:00Z |   3 | 2021-11-16T11:50:00Z | 2021-11-16T11:53:00Z | 0 years 0 mons 0 days 0 hours 3 mins 0.00 secs |
    | 2021-11-16T12:35:00Z |   1 | 2021-11-16T12:35:00Z | 2021-11-16T12:36:00Z | 0 years 0 mons 0 days 0 hours 1 mins 0.00 secs |
    | 2021-11-16T12:40:00Z |   1 | 2021-11-16T12:40:00Z | 2021-11-16T12:41:00Z | 0 years 0 mons 0 days 0 hours 1 mins 0.00 secs |
    | 2021-11-16T12:44:00Z |   1 | 2021-11-16T12:44:00Z | 2021-11-16T12:45:00Z | 0 years 0 mons 0 days 0 hours 1 mins 0.00 secs |
    | 2021-11-16T12:49:00Z |   2 | 2021-11-16T12:49:00Z | 2021-11-16T12:51:00Z | 0 years 0 mons 0 days 0 hours 2 mins 0.00 secs |
    | 2021-11-16T12:59:00Z |   2 | 2021-11-16T12:59:00Z | 2021-11-16T13:01:00Z | 0 years 0 mons 0 days 0 hours 2 mins 0.00 secs |
    | 2021-11-16T13:05:00Z |   1 | 2021-11-16T13:05:00Z | 2021-11-16T13:06:00Z | 0 years 0 mons 0 days 0 hours 1 mins 0.00 secs |

I think this code can be optimized, on one hand 4 SELECT statements seem pretty much for me. On the other hand currently there are 2 approaches included, however one only active.

The Approach I like more is checking if the BeginTime = lag(EndTime) however then I was not able to connect rows which are in the same series.

The active approach is calculating some common value t."BeginTime" - grp * make_interval(mins => t."LengthMin") and mapping them via the calculated position in the series n_in_series & lag("BeginTime", v."n_in_series" :: int) to a group.

\$\endgroup\$
5
  • 2
    \$\begingroup\$ Thanks for this great question - particularly for including your table definitions, which many new users overlook. I hope you get some good reviews, and I hope to see more of your contributions here in future! \$\endgroup\$ Nov 24 at 16:51
  • \$\begingroup\$ @TobySpeight thanks for your comment. I think in SQL there is always some possibility for improvements in readability and also in performance, therefore this topic gives good questions :) I'm always annoyed when there is missing info, thus I included the fiddle too \$\endgroup\$
    – Fritz
    Nov 24 at 21:04
  • \$\begingroup\$ What version of PostgreSQL? Your Fiddle uses 9.6 but that's very old at this point. \$\endgroup\$
    – Reinderien
    Nov 25 at 14:15
  • \$\begingroup\$ @Reinderien I'm currently using v13, however I will upgrade to v14 as it is available via the zalan.do k8s operator - are there any new features which would help me in this case? \$\endgroup\$
    – Fritz
    Nov 25 at 22:02
  • 1
    \$\begingroup\$ Probably nothing specific, but you should replace sqlfiddle with dbfiddle as the latter offers your specific version and the former doesn't. \$\endgroup\$
    – Reinderien
    Nov 25 at 22:12
1
\$\begingroup\$

It's unwise to have a LengthMin in your table - this is redundant, and can be found as the difference between the begin and end time fields.

None of your column or table names need quoting, and in most cases you also don't need to prefix the public. namespace.

timestamptz is a non-standard abbreviation for timestamp with time zone; prefer standards when you can - and PostgreSQL is uniquely very, very good at supporting standards compared to the other databases.

In your insert, you don't need explicit uuid casts, and you don't need to include second and millisecond fields in your times.

You have a mix of uppercase and lowercase keywords. Most people prefer uppercase; I prefer lowercase and (just like every other language we write in) use syntax highlighting rather than SHOUTING to make it clear where the keywords are. Also, internally PostgreSQL lowercase-folds everything anyway.

You also have many different capitalization styles for your own names - BeginTime, isInSeries and n_in_series being UpperCamelCase, lowerCamelCase and lower_snake_case respectively. Best to choose one style and be consistent.

I don't have much to say about the content of the query, other than - it's good that you've used subqueries instead of CTEs, as the latter impose an optimization boundary.

Suggested

create table TimeSeries (
    Id uuid not null,
    BeginTime timestamp with time zone not null,
    EndTime timestamp with time zone not null
);

insert into TimeSeries(Id, BeginTime, EndTime) values
     ('ecabcd8d-3129-4128-b126-a4e1abcd49c9', '2021-11-16 11:45', '2021-11-16 11:46'),
     ('e5abcd70-5125-412b-9128-93ecabcdcba9', '2021-11-16 11:46', '2021-11-16 11:47'),
     ('03abcdb8-8129-4124-b126-f5bbabcd7157', '2021-11-16 11:47', '2021-11-16 11:48'),
     ('54abcdcc-6129-4126-912b-34b2abcde19a', '2021-11-16 11:50', '2021-11-16 11:51'),
     ('77abcd28-212f-4122-912f-5060abcd2aa0', '2021-11-16 11:51', '2021-11-16 11:52'),
     ('8babcdd1-f12a-4124-9128-2529abcda136', '2021-11-16 11:52', '2021-11-16 11:53'),
     ('8aabcd94-9129-4121-b12c-1ebfabcd06b8', '2021-11-16 12:35', '2021-11-16 12:36'),
     ('96abcd04-b12e-4122-b12b-4dc2abcdcee4', '2021-11-16 12:40', '2021-11-16 12:41'),
     ('42abcd4a-f129-412c-9124-41b3abcd3ca3', '2021-11-16 12:44', '2021-11-16 12:45'),
     ('beabcd8f-c12f-4126-a12b-0a37abcdd0bb', '2021-11-16 12:49', '2021-11-16 12:50'),
     ('b8abcd79-d12c-4120-912f-754fabcdd220', '2021-11-16 12:50', '2021-11-16 12:51'),
     ('c3abcd08-e121-4127-b125-4e70abcd5756', '2021-11-16 12:59', '2021-11-16 13:00'),
     ('65abcdbe-7121-412a-a12c-1f68abcd94eb', '2021-11-16 13:00', '2021-11-16 13:01'),
     ('f5abcd38-9122-412c-b12f-957fabcd79b5', '2021-11-16 13:05', '2021-11-16 13:06');


select
    u.SeriesBegin,
    count(u.BeginTime) as cnt,
    min(u.BeginTime) as min,
    max(u.EndTime) as max,
    max(u.EndTime) - min(u.BeginTime) as delta
from (
    select
        v.BeginTime,
        v.EndTime,
        v.isInSeries,
        v.n_in_series,
        lag(BeginTime, v.n_in_series::int) over (order by BeginTime) as SeriesBegin,
        grp
    from (
        select
            t.*,
            row_number() over (
                partition by t.BeginTime - grp*make_interval(mins => t.LengthMin)
                order by t.BeginTime
            ) - 1 as n_in_series
        from
            (
                select
                    row_number() over (order by BeginTime) as grp,
                    BeginTime = lag(EndTime) over (order by BeginTime) as isInSeries,
                    *
                from (
                    select *,
                        (extract(epoch from EndTime - BeginTime)/60)::int as LengthMin
                    from TimeSeries
                ) as s
                where LengthMin = 1
            ) t
        order by t.BeginTime
    ) as v
) as u
group by SeriesBegin
order by SeriesBegin asc;

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f3c2228c4fafac88ffd33de00c07d2c0

\$\endgroup\$
1
  • \$\begingroup\$ Thank you for your answer. In short: I agree on everything. Long: 1. I using LengthMin to filter my data, but I agree, one of the 3 columns is too much. 2. I have to quote the column and table names since they are in Pascalcase in the DB - thanks to EFcore (I did not check if this is configurable). 3. this is one of the reasons why I switched to postgresl 4. about the data, I just exported it from dBeaver, but for positing in the future I will keep this in mind, to clean everything up. 5. this is a general problem I have when always switching languages \$\endgroup\$
    – Fritz
    Nov 25 at 22:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.