Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2207 > unrolled thread

Custom window functions

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-22 12:32 +0300
    Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-23 20:56 +0200
      Re: Custom window functions Anton Shepelev <anton.txt@g{oogle}mail.com> - 2023-05-25 18:38 +0300
        Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-25 21:27 +0200
          Re: Custom window functions Erland Sommarskog <esquel@sommarskog.se> - 2023-05-26 21:00 +0200
            Re: Custom window functions Anton Shepelev <anton.txt@gmail.moc> - 2023-05-26 22:55 +0300

#2207 — Custom window functions

Hello, all.

Does MSSQL support custom window functions?  For example,
suppose I want to implement exponential smoothing in .NET
and register it as a window function in MSSQL -- is that
possible?

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments

[toc] | [next] | [standalone]

#2210

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> Does MSSQL support custom window functions?  For example,
> suppose I want to implement exponential smoothing in .NET
> and register it as a window function in MSSQL -- is that
> possible?
> 

Depends on what you want to achieve. This works:

   SELECT object_id, name, 
          dbo.integerlist(column_id) OVER(PARTITION BY object_id)
   FROM   sys.columns
   ORDER BY object_id, column_id

dbo.integerlist is a user-defined aggegrate implemented in C#.

But when I tried:

   SELECT object_id, name, 
          dbo.integerlist(column_id) OVER(PARTITION BY object_id 
             ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
   FROM   sys.columns
   ORDER BY object_id, column_id

I got 

   Msg 156, Level 15, State 1, Line 3
   Incorrect syntax near the keyword 'ORDER'.

The error is a little surprising. But presumably there are different parse-
trees for built-in and user-defined functions. And supposedly when they
introduced windowed aggregates in SQL 2012, they only updated the parse
tree for tbe built-ins. Which opens the question what would happen if
they fixed the parser...

[toc] | [prev] | [next] | [standalone]

#2215

Erland Sommarskog to Anton Shepelev:

> > Does MSSQL support custom window functions?  For
> > example, suppose I want to implement exponential
> > smoothing in .NET and register it as a window function
> > in MSSQL -- is that possible?
>
> Depends on what you want to achieve. This works:
>    SELECT object_id, name,
>           dbo.integerlist(column_id) OVER(PARTITION BY object_id)
>    FROM   sys.columns
>    ORDER BY object_id, column_id
>
> dbo.integerlist is a user-defined aggegrate implemented in
> C#.

Thanks, I didn't know at least this was possible.

> But when I tried:
>
>    SELECT object_id, name,
>           dbo.integerlist(column_id) OVER(PARTITION BY object_id
>              ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
>    FROM   sys.columns
>    ORDER BY object_id, column_id
>
> I got
>
>    Msg 156, Level 15, State 1, Line 3
>    Incorrect syntax near the keyword 'ORDER'.
>
> The error is a little surprising. But presumably there are
> different parse- trees for built-in and user-defined
> functions. And supposedly when they introduced windowed
> aggregates in SQL 2012, they only updated the parse tree
> for tbe built-ins.

Surprising indeed, nor do I think this unexpected difference
is documented, seeming like a bug. Why should the parser
care to distinguish between built-in and user-defined window
functions?

> Which opens the question what would happen if they fixed
> the parser...

The expected behavior does not seem difficult to predict...

Now that ORDER BY is not supported for windows with user-
defined aggregate functions, no sort of sequential time-
series analysis is possible, so I wrote my own using a
cursor:
             https://pastebin.com/raw/iLVNXQ1m

Do you think a more convenient interface is possible?

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments

[toc] | [prev] | [next] | [standalone]

#2216

Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
> Surprising indeed, nor do I think this unexpected difference
> is documented, seeming like a bug. 

The Docs are smart enough to be sufficiently vague:

   Depending on the ranking, aggregate, or analytic function used with the 
   OVER clause, <ORDER BY clause> and/or the <ROWS and RANGE clause> may not 
   be supported.

So it certainly permits for this exception.

But I could certainly argue that it is a bug in that the error 
message should be semantic, and not a parsing error.

But now for some interesting news. In SQL 2022, they introduced the 
WINDOW clause:

SELECT object_id, column_id, SUM(column_id) OVER MyWindow
FROM   sys.columns
WINDOW MyWindow AS (PARTITION BY object_id 
               ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER  BY object_id, column_id

This is useful when you want to use the same Windows clause for multiple
columns in the same query, as you only have to define it in one place.

So what about this?

SELECT object_id, column_id, dbo.integerlist(column_id) OVER MyWindow
FROM   sys.columns
WINDOW MyWindow AS (PARTITION BY object_id 
                ORDER BY column_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
ORDER  BY object_id, column_id

Yes, it runs and returns the correct result.

However, I need to add a caveat here: The fact that this runs might 
be a bug. I seem to recall that there is something about CLR 
aggregates and ordering. That is, if this runs but produces an 
incorrect result, this is not good. Then again, my aggregate is
supposed to return an ordered result, and it seems to do with my
test query. But that may be due to chance.

I will need to bring this up with some people at Microsoft.

[toc] | [prev] | [next] | [standalone]

#2217

Erland Sommarskog (esquel@sommarskog.se) writes:
> However, I need to add a caveat here: The fact that this runs might 
> be a bug. I seem to recall that there is something about CLR 
> aggregates and ordering. That is, if this runs but produces an 
> incorrect result, this is not good. Then again, my aggregate is
> supposed to return an ordered result, and it seems to do with my
> test query. But that may be due to chance.
> 
> I will need to bring this up with some people at Microsoft.
> 

An MVP colleague was kind to point me to 
https://learn.microsoft.com/en-
us/dotnet/api/microsoft.sqlserver.server.sqluserdefinedaggregateattribute.is
invarianttoorder?view=sqlclient-server-dotnet-1.0
which suggest that there is something hiding here.

[toc] | [prev] | [next] | [standalone]

#2218

Erland Sommarskog:

> An MVP colleague was kind to point me to
> https://learn.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.server.sqluserdefinedaggregateattribute.isinvarianttoorder
> which suggest that there is something hiding here.

It being "reserved for future use", the hidden thing is the
latent potential for arbitrary time-series analysis via
cusom aggreage functions!

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web