Задачка по SQL...

← →
Дремучий (2002-10-29 22:28) [0]

Есть таблица, которая состоит из одного столбика целых чисел. Числа могут повторятся.
1)Написать запрос который находит число(а) которое повторяется максимальное количество раз.
2)Написать запрос который находит три(возможно реальных чисел будет больше) числа которые повторяются наибольшее количество раз.

Использовать стандартный СКЛ. Сразу скажу - у меня пока решения нет.... :))

← →
VictorT (2002-10-29 22:36) [1]

group by тебе поможет.

← →
VictorT (2002-10-29 22:48) [2]

select distinct count(field1) as cnt1, field1
from table1 group by field1 into table2;
select field1
from table2 where cnt1 = select max(cnt1) from table2;

Вроде так.

← →
Andrew M. (2002-10-30 07:04) [3]

select count(field1), field1
from table1
group by field1
order by 1 desc;

Затем читаешь столько записей, сколько надо

← →
Cu (2002-10-30 09:34) [4]

вот - проверила -работает
только это транзакт-SQL не знаю какой тебе надо
первый вопрос
select max (cnt) from
(
select distinct count (Quantity) cnt, Quantity from Table1 where Quantity in
(
select distinct Quantity from Table1
)
Group by Quantity
) Q
второй вопрос
select top 3 cnt from
(
select distinct count (Quantity) cnt, Quantity from Table1 where Quantity in
(
select distinct Quantity from Table1
)
Group by Quantity
) Q order by cnt desc

← →
Рыжик (2002-10-30 10:22) [5]

1)
select a.FieldValue, a.CountValue
from
(select max(CountValue) from
(select count(Field1) from Table1 group by value
) as a(CountValue)
) as b(MaxValue),
(select count(Field1),Field1 from Table1 group by value
) as a(CountValue,FieldValue)
where a.CountValue=b.MaxValue

2)
select a.FieldValue, a.CountValue
from
(select top 3 count(Field1) from Table1 group by Field1 order by count(Field1) desc
) as b(MaxValue),
(select count(Field1), Field1 from Table1 group by Field1
) as a(CountValue, FieldValue)
where a.CountValue=b.MaxValue
order by a.CountValue desc

Первое можно также сделать из второго заменой 3 на 1.

← →
Рыжик (2002-10-30 10:28) [6]

Имелось в виду "group by Field1" вместо "group by value"

← →
Дремучий (2002-10-30 18:48) [7]

>
> VictorT © (29.10.02 22:48)
> select distinct count(field1) as cnt1, field1
> from table1 group by field1 into table2;
> select field1
> from table2 where cnt1 = select max(cnt1) from table2;
>
> Вроде так.

ну вообщето речь шла об одной таблице и одном запросе,
поэтому такое решение не подходит...

> Andrew M. (30.10.02 07:04)
> select count(field1), field1
> from table1
> group by field1
> order by 1 desc;

> Затем читаешь столько записей, сколько надо

в том то весь и фокус, чтоб сразу получить количество записей, которые УДОВЛЕТВОРЯЮТ постановку задачи

>>Cu © (30.10.02 09:34)
Твои скрипты красивы, но к сожалению не идут на локальном СКЛ.
И еще они выдают максимальное количество повторений, а надо число(а), которые повторяются максимальное количество раз.

>>Рыжик ©
к сожалению твой скрипт у меня почему-то не заработал...
Хотя выглядит тоже очень красиво...

кстати для второго задания конструкция
select top 3
не подходит, потому что записей может быть больше трех...
о чем я кстати уже говорил....
а первом запросе записей может быть больше 1...
:))

для простоты тестирования -
скажу возьмите dbf-файл сполем q, назовите его qt
и тестируйте на
"x:\Program Files\Borland\Delphi5\Bin\dbexplor.exe"

жду НОВЫХ ответов!!!
:))

← →
VictorT (2002-10-30 20:11) [8]

> Дремучий © (30.10.02 18:48)
> >
> > VictorT © (29.10.02 22:48)
> > select distinct count(field1) as cnt1, field1
> > from table1 group by field1 into table2;
> > select field1
> > from table2 where cnt1 = select max(cnt1) from table2;
> >
> > Вроде так.
>
> ну вообщето речь шла об одной таблице и одном запросе,
> поэтому такое решение не подходит...

Ну тогда так:

select field1 from (
select distinct count(field1) as cnt1, field1
from table1 group by field1
)
where cnt1 = select max(cnt1) from table2;

← →
wicked (2002-10-30 21:21) [9]

2 Дремучий ©

> для простоты тестирования -
> скажу возьмите dbf-файл сполем q, назовите его qt
> и тестируйте на
> "x:\Program Files\Borland\Delphi5\Bin\dbexplor.exe"

имхо задача в такой постановке неразрешима - local sql есть урезаным диалектом даже стандартного sql, не говоря уже о расширениях языка...
еще одно имхо - для локальных таблиц использовать sql - изврат, лучше использовать курсоры... :)

