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

Откуда:
Сообщений: 41
Привет.

Хочу выбрать записи удовлетворяющие условию where и хочу что бы в результат добавилась еще одна колонка показывающая максимальное значение одной из колонок. В лоб воспользоваться функцией max() не удалось потом что одна из колонок имеет тип данных geometry а по нему не группирует. Поэтому вот такой веловипед:

    SELECT 
	Segment_ID.Segment_ID,
	Intensity.Date,
	Intensity.NumAll,
	Intensity.AverageDailyIntensCar,
	mx.maxAverageDailyIntensCar,
	Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
LEFT JOIN (
    SELECT 
          i.Segment_ID
        , maxAverageDailyIntensCar = MAX(i.AverageDailyIntensCar)
    FROM dbo.Intensity i
    GROUP BY i.Segment_ID
) mx ON Segment_ID.Segment_ID = mx.Segment_ID
where (DATEPART(yy, Intensity.Date) = 2009
AND DATEPART(mm, Intensity.Date) = 08
AND DATEPART(dd, Intensity.Date) = 14)


Но проблема в том, что он неверно ищет максимум. Допустим я вижу в колонке числа ~30 000, а этот запрос говорит что там максимум в районе ~6 000. В чем может быть ошибка?
И да, конструкцию with я не могу использовать.
22 май 13, 10:34    [14331024]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Glory
Member

Откуда:
Сообщений: 104760
    SELECT 
	Segment_ID.Segment_ID,
	Intensity.Date,
	Intensity.NumAll,
	Intensity.AverageDailyIntensCar,
	mx.maxAverageDailyIntensCar,
	Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
LEFT JOIN (
    SELECT 
          i.Segment_ID
        , maxAverageDailyIntensCar = MAX(i.AverageDailyIntensCar)
    FROM dbo.Intensity i
    GROUP BY i.Segment_ID
) mx ON Segment_ID.Segment_ID = mx.Segment_ID and Segment_ID.AverageDailyIntensCar = mx.maxAverageDailyIntensCar
where (DATEPART(yy, Intensity.Date) = 2009
AND DATEPART(mm, Intensity.Date) = 08
AND DATEPART(dd, Intensity.Date) = 14)
22 май 13, 10:40    [14331073]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
SELECT 
	Segment_ID.Segment_ID,
	Intensity.Date,
	Intensity.NumAll,
	Intensity.AverageDailyIntensCar,
	MAX(Intensity.AverageDailyIntensCar) OVER(PARTITION BY Intensity.Segment_ID) AS maxAverageDailyIntensCar,
	Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
WHERE Intensity.Date >= '20090814' AND Intensity.Date < '20090815'
22 май 13, 10:48    [14331143]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
mkliver
Member

Откуда:
Сообщений: 41
Glory

Ошибка в последнем LEFT JOIN в таблице Segment_ID нет колонки AverageDailyIntensCar.

Гость333

В колонке в которую вы добавили находятся такие же значение как и в Intense.AverageDailyIntensCar. Максимум не ищется.
22 май 13, 18:29    [14335119]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver
Гость333

В колонке в которую вы добавили находятся такие же значение как и в Intense.AverageDailyIntensCar. Максимум не ищется.

У меня ищется:
USE tempdb;
GO

CREATE TABLE Segment_ID (Segment_ID int);
CREATE TABLE Track (Segment_ID int, the_geom varchar(100) default 'some geometry data');
CREATE TABLE Intensity (Segment_ID int, Date datetime, NumAll int, AverageDailyIntensCar int);

INSERT Segment_ID (Segment_ID) VALUES (1);

INSERT Track (Segment_ID) VALUES(1);

INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090814 07:00:00', 1, 7);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090814 08:00:00', 1, 8);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090814 09:00:00', 1, 9);

SELECT 
	Segment_ID.Segment_ID,
	Intensity.Date,
	Intensity.NumAll,
	Intensity.AverageDailyIntensCar,
	MAX(Intensity.AverageDailyIntensCar) OVER(PARTITION BY Intensity.Segment_ID) AS maxAverageDailyIntensCar,
	Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
WHERE Intensity.Date >= '20090814' AND Intensity.Date < '20090815';


К сообщению приложен файл. Размер - 27Kb
22 май 13, 18:45    [14335164]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Это я реализовал пожелание "хочу что бы в результат добавилась еще одна колонка показывающая максимальное значение одной из колонок" — так, как его понял. Что нужно на самом деле, я не понял.

Лучше всего будет, если вы дадите какие-то исходные данные (по моему примеру) и скажете "вот здесь мой запрос показывает 6000, а надо бы 30000".
22 май 13, 18:52    [14335190]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
mkliver,

Вы как-то загадочно делаете left join Intensity, и потом используете значения Intensity в where.
22 май 13, 18:58    [14335213]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
mkliver
Member

Откуда:
Сообщений: 41
Гость333 uniqueidentifier

Мне выдает вот такой результат.

CREATE TABLE Segment_ID (Segment_ID uniqueidentifier);
CREATE TABLE Track (Segment_ID uniqueidentifier, the_geom varchar(100) default 'some geometry data');
CREATE TABLE Intensity (Segment_ID uniqueidentifier, Date datetime, NumAll int, AverageDailyIntensCar float);

INSERT Segment_ID (Segment_ID) VALUES (1);
INSERT Segment_ID (Segment_ID) VALUES (2);

INSERT Track (Segment_ID) VALUES(1);
INSERT Track (Segment_ID) VALUES(2);

INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090814 07:00:00', 1, 2000.235152);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090814 08:00:00', 1, 19000.562512);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090815 09:00:00', 1, 553.22232);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (2, '20090815 09:00:00', 1, 2362.22232);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (2, '20090815 09:00:00', 1, 5363.22232);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (2, '20090814 09:00:00', 1, 30000.22232);

Я правда не знаю, что за тип uniqueidentifier и как он формируется, но скорее всего можно на его место вставить варчар.

Я хочу получить записи на 2009-08-14, и что бы в вывод добавилась колонка в которой для каждой строки будет выставлен максимум из всех значений находящихся в колонке AverageDailyIntensCar в данном случае это 30000.22232. Далее если я делаю выборку на 2009-08-15, то я так же хочу видеть 30000.22232 в дополнительной колонке.

Извините, за то что так туманно указал все это в первом посте.

Кот Матроскин

То есть правильные было бы делать выборку из Intensity а уже к ней джойнить Segment_ID и Track?
22 май 13, 20:15    [14335398]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
mkliver
Member

Откуда:
Сообщений: 41
mkliver,

К предыдущему сообщению не приклеился файл.

К сообщению приложен файл. Размер - 120Kb
22 май 13, 20:16    [14335402]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
mkliver
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090814 07:00:00', 1, 2000.235152);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090814 08:00:00', 1, 19000.562512);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (1, '20090815 09:00:00', 1, 553.22232);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (2, '20090815 09:00:00', 1, 2362.22232);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (2, '20090815 09:00:00', 1, 5363.22232);
INSERT Intensity (Segment_ID, Date, NumAll, AverageDailyIntensCar) VALUES (2, '20090814 09:00:00', 1, 30000.22232);

Я хочу получить записи на 2009-08-14, и что бы в вывод добавилась колонка в которой для каждой строки будет выставлен максимум из всех значений находящихся в колонке AverageDailyIntensCar в данном случае это 30000.22232. Далее если я делаю выборку на 2009-08-15, то я так же хочу видеть 30000.22232 в дополнительной колонке.

Извините, за то что так туманно указал все это в первом посте.

Если Вам нужен просто общий максимум AverageDailyIntensCar (а не максимум для данного Segment_ID или данной даты) - то Вам надо просто написать
SELECT 
....,
(SELECT max(AverageDailyIntensCar) FROM  Intensity ) as maxAverageDailyIntensCar
from ...
23 май 13, 10:42    [14337146]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver
Я правда не знаю, что за тип uniqueidentifier и как он формируется, но скорее всего можно на его место вставить варчар.

Можно копировать из окна результатов как строку, SQL Server сделает неявную конвертацию к типу uniqueidentifier:
DECLARE @uid uniqueidentifier;
SET @uid = 'BBE874D3-A4D3-453E-BB1E-013F239A1C86';
SELECT @uid;

Желаемый вами запрос будет выглядеть примерно так:
SELECT 
	Segment_ID.Segment_ID,
	Intensity.Date,
	Intensity.NumAll,
	Intensity.AverageDailyIntensCar,
	mx.maxAverageDailyIntensCar,
	Track.the_geom
FROM Segment_ID
LEFT JOIN Track ON Segment_ID.Segment_ID=Track.Segment_ID
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID
CROSS JOIN 
(
    SELECT maxAverageDailyIntensCar = MAX(i.AverageDailyIntensCar)
    FROM dbo.Intensity i
) mx
WHERE Intensity.Date >= '20090815' AND Intensity.Date < '20090816';

Условие на Intensity.Date я пишу именно в таком виде, потому что с вашими DATEPART не будет эффективно использоваться индекс по Intensity.Date. (Даже если сейчас этого индекса нет — он может быть создан в дальнейшем, поэтому лучше бы сразу писать условия, обеспечивающие поиск по индексу.)
23 май 13, 10:53    [14337200]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver
Кот Матроскин

То есть правильные было бы делать выборку из Intensity а уже к ней джойнить Segment_ID и Track?

Коллега Кот Матроскин хотел указать, что в таком виде ваш LEFT JOIN превращается в INNER JOIN. Если вы хотите именно LEFT JOIN, то надо условие перенести из WHERE в ON:
LEFT JOIN Intensity ON Segment_ID.Segment_ID=Intensity.Segment_ID AND Intensity.Date >= '20090815' AND Intensity.Date < '20090816'

Вот пример, демонстрирующий разницу:
DECLARE @a TABLE (id_a int);
DECLARE @b TABLE (id_b int, name varchar(100));

INSERT @a(id_a) VALUES(1);
INSERT @a(id_a) VALUES(2);

INSERT @b(id_b, name) VALUES(1, 'Вася');
INSERT @b(id_b, name) VALUES(1, 'Маша');
INSERT @b(id_b, name) VALUES(2, 'Петя');
INSERT @b(id_b, name) VALUES(3, 'Коля');

-- В таком виде будет INNER JOIN
SELECT *
FROM @a a
     LEFT JOIN @b b ON b.id_b = a.id_a
WHERE b.name = 'Вася';
/*
id_a        id_b        name
----------- ----------- --------
1           1           Вася
*/
-- А теперь перенесём условие из WHERE в ON
SELECT *
FROM @a a
     LEFT JOIN @b b ON b.id_b = a.id_a AND b.name = 'Вася';
/*
id_a        id_b        name
----------- ----------- --------
1           1           Вася
2           NULL        NULL
*/
23 май 13, 11:02    [14337237]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
mkliver
Member

Откуда:
Сообщений: 41
Гость333

Спасибо, это то, что было нужно.

А, про джоины, вы так же правы. Я не силен в скл и хотел бы для полной ясности уточнить.
Я делаю джоин с таблицей Intensity для того что бы получить только те записи с таким Segmеnt_ID, которые существуют в таблице Intensity. А если какие то Segment_ID в таблице не содержится, то видеть их я не желаю.
В таком случае мне нужно использовать INNER JOIN?
23 май 13, 20:46    [14340915]     Ответить | Цитировать Сообщить модератору
 Re: Как найти максимальное значение колонки?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
mkliver,

Да, тогда вам нужен INNER JOIN.
24 май 13, 12:03    [14343837]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить