SQL запрос

shadow7256 · Post by **shadow7256** » 04 Aug 2004 15:02

Есть таблица

id time
----------------------------------------------------
1 7/10/2004 1:10AM
2 7/10/2004 23:10 PM

3 8/10/2004 4:15 PM
4 8/10/2004 5:15 PM

5 9/10/2004 10:00 AM

мне надо выбрать последнюю запись из каждого дня. То есть результатом выборки будут записи с id равными 2,4 и 5

Как такое написать?

FatCat · Post by **FatCat** » 04 Aug 2004 15:37

Поскольку Вы не указали сервер, а работа с datetime на разных серверах отличается, я взял на себя смелость слегка обобшить задачу. Пусть имеется таблица с полями id, event_date, event_time (в Вашем случае нужно применить соответствуюшие функции, чтобы разделить дату и время).

Тогда следуюший запрос выдаст искомое

Code: Select all

select * from event_table tb1
where exists(select event_date, max(event_time) from event_table 
                   group by event_date 
                   having tb1.event_date=event_date and 
                             tb1.event_time=max(event_time)
                   )
;

По-моему так...

shadow7256 · Post by **shadow7256** » 04 Aug 2004 15:59

Пардон, база - SQL Server 2000,

такой запрос не работает :cry:

NN13 · Post by **NN13** » 04 Aug 2004 16:03

shadow7256 wrote:Есть таблица

id time
----------------------------------------------------
1 7/10/2004 1:10AM
2 7/10/2004 23:10 PM

3 8/10/2004 4:15 PM
4 8/10/2004 5:15 PM

5 9/10/2004 10:00 AM

мне надо выбрать последнюю запись из каждого дня. То есть результатом выборки будут записи с id равными 2,4 и 5

Как такое написать?

select t1.* from mytable t1
inner join
(select max(time)as "time", convert(varchar(10), time, 101)
from mytable group by convert(varchar(10), time, 101)) t2
on t1.time=t2.time

PS If id is an identity field, the following would be faster:

select t1.* from mytable t1
inner join
(select max(id)as "id", convert(varchar(10), time, 101)
from mytable group by convert(varchar(10), time, 101)) t2
on t1.id=t2.id

shadow7256 · Post by **shadow7256** » 04 Aug 2004 16:50

выдает ошибку

"No column was specified for column 2 for t2"

FatCat · Post by **FatCat** » 04 Aug 2004 17:01

shadow7256 wrote:Пардон, база - SQL Server 2000,

такой запрос не работает

Увы SQL сервера у меня нет. На Информиксе работает.

shadow7256 · Post by **shadow7256** » 04 Aug 2004 18:51

Совсем забыл.

Есть еще третье поле - HouseID. Так вот надо выбрать эти значения только для определенного HouseID,

Суть такова, каждые 15 минут в базу дампятся данные для примерно 10 разных айтемов. Мне надо выбрать данные для конкретного айтема за последний временной интервал за каждый день. По другому говоря последнюю запись для каждого айтема за каждый день...

NN13 · Post by **NN13** » 04 Aug 2004 20:38

shadow7256 wrote:Совсем забыл.

Есть еще третье поле - HouseID. Так вот надо выбрать эти значения только для определенного HouseID,

Суть такова, каждые 15 минут в базу дампятся данные для примерно 10 разных айтемов. Мне надо выбрать данные для конкретного айтема за последний временной интервал за каждый день. По другому говоря последнюю запись для каждого айтема за каждый день...

Ну минимальные измения можете сами сделать?
second column там так, для удобства:

select t1.* from mytable t1
inner join
(select max(id) as "id", convert(varchar(10), time, 101) as "day"
from mytable group by convert(varchar(10), time, 101)) t2
on t1.id=t2.id
where houseid=... "Ваше значение"

Если надо не за день, а за другой интервал, то надо поменять условие группировки во внутреннем select'e.

SVK · Post by **SVK** » 04 Aug 2004 21:00

Некоторые возражения для NN13.

