Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
оу, это оказывается были координаты. а мне подумалось - цены в неких тугриках.
но в целом, все как я и говорил - отследить точки изменения значений в полях х,у
22 янв 13, 12:41    [13808382]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
ITHunter
Member

Откуда:
Сообщений: 101
Варианты:
1.
select
*
from supertable
inner join (select max(id) maxid from supertable group by x,y) subq on id = maxid

2.
select
*
from supertable
where id in (select max(id) maxid from supertable group by x,y)

зачем здесь row number?
22 янв 13, 12:43    [13808402]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
ITHunter
Member

Откуда:
Сообщений: 101
ITHunter
Варианты:
1.
select
*
from supertable
inner join (select max(id) maxid from supertable group by x,y) subq on id = maxid

2.
select
*
from supertable
where id in (select max(id) maxid from supertable group by x,y)

зачем здесь row number?

забыл добавить - в данном примере такой группировки достаточно, но если нужно - добавить по вкусу
22 янв 13, 12:44    [13808416]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
ITHunter,

затем, что одни и те же координаты будут повторяться во времени (судя по примеру данных от автора), твой запрос посчитает, что это был один кусок непрерывности, что опять не соответствует пожеланиям автора
22 янв 13, 12:49    [13808464]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
gang
Member

Откуда:
Сообщений: 1394
Добрый Э - Эх
одним row_number-ом задача не решается, нужен как минимум ещё один.

Да вроде как решается. В стилистике ТС приложу "картинки":
Картинка с другого сайта.
Картинка с другого сайта.
22 янв 13, 12:49    [13808468]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
Еще раз: допустим, машина кокое-то кол-во замеров стояла во дворе. После она несколько замеров куда-то ехала. Там опять постояла энное число замеров. После возвратилась в исходную точку. Так вот, первоначальное положение и конечная точка - должны разделиться, а не собраться в одну строку.
22 янв 13, 12:52    [13808491]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
aslan1980
Member

Откуда:
Сообщений: 178
К ITHunter Спасибо за ответ.

Ваш запрос не возвращает строку с номером ID=4, которая была показана в результирующей таблице.

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

Тоесть мне нужно получить Оба строки ID=4 и ID=10

К сообщению приложен файл. Размер - 17Kb
22 янв 13, 12:58    [13808532]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
gang,

ну и в общеобразовательных целях: если в конструкции OVER некое поле указано во фразе partition by, то во фразе ORDER BY этого OVER-а данное поле указывать бессмысленно. Ибо в пределах одной секции значения поля меняться не будут ;)
22 янв 13, 13:01    [13808559]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
aslan1980
К ITHunter Спасибо за ответ.

Ваш запрос не возвращает строку с номером ID=4, которая была показана в результирующей таблице.

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

Тоесть мне нужно получить Оба строки ID=4 и ID=10

Ну вот, ровно так я и уточнял задачу
22 янв 13, 13:03    [13808573]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
X и Y - какого типа?? FLOAT, небось?!

Тогда, Добрый Э - Эх, слишком простые решения предлагаете, вот что я Вам скажу!

Сравнивать на точное равенство координаты типа FLOAT (в PARTITION BY), мягко говоря, не совсем правильно...
22 янв 13, 13:18    [13808684]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
iap,

сложность решения от типа данных координат никак не повышается, общий алгоритм не изменяется. нужно лишь решить вопрос, в пределах какой точности числа считать равными. не более.
22 янв 13, 13:25    [13808753]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
ITHunter
Member

Откуда:
Сообщений: 101
Пойдет? (не уверен, что оптимально, если большие таблицы с индексами придется поработать, но row number совсем не самолет) :

select * from supertable
where id in
(
select
max(t1.id)
from supertable t1
left join supertable t3 on t3.id < t1.id and (t3.x<>t1.x or t3.y<>t1.y)
left join supertable t2 on t2.id > t1.id and t2.id > t3.id and (t2.x<>t1.x or t2.y<>t1.y)
group by t1.x, t1.y,t2.id
)

зы
row nuber нынче модно везде использовать?
22 янв 13, 13:51    [13809056]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
ну и я вариантик закину)
--declare @timing table(id int identity(1,1) primary key, EventId int, Avto varchar(50), Time datetime, X float, Y float)
declare @timing table(id int identity(9,7), EventId int, Avto varchar(50), Time datetime, X float, Y float)
+
declare @now datetime=getdate()
insert into @timing values
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.45, 40.25),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.96, 40.52),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.98, 40.58),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.98, 40.65),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.98, 40.65),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.99, 40.76)
select * from @timing 
select lst.* 
   from @timing lst
   --left join @timing nxt on nxt.id=lst.id+1 and nxt.X=lst.X and nxt.Y=lst.Y
   --where nxt.id is null 
   outer apply (
      select top 1 *
         from @timing t where t.EventId=lst.EventId and t.id>lst.id
         order by t.id
   ) nxt
   where nxt.id is null or nxt.X != lst.X or nxt.Y != lst.Y
