Получение 5 лучших продолжительностей вызовов для каждого добавочного номера телефона в MS Access


|-------------------------------------------------------------- ----------------|
| Я БЫ. EXT phneNumber продолжительность |
|-------------------------------------------------------------- ----------------|
| 78172 101 2233379092 00:00:23.0 |
| 78175 104 2233818185 00:08:15.0 |
| 78176 101 9339444840 00:04:56.0 |
| 78177 108 2581594433 00:01:28.0 |
| 78179 104 2533815944 00:02:30.0 |
| 78180 101 2335343977 00:00:00.0 |
| 78181 101 2335343977 00:01:36.0 |
| 78186 108 9229661175 00:00:53.0 |
| 78193 104 2538633408 00:00:00.0 |
| 78194 108 2580013364 00:00:11.0 |
| 78196 104 2583300164 00:00:16.0 |
| 78197 104 9963341189 00:02:44.0 |
| 78198 101 9663319313 00:03:58.0 |
| 78199 101 2104834340 00:03:19.0 |
| 78205 104 2222020401 00:00:00.0 |
|-------------------------------------------------------------- ----------------|

The table above shows a part of the data in a microsoft Access 2007 database.
EXT is the extension of a phone
phneNumber is the number that was called from that extention
duration is the amount of time that the called lasted

Мне нужно написать запрос, чтобы найти 5 самых длинных (по продолжительности) вызовов для каждого расширения. Желаемый результат показан ниже


 |--------------------------------------------------------------|
 |  ID.             EXT   phneNumber         duration    |
 |--------------------------------------------------------------|
 | 78176       101   9339444840         00:04:56.0 |
 | 78198       101   9663319313         00:03:58.0 |
 | 78199       101   2104834340         00:03:19.0 |
 | 78181       101   2335343977         00:01:36.0 |
 | 78172       101   2233379092         00:00:23.0 |
 | 78175       104   2233818185         00:08:15.0 |
 | 78197       104   9963341189         00:02:44.0 |
 | 78179       104   2533815944         00:02:30.0 |
 | 78196       104   2583300164         00:00:16.0 |
 | 78193       104   2538633408         00:00:00.0 |
 | 78177       108   2581594433         00:01:28.0 |
 | 78186       108   9229661175         00:00:53.0 |
 | 78194       108   2580013364         00:00:11.0 |
 |--------------------------------------------------------------|

Я попробовал приведенный ниже запрос, но он не работает должным образом и очень медленный.

SELECT *
FROM  (SELECT DISTINCT ext, phneNumber, duration FROM transactions) t
WHERE t.duration IN ( SELECT TOP 5 duration 
                       FROM   transactions
                       WHERE  t.ext = ext
                       ORDER  BY duration desc);

sql nested-queries ms-access-2007
person javasuns    schedule 14.11.2014    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Во-первых, я не понимаю, почему вы используете distinct во внешнем предложении from. Посмотрите, делает ли это то, что вы хотите:

SELECT ext, phneNumber, duration
FROM transactions as t
WHERE t.duration IN ( SELECT TOP 5 t2.duration 
                       FROM   transactions t2
                       WHERE  t.ext = t2.ext
                       ORDER  BY t2.duration desc);

TOP в MS Access работает иначе, чем top в SQL Server и Sybase, поскольку возвращает дубликаты. То есть он может вернуть более пяти строк, если есть дубликаты.

Если это проблема, вы можете попробовать:

SELECT ext, phneNumber, duration
FROM transactions as t
WHERE t.id IN (SELECT TOP 5 t2.id
               FROM transactions t2
               WHERE t.ext = t2.ext
               ORDER BY t2.duration desc, t2.id
              );
person Gordon Linoff    schedule 14.11.2014
comment
Первый действительно производит много дубликатов, в то время как второй работает вечно. - person javasuns; 14.11.2014
comment
@javasuns . . . Я удивлен этому. Индекс на transactions(ext, duration, id) должен фактически помочь обоим запросам. - person Gordon Linoff; 14.11.2014
comment
2-й запрос завершен, но результатов еще больше 5 (то же, что и 1) - person javasuns; 14.11.2014
comment
Речь идет о 137045 рядах - person javasuns; 14.11.2014
comment
@javasuns . . . Я думаю, что второй запрос требует id в предложении order by, чтобы получить ровно 5. - person Gordon Linoff; 14.11.2014
comment
Гордон спасибо за всю вашу помощь, но у меня все еще есть две проблемы. Сначала с вашим вторым запросом продолжительность не возвращается в порядке убывания. Во-вторых, запрос слишком медленный даже после создания индексов. - person javasuns; 17.11.2014
comment
Вы можете скачать базу данных, над которой я сейчас работаю, по следующей ссылке. dropbox.com/s/spza3zon0j2tbvg/Pabx2003.mdb?dl=0 Вот запрос, который я выполняю. ВЫБЕРИТЕ доб, число_набранных, продолжительность ОТ транзакций t ГДЕ t.aa IN (ВЫБЕРИТЕ ТОП 5 аа ИЗ транзакций, ГДЕ t.ext = ext ORDER BY продолжительность desc, аа); Цифры заменены на хххххххх по понятным причинам. Спасибо - person javasuns; 17.11.2014
comment
@javasuns . . . Если вы хотите, чтобы результаты запроса были упорядочены, вам нужно добавить order by к внешнему запросу. - person Gordon Linoff; 17.11.2014
comment
Да мне это удалось. Есть ли способ заставить его работать быстрее? Я имею в виду, что для получения результатов требуется до 30 минут. - person javasuns; 17.11.2014
comment
@javasuns . . . Если вы переключитесь на другое ядро ​​базы данных (например, на бесплатную версию SQL Server), вы, вероятно, обнаружите, что оно работает быстрее. Помимо упомянутого выше указателя, я не могу придумать никаких других идей для MS Access. - person Gordon Linoff; 17.11.2014