← →
Рыжик (2002-10-31 09:40) [10]

> Дремучий © (30.10.02 18:48)

> к сожалению твой скрипт у меня почему-то не заработал...

Это тоже Transact-SQL. И вообще, Local SQL - это вовсе не станданртный SQL. Надо сразу уточнять.

> кстати для второго задания конструкция
> select top 3
> не подходит, потому что записей может быть больше трех...
> о чем я кстати уже говорил....
> а первом запросе записей может быть больше 1...
> :))

Очень даже подходит. Select top 3 делается для выбора 3-х максимальных количеств повторений, а не первых трёх значений из наиболее часто повторяющихся. Например, имеем число 21 повторяется 5 раз, число 22 - 4 раза, числа 26 и 24 - 3 раза, 27 - 2 раза. Select top 3 будет делаться для значений [5,4,3,2] и вернёт соответственно [5,4,3]. А затем делается выбор значений, которые повторяются нужное количество раз. И итоговый запрос вернёт [21,22,26,24] - значений > 3. Это проверялось на SQLServer и работало именно так.

> wicked © (30.10.02 21:21)

> имхо задача в такой постановке неразрешима

Ничего невозможного нет.

Итак, вот ответ на первый вопрос для LocalSQL:
select count(a.q), a.q from qt as a where not exists (select count(b.q) from qt as b group by b.q having count(b.q)> (select count(*) from qt as c where c.q=a.q) ) group by a.q
Запрос возвращает количество повторений и само значение. Если есть несколько чисел, повторяющихся максимальное количество раз, то вернёт и несколько строк. На русский язык переводится так: "Выбрать такие значения, для которых не существует количества повторений каких-либо значений в таблице, большего чем количество повторений данного значения".

По второму позже придумаю.

← →
Дремучий (2002-10-31 10:13) [11]

> VictorT © (30.10.02 20:11)
> select field1 from (
> select distinct count(field1) as cnt1, field1
> from table1 group by field1
> )
> where cnt1 = select max(cnt1) from table2;

на Transact-SQL должно работать, а вот на локальном нет. Но все же хорошо. :))

> wicked © (30.10.02 21:21)
> имхо задача в такой постановке неразрешима - local sql есть
> урезаным диалектом даже стандартного sql, не говоря уже
> о расширениях языка...

так оно и есть и в этом вся прелесть задачки...
:))

> Рыжик © (31.10.02 09:40)

Катя, мои поздравления победительнице первого задания!!!
Ты молодчина, замечательный скрипт. Кстати, я почему-то думал, что там без функии max() никак не обойтись. Твой скрипт убил меня наповал. :) Я твой искренний поклонник. :))

← →
Aristarh (2002-10-31 10:27) [12]

Хороший подход с выдумыванием "задачек".
Когда не смогу составить запрос для своей проги обязательно завуалирую его под задачку. А то на прямые вопросы отвечают реже и неохотнее. :-)))

Рыжик молодец.

← →
Дремучий (2002-10-31 12:16) [13]

> Aristarh © (31.10.02 10:27)
> Хороший подход с выдумыванием "задачек".

да, это можно взять на вооружение :))
только если честно, мне это было просто интересно, а не нужно для какой-то прикладной задачи. Я частенько придумываю для себя подобные задачки, дабы не отупеть в будничном кодерстве стандартных алгоритмов... Правда задачку придумал, а сам даже над ней даже не посидел... Нагрузили тут работкой.

А насчет локального СКЛ - все запросы я стараюсь писать на нем - переносимость задачи увеличивается - если что можно и на локальные базы прикладную задачу перебросить... Были случаи - просто подарок - ни одного скрипта не нужно было переделывать...
:))

← →
Рыжик (2002-10-31 16:36) [14]

> Дремучий © (31.10.02 12:16)

> А насчет локального СКЛ - все запросы я стараюсь писать
> на нем - переносимость задачи увеличивается

Если использовать Local-SQL даже когда этого совсем не требуется, а потом ещё пытаться запихать всё в один запрос без использовния вспомогательных таблиц, то увеличивается не только переносимость, но ещё и время выполнения запроса. Мой ответ на Transact-SQL работает гораздо быстрее, чем на Local-SQL. Я, правда, мало смыслю в оптимизации запросов. Может кто-нибудь придумает что-нибудь получше?
Есть у меня и вариант для второй задачки, но он ещё более тормознутый. Причём размер текста скрипта растёт в геометрической прогрессии от того, сколько наиболее повторяющихся элементов надо найти. Я уж не говорю о понимаемости написанного. Если задача только в нахождении решения и только потому что "просто интересно", то это одно, а вот если на практике такое применять, то лучше повеситься.

← →
Андрей Прокофьев (2002-10-31 16:51) [15]

А вот так:
SELECT [TOP 3] COUNT(Field1) AS Expr1, Field1 AS Expr2
FROM Table
GROUP BY Field1
ORDER BY -COUNT(Field1)

