Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
select @@VERSION
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Есть таблица заявок:
CREATE TABLE [dbo].[UT_Orders](
	[id_Order] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[id_Instrument] [int] NOT NULL,
	[id_Account] [int] NOT NULL,
	[id_TradeSession] [int] NOT NULL,
	[ORDERNO] [int] NOT NULL,
	[_Time] [time](0) NOT NULL,
	[Price] [money] NULL,
	[LotCount] [int] NOT NULL,
	[BuySale] [bit] NOT NULL)
Где id_Instrument - биржевой инструмент, id_TradeSession - торговая сессия, Price - цена заявки, BuySale - вид заявки (1-покупка, 0-продажа), id_Account - трейдер, ORDERNO - порядковый номер заявки в течении одной торговой сессии.
Прошу вашей помощи в построении запроса, возвращающего: биржевой инструмент, торговую сессию, id_Order с минимальной ценой среди всех заявок на покупку, id_Order с максимальной ценой всех заявок на покупку, id_Order с миниальной ценой всех заявок на продажу, id_Order с максимальной ценой всех заявок на продажу. Может так получиться, что для торговой сессии и биржевого инструмента окажется несколько id_Order имеющих например минимальную цену среди заявок на покупку, нужно брать такую заявку, которую трейдер выставил раньше (т.е. ORDERNO меньше)
10 апр 12, 17:20    [12395148]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
AmKad
Member

Откуда:
Сообщений: 5211
rsolanov,

Дай пример данных через with.
10 апр 12, 17:26    [12395182]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
with Orders
as
(
	select 1 as id_Order, 1 as id_Instrument, 1 as id_Account, 1 as id_TradeSession, 1 as ORDERNO, 100 as Price, 1 as BuySale
	union select 2, 1, 2, 1, 2, 90, 1
	union select 3, 1, 3, 1, 3, 85, 1
	union select 4, 1, 2, 1, 4, 85, 1
	union select 5, 1, 4, 1, 5, 100, 1
	union select 6, 1, 5, 1, 6, 85, 1
	union select 7, 2, 2, 1, 7, 900, 1
	union select 8, 2, 3, 1, 8, 850, 1
	union select 9, 2, 4, 1, 9, 1000, 1
	union select 10, 2, 5, 1, 10, 850, 1
	union select 11, 2, 6, 1, 11, 1000, 1
	
	union select 12, 1, 7, 1, 12, 110, 0
	union select 13, 1, 8, 1, 13, 105, 0
	union select 14, 1, 9, 1, 14, 100, 0
	union select 15, 1, 10, 1, 15, 110, 0
	union select 16, 1, 10, 1, 16, 100, 0
	union select 17, 2, 11, 1, 17, 910, 0
	union select 18, 2, 12, 1, 18, 860, 0
	union select 19, 2, 13, 1, 19, 1010, 0
	union select 20, 2, 14, 1, 20, 860, 0
	union select 21, 2, 15, 1, 21, 1010, 0 
		
	union select 22, 1, 1, 2, 1, 130, 1
	union select 23, 1, 2, 2, 2, 120, 1
	union select 24, 1, 3, 2, 3, 115, 1
	union select 25, 1, 2, 2, 4, 115, 1
	union select 26, 1, 4, 2, 5, 130, 1
	union select 27, 1, 5, 2, 6, 115, 1
	union select 28, 2, 2, 2, 7, 930, 1
	union select 29, 2, 3, 2, 8, 880, 1
	union select 30, 2, 4, 2, 9, 1030, 1
	union select 31, 2, 5, 2, 10, 880, 1
	union select 32, 2, 6, 2, 11, 1030, 1
	
	union select 33, 1, 7, 2, 12, 140, 0
	union select 34, 1, 8, 2, 13, 135, 0
	union select 35, 1, 9, 2, 14, 130, 0
	union select 36, 1, 10, 2, 15, 140, 0
	union select 37, 1, 10, 2, 16, 130, 0
	union select 38, 2, 11, 2, 17, 940, 0
	union select 39, 2, 12, 2, 18, 890, 0
	union select 40, 2, 13, 2, 19, 1040, 0
	union select 41, 2, 14, 2, 20, 890, 0
	union select 42, 2, 15, 2, 21, 1040, 0 
)
select * from Orders
10 апр 12, 18:01    [12395466]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
AmKad
Member

Откуда:
Сообщений: 5211
rsolanov,

select id_Order, id_Instrument, id_Account, id_TradeSession, ORDERNO, Price, BuySale, 
case
  when rn1 = 1 and rn2 = 1 then 'Единственная'
  when rn1 = 1             then 'Минимальная'
  when rn2 = 1             then 'Максимальная'
end f1,  
case buysale
  when 0 then 'продажа'
  when 1 then 'покупка'
end f2  
from
 (select s.*,
  row_number() over (partition by BuySale order by Price  asc, orderno) rn1,
  row_number() over (partition by BuySale order by Price desc, orderno) rn2
  from Orders s
 )s
 where 1 in (rn1, rn2);

id_Order    id_Instrument id_Account  id_TradeSession ORDERNO     Price       BuySale     f1           f2      
----------- ------------- ----------- --------------- ----------- ----------- ----------- ------------ ------- 
40          2             13          2               19          1040        0           Максимальная продажа 
14          1             9           1               14          100         0           Минимальная  продажа 
30          2             4           2               9           1030        1           Максимальная покупка 
3           1             3           1               3           85          1           Минимальная  покупка
10 апр 12, 18:24    [12395576]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
AmKad,
немного не то, например у вас в запросе для id_Instrument = 1 и id_TradeSession = 1 не вычисленна максимальная продажа и максимальная покупка.
Нужно получить такой результат:
id_TradeSessionid_Instrumentid_OrderBuyMinPriceid_OrderBuyMaxPriceid_OrderSaleMinPriceid_OrderSaleMaxPrice
11311412
12891819
2124223533
2229303940
10 апр 12, 19:50    [12395890]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
aleks2
Guest
with Orders
as
(
	select 1 as id_Order, 1 as id_Instrument, 1 as id_Account, 1 as id_TradeSession, 1 as ORDERNO, 100 as Price, 1 as BuySale
	union select 2, 1, 2, 1, 2, 90, 1
	union select 3, 1, 3, 1, 3, 85, 1
	union select 4, 1, 2, 1, 4, 85, 1
	union select 5, 1, 4, 1, 5, 100, 1
	union select 6, 1, 5, 1, 6, 85, 1
	union select 7, 2, 2, 1, 7, 900, 1
	union select 8, 2, 3, 1, 8, 850, 1
	union select 9, 2, 4, 1, 9, 1000, 1
	union select 10, 2, 5, 1, 10, 850, 1
	union select 11, 2, 6, 1, 11, 1000, 1
	
	union select 12, 1, 7, 1, 12, 110, 0
	union select 13, 1, 8, 1, 13, 105, 0
	union select 14, 1, 9, 1, 14, 100, 0
	union select 15, 1, 10, 1, 15, 110, 0
	union select 16, 1, 10, 1, 16, 100, 0
	union select 17, 2, 11, 1, 17, 910, 0
	union select 18, 2, 12, 1, 18, 860, 0
	union select 19, 2, 13, 1, 19, 1010, 0
	union select 20, 2, 14, 1, 20, 860, 0
	union select 21, 2, 15, 1, 21, 1010, 0 
		
	union select 22, 1, 1, 2, 1, 130, 1
	union select 23, 1, 2, 2, 2, 120, 1
	union select 24, 1, 3, 2, 3, 115, 1
	union select 25, 1, 2, 2, 4, 115, 1
	union select 26, 1, 4, 2, 5, 130, 1
	union select 27, 1, 5, 2, 6, 115, 1
	union select 28, 2, 2, 2, 7, 930, 1
	union select 29, 2, 3, 2, 8, 880, 1
	union select 30, 2, 4, 2, 9, 1030, 1
	union select 31, 2, 5, 2, 10, 880, 1
	union select 32, 2, 6, 2, 11, 1030, 1
	
	union select 33, 1, 7, 2, 12, 140, 0
	union select 34, 1, 8, 2, 13, 135, 0
	union select 35, 1, 9, 2, 14, 130, 0
	union select 36, 1, 10, 2, 15, 140, 0
	union select 37, 1, 10, 2, 16, 130, 0
	union select 38, 2, 11, 2, 17, 940, 0
	union select 39, 2, 12, 2, 18, 890, 0
	union select 40, 2, 13, 2, 19, 1040, 0
	union select 41, 2, 14, 2, 20, 890, 0
	union select 42, 2, 15, 2, 21, 1040, 0 
),
norders as (
select *
     , ROW_NUMBER() OVER(PARTITION BY BuySale, id_TradeSession, id_Instrument ORDER BY  Price ASC, ORDERNO ASC ) nmin
     , ROW_NUMBER() OVER(PARTITION BY BuySale, id_TradeSession, id_Instrument ORDER BY  Price DESC, ORDERNO ASC ) nmax
FROM orders
)
select t1.id_TradeSession, t1.id_Instrument
     , t1.id_Order id_OrderBuyMinPrice  
     , t2.id_Order id_OrderBuyMaxPrice  
     , t3.id_Order id_OrderSaleMinPrice  
     , t4.id_Order id_OrderSaleMaxPrice  
    from 
         (select * from nOrders WHERE nmin=1 and BuySale=1) t1 
         inner join 
         (select * from nOrders WHERE nmax=1 and BuySale=1) t2
         on
         t1.id_TradeSession=t2.id_TradeSession AND t1.id_Instrument=t2.id_Instrument
         inner join 
         (select * from nOrders WHERE nmin=1 and BuySale=0) t3
         on
         t1.id_TradeSession=t3.id_TradeSession AND t1.id_Instrument=t3.id_Instrument
         inner join 
         (select * from nOrders WHERE nmax=1 and BuySale=0) t4
         on
         t1.id_TradeSession=t4.id_TradeSession AND t1.id_Instrument=t4.id_Instrument
10 апр 12, 20:29    [12395999]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
А как мне этот запрос вложить в селект, чтобы я мог сделать его подзапросом?
11 апр 12, 09:36    [12397189]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
aleks2
Guest
rsolanov
А как мне этот запрос вложить в селект, чтобы я мог сделать его подзапросом?


А нафега? Если у тя 2000-й - это тебе, фсе одно, не пойдет.
А если 2005-2008 - учи CTE и пользуй WITH.
11 апр 12, 11:12    [12397748]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
aleks2
rsolanov
А как мне этот запрос вложить в селект, чтобы я мог сделать его подзапросом?


А нафега? Если у тя 2000-й - это тебе, фсе одно, не пойдет.
А если 2005-2008 - учи CTE и пользуй WITH.
Просто мне этот запрос надо будет встроить в уже готовый другой огромный запрос и если использовать WITH, то придется все переписывать, что очень не хочется ))
11 апр 12, 11:17    [12397783]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
smallserg
Member

Откуда:
Сообщений: 82
rsolanov,
select id_instrument,id_tradeSession ,
sum(case when rn=1 and buysale=0 then id_order end),
sum(case when rn2=1 and buysale=0 then id_order end),
sum(case when rn=1 and buysale=1 then id_order end),
sum(case when rn2=1 and buysale=1 then id_order end)
 from
(select row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price) rn,
row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price desc) rn2,
* from orders ) q where rn=1 or rn2=1
group by id_instrument,id_tradeSession
12 апр 12, 14:13    [12405838]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
smallserg
Member

Откуда:
Сообщений: 82
только не использовать * в запросе, это я от лени.

select id_instrument,id_tradeSession ,
sum(case when rn=1 and buysale=0 then id_order end),
sum(case when rn2=1 and buysale=0 then id_order end),
sum(case when rn=1 and buysale=1 then id_order end),
sum(case when rn2=1 and buysale=1 then id_order end)
 from
(select row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price) rn,
row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price desc) rn2,
 id_instrument,id_tradeSession,id_order,buysale from orders ) q where rn=1 or rn2=1
group by id_instrument,id_tradeSession
12 апр 12, 14:27    [12405950]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
smallserg,
А что у вас за суммы в запросе?
Запрос от aleks2 выдает 16155 строк, ваш запрос-72877.
13 апр 12, 09:10    [12409726]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
smallserg
Member

Откуда:
Сообщений: 82
rsolanov,

выполните на своих данных
select  distinct  id_instrument,id_tradeSession  from orders 


Вот сколько записей выдаст этот запрос, столько и должно быть в искомом запросе.
13 апр 12, 09:23    [12409810]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
smallserg
rsolanov,

выполните на своих данных
select  distinct  id_instrument,id_tradeSession  from orders 


Вот сколько записей выдаст этот запрос, столько и должно быть в искомом запросе.
Да, вы правы. Очень хочется понять, почему тогда у aleks2 выдает 16155 строк?
13 апр 12, 09:28    [12409833]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
smallserg
Member

Откуда:
Сообщений: 82
rsolanov,

Ну... можно предположить, что он неверно написан ...
Причем написан сложно, с четырьмя чтениями одной таблицы -
разбираться что именно в нем неверно - нет ни времени ни желания.
13 апр 12, 09:34    [12409855]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
aleks2
Guest
rsolanov
smallserg
rsolanov,

выполните на своих данных
select  distinct  id_instrument,id_tradeSession  from orders 


Вот сколько записей выдаст этот запрос, столько и должно быть в искомом запросе.
Да, вы правы. Очень хочется понять, почему тогда у aleks2 выдает 16155 строк?


