Mysql group by MIN(price)

maidan
April 19, 2024
214 views
0 votes
2 Answers

I have a table filled with pre-calculated prices for rental objects. This is a simple version of it:

CREATE TABLE `pricecache` (
  `oid` int(11) NOT NULL DEFAULT '0',
  `start` date NOT NULL,
  `end` date NOT NULL,
  `duration` tinyint(3) UNSIGNED NOT NULL,
  `guests` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `child` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `animal` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',
  `price` float NOT NULL DEFAULT '0',
  `stamp` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `pricecache` (`oid`, `start`, `end`, `duration`, `guests`, `child`, `animal`, `price`, `stamp`) VALUES
(1162, '2023-11-01', '2023-11-08', 7, 1, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-01', '2023-11-08', 7, 2, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-01', '2023-11-08', 7, 3, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-01', '2023-11-08', 7, 4, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-01', '2023-11-08', 7, 5, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-01', '2023-11-08', 7, 6, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-02', '2023-11-09', 7, 1, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-02', '2023-11-09', 7, 2, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-02', '2023-11-09', 7, 3, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-02', '2023-11-09', 7, 4, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-02', '2023-11-09', 7, 5, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-02', '2023-11-09', 7, 6, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-03', '2023-11-10', 7, 1, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-03', '2023-11-10', 7, 2, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-03', '2023-11-10', 7, 3, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-03', '2023-11-10', 7, 4, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-03', '2023-11-10', 7, 5, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-03', '2023-11-10', 7, 6, 0, 0, 679, '2023-10-27 17:00:02'),
(1162, '2023-11-04', '2023-11-11', 7, 1, 0, 0, 679, '2023-10-27 17:00:02'),

Here is the complete file

I want to get the lowest price each day and the number of objects which have that price. I tried this:

SELECT oid, ROUND(MIN(price)/duration, 0) xprice
FROM `my_cache`
WHERE `price` > 0
GROUP BY ROUND(price/duration, 0)
ORDER BY `xprice` ASC

but this way I still see redundant/ungrouped xprice values.

I want to display the data as a price filter similar to airbnb:

How can I do this?

Answers

Here are some hints on what this could be about. There is no expected result and there are some issues in the OP’s code. It is unclear what is "lowest day price of every object". Some of the possibilities with the sample data provided could be:

Lowest price per duration (based on OP’s code)

SELECT x.OID, Min(x.XPRICE) "XPRICE"
FROM  ( Select   t.OID, t.DURATION,
                 ROUND(MIN(t.price)/t.duration, 0) "XPRICE" 
        From     tbl t 
        Where    t.price > 0 
        Group By t.OID, t.DURATION
     ) x
GROUP BY x.OID
HAVING   Min(x.XPRICE) = XPRICE

/*  R e s u l t :
OID    XPRICE
-----  ------
1168      443   */

Two more options in one code for lowest price and lowest price per duration regardless of price-duration pairs

SELECT x.*
FROM  ( Select   t.OID, t.DURATION, t.PRICE,
                 MIN(t.price) Over(Partition By t.OID) "MIN_XPRICE",
                 ROUND(Min(t.PRICE) Over(Partition By t.OID) / t.DURATION, 0) "MIN_XPRICE_PER_DURATION"
        From     tbl t 
        Where    t.price > 0 
     ) x
ORDER BY x.OID, x.MIN_XPRICE, x.MIN_XPRICE_PER_DURATION
LIMIT 1

/*    R e s u l t :
OID    DURATION   PRICE   MIN_XPRICE   MIN_XPRICE_PER_DURATION
----   --------   -----   ----------   -----------------------
1168          9    3990         3170                       352  */

If you want to display items per day, you need to group by the day rather than the price. And to do that you’ll need a calendar table.

In this case, to show all rows that have the minimum price for each day, I’d also use dense_rank() rather than GROUP BY.

with recursive calendar as (
    select (select min(start) from pricecache) dt
    union all 
    select dt + interval 1 day 
    from calendar 
    where dt <= (select max(end) from pricecache)
)
, ranked as ( 
    select
        date(dt) day, 
        pc.*,
        dense_rank() over (partition by date(dt) order by price) rk
    from calendar c
    inner join pricecache pc on pc.start<= date(c.dt) and pc.end >= date(c.dt)
)
select day, oid, start, end, duration, guests, child, animal, price, stamp
from ranked
where rk = 1
order by day, stamp

And then to further get the final number of items each day:

with recursive calendar as (
    select (select min(start) from pricecache) dt
    union all 
    select dt + interval 1 day 
    from calendar 
    where dt <= (select max(end) from pricecache)
)
, ranked as ( 
    select
        date(dt) day, 
        pc.*,
        dense_rank() over (partition by date(dt) order by price) rk
    from calendar c
    inner join pricecache pc on pc.start<= date(c.dt) and pc.end >= date(c.dt)
)
select day, min(price) as price, count(*) as NumberOfItems
from ranked
where rk=1
group by day
order by day

You can see them run here:

https://dbfiddle.uk/K55t94LW

But you won’t see much, because the provided sample data is not meaningful: all of the price and date values are the same.

Please signup or login to give your own answer.

Click here to cancel reply.