1. Поле ID в общем случае нельзя считать (хоть как-то) упорядоченным - оно только уникальное. Может быть и в обратном порядке, или вообще - как реализовано конкретно в этой версии. Нельзя по нему выбирать max(id) и т.п. К тому же в этой задаче разумно использовать вместо id собственно значение времени.

2. По-моему, надо выбрать не "те дни, где последним было заданное значение", а "последнюю запись для каждого (или нужного) значения в каждом дне". А тогда логика будет не такая.

Хотя синтаксис и зависит от выбранной базы, но разница больше сводится к разным именам используемых функций. Для DB2 работает так (ну, вместо HouseID использован TEXT):

При таком содержимом таблицы:

Code: Select all

----------------------------------------------------------
select * from TESTTIME                                    
order by date(TIMES), TEXT, TIMES                         
fetch first 20 rows only;                                                         
---------+---------+---------+---------+---------+--------
         ID  TIMES                       TEXT             
---------+---------+---------+---------+---------+--------
        139  1985-08-07-14.19.01.068476  TEXT000          
          2  1985-08-07-14.43.36.068476  TEXT000          
        136  1985-08-07-14.11.31.068476  TEXT001          
         69  1985-08-07-14.29.33.068476  TEXT001          
         70  1985-08-07-14.32.35.068476  TEXT001          
         36  1985-08-07-14.33.38.068476  TEXT001          
         68  1985-08-07-14.10.40.068476  TEXT002          
         72  1985-08-07-14.31.03.068476  TEXT002          
         17  1985-08-07-14.34.40.068476  TEXT002            
          9  1985-08-07-14.38.34.068476  TEXT002            
        134  1985-08-07-14.03.05.068476  TEXT003            
        130  1985-08-07-14.04.29.068476  TEXT003            
         18  1985-08-07-14.38.48.068476  TEXT003            
         65  1985-08-07-14.13.17.068476  TEXT004            
        138  1985-08-07-14.16.26.068476  TEXT004            
        143  1985-08-07-14.19.01.068476  TEXT004            
         34  1985-08-07-14.27.40.068476  TEXT004            
        133  1985-08-07-14.09.34.068476  TEXT005            
        141  1985-08-07-14.19.03.068476  TEXT005            
        140  1985-08-07-14.29.31.068476  TEXT005            
DSNE610I NUMBER OF ROWS DISPLAYED IS 20

Выборка будет такая

Code: Select all

------------------------------------------------------------  
select * from TESTTIME                                        
where (TEXT, TIMES) in (                                      
   select T.TEXT, max(T.TIMES) from TESTTIME as T             
   join ( select distinct TEXT, date(TIMES) as DATE_ONLY      
          from TESTTIME                                       
   ) as D                                                     
   on D.DATE_ONLY = date(T.TIMES)                             
   and D.TEXT = T.TEXT                                        
   group by T.TEXT, D.DATE_ONLY                               
)                                                             
order by date(TIMES), TEXT, TIMES                             
fetch first 20 rows only;                                                             
---------+---------+---------+---------+---------+---------+--
         ID  TIMES                       TEXT                 
---------+---------+---------+---------+---------+---------+--
          2  1985-08-07-14.43.36.068476  TEXT000              
         36  1985-08-07-14.33.38.068476  TEXT001     
          9  1985-08-07-14.38.34.068476  TEXT002     
         18  1985-08-07-14.38.48.068476  TEXT003     
         34  1985-08-07-14.27.40.068476  TEXT004     
        140  1985-08-07-14.29.31.068476  TEXT005     
        144  1985-08-07-14.34.04.068476  TEXT006     
         35  1985-08-07-14.30.24.068476  TEXT007     
        132  1985-08-07-14.17.34.068476  TEXT008     
        142  1985-08-07-14.33.42.068476  TEXT009     
         74  1989-02-27-14.21.45.180893  TEXT000     
         39  1989-02-27-14.38.41.180893  TEXT001     
         10  1989-02-27-14.39.56.180893  TEXT002     
        159  1989-02-27-14.31.14.180893  TEXT003     
         19  1989-02-27-14.35.07.180893  TEXT004     
        156  1989-02-27-14.24.57.180893  TEXT005     
        157  1989-02-27-14.30.45.180893  TEXT006     
          7  1989-02-27-14.43.51.180893  TEXT007    
         40  1989-02-27-14.31.43.180893  TEXT008    
         76  1989-02-27-14.16.33.180893  TEXT009    
