Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 группировка по полю с указанием последней даты  [new]
новичек скл
Guest
Есть таблица заказов

orders:
user_name
user_address
date
phone
order_sum

необходимо показать последний по дате заказ по каждому телефону + общее количество и сумму по каждому телефону
единственное уникальное поле здесь date, остальные поля могут дублироваться:
адреса и имена пользователей при одном и том же телефоне могут быть разными
пытался так:

SELECT 
		one.phone, 
		one.cnt, 
		one.last_date,
		one.sm,
		two.user_name,
		two.user_address
FROM 
(
	SELECT 
		phone, 
		count(*) AS cnt, 
		MAX(date) AS last_date, 
		SUM(order_sum) as sm,
	
	FROM orders 
	GROUP BY phone
)as one
	
INNER JOIN orders AS two
ON one.phone = two.phone AND one.last_date = two.date

но возвращается больше записей, чем нужно
если кто-то может помочь советом, заранее признателен
27 июл 12, 02:31    [12922664]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8734
Версия сервера?
27 июл 12, 03:36    [12922711]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
а что означает
автор
но возвращается больше записей, чем нужно
?

так в скрипте, я ошибку не вижу. все сделано правильно
27 июл 12, 08:03    [12922923]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
1) Такие вещи лучше хранить предподсчитанными на уровне нужной сущности
2) Определитесь какая сущность (и таблица для неё) у вас является заказчиком
- пользователь (плевать с каким телефоном)
- телефон (плевать с каким пользователем)
- "Телефон пользователя"

Структура сущностей должна быть соотвествтующся.

Выводится у вас больше строк, потому что сгруппировали вы по телефону, а потом в изначальной таблице по этому телефону могут достаться два пользователя. И датас скорее всего не datetime (или обрезанный) а date раз вы так лего "равно" написали в условии джойна. Да и один пользователь в один день c одного телефона несколько заказов может сделать.

База у вас НЕ нормализованная

В вашем варианте структуры сущностей можно
1) сгруппировать по пользователю-телефону (тем самым выделится сущность "на лету" "Телефон пользователя"), а потом делать то что вы делаете с джойном по суловию +Ссылка на пользователя (не уберёт факта того что один пользователь в один день с одного телефона сделает заказ)
2) найти "последний автоинкрементированный идешшник" или найти "последнюю запись" уникально при помощи row_number() или каким-то макаром выделить именно "последнюю запись в таблице orders для нужной сущности".
27 июл 12, 15:04    [12925935]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
NIIIK
1) сгруппировать по пользователю-телефону (тем самым выделится сущность "на лету" "Телефон пользователя"), а потом делать то что вы делаете с джойном по суловию +Ссылка на пользователя (не уберёт факта того что один пользователь в один день с одного телефона сделает заказ)
2) найти "последний автоинкрементированный идешшник" или найти "последнюю запись" уникально при помощи row_number() или каким-то макаром выделить именно "последнюю запись в таблице orders для нужной сущности".


если верить автору, то
автор
единственное уникальное поле здесь date

и связка идет по этому полю
ON one.phone = two.phone AND one.last_date = two.date

такого быть не должно


ЗЫЖ хотя возможно Вы и правы и автор что-то утаил
27 июл 12, 15:09    [12925988]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
[quot HandKot]
NIIIK
такого быть не должно


group by - выдаст уникальное значение не хуже дистинкта.
Если дата уникальна (и особенно у неё есть часть "время"), то MAX(date) AS last_date, так же будет уникально для каждого телефона (даже если два покупателя могут им пользоваться).

Если данных больше чем должно быть, то они появляются НЕ в подзапросе. Подзапрос гарантированно выводит один телефон.

Перемножение может идти только по условию

one.phone = two.phone AND one.last_date = two.date

(и если написать уже по этому "group by" а потом "count", то это можно увидеть)
И значит это что для одного телефона может быть запись с одинаковой датой (и она попала в максимальную) и неважно один это пользователь или несколько.

Обычный гавнокод с обычными проё...ами.
27 июл 12, 16:59    [12926824]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 2996
[quot NIIIK]
HandKot
пропущено...


group by - выдаст уникальное значение не хуже дистинкта.
Если дата уникальна (и особенно у неё есть часть "время"), то MAX(date) AS last_date, так же будет уникально для каждого телефона (даже если два покупателя могут им пользоваться).

Если данных больше чем должно быть, то они появляются НЕ в подзапросе. Подзапрос гарантированно выводит один телефон.

Перемножение может идти только по условию

one.phone = two.phone AND one.last_date = two.date

(и если написать уже по этому "group by" а потом "count", то это можно увидеть)
И значит это что для одного телефона может быть запись с одинаковой датой (и она попала в максимальную) и неважно один это пользователь или несколько.

Обычный гавнокод с обычными проё...ами.


что-то в конце пятницы плохо думается, да и окндей не холодит :(
пример можете приветси?
27 июл 12, 17:13    [12926907]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
Максим М.
Member

Откуда: Москва
Сообщений: 75
новичек скл
но возвращается больше записей, чем нужно
если кто-то может помочь советом, заранее признателен


Наверняка есть 2 записи с одинаковыми телефонами и одинаковыми датами заказа. Из-за них и размножаются записи.

Чтобы вывести имя и адрес пользователя, сделавшего последний заказ, используйте ROW_NUMBER() over (partition by phone order by date desc), а потом отберите только те записи где 1 получилось.
А чтобы одновременно с этим просуммировать выручку по телефону - sum(order_sum) over (partition by phone)
Тогда вообще без джойна можно обойтись.
27 июл 12, 18:31    [12927350]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
новичек скл
Guest
Максим М., NIIIK
Спасибо за подсказку
Действительно каким то образом имеются дублирующие записи
Надо было перепроверить, но был уверен, что такого быть не может


автор
Такие вещи лучше хранить предподсчитанными на уровне нужной сущности

Да понятно, но эта база уже потеряна для человечества, доживает свой век, нету на нее ресурсов времени

Еще раз всем спасибо



А не подскажете, как оптимально построить запрос по вышеуказанным условиям?
Без изменения структуры базы?
А то на 10 - 20 млн. записей подтормаживает?
В смысле оптимально в отношении скорости исполнения запроса?

Сервер - sql server 2008 r2
27 июл 12, 21:24    [12927773]     Ответить | Цитировать Сообщить модератору
 Re: группировка по полю с указанием последней даты  [new]
trew
Member

Откуда: Москва
Сообщений: 2646
новичек скл,

declare @tab table (phone int, date smalldatetime, order_sum int, user_address varchar(20))

insert into @tab
select 11, '20120701', 2, 'тест'
union all
select 12, '20120702', 4, 'адрес разный'
union all
select 12, '20120702', 7, 'все разное'

SELECT 
		one.phone, 
		one.cnt, 
		one.last_date,
		two.user_address
FROM 
(
	SELECT 
		phone, 
		count(*) AS cnt, 
		MAX(date) AS last_date, 
		SUM(order_sum) as sm
	
	FROM @tab 
	GROUP BY phone
)as one
	
INNER JOIN @tab AS two
ON one.phone = two.phone AND one.last_date = two.date

-- проверка уникальности, даты повторяются
SELECT date, COUNT(1) AS NN
FROM @tab
GROUP BY date
HAVING COUNT(1)>1


А тип поля phone какой ? Числа быстрее сравниваются, чем текстовые поля.
27 июл 12, 22:21    [12927972]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить