[MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 13:59
Оценка:
Имеется некая табличка, к которой обращаются с запросами. В запросах имееюся некая группировка и выборка значений. Например:

CREATE TABLE [#T] (
  [Number] int,
  [DateTime] datetime,
  [Value] nchar,
);

INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-01-01', N'D');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-01-01', N'E');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-01-02', N'A');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-01-03', N'B');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-01-03', N'C');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-02-01', N'3');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-02-01', N'2');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-02-02', N'1');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-02-03', N'4');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (1, N'2010-02-03', N'5');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (2, N'2010-01-01', N'U');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (2, N'2010-01-01', N'V');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (2, N'2010-01-02', N'X');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (2, N'2010-01-03', N'Z');
INSERT [#T] ([Number], [DateTime], [Value]) VALUES (2, N'2010-01-03', N'Y');

SELECT [Number], MONTH([DateTime]) [Month], MIN([Value]) [Value]
FROM [#T]
GROUP BY [Number], MONTH([DateTime]);

DROP TABLE [#T];

Результат:
Number  Month   Value
1       1       A
2       1       U
1       2       1


Требуется уметь считать по [Value] не только стандартные COUNT/AVG/SUM/MIN/MAX но, например, и FIRST и LAST — то есть в пределах группы требуется найти строку с минимальным или максимальным значением [DateTime] и вернуть значение [Value] этой строки.

У меня вышло как-то сложновато:
SELECT T.[Number], T.[Month], X.[Value]
FROM (SELECT [Number], MONTH([DateTime]) [Month], MIN([DateTime]) [Order] FROM [#T] GROUP BY [Number], MONTH([DateTime])) T
JOIN (SELECT [Number], [DateTime] [Order], MIN([Value]) [Value] FROM [#T] GROUP BY [Number], [DateTime]) X
  ON X.[Number] = T.[Number] AND T.[Order] = X.[Order];

Number  Month   Value
1       1       D
1       2       2
2       1       U


В реальности количество строк в таблице — несколько миллионов. Агрегаты над [DateTime] не просто MONTH() а немного более хитрые вычисления (Начало/конец года, например). Смущает необходимость дважды считать группы.

Может ли кто подсказать, как можно упростить второй запрос или, вообще, решить задачу другим, более правильным способом?
Help will always be given at Hogwarts to those who ask for it.
Re: [MSSQL] Агрегаты FIRST и LAST
От: Sinix  
Дата: 15.10.10 14:11
Оценка:
Здравствуйте, _FRED_, Вы писали:

_FR>Может ли кто подсказать, как можно упростить второй запрос или, вообще, решить задачу другим, более правильным способом?

Можно попробовать SQL CLR Aggregate
Re[2]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 14:15
Оценка:
Здравствуйте, Sinix, Вы писали:

_FR>>Может ли кто подсказать, как можно упростить второй запрос или, вообще, решить задачу другим, более правильным способом?

S>Можно попробовать SQL CLR Aggregate

Не, свой агрегат — не спортивно :о))

Хотя (может, кто и пробовал), есть ли вероятность, что с ним будет заметно быстрее (на больших объёмах данных)?
Help will always be given at Hogwarts to those who ask for it.
Re: [MSSQL] Агрегаты FIRST и LAST
От: Sinix  
Дата: 15.10.10 14:17
Оценка:
Здравствуйте, _FRED_, Вы писали:
Кстати, так почитабельней будет

WITH T
AS
(
  SELECT [Number], MONTH([DateTime]) [Month], MIN([DateTime]) [Order]
    FROM [#T]
    GROUP BY [Number], MONTH([DateTime])
),
X
AS
(
  SELECT [Number], [DateTime] [Order], MIN([Value]) [Value]
    FROM [#T]
    GROUP BY [Number], [DateTime]
)
SELECT T.[Number], T.[Month], X.[Value]
  FROM T
    INNER JOIN X ON X.[Number] = T.[Number] AND T.[Order] = X.[Order]


P.S. Использовать зарезервированные кейворды для идентефикаторов — зло
Re[3]: [MSSQL] Агрегаты FIRST и LAST
От: Sinix  
Дата: 15.10.10 14:23
Оценка:
Здравствуйте, _FRED_, Вы писали:

_FR>Не, свой агрегат — не спортивно :о))
Да ну! А как же NIH?

_FR>Хотя (может, кто и пробовал), есть ли вероятность, что с ним будет заметно быстрее (на больших объёмах данных)?

Насчёт производительности самих агрегатов можно не волноваться — у нас в достаточно нагруженных запросах на слабом железе своим агрегатом считаются битмаски, тормоза не в них.
Но (в теории) оптимизатор сможет выдернуть значения для min/max ч/з index seek и получить чуть-чуть выигрыша. На практике эти копейки съедятся стоимостью всего запроса. Надо смотреть планы
Re[2]: [MSSQL] Агрегаты FIRST и LAST
От: Lloyd Россия  
Дата: 15.10.10 14:29
Оценка:
Здравствуйте, Sinix, Вы писали:

_FR>>Может ли кто подсказать, как можно упростить второй запрос или, вообще, решить задачу другим, более правильным способом?

S>Можно попробовать SQL CLR Aggregate

Разве можно написать такой агрегат? Вроде как ограничение же есть на кол-во аргументов агрегирующей функции — не больше одного. А тут их 2 нужно.
Re[4]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 14:31
Оценка:
Здравствуйте, Sinix, Вы писали:

_FR>>Не, свой агрегат — не спортивно :о))
S>Да ну! А как же NIH?

На самом деле мой запрос создаётся неким своим билдером, который умеет билдить не только в MSSQL, но и в Oracle и MySQL, поэтому требуется просто запрос и даже без украшательств.
Help will always be given at Hogwarts to those who ask for it.
Re[3]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 14:36
Оценка:
Здравствуйте, Lloyd, Вы писали:

_FR>>>Может ли кто подсказать, как можно упростить второй запрос или, вообще, решить задачу другим, более правильным способом?
S>>Можно попробовать SQL CLR Aggregate

L>Разве можно написать такой агрегат? Вроде как ограничение же есть на кол-во аргументов агрегирующей функции — не больше одного. А тут их 2 нужно.

Ограничение по количеству параметров, наверное, можно обойти, задав в изначальном запросе необходимый ORDER BY:
SELECT [Number], MONTH([DateTime]) [Month], FIRST([Value]) [Value]
FROM [#T]
GROUP BY [Number], MONTH([DateTime])
ORDER BY [Number], MONTH([DateTime]), [DateTime];

это если порядок вызова агрегата кем-то нарантируется, в чём я, правда, не уверен.
Help will always be given at Hogwarts to those who ask for it.
Re[2]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 14:37
Оценка:
Здравствуйте, Sinix, Вы писали:

S>P.S. Использовать зарезервированные кейворды для идентефикаторов — зло

Мне больше импонирует понятность и очевидность идентификаторов, нежели их кейвордность. Именно поэтому я использую [] для всех идентификаторов, а не только там, где приходится.
Help will always be given at Hogwarts to those who ask for it.
Re[2]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 14:39
Оценка:
Здравствуйте, Sinix, Вы писали:

_FR>>Может ли кто подсказать, как можно упростить второй запрос или, вообще, решить задачу другим, более правильным способом?

S>Можно попробовать SQL CLR Aggregate

А можно ли написать агрегат, тип которого зависит от типа поля, по которому происходит агрегация?
Help will always be given at Hogwarts to those who ask for it.
Re[3]: [MSSQL] Агрегаты FIRST и LAST
От: Sinix  
Дата: 15.10.10 14:50
Оценка: 11 (1)
Здравствуйте, Lloyd, Вы писали:

L>Разве можно написать такой агрегат? Вроде как ограничение же есть на кол-во аргументов агрегирующей функции — не больше одного. А тут их 2 нужно.
Можно, но только для sql server 2008 и выше.

Кстати, а зачем 2 параметра? Агрегат FirstFromRange(SourceColumn, GroupByColumn) будет мягко говоря меееедленный. И вообще, можно же использовать OVER PARTITION BY.
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);

Тормоз я сегодня
Re[4]: [MSSQL] Агрегаты FIRST и LAST
От: Sinix  
Дата: 15.10.10 15:04
Оценка:
Здравствуйте, _FRED_, Вы писали:

Ндя, я ступил Есть же OVER PARTITION BY!
USE AdventureWorks2008R2;
GO
SELECT SalesOrderID, ProductID, OrderQty
    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
FROM Sales.SalesOrderDetail 
WHERE SalesOrderID IN(43659,43664);





_FR>это если порядок вызова агрегата кем-то нарантируется, в чём я, правда, не уверен.

Не гарантируется. Точнее, при определении агрегата можно указать [SqlUserDefinedAggregate(... IsInvariantToOrder=true)], но, увы в сложных запросах данные могут оказаться отсортированы не так, как ожидается. Это не баг, просто sql server не поддерживает over order by для агрегатов.

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

_FR>А можно ли написать агрегат, тип которого зависит от типа поля, по которому происходит агрегация?
Насколько знаю, нет.
Re[4]: [MSSQL] Агрегаты FIRST и LAST
От: Lloyd Россия  
Дата: 15.10.10 15:06
Оценка:
Здравствуйте, _FRED_, Вы писали:

L>>Разве можно написать такой агрегат? Вроде как ограничение же есть на кол-во аргументов агрегирующей функции — не больше одного. А тут их 2 нужно.

_FR>Ограничение по количеству параметров, наверное, можно обойти, задав в изначальном запросе необходимый ORDER BY:
_FR>
_FR>SELECT [Number], MONTH([DateTime]) [Month], FIRST([Value]) [Value]
_FR>FROM [#T]
_FR>GROUP BY [Number], MONTH([DateTime])
_FR>ORDER BY [Number], MONTH([DateTime]), [DateTime];
_FR>

_FR>это если порядок вызова агрегата кем-то нарантируется, в чём я, правда, не уверен.

ORDER идет после группировки.
Re[4]: [MSSQL] Агрегаты FIRST и LAST
От: Lloyd Россия  
Дата: 15.10.10 15:08
Оценка:
Здравствуйте, Sinix, Вы писали:

S>Здравствуйте, Lloyd, Вы писали:

L>>Разве можно написать такой агрегат? Вроде как ограничение же есть на кол-во аргументов агрегирующей функции — не больше одного. А тут их 2 нужно.
S>Можно, но только для sql server 2008 и выше.

S>Кстати, а зачем 2 параметра? Агрегат FirstFromRange(SourceColumn, GroupByColumn) будет мягко говоря меееедленный. И вообще, можно же использовать OVER PARTITION BY.
S>
S>USE AdventureWorks2008R2;
S>GO
S>SELECT SalesOrderID, ProductID, OrderQty
S>    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
S>    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
S>    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
S>    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
S>    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
S>FROM Sales.SalesOrderDetail 
S>WHERE SalesOrderID IN(43659,43664);
S>

S>Тормоз я сегодня

BOL и я умею читать. Ты лучше нашиги для приведенного примера.
Re: [MSSQL] Агрегаты FIRST и LAST
От: vmpire Россия  
Дата: 15.10.10 15:18
Оценка: 26 (1)
Здравствуйте, _FRED_, Вы писали:

_FR>Требуется уметь считать по [Value] не только стандартные COUNT/AVG/SUM/MIN/MAX но, например, и FIRST и LAST — то есть в пределах группы требуется найти строку с минимальным или максимальным значением [DateTime] и вернуть значение [Value] этой строки.

Не знаю, как оно будет на нескольких миллионах, но попробовать можно:

SELECT
    [Number],
    [Month],
    MIN([Value]) [Min],
    MAX([Value]) [Max],
    MAX(CASE WHEN [FlagFirst] = 1 THEN [Value] ELSE NULL END) [First],
    MAX(CASE WHEN [FlagLast] = 1 THEN [Value] ELSE NULL END) [Last]
FROM(
    SELECT 
        [Number],
        MONTH([DateTime]) [Month],
        [Value],
        CASE WHEN 1 = ROW_NUMBER() OVER (PARTITION BY [Number], MONTH([DateTime]) ORDER BY [DateTime])
             THEN 1 ELSE 0 END [FlagFirst],
        CASE WHEN 1 = ROW_NUMBER() OVER (PARTITION BY [Number], MONTH([DateTime]) ORDER BY [DateTime] DESC)
             THEN 1 ELSE 0 END [FlagLast]
    FROM [#T]
) Q
GROUP BY [Number], [Month];
Re[5]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 15:35
Оценка:
Здравствуйте, Sinix, Вы писали:

S>Ндя, я ступил Есть же OVER PARTITION BY!
S>USE AdventureWorks2008R2;
S>GO
S>SELECT SalesOrderID, ProductID, OrderQty
S>    ,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Total'
S>    ,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Avg'
S>    ,COUNT(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Count'
S>    ,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Min'
S>    ,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS 'Max'
S>FROM Sales.SalesOrderDetail 
S>WHERE SalesOrderID IN(43659,43664);

S>

И как OVER PARTITION BY поможет посчитать FIRST / LAST?
Help will always be given at Hogwarts to those who ask for it.
Re[2]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 15:41
Оценка:
Здравствуйте, vmpire, Вы писали:

_FR>>Требуется уметь считать по [Value] не только стандартные COUNT/AVG/SUM/MIN/MAX но, например, и FIRST и LAST — то есть в пределах группы требуется найти строку с минимальным или максимальным значением [DateTime] и вернуть значение [Value] этой строки.

V>Не знаю, как оно будет на нескольких миллионах, но попробовать можно:

Спасибо! Идея понятна, буду сравнивать и гонять.
Help will always be given at Hogwarts to those who ask for it.
Re[5]: [MSSQL] Агрегаты FIRST и LAST
От: _FRED_ Черногория
Дата: 15.10.10 15:45
Оценка:
Здравствуйте, Lloyd, Вы писали:

L>>>Разве можно написать такой агрегат? Вроде как ограничение же есть на кол-во аргументов агрегирующей функции — не больше одного. А тут их 2 нужно.

_FR>>Ограничение по количеству параметров, наверное, можно обойти, задав в изначальном запросе необходимый ORDER BY:
_FR>>SELECT [Number], MONTH([DateTime]) [Month], FIRST([Value]) [Value]
_FR>>FROM [#T]
_FR>>GROUP BY [Number], MONTH([DateTime])
_FR>>ORDER BY [Number], MONTH([DateTime]), [DateTime];

_FR>>это если порядок вызова агрегата кем-то нарантируется, в чём я, правда, не уверен.

L>ORDER идет после группировки.

В принципе, это логично, но почему тогда можно в ORDER BY указывать поля, которые не учавствуют в группировке?
Или где-то точно описано, как работает ORDER BY при наличии GROUP BY?
Help will always be given at Hogwarts to those who ask for it.
Re[6]: [MSSQL] Агрегаты FIRST и LAST
От: Lloyd Россия  
Дата: 15.10.10 16:16
Оценка:
Здравствуйте, _FRED_, Вы писали:

L>>ORDER идет после группировки.

_FR>В принципе, это логично, но почему тогда можно в ORDER BY указывать поля, которые не учавствуют в группировке?

Вообще-то нельзя:
SELECT TABLE_NAME, COUNT(*) 
FROM INFORMATION_SCHEMA.COLUMNS
GROUP BY TABLE_NAME
ORDER BY DATA_TYPE


Результат:

Msg 8127, Level 16, State 1, Line 4
Column "INFORMATION_SCHEMA.COLUMNS.DATA_TYPE" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.


_FR>Или где-то точно описано, как работает ORDER BY при наличии GROUP BY?

Наверняка где-то описано. Я давно по SQL-ю книжек не читал, что осталось в голове, тем и пользуюсь.
Re[6]: [MSSQL] Агрегаты FIRST и LAST
От: Sinix  
Дата: 15.10.10 17:28
Оценка:
Здравствуйте, _FRED_, Вы писали:

_FR>Здравствуйте, Sinix, Вы писали:

_FR>И как OVER PARTITION BY поможет посчитать FIRST / LAST?
Пардон, пропустил про требование портируемости для оракла. С кастомным агрегатом ValueForMin всё было бы просто.
Подождите ...
Wait...
Пока на собственное сообщение не было ответов, его можно удалить.