SQL задачка (как сравнить несколько строк в таблице)

[Niky]

Алгоритм vc работает, если повторы (ioi_amt, price) в пределах ордера исключены. Если повторы разрешены - не работает.
Пример:

Code: Select all

ioi_id   ord_id |  ioi_amt   price 
     17      1000 |        1      2 
     18      1000 |        1      2 
     19      1100 |        1      2 
     20      1100 |        1      2 
     21      1200 |        1      2 
     22      1200 |        1      2 
     23      1200 |        3      4 
     24      1200 |        5      6 
     25      1300 |        1      2 
     26      1300 |        1      2 
     27      1300 |        7      8 
     28      1300 |        9      10 

Результат:

Code: Select all

...
1000    1100    not eq
...
1200    1300    eq

[KVA]

Алгоритм vc работает, если повторы (ioi_amt, price) в пределах ордера исключены. Если повторы разрешены - не работает.

Запрещены.

[vc]

The reason why is it seems like algorithm doesn't cover all possible combinations and could be expanded at any time.

Damn! You are right. In this case it SELECT returns 'not eq', but business logic says that oders are equal in this case.

Code: Select all

ioi_id   ord_id |  ioi_amt   price 
     1      200 |        1      90 
     2      200 |        2      80 
     3      200 |        3      70 
     4      300 |        1      70
     5      300 |        2      80 
     6      300 |        3      90 

That's because in my SQL (1,90) is not equal to (3, 90) etc. I assumed that the equality should be checked for whole rows...

Please define exactly what account equality means in the 'business' logic. There may be a way to solve your problem differently if the number of rows per account is limited by some reasonable figure like, say, 10.

Rgds.

[KVA]

Please define exactly what account equality means in the 'business' logic. There may be a way to solve your problem differently if the number of rows per account is limited by some reasonable figure like, say, 10.

Сам запутался и всех запутал. Конечно полные rows должны совпадать. Так что все работает правильно.

А что за другой путь? Обычно у order мало IOI (<5), чаще всего 1,2.

[vc]

Please define exactly what account equality means in the 'business' logic. There may be a way to solve your problem differently if the number of rows per account is limited by some reasonable figure like, say, 10.

Сам запутался и всех запутал. :oops: Конечно полные rows должны совпадать. Так что все работает правильно.

А что за другой путь? Обычно у order мало IOI (<5), чаще всего 1,2.

You can flatten the table ('pivot') so that all the values are in one row. It's trivial to accomplish in Oracle but I need to think how to do the same in MS SQL Server ;)
What's the IOI range ? Is it like [1..25] or random ?

Rgds.

[vc]

Please define exactly what account equality means in the 'business' logic. There may be a way to solve your problem differently if the number of rows per account is limited by some reasonable figure like, say, 10.

Сам запутался и всех запутал. Конечно полные rows должны совпадать. Так что все работает правильно.

А что за другой путь? Обычно у order мало IOI (<5), чаще всего 1,2.

In Oracle (as well as in DB2), I would do this:

Code: Select all

select a.ord_id, 
       b.ord_id, 
       case when a.data=b.data then 'eq' else 'not eq' end 
from 
  (select ord_id,
       max(case when rn = 1 then ioi_amt else null end) ||'/'||
       max(case when rn = 2 then ioi_amt else null end) ||'/'||
       max(case when rn = 3 then ioi_amt else null end) ||'/'||
       max(case when rn = 4 then ioi_amt else null end) ||'/'||
       max(case when rn = 5 then ioi_amt else null end) ||'/'||
       max(case when rn = 1 then price else null end) ||'/'||
       max(case when rn = 2 then price else null end) ||'/'||
       max(case when rn = 3 then price else null end) ||'/'||
       max(case when rn = 4 then price else null end) ||'/'||
       max(case when rn = 5 then price else null end) data
  from
    (select ord_id, ioi_amt, price,  row_number() over (partition by ord_id order by ioi_amt) rn from t1)
  group by ord_id) a 
join
  (select ord_id,
       max(case when rn = 1 then ioi_amt else null end) ||'/'||
       max(case when rn = 2 then ioi_amt else null end) ||'/'||
       max(case when rn = 3 then ioi_amt else null end) ||'/'||
       max(case when rn = 4 then ioi_amt else null end) ||'/'||
       max(case when rn = 5 then ioi_amt else null end) ||'/'||
       max(case when rn = 1 then price else null end) ||'/'||
       max(case when rn = 2 then price else null end) ||'/'||
       max(case when rn = 3 then price else null end) ||'/'||
       max(case when rn = 4 then price else null end) ||'/'||
       max(case when rn = 5 then price else null end) data
  from
    (select ord_id, ioi_amt, price,  row_number() over (partition by ord_id order by ioi_amt) rn from t1)
  group by ord_id) b on a.ord_id < b.ord_id

