Выберите топ 1 для каждой группы

У меня есть база данных Access, содержащая таблицу с информацией о частях, которые мы сортируем. Эта таблица имеет поле идентификатора автонумерации и идентификатор 110ID, который ссылается на другую таблицу с информацией о детали. Он также содержит sortDate, sortShift, отсортировано, утилизировано и отремонтировано. Мне нужно найти, сколько деталей было отсортировано с момента обнаружения последнего дефекта (ни одного бракованного или отремонтированного) для каждого 110ID.

Проблема в том, что я не могу гарантировать, что информация будет занесена в базу данных в хронологическом порядке. Поэтому мне нужно суммировать поле «отсортировано» для любых записей, у которых «sortDate» больше, чем последний дефект, или «sortDate» такой же, как последний дефект, но больше «sortShift», или использовать идентификатор автономера в качестве последнего средства если совпадают и sortDate, и sortShift.

Это запрос, который я сейчас использую:

SELECT SortInfo.[110ID], Sum(SortInfo.Sorted) AS SumOfSorted
FROM SortInfo
WHERE (
    ((SortInfo.sortdate)>(select top 1 dupe.sortdate from sortinfo as dupe where     (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)))
    OR (((SortInfo.sortdate)=(select top 1 dupe.sortdate from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.sortshift)>(select top 1 dupe.sortshift from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc))) 
    OR (((SortInfo.sortdate)=(select top 1 dupe.sortdate from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.sortshift)=(select top 1 dupe.sortshift from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc)) 
        AND ((SortInfo.ID)>(select top 1 dupe.id from sortinfo as dupe where (((dupe.[110id])=(sortinfo.[110id])) and (((dupe.repaired)<>0) or ((dupe.scrapped)<>0))) order by dupe.sortdate desc, dupe.sortshift desc, dupe.id desc))
)
GROUP BY SortInfo.[110ID];

Проблема в том, что это очень медленно. Есть ли лучший способ сделать это, который даст лучшую производительность?


sql query-optimization greatest-n-per-group ms-access
person AdmSteck    schedule 21.07.2010    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Вместо того, чтобы использовать все эти подзапросы, вы можете сделать это либо с помощью LEFT OUTER JOIN, либо с подзапросом NOT EXISTS. Я не очень часто использую Access, поэтому вам может потребоваться настроить их, если это одна из тех областей, где Access не соответствует стандарту ANSI.

SELECT
    SI.[110ID],
    SUM(SI.Sorted) AS SumOfSorted
FROM
    SortInfo SI
LEFT OUTER JOIN SortInfo SI2 ON
    SI2.Repaired <> 0 AND
    SI2.Scrapped <> 0 AND
    (
        SI2.SortDate > SI.SortDate OR
        (SI2.SortDate = SI.SortDate AND SI2.SortShift > SI.SortShift) OR
        (SI2.SortDate = SI.SortDate AND SI2.SortShift = SI.SortShift AND SI2.ID > SI.ID)
    )
WHERE
    SI2.ID IS NULL
GROUP BY
    SI.[110ID]

SELECT
    SI.[110ID],
    SUM(SI.Sorted) AS SumOfSorted
FROM
    SortInfo SI
WHERE
    NOT EXISTS
    (
        SELECT *
        FROM
            SortInfo SI2
        WHERE
            SI2.Repaired <> 0 AND
            SI2.Scrapped <> 0 AND
            (
                SI2.SortDate > SI.SortDate OR
                (SI2.SortDate = SI.SortDate AND SI2.SortShift > SI.SortShift) OR
                (SI2.SortDate = SI.SortDate AND SI2.SortShift = SI.SortShift AND SI2.ID > SI.ID)
            )    
    GROUP BY
        SI.[110ID]
    )
person Tom H    schedule 21.07.2010
comment
Первоначальные выводы: я не могу заставить вариант один работать, потому что логика должна быть ((SI2.Repaired <> 0) OR (SI2.Scrapped <> 0)) AND..., но не похоже, что Access позволит extra () сгруппировать два оператора or'd. Второй вариант либо не работает, либо такой же медленный. - person AdmSteck; 21.07.2010
comment
Очень странно, что скобки не разрешены. К сожалению, у меня нет доступа на этом компьютере для тестирования. Вы уверены, что нигде нет опечатки? - person Tom H; 21.07.2010
comment
Access/Jet/ACE очень требователен к синтаксису JOIN. Также обратите внимание, что NOT IN и NOT EXISTS часто плохо оптимизируются Jet и могут использовать индекс только на одной стороне сравнения. - person David-W-Fenton; 22.07.2010