DSNE610I NUMBER OF ROWS DISPLAYED IS 20

NN13 · Post by **NN13** » 05 Aug 2004 15:26

SVK wrote:Некоторые возражения для NN13.

1. Поле ID в общем случае нельзя считать (хоть как-то) упорядоченным - оно только уникальное. Может быть и в обратном порядке, или вообще - как реализовано конкретно в этой версии. Нельзя по нему выбирать max(id) и т.п. К тому же в этой задаче разумно использовать вместо id собственно значение времени.

2. По-моему, надо выбрать не "те дни, где последним было заданное значение", а "последнюю запись для каждого (или нужного) значения в каждом дне". А тогда логика будет не такая.

Я отметил в своем первом сообщении, что с id будет работать быстрее, если это identity field, а тогда оно упорядочено.
А так:
select t1.* from mytable t1
inner join
(select max(time)as "time", convert(varchar(10), time, 101) as "day"
from mytable group by convert(varchar(10), time, 101)) t2
on t1.time=t2.time

дает как раз "последнюю запись для каждого (или нужного) значения в каждом дне".

Я вот хотел спросить про DB2 - там такой оптимизатор умный, что можно позволить себе писать "where (...,...) in (...) вместо inner join?
Для MS SQL это определенно не пройдет.
А вообще, DB2 стоит, завтра сам проверю.

SVK · Post by **SVK** » 05 Aug 2004 16:08

NN13 wrote:Я отметил в своем первом сообщении, что с id будет работать быстрее, если это identity field, а тогда оно упорядочено.

1) Identity зависит от реализации. Никто не гарантирует, что всегда будет по возрастанию. Это - как надеяться, что записи будут выданы по порядку даже без ORDER BY - "потому что они по порядку добавлялись". Иногда такое проходило...

2) Например в той же DB2 можно явно указать другой порядок генерации identity. Иногда нужно его менять, для оптимального partitioning, или еще зачем... Могут его поменять и при реорганизации базы, и много еще чего...

3) Ниоткуда также не следует, что записи а базу добавлялись в хронологическом порядке. Стоит только один раз добавить "вчерашнюю" запись, и вся та логика с ID может опять пойти насмарку

Не стоит программе выборки полагаться на ненужные предположения... Рано или поздно это "вдруг" перестанет работать :nono#:

NN13 wrote:А так:
select t1.* from mytable t1
inner join
(select max(time)as "time", convert(varchar(10), time, 101) as "day"
from mytable group by convert(varchar(10), time, 101)) t2
on t1.time=t2.time

дает как раз "последнюю запись для каждого (или нужного) значения в каждом дне".

По-моему, тут будет выдана "последняя запись за каждый день", без какой-либо связи с возможными значениями HouseID (или TEXT в моем примере). А нужно "последнюю в день для каждого HouseID"

А затем WHERE из примера в предыдущем посте просто отсеет те записи, где заданный HouseID не был последний за день? Или я чего-то не понимаю?

NN13 wrote:Я вот хотел спросить про DB2 - там такой оптимизатор умный, что можно позволить себе писать "where (...,...) in (...) вместо inner join?
Для MS SQL это определенно не пройдет.

Не во всех версиях DB2 это работало.
В принципе, если не работает, то это тоже заменить на JOIN по двум ключам...

shadow7256 · Post by **shadow7256** » 05 Aug 2004 16:21

Мне нужно последнюю в день для определенного House ID

SVK · Post by **SVK** » 05 Aug 2004 16:43

Вот, кстати, для полноты картины вариант для отбора по нужным HouseID/TEXT (для 'TEXT002'-'TEXT003'):