The code is much more efficient (only one join as opposed to four in the previous versions) and handles duplicates nicely, but I am not sure how to translate "row_number() "( SQL'99 function) to the SQL Server dialect.

Rgds.

[KVA]

The code is much more efficient (only one join as opposed to four in the previous versions) and handles duplicates nicely, but I am not sure how to translate "row_number() "( SQL'99 function) to the SQL Server dialect.

Thanks a lot! Я дам посмотреть этот SQL нашему DBA. Может он что-нибудь посоветует на счет диалектов. Правда я не думаю что мне это решение поможет. IOI у order-a обычно мало < 5, но мы не ограничивает юзеров сколько IOI они могут ввести.

[vc]

The code is much more efficient (only one join as opposed to four in the previous versions) and handles duplicates nicely, but I am not sure how to translate "row_number() "( SQL'99 function) to the SQL Server dialect.

Thanks a lot! Я дам посмотреть этот SQL нашему DBA. Может он что-нибудь посоветует на счет диалектов. Правда я не думаю что мне это решение поможет. IOI у order-a обычно мало < 5, но мы не ограничивает юзеров сколько IOI они могут ввести.

The translation I came up with turned out to be quite ugly:

Code: Select all

select a.ord_id, 
       b.ord_id, 
       case when a.data=b.data then 'eq' else 'not eq' end 
from 
  (select ord_id,
       cast(max(case when rn = 1 then ioi_amt else null end)as varchar)  +'/'+
       cast(max(case when rn = 2 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 3 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 4 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 5 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 1 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 2 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 3 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 4 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 5 then price else null end)as varchar) data
  from
    (select t1.ord_id, t1.ioi_amt, t1.price, x.cnt rn from t1 join
      (select a.ord_id, cast(a.ioi_amt as varchar)+'/'+ cast(a.price as varchar) ap, count(*) cnt
       from t1 a join t1 b on a.ord_id=b.ord_id and cast(a.ioi_amt as varchar)+'/'+cast(a.price as varchar)>= cast(b.ioi_amt as varchar)+'/'+cast(b.price as varchar)
       group by a.ord_id, cast(a.ioi_amt as varchar)+'/'+cast(a.price as varchar)) x on t1.ord_id=x.ord_id and cast(t1.ioi_amt as varchar)+'/'+cast(t1.price as varchar)=x.ap) x
  group by ord_id) a 
join
  (select ord_id,
       cast(max(case when rn = 1 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 2 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 3 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 4 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 5 then ioi_amt else null end)as varchar) +'/'+
       cast(max(case when rn = 1 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 2 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 3 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 4 then price else null end)as varchar) +'/'+
       cast(max(case when rn = 5 then price else null end)as varchar) data
  from
    (select t1.ord_id, t1.ioi_amt, t1.price, x.cnt rn from t1 join
      (select a.ord_id, cast(a.ioi_amt as varchar)+'/'+ cast(a.price as varchar) ap, count(*) cnt
       from t1 a join t1 b on a.ord_id=b.ord_id and cast(a.ioi_amt as varchar)+'/'+cast(a.price as varchar)>= cast(b.ioi_amt as varchar)+'/'+cast(b.price as varchar)
       group by a.ord_id, cast(a.ioi_amt as varchar)+'/'+cast(a.price as varchar)) x on t1.ord_id=x.ord_id and cast(t1.ioi_amt as varchar)+'/'+cast(t1.price as varchar)=x.ap) x
  group by ord_id) b on a.ord_id < b.ord_id

The SQL is not very performant because my row_number() 'implementation' added 4 extra joins although it appears to handle duplicates correctly.

For those who might bother to run the SQL above, SET CONCAT_NULL_YIELDS_NULL should be OFF to allow null concatenation (null yields an empty string). The maximum number of items per order is 5.

Rgds.

[camel]

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

Code: Select all

SELECT a_ord_id, b_ord_id FROM
( SELECT a.ord_id a_ord_id, b.ord_id b_ord_id,
count(*)*2 - (first_value(count(*)) over (partition by a.ord_id order by abs(a.ord_id-b.ord_id)))
- (first_value(count(*)) over (partition by b.ord_id order by abs(a.ord_id-b.ord_id))) cnt
FROM t1 a, t1 b
WHERE a.price=b.price
  AND a.ioi_amt=b.ioi_amt
  AND a.ord_id <= b.ord_id
GROUP BY a.ord_id,b.ord_id)
WHERE a_ord_id <> b_ord_id
  AND cnt=0;

  A_ORD_ID   B_ORD_ID
---------- ----------
       600        700

[yukami]

maybe this will give you an idea:

Code: Select all

create procedure isDif

@id_1 int,
@id_2 int

as

declare @n1 int, @n2 int 

SELECT * INTO #tmp FROM privet_1 WHERE ord_id in (@id_1,@id_2)
set @n1 = @@ROWCOUNT

select @n2 = count(*) from #tmp t1, #tmp t2
where t1.ioi_amt=t1.ioi_amt and t1.price=t2.price and t1.ord_id!=t2.ord_id

drop table #tmp

select cast((@n1-@n2) as bit) as isDif