На главную Наши проекты:
Журнал   ·   Discuz!ML   ·   Wiki   ·   DRKB   ·   Помощь проекту
ПРАВИЛА FAQ Помощь Участники Календарь Избранное RSS
msm.ru
! информация о разделе
user posted imageДанный раздел предназначается исключительно для обсуждения вопросов использования языка запросов SQL. Обсуждение общих вопросов, связанных с тематикой баз данных - обсуждаем в разделе "Базы данных: общие вопросы". Убедительная просьба - соблюдать "Правила форума" и не пренебрегать "Правильным оформлением своих тем". Прежде, чем создавать тему, имеет смысл заглянуть в раздел "Базы данных: FAQ", возможно там уже есть ответ.

Модераторы: Akina
Страницы: (2) [1] 2  все  ( Перейти к последнему сообщению )  
> Группировка данных, а затем фильтрация групп по максимальному значению , MS SQL Server 2000
    Всем хай! Сразу к делу.

    Есть таблица цветов, причем ненормализованная и пр. Максимально упрощенный вариант - всего 1 колонка.
    Очевидно, что дубликаты названия цветов присутствуют:

    ExpandedWrap disabled
      Красный
      Желтый
      Синий
      Красный
      Зеленый
      Красный
      Желтый
      Синий
      ....


    Мне нужно посчитать частотность каждого цвета.
    Это я сделал:
    ExpandedWrap disabled
      select name, count(name) as [Популярность цвета]
      from Color
      group by name
      order by name;


    Допустим получились такие ответы:
    ExpandedWrap disabled
      Бордовый 6
      Желтый 2
      Зеленый 9
      Красный 9
      Синий 8
      Фиолетовый 6

    ==============
    И теперь мне нужно оставить только цвета с максимальной частотностью, т е в ответе должны остаться
    ExpandedWrap disabled
      Зеленый 9
      Красный 9


    Как написать такой запрос?
    Сообщение отредактировано: FasterHarder -
      ExpandedWrap disabled
        SELECT TOP 1 WITH TIES
        a.name
        FROM
        (    select name, count(name) as [Популярность цвета]
            from Color
            group by name)
        as a
        ORDER BY a.[Популярность цвета] DESC
      Сообщение отредактировано: MIF -
        Цитата MIF @
        TOP 1 WITH TIES

        Без этих директив можно?
        или это единственный вариант?

        И зачем сортировать по популрности цвета, если она максимальная для всей выборки

        P.S. Может через подзапрос или having...
          MIF
          А подзапрос-то зачем?
          ExpandedWrap disabled
            SELECT TOP 1 WITH TIES [name], count([name]) as [Популярность цвета]
            FROM [Color]
            GROUP BY [name]
            ORDER BY 2 DESC

          Цитата FasterHarder @
          Без этих директив можно?

          ЗАЧЕМ?
            1. Не думал, что 2000 sql его поддерживает, а оказывается with ties был уже в 7-ке!
            2. Это задачка на фильтрацию групп, т е как бы намек на having

            Ну, with ties, так with ties! Лишь бы работало!
                Еще возник уточняющий вопрос!
                Допустим есть такие данные (все те же цвета):
                ExpandedWrap disabled
                  Алый 2
                  Бордовый 6
                  Голубой 5
                  Желтый 2
                  Зеленый 9
                  Красный 9
                  Синий 8
                  Сиреневый 7
                  Фиолетовый 6


                И нужно отобрать группы цвета с указанием их частотности, которые не лучшие и не худшие, а "срединные", т е ответ будет таким:

                ExpandedWrap disabled
                  Бордовый 6
                  Голубой 5
                  Синий 8
                  Сиреневый 7
                  Фиолетовый 6

                Как видно ушли цвета с Макс.частотностью = 9 и Мин.част. = 2

                Это возможно через with ties, да? или нет...
                  Цитата FasterHarder @
                  Это возможно через with ties, да? или нет...

                  WITH TIES всего лишь добавляет равноценные записи в группу, если их часть отсекается ограничением количества.

                  Цитата FasterHarder @
                  нужно отобрать группы цвета с указанием их частотности, которые не лучшие и не худшие, а "срединные"

                  Вот в этом случае придётся делать как минимум подзапрос. А с учётом неопределённости лучше задачу отбора возложить на клиента.
                    Цитата Akina @
                    Вот в этом случае придётся делать как минимум подзапрос. А с учётом неопределённости лучше задачу отбора возложить на клиента.

                    Akina, так подскажи как написать запрос) ты ведь понимаешь, что, если я не сумел создать с простым with ties (даже не знал его раньше), то с отбором "срединных" тем более не получится.
                    Тут даже непонятно, к чему должен стремиться подзапрос, т е я не понимаю до конца логику запроса -------------> нет шансов написать его на ms sql.
                    Я только знаю, что having фильтрует группы, но от этого не легче...

                    А срединные данные нужны в любых соревнованиях, когда, например:
                    - победители, набравшие макс. балл переходят в след. тур
                    - худшие выбывают
                    - среди срединных проводят доп.соревнование

                    Добавлено
                    Есть такая мысль:
                    отобрать лучших UNION худших в отдельную таблицу
                    потом пытаться связывать результаты по неэквиваленции, либо через exists как-то...
                      Цитата FasterHarder @
                      подскажи как написать запрос

                      ExpandedWrap disabled
                        SELECT *
                        FROM table
                        WHERE field NOT IN
                          (
                           'SELECT для получения MAX'
                           UNION ALL
                           'SELECT для получения MIN'
                          )

                      Если это не то - формулируй задачу. Так, чтобы ни один самый въедливый зануда не знал, что спросить дополнительно.

                      Цитата FasterHarder @
                      Я только знаю, что having фильтрует группы

                      Это неправильно. Having выполняет фильтрацию после стадии группировки, в отличие от WHERE. Но необязательно для группы. Более того, группировки в запросе может вообще не быть (правда, не все СУБД такое позволят).
                        Цитата Akina @
                        SELECT *
                        FROM table
                        WHERE field NOT IN
                        (
                        'SELECT для получения MAX'
                        UNION ALL
                        'SELECT для получения MIN'
                        )



                        Как я понял, опция with ties требует обязательно прописывать order by, а команда union all не допускает двух order by ----> возникает противоречие!
                        Или я не прав?!

                        Все остальное работает на 100%!
                          Цитата FasterHarder @
                          команда union all не допускает двух order by

                          сфига бы? допускает... и не просто два, а по одному на каждый подзапрос плюс ещё один на суммарный запрос.
                            Цитата Akina @
                            сфига бы? допускает... и не просто два, а по одному на каждый подзапрос плюс ещё один на суммарный запрос.

                            Akina, я вот тут открыл книжечку одну и тут есть такой абзац: "Можно также упорядочить результаты с помощью ORDER BY. Но при этом позволяется использовать только одну директиву ORDER BY, и она должна размещаться после завершающего оператора SELECT. Кроме того, в таком предложении ORDER BY можно использовать имена столбцов только из самого первого оператора SELECT".
                            Я ни на грамм не ставлю под сомнения вашу квалификацию и не хочу спорить вообще, но ведь действительно, когда я записывал это в редакторе запросов, используя ДВА ORDER BY, в каждом запросе, то возникала ошибка с указанием на ORDER BY.

                            Может дело в версиях используемого MS SQL Server?
                              Цитата FasterHarder @
                              когда я записывал это в редакторе запросов, используя ДВА ORDER BY, в каждом запросе, то возникала ошибка с указанием на ORDER BY.

                              Пример в студию.
                              ORDER BY один для каждого SELECTа. Подзапрос это отдельный SELECT.
                                FasterHarder
                                Ну вот тебе шаблон:
                                ExpandedWrap disabled
                                  (
                                    select top X
                                    from ...
                                    order by ...
                                  )
                                  union all
                                  (
                                    select top Y
                                    from ...
                                    order by ...
                                  )

                                Вполне жизненная задачка, не так ли? и два ORDER BY...

                                Цитата FasterHarder @
                                когда я записывал это в редакторе запросов, используя ДВА ORDER BY, в каждом запросе, то возникала ошибка с указанием на ORDER BY

                                Редактор - это ещё не истина в последней инстанции. Поинтересуйся мнением сервера...
                                Сообщение отредактировано: Akina -
                                0 пользователей читают эту тему (0 гостей и 0 скрытых пользователей)
                                0 пользователей:


                                Рейтинг@Mail.ru
                                [ Script execution time: 0,0426 ]   [ 16 queries used ]   [ Generated: 28.03.24, 16:38 GMT ]