Как удалить меньшие записи для каждой группы?

Моя таблица price имеет текущую схему:

item date         shift     price1  price2
1    20110723     day       40      50
1    20110723     night     42      52
1    20110723     weekend   42      52
2    20110723     Night     40      50
...

И я хочу сохранить записи с самой высокой ценой1 или ценой2 для каждого элемента в таблице, т.е. удалить записи с меньшей ценой1 или ценой2 из таблицы. Как написать sql?

Я использую sybase ASE 12.5.


sql sql-delete group-by select max
person taox    schedule 25.07.2011    source источник
comment
Что такое первичный ключ, если он есть? (Элемент, Дата, Смена)?   -  person Jacob    schedule 25.07.2011
comment
Итак, если у вас есть три строки для item=7, где price1,price2 = 30,100, 100,30 и 40,40, какие из них следует удалить?   -  person ypercubeᵀᴹ    schedule 25.07.2011
comment
@cularis: вы можете принять элемент, дату, сдвиг в качестве ключа.   -  person taox    schedule 29.07.2011
comment
@ypercube: 40,40 будут удалены.   -  person taox    schedule 29.07.2011

Ответы (2)


arrow_upward
0
arrow_downward

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

delete from item_table where rowid not in
(
     select rowid from item_table 
     where (item,price1) in (select item,max(price1) from item_table group by item)
        or (item,price2) in (select item,max(price2) from item_table group by item)
)

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

вы также можете написать следующее ..

delete from item_table where (item,date,shift,price1,price2 ) not in
    (
        select item,date,shift,price1,price2  from item_table 
        where (item,price1) in (select item,max(price1) from item_table group by item)
           or (item,price2) in (select item,max(price2) from item_table group by item)
    )
person pratik garg    schedule 25.07.2011
comment
Я использую sybase ASE 12.5. Я не знаком ни с SQL, ни с sybase. Поддерживается ли rowid sybase? И если набор данных огромен, повлияет ли это на производительность? - person taox; 29.07.2011
comment
если rowid поддерживается вашей базой данных, то лучше использовать rowid... это улучшит вашу производительность - person pratik garg; 31.07.2011
comment
Похоже, в sybase нет rowid. Но ваш второй sql работает. Спасибо! - person taox; 15.11.2012

arrow_upward
1
arrow_downward

В SQL Server (и, возможно, в некоторых других РСУБД) вы можете использовать CTE. и рейтинг, например:

WITH ranked AS (
  SELECT
    *,
    price1rank = RANK() OVER (PARTITION BY item ORDER BY price1 DESC),
    price2rank = RANK() OVER (PARTITION BY item ORDER BY price2 DESC)
  FROM Test1
)
DELETE FROM ranked
WHERE price1rank <> 1
  AND price2rank <> 1;

ОБНОВЛЕНИЕ

Публикация всего сценария тестирования, включая приведенный выше оператор DELETE, чтобы любой желающий мог поиграть с ним или просто проверить, работает ли он:

CREATE TABLE Test1 (
  item int,
  date date,
  shift varchar(30),
  price1 money,
  price2 money
);
GO
INSERT INTO Test1 (item, date, shift, price1, price2)
SELECT 1, '20110723', 'day    ', 40, 50 UNION ALL
SELECT 1, '20110723', 'night  ', 42, 52 UNION ALL
SELECT 1, '20110723', 'weekend', 42, 52 UNION ALL
SELECT 2, '20110723', 'Night  ', 40, 50;
GO
SELECT * FROM Test1
GO
WITH Test1Ranked AS (
  SELECT
    *,
    price1rank = RANK() OVER (PARTITION BY item ORDER BY price1 DESC),
    price2rank = RANK() OVER (PARTITION BY item ORDER BY price2 DESC)
  FROM Test1
)
DELETE FROM Test1Ranked
WHERE price1rank <> 1
  AND price2rank <> 1;
GO
SELECT * FROM Test1
GO
DROP TABLE Test1
GO
person Andriy M    schedule 25.07.2011