22 янв 13, 14:04    [13809145]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
ITHunter
Member

Откуда:
Сообщений: 101
ITHunter
Пойдет? (не уверен, что оптимально, если большие таблицы с индексами придется поработать, но row number совсем не самолет) :

select * from supertable
where id in
(
select
max(t1.id)
from supertable t1
left join supertable t3 on t3.id < t1.id and (t3.x<>t1.x or t3.y<>t1.y)
left join supertable t2 on t2.id > t1.id and t2.id > t3.id and (t2.x<>t1.x or t2.y<>t1.y)
group by t1.x, t1.y,t2.id
)

зы
row nuber нынче модно везде использовать?
22 янв 13, 14:05    [13809162]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
ITHunter
Member

Откуда:
Сообщений: 101
ITHunter
ITHunter
Пойдет? (не уверен, что оптимально, если большие таблицы с индексами придется поработать, но row number совсем не самолет) :

select * from supertable
where id in
(
select
max(t1.id)
from supertable t1
left join supertable t3 on t3.id < t1.id and (t3.x<>t1.x or t3.y<>t1.y)
left join supertable t2 on t2.id > t1.id and t2.id > t3.id and (t2.x<>t1.x or t2.y<>t1.y)
group by t1.x, t1.y,t2.id
)

зы
row nuber нынче модно везде использовать?



вот теперь точно все. упустил группировочку
select * from supertable
where id in
(
select
max(t1.id)
from supertable t1
left join supertable t3 on t3.id < t1.id and (t3.x<>t1.x or t3.y<>t1.y)
left join supertable t2 on t2.id > t1.id and t2.id > t3.id and (t2.x<>t1.x or t2.y<>t1.y)
group by t1.x, t1.y,t2.id,t3.id
)
22 янв 13, 14:10    [13809216]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
PS
aslan1980
Я еще более шире сформулирую свой вопрос.
улыбнуло)
Правильнее было бы "Я еще ширше сформулирую" :)
22 янв 13, 14:22    [13809332]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
aslan1980
Member

Откуда:
Сообщений: 178
К ITHunter и Cygapb-007 и другим участникам принимавшие участие в решение задачи огромнейшее спасибо.

Задача решена
22 янв 13, 14:23    [13809347]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
ITHunter
зы
row nuber нынче модно везде использовать?
просто как ни крути, но один скан таблицы + пара row_number-ов по любому читабельней смотрится и сервер нагружает не сильнее трехкратного обращения к таблице с последующими LEFT JOIN-ами и группировкой. ;)

Кроме того, новые инструменты для того и делают - чтобы облегчить жизнь и программистам, и серверу.

К примеру, ты же на гужевой повозке сейчас не ездишь, хоть она с изобретением автомобиля не потеряла способности передвигаться... ;)
22 янв 13, 16:10    [13810537]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
а меня от cross/outer apply торкает:)
22 янв 13, 16:36    [13810774]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
ITHunter
вот теперь точно все. упустил группировочку
+ < Свернул портянку, чтобы цитирование было не сильно громоздким :)
select * from supertable
where id in
(
select
max(t1.id)
from supertable t1
left join supertable t3 on t3.id < t1.id and (t3.x<>t1.x or t3.y<>t1.y)
left join supertable t2 on t2.id > t1.id and t2.id > t3.id and (t2.x<>t1.x or t2.y<>t1.y)
group by t1.x, t1.y,t2.id,t3.id
)

В твоём варианте видим 4 (четыре) обращения к таблице, малоприятный IN-subquery (так-то понятно, что если оптимизатор не совсем глуп, то раскроет его в join), группировка.

Сравни с вариантом на row_number-е (названия объектов соответствуют тому, что у тебя в запросе):
select max(id) as id, x,y
  from (
         select t.*, 
                row_number() over(partition by x,y order by id) - 
                row_number() over(order by id) as grp_id
           from supertable t
       ) as v
 group by x,y,grp_id
 order by 1

Вроде как, обращений к таблице поменьше, но две сортировки и группировка.
Если не лень - посмотри на планы, погоняй оба варианта на относительно больших объемах. Возможно, твоё мнение изменится в пользу новых фич ;)
22 янв 13, 16:56    [13810941]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
aslan1980
К ITHunter и Cygapb-007 и другим участникам принимавшие участие в решение задачи огромнейшее спасибо.

Задача решена


Как жаль, а у меня ещё столько идей было...
22 янв 13, 17:01    [13810976]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Добрый Э - Эх
Guest
Cygapb-007
а меня от cross/outer apply торкает:)
сколь раз с ним сталкивался, столько раз создавалось впечатление, что это очень уж тормозная штука.

Может задачи не под него были?
22 янв 13, 17:06    [13811013]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
ITHunter
Member

