Path: csiph.com!news.mixmin.net!eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail
From: Anton Shepelev <anton.txt@g{oogle}mail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Custom window functions
Date: Thu, 25 May 2023 18:38:54 +0300
Organization: A noiseless patient Spider
Lines: 58
Message-ID: <20230525183854.3b73cd327e64ae145fe48c5b@g{oogle}mail.com>
References: <20230522123254.38c0b664930a7bc595f60a3a@g{oogle}mail.com> <XnsB00DD51AA11A2Yazorman@127.0.0.1>
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Injection-Info: dont-email.me; posting-host="e4cbbebb96708d56782a38c00dd49d23"; logging-data="3740520"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1/qJkpLxZ4b5I2thZfaCtMPJZKyNfPpzgA="
Cancel-Lock: sha1:sOlYapc8837QftMTG6iz9WAMuzM=
X-Newsreader: Sylpheed 3.7.0 (GTK+ 2.24.30; i686-pc-mingw32)
Xref: csiph.com comp.databases.ms-sqlserver: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