CODEWITHSUNDEEP
sqlpostgresql
Jens
Jens

Reputation: 291

How to get the time of the minimum and maximum requests?

I've got a table with product's sections.

__________________________________
| SECTION| PRICE |     TIME      |
|--------------------------------|
|  sec1  |   10  | 06-12-17 12:00|
|  sec2  |   20  | 06-12-17 12:01|
|  sec1  |   20  | 06-12-17 12:02|
|  sec1  |   30  | 06-12-17 12:03|
|  sec2  |   30  | 06-12-17 12:04|
----------------------------------

I need to have min, max and average for every section. I did it.

SELECT MAX(PRICE), MIN(PRICE), AVG(PRICE) FROM table1 GROUP BY SECTION;

I also need a sale time with a minimum and maximum price. If biggest sale was in different times I need any of them. How to get it in one table?

___________________________________________________________
| SECTION| MIN | MAX | AVG |  TIME OF MAX   |  TIME OF MIN |
|----------------------------------------------------------|
|  sec1  | 10  |  30 |  20 | 06-12-17 12:03 |06-12-17 12:00|
|  sec2  | 20  |  30 |  25 | 06-12-17 12:04 |06-12-17 12:01|
-----------------------------------------------------------

Upvotes: 0

Views: 69

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

You are looking for something like Oracle KEEP FIRST, which can only be emulated in PostgreSQL with window functions:

select
  section,
  min(price),
  max(price),
  avg(price),
  max(case when rnk_max = 1 then time end) as time_of_max,
  max(case when rnk_min = 1 then time end) as time_of_min
from
(
  select 
    section, 
    price, 
    time,
    rank() over (partition by section order by price desc) as rnk_max,
    rank() over (partition by section order by price) as rnk_min
  from sections
) ranked
group by section;

I am using RANK, so you can get several records for a minimum or maximum price. With max(case ...) I'll take the newest date from the sets then.

Upvotes: 1

Vao Tsun
Vao Tsun

Reputation: 51629

using window functions without tripple join:

t=# WITH a as (
SELECT
      SECTION
    , MAX(PRICE) over w
    , MIN(PRICE) over w
    , AVG(PRICE) over w
    , TIME t, price
    , case when MAX(PRICE) over w = price then TIME end maxt
    , case when MIN(PRICE) over w = price then TIME end mint
FROM s154
WINDOW w as (partition by section)
)
select DISTINCT
      SECTION
    , MAX
    , MIN
    , AVG
    , max(maxt) over (partition by section)
    , min(mint) over (partition by section)
from a
;
 section  | max | min |         avg         |         max         |         min
----------+-----+-----+---------------------+---------------------+---------------------
   sec1   |  30 |  10 | 20.0000000000000000 | 2017-06-12 12:03:00 | 2017-06-12 12:00:00
   sec2   |  30 |  20 | 25.0000000000000000 | 2017-06-12 12:04:00 | 2017-06-12 12:01:00
(2 rows)

Also as Abelisto noted, on larger data sets, Filtering results before aggregation can significantly reduce the cost. So adding

where maxt is not null or mint is not null

to the end is recommended.

Upvotes: 2

jlee88my
jlee88my

Reputation: 3043

Assumption: Min and Max price can only happen in unique time.

Try this (I choose to write in easy to understand instead of efficient query):

SELECT      sq.*
            , tmin.time AS minTime
            , tmax.time AS maxTime
FROM        (
            SELECT      section
                        , MAX(price)    AS maxPrice
                        , MIN(price)    AS minPrice
                        , AVG(price)    AS avgPrice
            FROM        table1
            GROUP BY    section
        ) AS sq
    INNER JOIN table1 AS tmin
        ON tmin.section = sq.section
        AND tmin.price  = sq.minPrice
    INNER JOIN table1 AS tmax
        ON tmax.section = sq.section
        AND tmax.price  = sq.minPrice;

Upvotes: 0

Related Questions