Mysql. Как выбрать записи по ближайшей дате?

Question

[digdream]

Имеется таблица со свойствами вещей, самодостаточная:
storage(id,name,status,price,date_added,filial_id....)
данные например:
1,стол,1,4000,2015-01-07,...
2,стул,2,5000,2015-02-06,...

в ней есть переодические реквизиты, которые меняются со временем это например - поля status и price
Для того чтобы иметь возможность отслеживать эти атрибуты, была создана вторая таблица - состояний:
states (id,thing_id,status,price, state_change_date)
куда при каждой смене атрибута добавляется запись с текущими параметрами и датой смены
1,1,1,4000,2015-01-07
2,1,1,5500,2015-02-01
3,1,4,5500,2015-02-20
4,2,2,5000,2015-02-06

требуется - сделать отчет по всему хранилищу на определенную дату (да еще и по каждому статусу отдельно посчитать, но это частности)

как правильно выбрать из второй таблицы записи, которые являются ближайшими к требуемой дате, чтобы потом джойнить к ним остальные значения и считать количества?

запрос

select *,max(state_change_date) from states where state_change_date<="2015-02-01" group by thing_id

вернет со стороны max - правильный кортеж последних дат по отношению к заданной, а * выберет лишь первые записи, а не те у которых дата=макс_дате

Answer 1

[Rsa97]

SELECT * 
    FROM `states` AS `s` 
    JOIN (
        SELECT `thing_id`, MAX(`state_change_date`) AS `date`
            FROM `states`
            WHERE `state_change_date` <= :request_date
            GROUP BY `thing_id`
    ) AS `d` ON `d`.`thing_id` = `s`.`thing_id` AND `d`.`date` = `s`.`state_change_date`

Comments

[digdream]

Спасибо, вроде то что надо, но очень уж длительный запрос получается. на тестовой выборке в 15000 элементов (23000 состояний) у меня показывает время около минуты

/* Affected rows: 0  Найденные строки: 15 834  Предупреждения: 0  Длительность  1 query: 0,359 sec. (+ 50,591 sec. network)*/

и, развивая этот запрос - пытаюсь объединить пару - до начальной даты и до конечной даты
но видимо если первый join длинный и второй длинный, то все вместе - умножается и у меня еще не удалось дождаться результата:

SELECT s.thing_id as start_tid, s.thing_status as start_ts, s.thing_price as start_tp, s.thing_store_num as start_tsnum,d.date_st as start_date 
    FROM `states` AS `s` 
    JOIN (
        SELECT `thing_id`, MAX(`state_change_date`) AS `date_st`
            FROM `states`
            WHERE `state_change_date` <= "2015-01-01"
            GROUP BY `thing_id`
    ) AS `d` ON `d`.`thing_id` = `s`.`thing_id` AND `d`.`date_st` = `s`.`state_change_date`
right join
(SELECT sf.thing_id as finish_tid, sf.thing_status as finish_ts, sf.thing_price as finish_tp, sf.thing_store_num as finish_tsnum,df.date_f as finish_date 
    FROM `states` AS `sf` 
    JOIN (
        SELECT `thing_id`, MAX(`state_change_date`) AS `date_f`
            FROM `states`
            WHERE `state_change_date` <= "2015-01-01"
            GROUP BY `thing_id`
    ) AS `df` ON `df`.`thing_id` = `sf`.`thing_id` AND `df`.`date_f` = `sf`.`state_change_date`
) as `ft` on `s`.`thing_id`=`ft`.`finish_id`

[Rsa97]

Дмитрий Лихачев: делайте EXPLAIN запроса, смотрите использование индексов

[digdream]

добавление индекса по thing_id+state_change_date существенно подняло производительность частичек (теперь весь запрос выполняется около 5 минут, приемлимо конечно, однако хотелось бы чуть быстрее). есть еще варианты как ускорить join?
или может как то обойтись без join-ов?

[Rsa97]

digdream: Можно попробовать поиграться с порядком JOIN'ов, а можно сделать две выборки на разные даты и объединять их уже в клиенте, занося в ассоциативные массивы.

[digdream]

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

Answer 2

[Алексей Рытиков]

