Блог

У меня есть таблица данных «фондовый рынок» со 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 столбцом?

 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)
  

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

 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 .)

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

 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 , потому что он получит строку с последней датой.

Таким образом, в других СУБД это невозможно, у вас должны быть все столбцы, которые вы выбираете в 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)