Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > Составление SQL-запросов > Запрос на группировку.


Автор: Колесо 25.9.2013, 12: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

Автор: Колесо 25.9.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

Хотелось бы видеть по заказу агрегированную сумму.

Автор: Akina 25.9.2013, 13:06
Цитата(Колесо @  25.9.2013,  13:13 Найти цитируемый пост)
 нужно видеть сумму покупок каждого менеджера по каждой категории по каждому поставщику за 2013 год. 

А теперь посмотрите на свой запрос.
Что в GROUP BY делают поля, которые в списке требуемой группировки НЕ УКАЗАНЫ?

Автор: Magistrus 25.9.2013, 13:29
Код
SELECT 
       COUNT(PO.PO_id) as PO_Number
       ,MAX(Users.Name) as User
       ,Category.Code
       ,MAX(Category.Description)  as 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 

Автор: Колесо 25.9.2013, 14:28
Akina, все что указано в SELECT нужно видеть (кроме номера самого заказа PO_ID)

Добавлено @ 14:28
Magistrus, спасибо, попробую) а по PO.PO_id не нужно группировать?

Автор: Akina 25.9.2013, 14:49
Цитата(Колесо @  25.9.2013,  15:28 Найти цитируемый пост)
 все что указано в SELECT нужно видеть (кроме номера самого заказа PO_ID)

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

А то ведь если в двух разных заказах от одного и того же поставщика у одного и того же менеждера будут товары одной и той же категории - именно группировка по заказу не даст им собраться воедино.

Автор: Колесо 25.9.2013, 15:33
Спасибо, понял. 

Автор: Колесо 26.9.2013, 10:39
Еще не большой вопросик. 
В таблице Purchase_Order есть поля currency, USD, EUR; в таблице Goods (Price, Quantity). Значениями поля currency м.б. : 1 (rub),2 (eur), 3(usd). Нужно получить результаты в рублях. Т.е. добавить условие, что если currency = 2 или 3 нужно дополнительно Price и Quantity умножить на соответствующий курс валюты. Можно ли это сделать прямо в select?

СУБД MsSQL. Я так понимаю там нет оператора if? 

Автор: Zloxa 26.9.2013, 11:14
Цитата(Колесо @  26.9.2013,  11:39 Найти цитируемый пост)
Я так понимаю там нет оператора if? 

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

Автор: Колесо 26.9.2013, 11:21
курсы валют хранится в таблице Purchase_Order в столбцах USD, EUR. 

Автор: Akina 26.9.2013, 11:47
Код

SELECT ..., 
Price * CASE currency 
        WHEN 1 THEN 1 
        WHEN 2 THEN 32 
        WHEN 3 THEN 41 
               ELSE 1000000 
        END
, ...

Автор: Колесо 26.9.2013, 13:06
Спасибо!) Все работает smile 

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)