Патамушто в нем стоит INNER JOIN.
13 апр 12, 10:38    [12410173]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
aleks2, конечно ваш запрос работает не совсем точно: если для биржевого инструмента и торговой сессии есть минимальная и максимальная цена продажи, а покупки отсутстсвуют, то данные по продажам запрос не отображает. Влюбом случае огромное вам спасибо за оказанную помощь, ваш запрос мне в определенное время очень помог.
13 апр 12, 11:23    [12410532]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
smallserg
только не использовать * в запросе, это я от лени.

select id_instrument,id_tradeSession ,
sum(case when rn=1 and buysale=0 then id_order end),
sum(case when rn2=1 and buysale=0 then id_order end),
sum(case when rn=1 and buysale=1 then id_order end),
sum(case when rn2=1 and buysale=1 then id_order end)
 from
(select row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price) rn,
row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price desc) rn2,
 id_instrument,id_tradeSession,id_order,buysale from orders ) q where rn=1 or rn2=1
group by id_instrument,id_tradeSession
Это конечно отличный запрос, теперь я лучше понял смысл использования ранжирующих функций и создания на их основе запросов. Огромное вам спасибо!
13 апр 12, 12:11    [12410979]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
aleks2
Guest
rsolanov
aleks2, конечно ваш запрос работает не совсем точно: если для биржевого инструмента и торговой сессии есть минимальная и максимальная цена продажи, а покупки отсутстсвуют, то данные по продажам запрос не отображает. Влюбом случае огромное вам спасибо за оказанную помощь, ваш запрос мне в определенное время очень помог.

Освойте Left Join. Хе-хе.
13 апр 12, 13:19    [12411549]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
aleks2
rsolanov
aleks2, конечно ваш запрос работает не совсем точно: если для биржевого инструмента и торговой сессии есть минимальная и максимальная цена продажи, а покупки отсутстсвуют, то данные по продажам запрос не отображает. Влюбом случае огромное вам спасибо за оказанную помощь, ваш запрос мне в определенное время очень помог.

Освойте Left Join. Хе-хе.
Согласен, можно и применить левое внешнее соединение, но ваш вариант запроса, имея даже внутренние соединения работает немного медленнее, чем предложенный от smallserg. А если внутренние соединения заменить на внешние он конечно станет работать еще медленнее. aleks2, еще раз повторюсь, я в любом случае вам благодарен запомощь, поскольку именно ваш вариант запроса мне сильно помог в определенный момент, Спасибо!
13 апр 12, 15:16    [12412665]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
aleks2
Guest
rsolanov
aleks2
пропущено...

Освойте Left Join. Хе-хе.
Согласен, можно и применить левое внешнее соединение, но ваш вариант запроса, имея даже внутренние соединения работает немного медленнее, чем предложенный от smallserg. А если внутренние соединения заменить на внешние он конечно станет работать еще медленнее. aleks2, еще раз повторюсь, я в любом случае вам благодарен запомощь, поскольку именно ваш вариант запроса мне сильно помог в определенный момент, Спасибо!


1. За быстродействие базара не было. Зато мой идейно правильнее.
2. Впрочем, неправильна ваша постановка задачи.
13 апр 12, 17:37    [12413656]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
select id_instrument,id_tradeSession ,
sum(case when rn=1 and buysale=0 then id_order end),
sum(case when rn2=1 and buysale=0 then id_order end),
sum(case when rn=1 and buysale=1 then id_order end),
sum(case when rn2=1 and buysale=1 then id_order end)
 from
(select row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price) rn,
row_number() over (partition by buySale,id_instrument,id_tradeSession order by Price desc) rn2,
 id_instrument,id_tradeSession,id_order,buysale from orders ) q where rn=1 or rn2=1
group by id_instrument,id_tradeSession
А как в этом запросе получить первую и последнюю цену так же в разрезе group by id_instrument,id_tradeSession?
10 окт 13, 19:11    [14952920]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Добрый Э - Эх
Guest
rsolanov,

first_value / last_value тебе в помощь.
11 окт 13, 04:46    [14954299]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
rsolanov
Member

Откуда:
Сообщений: 930
Добрый Э - Эх, эти функции появились только в 2012, а у меня 2008R2
11 окт 13, 12:28    [14955905]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
prog882
Guest
rsolanov,
declare @t table(d datetime)
insert into @t
select '20120101' union
select '20120102' union
select '20120108' union
select '20120111' union
select '20120121' union
select '20120130'

select 
Date = t1.d,
Nextdate = t2.d,
Prevdate = t3.d
from (select i=ROW_NUMBER() over(order by d),d from @t) t1
left join (select i=ROW_NUMBER() over(order by d),d from @t) t2 on (t2.i=t1.i+1)
left join (select i=ROW_NUMBER() over(order by d),d from @t) t3 on (t3.i=t1.i-1)
11 окт 13, 12:46    [14956077]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить