Aggregation + last & first -> losing order

Question

I'm trying to select data within 15min step interval. The major grouping seams to work close as expected however i'm loosing order within each 15min group. The reason is that e.g.: For 4 points where time_stamp is in range 0-14 minutes -> "floor(EXTRACT(minute FROM time_stamp) / 15) AS quarter", will return value "0" (as expected). So then ORDER BY "quarter" 4 the rows with "quarter" == "0" from which last and first values is choose. That's leading to the situation where i'm not able to guarantee a sort based on timestamp.

SELECT
    first(value) as first_value,
    last(value) as last_value,
    CAST(EXTRACT(year FROM time_stamp) AS INTEGER) AS year,
    CAST(EXTRACT(month FROM time_stamp) AS INTEGER) AS month,
    CAST(EXTRACT(day FROM time_stamp) AS INTEGER) AS day,
    CAST(EXTRACT(hour FROM time_stamp) AS INTEGER) AS hour,
    floor(EXTRACT(minute FROM time_stamp) / 15) AS quarter,
FROM
    my_table
GROUP BY
    year,
    month,
    day,
    hour,
    quarter,
ORDER BY
    year,
    month,
    day,
    hour,
    quarter

Below is table example:

CREATE TABLE my_table (
    id integer NOT NULL,
    time_stamp timestamp without time zone NOT NULL,
    value double precision NOT NULL,
);


CREATE SEQUENCE my_table_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE ONLY my_table ALTER COLUMN id SET DEFAULT nextval('my_table_id_seq'::regclass);


ALTER TABLE ONLY my_table
    ADD CONSTRAINT my_table_pkey PRIMARY KEY (id);


CREATE INDEX ix_my_table_time_stamp ON my_table USING btree (time_stamp);

I also removed "first" and "last" function from the query to notify that the sort is truly missing.

Any advice how to keep sort withing each 15min step ?

Possible duplicate of [First and last value of window function in one row in PostgreSQL](https://stackoverflow.com/questions/44368672/first-and-last-value-of-window-function-in-one-row-in-postgresql) — JGH, Jun 11 '18 at 15:34

klin · Accepted Answer · 2018-06-11T16:50:56.907

There are no standard aggregate functions first() and last(), you probably mean user defined aggregates like:

create or replace function first_agg(anyelement, anyelement)
returns anyelement language sql immutable strict
as $$ select $1; $$;

create or replace function last_agg(anyelement, anyelement)
returns anyelement language sql immutable strict
as $$ select $2; $$;

create aggregate first(anyelement) (
    sfunc = first_agg,
    stype = anyelement
);

create aggregate last(anyelement) (
    sfunc = last_agg,
    stype = anyelement
);

Use order by in the aggegates, see 4.2.7. Aggregate Expressions in the documentation.

SELECT
    first(value order by time_stamp) as first_value,
    last(value order by time_stamp) as last_value,
    CAST(EXTRACT(year FROM time_stamp) AS INTEGER) AS year,
    CAST(EXTRACT(month FROM time_stamp) AS INTEGER) AS month,
    CAST(EXTRACT(day FROM time_stamp) AS INTEGER) AS day,
    CAST(EXTRACT(hour FROM time_stamp) AS INTEGER) AS hour,
    floor(EXTRACT(minute FROM time_stamp) / 15) AS quarter
FROM
    my_table
GROUP BY
    year,
    month,
    day,
    hour,
    quarter
ORDER BY
    year,
    month,
    day,
    hour,
    quarter

DbFiddle.

Aggregation + last & first -> losing order

1 Answers1