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

Groups > comp.databases.ms-sqlserver > #2215

Re: Custom window functions

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

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

csiph-web