SQL-запрос с ГРУППОЙ ПО и условиями

#mysql #sql #group-by

#mysql #sql #группировка по

Вопрос:

У меня есть таблица данных «фондовый рынок» со contract значениями. Я хочу получить объем (количество сделок, совершенных в день) и цену закрытия, при этом цена закрытия является последней зарегистрированной ценой контракта в этот торговый день.

 CREATE TABLE IF NOT EXISTS `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `price` decimal(5,2) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1502 ;
  

Я могу получить нужные мне данные (в основном!), Выполнив такой запрос:

 SELECT count(id) as volume, price, DATE(created_at) FROM `contracts` 
GROUP BY DATE(created_at)
  

Однако я хочу, чтобы цена закрытия — возвращаемая цена — это цена любой записи, которая является последней по идентификатору. Есть ли способ получить последнее price значение, заданное created_at столбцом?

Ответ №1:

 SELECT id,
   count(id) AS volume,
   DATE(created_at),
   (SELECT price
      FROM `contracts` as innerContracts
      WHERE innerContracts.id = outerContracts.id
      ORDER BY created_at DESC LIMIT 1)
FROM `contracts` AS outerContracts
GROUP BY DATE(created_at)
  

Комментарии:

1. тестирование этого, похоже, возвращает одинаковую цену для каждой строки.

2. Обновил свой запрос, я ошибся в первый раз. Это должно сработать для вас, мне сложно протестировать, когда у меня нет базы данных передо мной.

3. извините, но теперь он возвращается: #1054 - Unknown column 'outerContracts.id' in 'where clause'

4. #1052 - Column 'id' in field list is ambiguous

5. Попробуйте это. Если это не сработает, не могли бы вы показать мне его результаты? Из того, что я могу сказать, синтаксис правильный, и логика должна работать.

Ответ №2:

Уже есть несколько других ответов, но именно так я бы вернул указанный результирующий набор, используя спецификацию, согласно которой «наибольшее» значение идентификатора для данного дня является идентификатором «последнего» контракта.

 SELECT d.volume
     , p.price     AS close_price
     , d.created_dt
  FROM ( SELECT MAX(c.id)          AS id
              , COUNT(c.id)        AS volume
              , DATE(c.created_at) AS created_dt
           FROM contracts c
          GROUP BY DATE(c.created_at)
       ) d
  JOIN contracts p
    ON p.id = d.id
  

Большая часть работы выполняется встроенным представлением (псевдоним as d ), которое аналогично исходному запросу; это возвращает одну строку для каждой даты, а также количество строк ( volume ) и «наибольшее» значение идентификатора для даты ( id ).

Учитывая, что id это первичный ключ в таблице, мы можем использовать его в операции объединения для извлечения строки из таблицы contracts (псевдоним as p ), чтобы мы могли получить price столбец.


Другой альтернативой может быть использование коррелированного подзапроса в списке ВЫБОРА, например

 SELECT d.volume
     , ( SELECT p.price
           FROM contracts p
          WHERE p.created_at >= d.created_dt
            AND p.created_at < d.created_dt   INTERVAL 1 DAY
          ORDER BY p.created_at DESC
          LIMIT 1
       ) AS close_price
     , d.created_dt
  FROM ( SELECT COUNT(c.id)        AS volume
              , DATE(c.created_at) AS created_dt
           FROM contracts c
          GROUP BY DATE(c.created_at)
       ) d
  

В этом примере я использовал created_at столбец для определения «последнего» контракта на заданную дату. (Мы получаем последний, упорядочивая в порядке убывания, а затем используя предложение LIMIT, чтобы гарантировать, что мы возвращаем не более одной строки. Предложение ORDER BY может так же легко ссылаться p.id , а не на p.created_at .)

Ответ №3:

Одним из решений является использование подзапроса для получения этой цены

 SELECT count(ctr.id) as volume, 
    (SELECT cc.price
        FROM contracts as cc 
        WHERE DATE(cc.created_at) = DATE(ctr.created_at)
        ORDER BY cc.created_at DESC
        LIMIT 1) as price, 
    DATE(ctr.created_at) 
FROM `contracts` as ctr
GROUP BY DATE(ctr.created_at)
  

РЕДАКТИРОВАТЬ: изменен запрос на использование LIMIT и выбор цены. Я использовал ODER BY DESC , потому что он получит строку с последней датой.

Комментарии:

1. @user2989408 — будет ли подзапрос иметь большое снижение производительности? Или это будет минимальным?

2. И это работает? Я не думаю, что он делает то, что вы хотите

3. этот запрос завершается ошибкой с ошибкой «подзапрос возвращает более одной строки»

4. спасибо — но теперь он возвращает одну и ту же цену для каждой даты.

5. @user101289 Не могли бы вы проверить сейчас, просто добавили псевдонимы таблиц к столбцам.

Ответ №4:

Таким образом, в других СУБД это невозможно, у вас должны быть все столбцы, которые вы выбираете в GROUP BY clausule

Попробуйте это

 SELECT volume, price, created_at FROM contracts JOIN ((
    SELECT count(id) as volume, Max(id) maxid FROM `contracts` 
    GROUP BY DATE(created_at)) AS s) ON (s.maxid = contracts.id)