Code: Select all

------------------------------------------------------------ 
select * from TESTTIME                                       
where (TEXT, TIMES) in (                                     
   select T.TEXT, max(T.TIMES) from TESTTIME as T            
   join ( select distinct TEXT, date(TIMES) as DATE_ONLY     
          from TESTTIME                                      
          where TEXT between 'TEXT002'                       
                         and 'TEXT003'                       
   ) as D                                                    
   on D.DATE_ONLY = date(T.TIMES)                            
   and D.TEXT = T.TEXT                                       
   group by T.TEXT, D.DATE_ONLY                              
)                                                            
order by date(TIMES), TEXT                                   
fetch first 20 rows only                                     
;                                                            
---------+---------+---------+---------+---------+---------+-
         ID  TIMES                       TEXT                
---------+---------+---------+---------+---------+----
          9  1985-08-07-14.38.34.068476  TEXT002      
         18  1985-08-07-14.38.48.068476  TEXT003      
         10  1989-02-27-14.39.56.180893  TEXT002      
        159  1989-02-27-14.31.14.180893  TEXT003      
        173  1990-05-23-14.24.12.180893  TEXT002      
          8  1990-05-23-14.43.51.180893  TEXT003      
        186  1991-04-10-14.14.46.361206  TEXT002      
        190  1991-04-10-14.15.37.361206  TEXT003      
         13  1992-09-22-14.40.00.361206  TEXT002      
        195  1992-09-22-13.55.00.361206  TEXT003      
        112  1996-07-27-14.25.50.180893  TEXT002      
        109  1996-07-27-14.05.55.180893  TEXT003      
        119  1999-09-12-14.21.36.180893  TEXT002      
        120  1999-09-12-14.28.26.180893  TEXT003      
        256  2004-07-25-14.37.04.582338  TEXT002      
        125  2004-07-25-14.22.50.582338  TEXT003            
DSNE610I NUMBER OF ROWS DISPLAYED IS 16

SVK · Post by **SVK** » 05 Aug 2004 16:48

shadow7256 wrote:Мне нужно последнюю в день для определенного House ID

Ну, я думал, что такую модификацию сделать проще, чем заменить код для MS SQL (которого у меня под рукой нет, а специально для этого дела затевать мороку неохота)

А если именно для одного значения - надо в пред. примере заменить выражение
. . . where TEXT between значение1 and значение2
на выражение
. . . where TEXT = значение1

That's it.

SVK · Post by **SVK** » 05 Aug 2004 16:57

Можно делать отбор и "в лоб", вот так:

Code: Select all

------------------------------------------------------------ 
select * from TESTTIME                                       
where (TEXT, TIMES) in (                                     
   select T.TEXT, max(T.TIMES) from TESTTIME as T            
   join ( select distinct TEXT, date(TIMES) as DATE_ONLY     
          from TESTTIME                                      
   ) as D                                                    
   on D.DATE_ONLY = date(T.TIMES)                            
   and D.TEXT = T.TEXT                                       
   group by T.TEXT, D.DATE_ONLY                              
)                                                            
and TEXT = 'TEXT002'                       
order by date(TIMES), TEXT;

Но это гораздо хуже из-за излишней внутренней выборки. Лучше выбирать по одному значению так:

Code: Select all

------------------------------------------------------------ 
select * from TESTTIME                                       
where (TEXT, TIMES) in (                                     
   select T.TEXT, max(T.TIMES) from TESTTIME as T            
   join ( select distinct TEXT, date(TIMES) as DATE_ONLY     
          from TESTTIME 
          where TEXT = 'TEXT002'                       
   ) as D                                                    
   on D.DATE_ONLY = date(T.TIMES)                            
   and D.TEXT = T.TEXT                                       
   group by T.TEXT, D.DATE_ONLY                              
)                                                            
order by date(TIMES), TEXT;

А если таблица большая, то еще надо серьезно думать про оптимизацию запроса...

Привет

SQL запрос

SQL запрос

Re: SQL запрос