Вы пытаетесь выполнить 2 разные задачи одним запросом. * выбирает лишь «первые» записи, т.к. остальные уже были сгруппированы.
Просто сделайте 2 запроса вместо 1го:

select max(state_change_date) from states where state_change_date<="2015-02-01" group by thing_id
select * from states where state_change_date<="2015-02-01"

P.S. и хватит даты записывать строками "2015-02-01". Используйте специально отведенный тип данный www.mysql.ru/docs/man/DATETIME.html или уж на крайняк в int'е храните timestamp.

Comments

[digdream]

необходимо в один запрос, мало того, в результате это должен получиться один столбец из почти двух десятков других с разными условиями. без второй таблицы результирующий запрос получался под 5Кб текста, теперь надо переделать с учетом состояний.

[Алексей Рытиков]

Дмитрий Лихачев: один столбец запросом select *,max(state_change_date). Напишите пример ожидаемой в ответе таблицы.

[digdream]

с одной таблицей был вот такой запрос, полностью рабочий:

QR.SQL.Add('select n.node_name, '+
   'count(case     when (date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and status=2) or (date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and status=4 and date(sell_date)>="'+date2mysql(report_datestart.Text)+'") then s.id end) as tot_cnt,'+
   'round(sum(case when date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and (status=2) or (date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and status=4 and date(sell_date)>="'+date2mysql(report_datestart.Text)+'") then total_weight end),2) as tot_wt,'+
   'sum(case       when date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and (status=2) or (date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and status=4 and date(sell_date)>="'+date2mysql(report_datestart.Text)+'") then kredit end) as tot_kr,'+
   'sum(case       when date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and (status=2) or (date(s.shop_date)<"'+date2mysql(report_datestart.Text)+'" and status=4 and date(sell_date)>="'+date2mysql(report_datestart.Text)+'") then price end) as tot_pr,'+

   'count(case when date(s.shop_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and (status in(2,4)) then s.id end) as new_cnt,'+
   'round(sum(case when date(s.shop_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and (status in(2,4)) then total_weight end),2) as new_wt,'+
   'sum(case when date(s.shop_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and (status in(2,4)) then kredit end) as new_kr,'+
   'sum(case when date(s.shop_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and (status in(2,4)) then price end)as new_pr,'+

   'count(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then s.id end) as sell_cnt,'+
   'round(sum(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then total_weight end),2) as sell_wt,'+
   'sum(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then kredit end) as sell_kr,'+
   'sum(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then vyruchka end) as sell_pr,'+
   'sum(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 and price>vyruchka then price-vyruchka end) as sell_discount,'+


   'count(case when store_num is not null and date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then s.id end) as sell_l_cnt,'+
   'round(sum(case when store_num is not null and date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then total_weight end),2) as sell_l_wt,'+
   'sum(case when store_num is not null and  date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then kredit end) as sell_l_kr,'+
   'sum(case when store_num is not null and date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=4 then vyruchka end) as sell_l_pr,'+

   'count(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=5 then s.id end) as ret_cnt,'+
   'round(sum(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=5 then total_weight end),2) as ret_wt,'+
   'sum(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=5 then kredit end) as ret_kr,'+
   'sum(case when date(s.sell_date) between "'+date2mysql(report_datestart.Text)+'" and "'+date2mysql(report_dateend.Text)+'" and status=5 then price end) as ret_pr,'+

   'count(case when (status=2 and date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'") or (date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'" and status=4 and date(sell_date)>"'+date2mysql(report_dateend.Text)+'") then s.id end) as end_cnt,'+
   'round(sum(case when (status=2  and date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'") or (date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'" and status=4 and date(sell_date)>"'+date2mysql(report_dateend.Text)+'") then total_weight end),2) as end_wt,'+
   'sum(case when (status=2 and date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'") or (date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'" and status=4 and date(sell_date)>"'+date2mysql(report_dateend.Text)+'") then kredit end) as end_kr,'+
   'sum(case when (status=2 and date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'") or (date(s.shop_date)<="'+date2mysql(report_dateend.Text)+'" and status=4 and date(sell_date)>"'+date2mysql(report_dateend.Text)+'") then s.price end) as end_pr '+

	 'from nodes n left join storage s  on n.Id=s.node_num group by s.node_num order by node_name'
   );

[digdream]

чуть нагляднее pastebin.com/juR38uVE