2

How do I get the MAX (or another aggregate function) for of a column of each row, OVER its 3 days worth of rows?

SQL example with expected output and db schema: http://sqlfiddle.com/#!17/24686/3

CREATE TABLE public.tbl (
  date    DATE       NOT NULL,
  someNum DECIMAL    NOT NULL,
  name    VARCHAR(6) NOT NULL,
  elem    VARCHAR(9) NOT NULL,
  PRIMARY KEY ("date", someNum, "name", elem)
);

INSERT INTO public.tbl ("date", someNum, "name", elem) VALUES
  ('2017-12-05', 50.5, '0hello', 'nice elem'),
  ('2017-12-05', 05.5, '1hello', 'nice elem'),
  ('2017-12-05', 55.5, '2hello', 'nice elem'),
  ('2017-12-09', 59.5, '3hello', 'nice elem'),
  ('2017-12-09', 60.5, '4hello', 'nice elem'),
  ('2017-12-10', 90.5, '5hello', 'nice elem'),
  ('2017-12-12', 10.5, '6hello', 'nice elem'),
  ('2017-12-15', 50.3, '7hello', 'nice elem'),
  ('2017-12-30', 70.5, '8hello', 'nice elem'),
  ('2018-01-01', 50.5, '9hello', 'nice elem'),
  ('2017-12-05', 05.5, '10ello', 'mean elem'),
  ('2017-12-05', 5505, '11ello', 'mean elem'),
  ('2017-12-05', 6045, '12ello', 'mean elem'),
  ('2017-12-03', 9045, '13ello', 'mean elem'),
  ('2017-12-04', 1345, '14ello', 'mean elem'),
  ('2017-10-02', 1111, '15ello', 'mean elem'),
  ('2017-10-03', 5555, '16ello', 'mean elem'),
  ('2017-10-04', 66.6, '16ello', 'mean elem');

Desired output,

-- MAX over 3 day period
-- date         somenum     name        elem            max_over_3days
-- 2017-10-02   1111        '15ello'    'mean elem'     5555
-- 2017-10-03   5555        '16ello'    'mean elem'     5555
-- 2017-10-04   66.6        '16ello'    'mean elem'     5555
-- 2017-12-03   9045        '13ello'    'mean elem'     9045
-- 2017-12-05   6045        '12ello'    'mean elem'     9045
-- 2017-12-04   1345        '14ello'    'mean elem'     9045
-- 2017-12-05   05.5        '10ello'    'mean elem'     9045
-- 2017-12-05   5505        '11ello'    'mean elem'     9045
-- 2017-12-05   50.5        '0hello'    'nice elem'     9045
-- 2017-12-05   55.5        '2hello'    'nice elem'     9045
-- 2017-12-05   05.5        '1hello'    'nice elem'     9045
-- 2017-12-09   60.5        '4hello'    'nice elem'     90.5
-- 2017-12-09   59.5        '3hello'    'nice elem'     90.5
-- 2017-12-10   90.5        '5hello'    'nice elem'     99.5
-- 2017-12-13   99.5        '6hello'    'nice elem'     99.5
-- 2017-12-15   50.3        '7hello'    'nice elem'     99.5
-- 2017-12-30   70.5        '8hello'    'nice elem'     70.5
-- 2018-01-01   50.5        '9hello'    'nice elem'     70.5

SELECT * FROM public.tbl
GROUP BY elem, "date", name, someNum
ORDER BY elem, "date";

PS: Also interested with how I can cover 3 work days rather than calendar days

  • sqlfiddle.com/#!17/24686/3 , the max_over_3days of 2017-12-04 should be 6045, right ? – Luan Huynh Aug 14 '17 at 2:08
  • No, 9045, because 2017-12-03 has 9045, so the MAX would be that. Also interested in other aggregate functions, MAX to illustrate the case. – A T Aug 14 '17 at 2:11
  • 1
    What he really wants is RANGE with a defined window. PostgreSQL doesn't have that yet. – Evan Carroll Aug 14 '17 at 15:51
1

Using CROSS JOIN LATERAL

The output is similar to your output, but please check again. Here's, I using lateral (as loop) to find the max value of someNume for the 3 surrounding days (it doesn't mention about performance).

SELECT tbl.date, tbl.somenum, tbl.name, tbl.elem, t2.max_somenum
FROM tbl
CROSS JOIN LATERAL (
  SELECT MAX(t2.somenum) AS max_somenum
  FROM tbl t2 
  WHERE t2.date >= (tbl.date - interval '3 days') AND t2.date <= (tbl.date + interval '3 days')
) t2
ORDER BY date;
  • 1
    Great answer, this is how I would do it too. Stick around! Except I would make sure t2.date and tbl.date was always TIMESTAMP WITHOUT TIME ZONE before you did the date math so you don't throw different results on DST – Evan Carroll Aug 14 '17 at 15:49
1

http://sqlfiddle.com/#!17/24686/15

Note: It wasn't clear what 3 surrounding days meant, so I just divided the date range into groups of 3 days based on dense rank. If you wanted 3 days (prior date, this date, next date) you could use LEAD / LAG or, if your database supported it, see "window frame between" in the spec. That should do it.

For my example: I include a few extra calculations for you to consider.

Basically, we obtain the dense rank of each row by date and then divide by 3 (with integer truncation) to calculate a value which allows grouping the rows in groups of 3 (dates) via the PARTITION BY clause. The MAX window function is then applied to these partitions.

WITH cte1 AS (
          SELECT t.*
               , ROW_NUMBER() OVER (ORDER BY date, someNum, "name") AS rn
               , ROW_NUMBER() OVER (ORDER BY date                 ) AS rn2
               , RANK()       OVER (ORDER BY date, someNum, "name") AS r
               , RANK()       OVER (ORDER BY date                 ) AS r2
               , DENSE_RANK() OVER (ORDER BY date                 ) AS r3
            FROM public.tbl AS t
     )
   , cte2 AS (
          SELECT c1.*
               , r3/3                 AS part
               , ((r3-1)/3)           AS part2
            FROM cte1 AS c1
     )
SELECT c2.*
     , SUM(someNum) OVER (PARTITION BY part2) AS sumv
     , MIN(someNum) OVER (PARTITION BY part2) AS minv
     , MAX(someNum) OVER (PARTITION BY part2) AS maxv
  FROM cte2 AS c2
 ORDER BY elem, date
;

After further review, it looks like your expected results are wrong, if you wanted the max for 3 days (1 before, current, 1 after). Here's the solution for that case:

http://sqlfiddle.com/#!17/24686/26

WITH cte1 AS (
          SELECT t.date
               , MAX(t.someNum) AS maxnum
            FROM public.tbl AS t
           GROUP BY t.date
     )
   , cte2 AS (
          SELECT c1.date
               , c1.maxnum
               , MAX(maxnum) OVER (ORDER BY date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS maxnum2
            FROM cte1 AS c1
     )
SELECT t.*
     , c2.maxnum2
  FROM public.tbl AS t
  JOIN cte2       AS c2
    ON c2.date = t.date
 ORDER BY t.elem, t.date
;

Additionally, if your database supports it, see: "MAX(someNum) OVER (ORDER BY date RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)"

  • Btw, you also have data in your expected results that are not in your test data. You can't have a MAX that isn't in the set (see: 99.5). – Jon Armstrong Aug 14 '17 at 6:24

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.