Запрос на группировку.

 
0
 
SQL
ava
Колесо | 25.09.2013, 13:13
Здравствуйте.
Есть БД в которой ведутся закупки со след. таблицами:

Purchase_Order (PO_id, User_id, Order_placed_date, Vendor)
Users (Id, name),
Goods (Id,POID, Category_id, Price, Quantity),
Category (Code, Description).

В одном заказе может содержаться несколько позиций. Необходимо Получить данные по закупкам в сгруппированном виде: нужно видеть сумму покупок каждого менеджера по каждой категории по каждому поставщику за 2013 год.

Получился вот такой запрос. Только не совсем понятно с группировками. Правильно ли сделано или нет. Может подскажете как улучшить его.


SELECT PO_id
       ,Users.Name
       ,Goods.Category_id
       ,Category.Description
       ,Vendor
       ,sum(Goods.Price * Goods.Quantity) AS Amount
FROM Purchase_Order as PO,
    Users,
    Goods,
    Category
WHERE PO.User_id=Users.id
        AND    PO.PO_id=Goods.POID
        AND PO.Order_placed_date>='2013.01.01'
        AND Category.Code=Goods.Category_id

GROUP BY    
    PO_ID,
    Users.Name,
    Vendor,
    Goods.Category_id,
    Category.Description
Comments (11)
ava
Колесо | 25.09.2013, 12:40 #
Кусок результата:

PO_id            Name       Category_Id    Description                        Vendor    Amount
1006       Petrova, Anna        H3605    Freight forwarders, customs duties & customs brokers    651    22500.0000
1014       Isaeva, Alevtina    I4101    Project engineering services                728    2000000.0000
3479       Ivanov, Sergey        D2412    Other electronics specific application equipment    874    54100.0000
3479       Ivanov, Sergey        G2107    Pressure transmitting sensors                874    1331.0000
3479       Ivanov, Sergey        G2802    Safety equipment (extinguisher etc)            874    1948.0000
3479       Ivanov, Sergey        G2809    Ind. miscel. supplies & consummables (tools,...)    874    8.0000
3639       Peev, Sergey        C1501    Cylinders - steel                    724    780000.0000

Хотелось бы видеть по заказу агрегированную сумму.
ava
Akina | 25.09.2013, 13:06 #
Цитата (Колесо @  25.9.2013,  13:13 findReferencedText)
 нужно видеть сумму покупок каждого менеджера по каждой категории по каждому поставщику за 2013 год. 

А теперь посмотрите на свой запрос.
Что в GROUP BY делают поля, которые в списке требуемой группировки НЕ УКАЗАНЫ?
ava
Magistrus | 25.09.2013, 13:29 #
SELECT 
       MAX(Users.Name)
       ,MAX(Category.Description) 
       ,Vendor
       ,sum(Goods.Price * Goods.Quantity) AS Amount
FROM Purchase_Order as PO,
    Users,
    Goods,
    Category
WHERE PO.User_id=Users.id
        AND    PO.PO_id=Goods.POID
        AND PO.Order_placed_date>='2013.01.01'
        AND Category.Code=Goods.Category_id
GROUP BY    
    Users.id
    Category.Code,
    Vendor


в MySQl можно вместо MAX использовать просто Users.Name и Category.Description 
ava
Колесо | 25.09.2013, 14:28 #
Akina, все что указано в SELECT нужно видеть (кроме номера самого заказа PO_ID)

added later:
Magistrus, спасибо, попробую) а по PO.PO_id не нужно группировать?
ava
Akina | 25.09.2013, 14:49 #
Цитата (Колесо @  25.9.2013,  15:28 findReferencedText)
 все что указано в SELECT нужно видеть (кроме номера самого заказа PO_ID)

Вот именно этот PO_ID нужно вообще из запроса выкинуть! Тебе не требуется группировка по отдельным заказам? вывод однозначен - нафиг её...
А коли нафиг из группирования, то либо нафиг из вывода, либо, как сделал Magistrus (очень, кстати, логично, хоть ты эти данные и не просишь), в групповую операцию.

А то ведь если в двух разных заказах от одного и того же поставщика у одного и того же менеждера будут товары одной и той же категории - именно группировка по заказу не даст им собраться воедино.
ava
Колесо | 25.09.2013, 15:33 #
Спасибо, понял. 
ava
Колесо | 26.09.2013, 10:39 #
Еще не большой вопросик.
В таблице Purchase_Order есть поля currency, USD, EUR; в таблице Goods (Price, Quantity). Значениями поля currency м.б. : 1 (rub),2 (eur), 3(usd). Нужно получить результаты в рублях. Т.е. добавить условие, что если currency = 2 или 3 нужно дополнительно Price и Quantity умножить на соответствующий курс валюты. Можно ли это сделать прямо в select?
ava
Zloxa | 26.09.2013, 11:14 #
Цитата (Колесо @  26.9.2013,  11:39 findReferencedText)
Я так понимаю там нет оператора if? 

там есть православный case, но он вам не нужен.
что вам нужно, так это подтянуть справчоник курсов валют, который скорее всего имеет измерение даты, которое надо коррелировать с атрибутом даты заказа. Думаю, что вам, скорее всего будет проще всего реализовать это посредством скалярного кореллированного подзапроса.
ava
Колесо | 26.09.2013, 11:21 #
курсы валют хранится в таблице Purchase_Order в столбцах USD, EUR. 
ava
Akina | 26.09.2013, 11:47 #

SELECT ...,
Price * CASE currency
        WHEN 1 THEN 1
        WHEN 2 THEN 32
        WHEN 3 THEN 41
               ELSE 1000000
        END
, ...
ava
Колесо | 26.09.2013, 13:06 #
Спасибо!) Все работает smile 
Please register or login to write.
Firm of day
Вы также можете добавить свою фирму в каталог IT-фирм, и публиковать статьи, новости, вакансии и другую информацию от имени фирмы.
Подробнее
Contributors
advanced
Submit