Откуда:
Сообщений: 101
Добрый Э - Эх
ITHunter
вот теперь точно все. упустил группировочку
+
+ < Свернул портянку, чтобы цитирование было не сильно громоздким :)
select * from supertable
where id in
(
select
max(t1.id)
from supertable t1
left join supertable t3 on t3.id < t1.id and (t3.x<>t1.x or t3.y<>t1.y)
left join supertable t2 on t2.id > t1.id and t2.id > t3.id and (t2.x<>t1.x or t2.y<>t1.y)
group by t1.x, t1.y,t2.id,t3.id
)

В твоём варианте видим 4 (четыре) обращения к таблице, малоприятный IN-subquery (так-то понятно, что если оптимизатор не совсем глуп, то раскроет его в join), группировка.

Сравни с вариантом на row_number-е (названия объектов соответствуют тому, что у тебя в запросе):
select max(id) as id, x,y
  from (
         select t.*, 
                row_number() over(partition by x,y order by id) - 
                row_number() over(order by id) as grp_id
           from supertable t
       ) as v
 group by x,y,grp_id
 order by 1

Вроде как, обращений к таблице поменьше, но две сортировки и группировка.
Если не лень - посмотри на планы, погоняй оба варианта на относительно больших объемах. Возможно, твоё мнение изменится в пользу новых фич ;)


лень :)
кроме плана выполнения, нужно еще с индексами поработать. А в row_number на больших объемах я пока не верю. Может и зря. А на небольших и спорить бессмысленно. Применял его для постраничной выборки в 2008 - жалкое зрелище, жду когда админы 2012 накатят с Limit. А красота кода все же вторична, хотя значение свое имеет. Если честно, apply еще не использовал, но он красив - не спорю. И несмотря на все это, настаиваю, что неосмысленная гонка за модой и новыми фичами до добра не доведет :)
22 янв 13, 17:16    [13811075]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
<StmtSimple StatementCompId="5" StatementEstRows="1" StatementId="3" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0179339" StatementText="select lst.* &#xD;&#xA;   from @timing lst&#xD;&#xA;   --left join @timing nxt on nxt.id=lst.id+1 and nxt.X=lst.X and nxt.Y=lst.Y&#xD;&#xA;   --where nxt.id is null &#xD;&#xA;   outer apply (&#xD;&#xA;      select top 1*&#xD;&#xA;         from @timing t where t.EventId=lst.EventId and t.id&gt;lst.id&#xD;&#xA;         order by t.id&#xD;&#xA;   ) nxt&#xD;&#xA;   where nxt.id is null or nxt.X != lst.X or nxt.Y != lst.Y&#xD;" StatementType="SELECT" QueryHash="0x67420165A9E340EF" QueryPlanHash="0xF27B65412DFF88B1">
<RelOp AvgRowSize="68" EstimateCPU="1.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Filter" NodeId="0" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0179339">
<StmtSimple StatementCompId="6" StatementEstRows="1" StatementId="4" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0487297" StatementText="select max(id) as id, x,y&#xD;&#xA;  from (&#xD;&#xA;         select t.*, &#xD;&#xA;                row_number() over(partition by x,y order by id) - &#xD;&#xA;                row_number() over(order by id) as grp_id&#xD;&#xA;           from @timing t&#xD;&#xA;       ) as v&#xD;&#xA; group by x,y,grp_id&#xD;&#xA; order by 1" StatementType="SELECT" QueryHash="0x826BA684E0D454ED" QueryPlanHash="0xBD6B7D5B0893F145">
<RelOp AvgRowSize="27" EstimateCPU="0.000100027" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0487297">
итого 0.0179 супротив 0.0487
22 янв 13, 17:19    [13811094]     Ответить | Цитировать Сообщить модератору
 Re: Создание_SQL_запроса  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
сравнение на одних и тех же данных (продублировано в свертке)
+
--declare @timing table(id int identity(1,1) primary key, EventId int, Avto varchar(50), Time datetime, X float, Y float)
declare @timing table(id int identity(9,7), EventId int, Avto varchar(50), Time datetime, X float, Y float)
declare @now datetime=getdate()
insert into @timing values
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.45, 40.25),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.96, 40.52),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.98, 40.58),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.98, 40.65),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.98, 40.65),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.8, 40.41),
   (123456, 'Мерседес', DATEADD(SECOND, 25, @now), 49.99, 40.76)
select * from @timing 
select lst.* 
   from @timing lst
   --left join @timing nxt on nxt.id=lst.id+1 and nxt.X=lst.X and nxt.Y=lst.Y
   --where nxt.id is null 
   outer apply (
      select top 1*
         from @timing t where t.EventId=lst.EventId and t.id>lst.id
         order by t.id
   ) nxt
   where nxt.id is null or nxt.X != lst.X or nxt.Y != lst.Y
select max(id) as id, x,y
  from (
         select t.*, 
                row_number() over(partition by x,y order by id) - 
                row_number() over(order by id) as grp_id
           from @timing t
       ) as v
 group by x,y,grp_id
 order by 1
22 янв 13, 17:25    [13811127]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить