Консультация № 178964
06.06.2010, 22:12
0.00 руб.
11.06.2010, 16:19
0 11 1
Уважаемые эксперты. Вопрос: Имеется база данных с полями: Kard -символьное поле карточек, Dat1 - дата. Карточка может использоваться несколько раз в день. Количество строк в таблице около 200000. Необходимо составить SQL-запрос, который выводит номера Kard которые использовались в день больше определенного числа раз - Kol и вывести эту дату и количество раз использований карты в этот день.
То есть должна получиться следующая таблица:
Kard Dat1 (Количество использований карты в день)
2345 01.02.2010 4
2347 02.02.2010 5
5879 02.02.2010 5

Обсуждение

Неизвестный
06.06.2010, 22:28
общий
KazAndr:
Вы не указали собственно имя таблицы, поэтому пусть она будет называться просто "Base"
Код:
Select Kard, Dat1, Count(Kard) 
from Base
group by Kard, Dat1
Having Count(Kard) > Kol
Неизвестный
07.06.2010, 08:26
общий
если поле Dat1 имеет тип DateTime то, возможно надо будет усекать до даты, т.к. по умолчанию может участвовать и время, в этом случае не получить группировку.
Неизвестный
07.06.2010, 11:43
общий
Измалков Эдуард Леонидович:
Большое спасибо, все заработало. Этот запрос считает по каждому дню. Можно немножко усложнить: Как выбрать только те дни в которых карта использовалась максимальное количество раз т.е что-то Max(Count(Kard))
Неизвестный
09.06.2010, 00:31
общий
KazAndr:
есть 2 варианта, выбирайте, какой больше нравится.
Код:
declare @findmax table
(KardM bigint, Dat1M datetime, maxC int)
insert into @findmax
select Kard, Dat1, count(Kard)
from Base
group by Kard, Dat1
having count(Kard) > Kol

select Kard, Dat1, max(maxC) from @findmax, orders
where Kard = KardM
and Dat1 = Dat1M
group by Kard, Dat1
order by 1


или без объявления таблицы
Код:
select b.Kard, b.Dat1, count(b.Kard)
from Base b,
(select Kard, max(ck) as ck1 -- начало поиска максимального значения count(Kard) для каждого значения Kard
from
(select Kard, Dat1, count(Kard) as ck -- начало выборки Kard, Dat1, count(Kard)
from Base
group by Kard, Dat1
having count(Kard) > Kol) t1 -- конец выборки Kard, Dat1, count(Kard). Заодно ее объявляем таблицей t1
group by Kard) t2 -- конец поиска максимального значения count(Kard) для каждого значения Kard. Заодно объявляем его как таблицу t2.
where t2.Kard = b.Kard
group by b.Kard, b.Dat1, ck1
having count(b.Kard) = ck1 -- условие, что количество Kard в этот день было максимальным.
order by 1
Неизвестный
10.06.2010, 11:40
общий
Измалков Эдуард Леонидович:
Работа подобным образом приведет к значительному падению производительности на большом кол-ве записей. 3 группирующих запроса и неявное связывание проиграют хранимой процедуре.
PS: Не заметил сразу что автор в 2-х рассылках сразу запостил вопрос.
Неизвестный
10.06.2010, 19:54
общий
Antony Belov:
Вопрос о производительности не стоял, я привел 2 варианта без создания View и хранимой процедуры. Вы привели пример с их созданием, теперь у автора вопроса есть выбор. А уж что ему больше подходит - решать ему. Вы кстати напишите модератору в той рассылке, чтобы Ваш ответ поправили, дабы неверный ответ не ушел в рассылку. Можете здесь тоже разместить ответ.
Неизвестный
10.06.2010, 23:07
общий
Измалков Эдуард Леонидович:
ок напишу, спасибо за информацию.
просто было указано довольно большое кол-во записей, я думал автор делает акцент на производительности. кстати насчет производительности вашего первого варианта не уверен что он будет хуже, т.к. если вся временная таблица сумеет разместится в памяти, то возможно он будет и пошустрее...
думаю если тут разместить ответ, то он будет офтопик. там у меня пример под FireBird, а тут другие СУБД, синтаксис у них всё таки отличается.
Неизвестный
11.06.2010, 14:30
общий
Уважаемые эксперты, всем большое спасибо за помощь.
Использовал (для Fireburd) ваш код:

select b.Kard, b.Dat1, count(b.Kard)from Base b, (select Kard, max(ck) as ck1 -- начало поиска максимального значения count(Kard) для каждого значения Kardfrom (select Kard, Dat1, count(Kard) as ck -- начало выборки Kard, Dat1, count(Kard)from Basegroup by Kard, Dat1having count(Kard) > Kol) t1 -- конец выборки Kard, Dat1, count(Kard). Заодно ее объявляем таблицей t1group by Kard) t2 -- конец поиска максимального значения count(Kard) для каждого значения Kard. Заодно объявляем его как таблицу t2.where t2.Kard = b.Kardgroup by b.Kard, b.Dat1, ck1having count(b.Kard) = ck1 -- условие, что количество Kard в этот день было максимальным.order by 1

В результате на среднем ноутбуке запрос на 65000 записей выполнялся в течении 2 секунд.
Результативность вполне устраивает.

Неизвестный
11.06.2010, 14:57
общий
еще бы это успеть ответом оформить и проставить оценку.
Неизвестный
11.06.2010, 16:14
общий
это ответ
Здравствуйте, KazAndr.
Возможно так:
Код:

SELECT A.CARD, A.DAT1, COUNT(*) COUNT_WORK FROM TEST A
WHERE (SELECT COUNT(*) FROM TEST T WHERE T.CARD = A.CARD AND T.DAT1 = A.DAT1) > 1
GROUP BY A.CARD, A.DAT1


Тестовые данные:
CARD DAT1
2345 05.06.2010
2345 05.06.2010
2345 06.06.2010
2346 06.06.2010
2346 06.06.2010
2346 06.06.2010
2347 05.06.2010
2347 05.06.2010
2347 05.06.2010
2347 05.06.2010
2347 06.06.2010
2347 06.06.2010

Результат:
CARD DAT1 KOL
2345 06.06.2010 1
2347 05.06.2010 4
2347 06.06.2010 2
2345 05.06.2010 2
2346 06.06.2010 3


-- 1 Это ваше KOL.. можно задать как параметр (указав 2, 3 и т.д.)
Но для больших наборов данных, лучше ограничивать диапазон, например:
AND A.DAT1 BETWEEN SYSDATE-30 AND SYSDATE

Ну и для условия: «Вывести все номера всех карт и дни обращения, для которых количество обращений максимально»
Код:

SELECT CARD, DAT1, MAX(KOL) KOL FROM
(
SELECT CARD, DAT1, COUNT(*) KOL FROM TEST A
GROUP BY CARD, DAT1
) T GROUP BY CARD, DAT1
HAVING MAX(KOL) >= (SELECT MAX((SELECT COUNT(*) FROM TEST C WHERE C.CARD = B.CARD AND C.DAT1 = B.DAT1 )) FROM TEST B WHERE B.CARD = T.CARD)

результат запроса:
CARD DAT1 KOL
2347 05.06.2010 4
2345 05.06.2010 2
2346 06.06.2010 3

5
Неизвестный
11.06.2010, 16:20
общий
перенесен на основе комментария задавшего вопрос
Цитата: KazAndr, 1-й класс
Использовал (для Fireburd) ваш код:
Форма ответа