Клубове

SQL въпрос - за group

11 отговора 863 прегледа
dzver #1

SQL въпрос - за group

Имам следният проблем.

Имам таблица A с уникално a_id, b_id - връзка с таблица B и някаква стойност c. Имам няколко b_id, за които искам точно по 1 a_id, чието c е най-голямо.

Първо се опитах с distinct, но до колкото знам, той не позволява уникалност по 1 поле?. С group пък не мога да управлявам кой точно запис искам от таблицата A - имам само group functions, а на мен дефакто ми трябва order by - с уникалност за b_id.

Знам, че за някои от вас този въпрос е тривиален, моля за помощ :)

Отговор
Dakota в отговор на dzver #2

за коя база?

Не разбрах за коя СУБД ти трябва решение.

Eто как може да стане за PostgreSQL: 

kouber=# select * from A;

 a_id | b_id | c

------+------+----

    1 |    1 |  1

    2 |    1 |  3

    3 |    2 |  2

    4 |    3 |  6

    5 |    3 |  7

    6 |    3 |  8

    7 |    3 | -1

    8 |    1 |  3

    9 |    3 |  8

   10 |    2 | 10

(10 rows)


В случая за b_id=1 и b_id=3 имаш по две максимални стойности:
- за b_id=1, c=3 при a_id=2 и a_id=8;
- за b_id=3, ц=8 при a_id=6 и a_id=9.

Което значи, че трябва да решиш тази двусмисленост, като подредиш и по a_id: 

kouber=# select distinct on (b_id) * from A order by b_id, c desc, a_id;

 a_id | b_id | c

------+------+----

    2 |    1 |  3

   10 |    2 | 10

    6 |    3 |  8

(3 rows)



kouber=# select distinct on (b_id) * from A order by b_id, c desc, a_id desc;

 a_id | b_id | c

------+------+----

    8 |    1 |  3

   10 |    2 | 10

    9 |    3 |  8

(3 rows)


Клаузата DISTINCT ON не е стандартна, а е разширение на PostgreSQL, но същият ефект можеш да получиш и с една допълнителна подзаявка или с някакви GROUP BY хватки.

Всъщност опитай и така: select a_id, b_id, max(c) from A group by b_id;, но поне в PostgreSQL не ти дава, точно заради по-горната двусмисленост.

Everything louder than everything else...

Редактирано от Dakota на

Отговор
dzver в отговор на Dakota #3

Re: за коя база?

MS SQL 2000 - нямам такъв distinct по 1 поле, търсих и в нет-а (или поне аз не открих). Вторият вариант не може да сработи заради групирането - в селекцията имаш само group functions и полетата, по които групираш. Като цяло ми трябва някаква врътка... Мога да го реализирам със сторед процедура и курсор, ама ми е дървено.

Благодаря за предложената помощ :) Хубаво е да знаеш, че поне в postgreSQL са помислили за подобни неща.

Отговор
phpGuru в отговор на dzver #4

Re: за коя база?

нещо от сорта:

SELECT t.a_id, t.b_id FROM tbl_name AS t, (SELECT b_id, max(c) AS max_c FROM tbl_name GROUP BY b_id) AS t2 WHERE t.b_id = t2.b_id AND t.c = t2.max_c;

трябва да може да се пренапише и по-готин начин май, ма не го виждам в момента

Отговор
phpGuru в отговор на dzver #5

Re: за коя база?

ето го май

SELECT t.a_id, t.b_id FROM tbl_name AS t WHERE t.c = (SELECT max(t2.c) FROM tbl_name AS t2 WHERE t2.b_id = t.b_id);

Отговор
phpGuru в отговор на phpGuru #6

Re: за коя база?

ако изобщо работят тези варианти вариант 1 май е по-гот

Отговор
dzver в отговор на phpGuru #7

Re: за коя база?

Благодаря, това е стъпка в правилната посока:) Не работи съвсем, защото не гарантира, че записът е 1 - ако има няколко записа с еднакво C ще ги върне всичките. Като се замисля обаче, наличието на 1-2 записа повече не е такава грижа.

Просто задачката остава без логическо точно SQL решение, иначе скриптът ми ще тръгне:)

Отговор
NDeu в отговор на dzver #8

Re: за коя база?

Тогава: 
select a1.a_id, a1.b_id, a1.c

  from a a1

  where not exists(select * from a a2

                    where a2.b_id=a1.b_id

                      and a2.c>a1.c)

  and not exists(select * from a a3

                    where a1.b_id=a3.b_id

                      and a1.c=a3.c

                      and a1.a_id<a3.a_id)



или пък от phpGuru

SELECT t.a_id, t.b_id FROM tbl_name AS t WHERE t.c = (SELECT max(t2.c) FROM tbl_name AS t2 WHERE t2.b_id = t.b_id)
and not exists(select * from tbl_name a3
where t.b_id=a3.b_id
and t.c=a3.c
and t.a_id<a3.a_id)

Редактирано от NDeu на

Отговор
Dakota в отговор на dzver #9

вложени заявки

С MS SQL нямам опит, но предполагам, че има вложени заявки, с които, както написах по-горе, можеш да постигнеш същия ефект: 

kouber=# select max(A.a_id) as a_id, A.b_id, A.c from

         (select b_id, max(c) as c from A group by b_id) as x1 

         inner join A on A.b_id=x1.b_id and A.c=x1.c

         group by A.b_id, A.c;



 a_id | b_id | c

------+------+----

    8 |    1 |  3

   10 |    2 | 10

    9 |    3 |  8

(3 rows)



kouber=# select min(A.a_id) as a_id, A.b_id, A.c from

         (select b_id, max(c) as c from A group by b_id) as x1 

         inner join A on A.b_id=x1.b_id and A.c=x1.c

         group by A.b_id, A.c;



 a_id | b_id | c

------+------+----

    2 |    1 |  3

   10 |    2 | 10

    6 |    3 |  8

(3 rows)


Просто избери min или max.

Everything louder than everything else...

Редактирано от Dakota на

Отговор
wqw в отговор на Dakota #10

Re: вложени заявки

@Dakota: Доста добре! Още повече, че OP въобще не осъзнава, че въпроса му е недетерминистичен -- какво правим ако за едно b_id има две a_id-та за които c e max?

В двете твои заявки си предложил min или max на a_id. Можеше да е TOP 1 (или first в някои бази) - със сигурност тиня, 100% лакмус, че има проблем с логическия дизайн на базата.

cheers,
</wqw>

Отговор
dzver в отговор на Dakota #11

Re: вложени заявки

да, това е:) и работи доста бързо
много ви благодаря!

Отговор
dzver в отговор на wqw #12

Re: вложени заявки

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

Представи си, че имаш поръчка за хладилник и искаш да предложиш на човека 1 печка и 1 тенджера - с най-много реализирани продажби до момента. Грубо. Може да е за фотоапарат - flash памет и батерии със зарядно. Има 100 зарядни АА, 1000 съвместими памети и 10 калъфчета... и точно query-то на dakota идва на помощ.

Отговор
Отговор в темата