← →
Дремучий (2002-10-31 21:30) [16]

> увеличивается не только переносимость, но ещё и время выполнения
> запроса. Мой ответ на Transact-SQL работает гораздо быстрее,
> чем на Local-SQL.

за все приходится платить, для меня пока в приоритете больше переносимость... :)

> Если задача только в нахождении решения и только потому
> что "просто интересно", то это одно, а вот если на практике
> такое применять, то лучше повеситься.

по поводу даной задачи - "просто интересно", но были другие подобные... уу.уууу.... :)) Не повеслся, но секс имел первоклассный... ;)

> Причём размер текста скрипта растёт в геометрической прогрессии
> от того, сколько наиболее повторяющихся элементов надо найти.
Я уж не говорю о понимаемости написанного
вложенность с сылкой на предыдущий набор данных? есть такое... :) Понимаемость? Не только понимаемость, простая читабельность резко падает... Но что поделаешь... иногда "интересно".

>>Андрей Прокофьев © (31.10.02 16:5
на транзакте должен прокатать, на локальном - нет.

Кстати зацените одну из моих попыток

SELECT COUNT(q) AS Expr1, q AS Expr2 FROM qt t1 GROUP BY q Having count(q)+1 > ALL (select COUNT(q) from qt t2 GROUP BY q ) ORDER BY Expr1 desc

по идее должен работать...
так нет вылетает акцес виолейшен для длл БДЕ...
:))) Как я ее сделал?

← →
Дремучий (2002-10-31 22:38) [17]

как мне сказали, в подзапросах (local-SQL) нельзя использовать
GROUP BY

обидно, что до сих пор не знал
:))

← →
Рыжик (2002-11-01 09:37) [18]

А вот оказывается, что бывают запросы, работающие в локальном SQL и не работающие в транзакте.
Вот пример:
select distinct count(c.q) from qt as c where not exists (select count(d.q) from qt as d group by d.q having count(d.q)>(select count(*) from qt as e where e.q=c.q) and count(d.q)< (select distinct count(e.q) from qt as e where not exists (select count(f.q) from qt as f group by f.q having count(f.q)>(select count(*) from qt as g where g.q=e.q) ) group by e.q ) ) group by c.q
В локальном работает, а в транзакте (SQLServer) помогает только замена count(d.q)< на count(d.q)< all. Причём с all работает в транзакте, и не работает на локальном.
Так что с переносимостью тоже есть проблемы.
Только не понятно, почему так. Вроде бы Transact-SQL > Standard SQL > Local SQL, поэтому то, что работает в локальном, должно работать и в транзакте. А вот нет.

← →
Рыжик (2002-11-01 10:39) [19]

Или вот ещё запрос, который работает и там, и там, но с разными результатами:
select count(q),q
from qt t1
group by q
having count(q) =
(select count(q)
from qt t2
group by q
having count(q) =
(select count(*)
from qt t3
where t3.q=t1.q
)
)

← →
Рыжик (2002-11-01 13:21) [20]

Ну вот и вторая задача.

Наиболее приемлемое для практического воплощения решение из всех, что пришли мне в голову.

level1 Выбираем лидеров (это решение первой задачи)

select count(q) cnt, q from qt as t1 where not exists ( select count(q) from qt group by q having count(q) > (select count(*) from qt where q=t1.q) ) group by q order by cnt desc

level2 Первое и второе места

select count(q) cnt, q from qt as t2 where not exists ( select count(q) from qt group by q having count(q) > (select count(*) from qt where q=t2.q) and count(q) not in --level1 ( select count(q) from qt as t1 where not exists (select count(q) from qt group by q having count(q) > (select count(*) from qt where q=t1.q) ) group by q ) ) group by q order by cnt desc

level3 Тройка лидеров

select count(q) cnt, q from qt as t3 where not exists ( select count(q) from qt group by q having count(q) > (select count(*) from qt where q=t3.q) and count(q) not in --level2 ( select count(q) from qt as t2 where not exists ( select count(q) from qt group by q having count(q) > (select count(*) from qt where q=t2.q) and count(q) not in --level1 ( select count(q) from qt as t1 where not exists (select count(q) from qt group by q having count(q) > (select count(*) from qt where q=t1.q) ) group by q ) ) group by q ) ) group by q order by cnt desc

Можно продолжать и дальше.

Смысл этих запросов такой:"выбираем такие числа, для которых количество повторений либо принадлежит множеству количеств повторений, найденных на предыдущем уровне, либо является максимальным в дополнении к этому множеству".

← →
Дремучий (2002-11-01 13:35) [21]

>>Рыжик ©
БРАВО! Молодчина!!!
:))

мда...
сколько интересного можно для себя извлечь из простенькой задачки
и заодно буду знать к кому за помощью обращаться, если проблемы с написанием скриптов будут... ;)

Задачка по SQL... Найти похожие ветки