Как получить только первые строки из групп строк отсортированной таблицы?

Question

[sangan]

У меня есть таблица вида:

id | size | price | date
0  | 30   | 800   | 2021-10-01
1  | 30   | 900   | 2021-10-02
2  | 32   | 700   | 2021-09-11
3  | 30   | 800   | 2021-09-21
4  | 32   | 800   | 2021-09-01
5  | 32   |  0    | 2021-10-03

Мне нужно получить последние цены из истории цен для искомого размера или меньше, если последняя цена искомого размера нулевая.
Я сортирую таблицу по убыванию размера, потом убыванию даты. Получаю сгруппированные по размеру строки. Но мне нужны только первые из групп, чтобы проверить цену на ноль и взять первую ненулевую.

Мой запрос:

SELECT *
    FROM (SELECT * 
            FROM `prices` 
                WHERE model_id = '269'
                    AND partner_id = '0'
                    AND size <= '32'
                    AND date_time <= '2021-10-19' 
             ORDER BY size DESC, date_time DESC
    ) AS t_1 
        GROUP BY size
        LIMIT 1

Первая часть выполняется правильно, но при группировке сортировка не сохраняется, что и логично. Как правильно выполнить этот запрос, чтобы получить сначала:

id | size | price | date
5  | 32   |  0    | 2021-10-03
2  | 32   | 700   | 2021-09-11
4  | 32   | 800   | 2021-09-01
1  | 30   | 900   | 2021-10-02
0  | 30   | 800   | 2021-10-01
3  | 30   | 800   | 2021-09-21

Потом:

id | size | price | date
5  | 32   |  0    | 2021-10-03
1  | 30   | 900   | 2021-10-02

А в итоге 900 или, если бы у id 5 было ненулевое значение цены, то это ненулевое значение.
В общем нужно по аналогии с GROUP BY выбрать по одной строке на размер, но сохранить при этом сортировку внутри группы.
Или есть какой то более правильный путь для решения этой задачи?

Comments

[Slava Rozhnev]

Версия MySQL?

[sangan]

Slava Rozhnev, 5.6

[Akina]

Мне нужно получить последние цены из истории цен для искомого размера или меньше, если последняя цена искомого размера нулевая.

А что насчёт размера, если у него все цены - нулевые? вывести с нулём? вообще не выводить?

И каково приблизительно среднее количество записей для каждого размера (соотношение COUNT(*)/COUNT(DISTINCT size))?

[sangan]

Akina, можно не выводить. А можно и с нулем. Не важно.

Answer 1

[alexalexes]

model_id = '269'
AND partner_id = '0'
AND size <= '32'

Ай-ай-ай. Все Id-шники строками заданы, не тот тип данных выбрали для столбцов.
Выбрать первые записи от какой-то категории можно c помощью коррелирующего группирующего подзапроса (где count считается).

SELECT id, size, price, date
  FROM prices p
  WHERE model_id = 269
       AND partner_id = 0
       AND date_time <= '2021-10-19' 
       AND (select count(*)
           from prices p2
          where  -- сюда нужно прописать параметры строк, по которым будет определяться окно счетчика
                     -- то есть, отсчет счетчика будет в пределах одного размера, одной и той же модели, одного и того де партнера.
                      p2.size = p.size
               and p2.partner_id = p.partner_id
              and  p2.model_id = p.model_id
              -- а тут параметр, чем отличается одна строка счетчика от другой
              and p2.date > p.date 
        ) < 1 -- сколько строк отсечь от категории (n + 1) ?
order by p.date desc

Comments

[sangan]

Ай-ай-ай. Все Id-шники строками заданы, не тот тип данных выбрали для столбцов.

Это кавычки лишние)
Спасибо большое. Мощно) Только условие по размеру и ненулевой цене добавил. И сортировка по размеру, а не по дате.

Answer 2

[Slava Rozhnev]

MySQL 5.* (SUB-QUERY):

SELECT 
  prices.* 
FROM 
  `prices` 
JOIN (
	SELECT size, MAX(date_time) date_time FROM prices GROUP BY size
) last_price USING (size, date_time)
WHERE 
  -- model_id = '269' AND partner_id = '0' AND 
  size <= '32' 
  AND date_time <= '2021-10-19' 
  AND price > 0
ORDER BY 
  size DESC, 
  date_time DESC;

MySQL 8.0 (WINDOW Functions):

SELECT * FROM (
  SELECT 
	prices.*,
	ROW_NUMBER() OVER (PARTITION BY size ORDER BY date_time DESC) last_price
  FROM 
	`prices` 
  WHERE 
	-- model_id = '269' AND partner_id = '0' AND 
	size <= '32' 
	AND date_time <= '2021-10-19' 
) data
WHERE last_price = 1 AND price > 0
ORDER BY 
  size DESC;

Test SQL queries online

Answer 3

[Rsa97]

Для MySQL 8

WITH `cte` AS (
  SELECT *, ROW_NUMBER() OVER `w` AS `row_num` 
    FROM `table`
    WHERE `size` <= :size
    WINDOW `w` AS (
      PARTITION BY `size`
      ORDER BY `date` DESC
    )
) SELECT *
  FROM `cte`
  WHERE `row_num` = 1
  ORDER BY `price` = 0, `size` DESC
  LIMIT 1

Для MySQL < 8

SELECT `t`.*
  FROM (
    SELECT `size`, MAX(`date`) as `date`
      FROM `table`
      WHERE `size` <= 32
      GROUP BY `size`
  ) AS `m`
  JOIN `table` AS `t`
    ON `t`.`size` = `m`.`size` AND `t`.`date` = `m`.`date`
  ORDER BY `t`.`price` = 0, `t`.`size` DESC
  LIMIT 1

Answer 4

[Akina]

SELECT prices.* 
FROM prices
JOIN ( SELECT size, MAX(`date`) AS `date`
       FROM prices 
       WHERE price
--       AND `date` <= @some_date
       GROUP BY size ) AS last_nonzero_date USING (size, `date`);

Запрос предполагает, что (size, `date`) - уникально.

Если для size все цены нулевые (или NULL) - запись для такого размера не будет выведена.

Если нужно состояние на определённую дату - раскомментировать WHERE.