New Window Functions syntax. DistinctBy translation support. #4844
Conversation
# Conflicts: # Source/LinqToDB/SqlQuery/QueryElementVisitor.cs
# Conflicts: # Source/LinqToDB/Linq/Builder/SelectBuilder.cs # Source/LinqToDB/Linq/Translation/ProviderMemberTranslatorDefault.cs # Tests/Linq/Linq/DistinctTests.cs
|
@linq2db/developers As we're introducing new apis for this complex and often-used feature, I think this deserves a thorough discussion with the whole team to try and land the best DX possible. This is a complex API surface for sure, there could be many different ideas. It's also good to design in a way where we're able to cover the complete SQL standard regarding windows, so I'll start by summarizing Postgre docs on the topic (as Postgre is probably the most standard-compliant DB that I know of). First a quick note: window functions apply to aggregate-like functions, not aggregates. I think we should keep them in our mind but they could also have a different "entry" api? Now here's the Postgre syntax for window functions. First, what's an aggregate-like function?
The ability to reference a Here's the
Finally
Frames are: Note that offset is usually an integer, but when the frame is defined as Exclusion are: |
|
@sdanyliv You'll have to let me know what is possible with the new parser or not. Here's the current PR syntax from a random test case: Sql.Window.Sum(t.IntValue, w => w.PartitionBy(t.CategoryId).OrderBy(t.Id))I am not a big fan of nesting the window definition inside the aggregate function, nor using lots of lambdas, esp. in a LINQ expression where I think they wouldn't be required? Here's one idea: what if we started with the window and ended with the aggregate? from t in table
// Can we use fluent api without lambdas to define the window?
let w = Sql.Window("optional_name").PartitionBy(t.Country)
select new
{
// Can we end with aggregate?
// This works naturally for existing windows:
sumByCountry = w.Sum(t.Salary),
// Also work to modify a named window?
sumByAge = w.PartitionBy(t.Age).Sum(t.Salary),
// Or define a new window
localWindowSum = Sql.Window.PartitionBy(t.Country).Sum(t.Salary),
}What do you guys think? |
|
An issue with the proposed |
|
@jods4, try applying your syntax to small test classes first. The issue here is that it doesn’t guarantee a correct window function definition. Some functions require a partition, while others require I’m also not a big fan of using a nested lambda inside a window function, but in this case, it’s the most capable approach since the builder is strictly constrained to a specific window function. Keep in mind that lambda syntax greatly simplifies translation. Once we identify the Regarding window definition, I might be mistaken, but I don’t see much benefit in defining a window the way you suggested. Check the |
# Conflicts: # Source/LinqToDB/Reflection/Methods.cs
Wasn't it introduced for this release? I just looked in 5.1.1 completion and I don't see it.
This is a good first design question. Is it an explicit goal to enforce 100% valid syntax? But let's talk about some concrete examples, as it's interesting. You mention In fact in Postgre, these functions DO NOT support windows! It seems to be the simple approach for these would be a dedicated two args function: // T PercentileCont<T>(double fraction, T withinGroupOrderBy)
Sql.PercentileCont(0.34, table.Salary)Things get more fun if you look at other DB such as MSSQL. Per-provider syntax is not enforceable in our API. For the MSSQL extension, I personally would suggest re-using the same Window.PartitionBy(table.Department).PercentileCont(0.34, table.Salary)Now this is not perfect:
I would need to lookup how the standard describes these functions. Maybe the @sdanyliv can you think of other significant cases where using the window first would allow invalid SQL, that are significant enough that we shouldn't allow them?
Are you referring to the name usage only, or something else? // WINDOW w as (PARTITION BY t.Department)
let w = Window.PartitionBy(t.Department)
// WINDOW dept as (PARTITION BY t.Department)
let w = Window.WithName("dept").PartitionBy(t.Department)I don't feel strongly about this, we can always add later or not at all. As I said, I was mostly inspired by similar CTE option. |
|
I did a quick search on If we really wanted to enforce this at syntax level (personally not convinced) we could do it with "clever" extensions methods. Likewise frames ( It should be noted that if we want to go to the very end of this, |
|
@jods4, I'm open to your API proposal—mainly consisting of extension methods and interfaces—which would require minimal rework on my end.
If you can implement this for several complex window functions, I'll review and compare. This part is quite tricky... Leave PercentileCont behind, It is realy strange function and you are wrong with syntax, it is aggregation function with OVER PART and actually it perfectly fits to proposed API. Check sample in first PR comment. Why I proposed the new syntax:
|
|
@sdanyliv I like this initiative and I agree with you the current syntax is clumsy. I think my proposal is not perfect either, I'm just trying to put out as many ideas as possible so we can pick the best one before publishing them.
What do you mean I'm wrong with syntax? I have check 6 different DB docs, the following was quoted straight from MS documentation. With exception of Postgre, which doesn't support Compared approaches from point 5: // Current linq2db, I think this is worse option
RN1 = Sql.Ext.RowNumber().Over().PartitionBy(t.Field1).OrderBy(t.Field2).ToValue(),
// Current PR, using lambda inside analytical function
RN1 = Sql.Window.RowNumber(f => f.PartitionBy(t.Field1).OrderBy(t.Field2)),
// What I had proposed in comment above:
RN1 = Sql.Window.PartitionBy(t.Field1).OrderBy(t.Field2).RowNumber()On this example, the PR and my proposal are very similar, I personally like the 3rd option better that 2nd because there's no nesting or lambda. Can you tell me what you think are "hard" examples so that I can try to see if they could fit in a different proposal? For ordered set aggregates Note that some DB (e.g. Postgre and Oracle) also have very weird Hypothetical-Set Aggregates like |
This one is bad idea ever :) When you want Fail example, with filter: |
|
I suggest adding another option:
This would be more consistent with:
and other similar patterns. |
|
@igor-tkachev (1) How is your expression typed as its value? (2) Can you provide an example for more analytical functions, do you think the syntax scales nicely? |
|
@sdanyliv With the syntax currently proposed in this PR, what's your take on differentiating This is a bit hypothetical at the moment because I don't know any DB engine that supports it, but the following does make sense: string_agg(name, ',' ORDER BY age) OVER (
PARTITION BY dept
ORDER BY code
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)This extremely convoluted but it has sense: If you try that with Postgre it doesn't say this is invalid. It says: "aggregate ORDER BY is not implemented for window functions" (so you can't even use A similar logic/argument exists for functions that use So I think it'd be nice to reflect this structure in our API, although I also don't want it to become overly verbose. Proposal: should we add Count(x => x.Over(declaredWindow));
Sum(cost, x => x.Over.PartitionBy(color).OrderBy(date));
StringAgg(name, ", ", x => x.OrderBy(age).Over.OrderBy(dept).Groups(1, 1)) |
That's why we rewrite window functions, they have almost similar syntax: var query =
from t in db.Some
select new
{
a = Sql.Window.Count(), // invalid but compilable
b = Sql.Window.Count().Distinct(x.Some), // invalid but compilable
valid = Sql.Window.Count().Distinct(x.Some) .Over(f => f.PartitionBy(x.Some))
}Pros:
Const:
|
|
@jods4, good point! I'm starting to think that the I also changed Sql.Functions.Count(x => x.Over(declaredWindow));
Sql.Functions.Sum(cost, x => x.Over().PartitionBy(color).OrderBy(date));Regarding aggregates, you missed how they should be defined—as an extension over var query =
from t in db.Some
group t by t.SomeId into g
select new
{
g.StringAgg(e => e.name, ", ", (e, x) => x.OrderBy(e.age).Over().OrderBy(e.dept).Groups(1, 1))
};There are still open questions about |
|
@sdanyliv maybe I'm crazy but I'd like to circle back to my initial idea (put window/over first, like groupby) with a mix of your options builder. I'm going to put the window upfront (everything inside // First we'll have a Window class that extends IEnumerable
sealed class Window<T> extends IEnumerable<T>;
// A window can be defined with a builder. Here's a WINDOW clause:
from p in People
let wnd = Sql.Window(w => w.PartitionBy(p.Gender).OrderBy(p.Age))
// Aggregates are extensions of IEnumerable<T>
// They are configured with builder, too
from g in db.People.GroupBy(p => p.Age)
select new
{
Count = g.Count(), // basic usage: COUNT(*)
Count = g.Count(e => e.Nationality, (a, _) => a.Distinct()), // COUNT(DISTINCT nationality)
FilteredMax = g.Max(e => e.Siblings, (a, e) => a.Filter(e.Gender == 'F')) // MAX(siblings) FILTER (WHERE gender = 'F')
}
// Aggregates can be called on IQueryable through `Aggregate[Async]()` (name up to bikeshed),
// and for most useful cases we can define an shortcut method on IQueryable directly
db.People.AggregateAsync(e => e.Count(o => o.Distinct())); // Any aggregate can be immediately executed
db.People.CountAsync(o => o.Distinct()); // Nice-to-have shortcut for count.
// Because Window is IEnumerable, aggregates can naturally be called on windows.
// Although I can't figure how to make Window generic in this context, so it still has to be a different overload :(
// The upside is that less lambdas are required.
from p in db.People
let wnd = Sql.Window() // OVER (): everything in one partition
select wnd.Max(p.Age, a => a.Filter(p.Gender == 'M')) // MAX(age) FILTER (WHERE gender = 'M') OVER (wnd)
// Windows can also be declared on the spot or extended
from p in db.People
select new
{
oldest = Sql.Window(w => w.PartitionBy(p.Gender)).Max(p.Age), // MAX(age) OVER (PARTITION BY gender)
youngest = Sql.Window(wnd, w => w.PartitionBy(p.Gender)).Min(p.Age), // MIN(age) OVER (wnd PARTITION BY gender)
}
// Windowed functions are defined as extensions of Window, so they can't be used without one:
from p in db.People
select Sql.Window(w => w.PartitionBy(p.Gender).OrderBy(p.Name, Nulls.First))
.Rank() // RANK() OVER (PARTITION BY gender ORDER BY name NULLS FIRST)
// Finally, my hypothetical string_agg example that no DB supports today:
Sql.Window(w => w.OrderBy(p.birthday.Month).Groups(1, 1))
.StringAgg(p.name, ",", o => o.OrderBy(p.name))
// STRING_AGG(name, ',' ORDER BY name)
// OVER (ORDER BY month(birthday) GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) |
|
Okay small tweak on my previous idea that I think I like better: // Use `Over()` to be closer to actual SQL code, make it fluent
select Sql.Over()
.PartitionBy(x.Country)
.OrderBy(x.Date)
.Sum(x.Amount, o => o.Filter(x.Amount > 0))
// Declared windows can be fluent too (less lambdas) and use Window() to remain closer to SQL
let wnd = Sql.Window().PartitionBy(x.Color) // WINDOW wnd AS (PARTITION BY color)
// Basic usage doesn't really require `Over()` but I think I like it.
// The main issue _without_ `Over()` is that it allows calling `wnd` fluent builder and that's ambiguous here,
// esp. considering the next example.
select Sql.Over(wnd).Max(x.Price) // MAX(price) OVER (wnd)
// Windows can be modified when they're used:
select Sql.Over(wnd)
.PartitionBy(x.Year)
.Max(x.Price) // MAX(price) OVER (wnd PARTITION BY year)
// Window declarations can be based off previous windows
let wnd2 = Sql.Window(wnd).OrderBy(x.Age) // WINDOW wnd2 AS (wnd ORDER BY age) |
Summary:Comparison of Syntax:Sql.Over()
.PartitionBy(x.Country)
.OrderBy(x.Date)
.Sum(x.Amount)vs Sql.Functions.Sum(x.Amount, f => f
.Over()
.PartitionBy(x.Country)
.OrderBy(x.Date))Sql.Over()
.PartitionBy(x.Country)
.OrderBy(x.Date)
.Sum(x.Amount, o => o.Distinct().Filter(x.Amount > 0))vs Sql.Functions.Sum(x.Amount, f => f.Distinct().Filter(x.Amount > 0)
.Over()
.PartitionBy(x.Country)
.OrderBy(x.Date))Aggregates:Currently, only I have defined them: var query =
from t in db.Some
group t by 1
select g.StringAggregate(x => x.Name, (x, f) => f.OrderBy(x.Name).Over().PartitionBy(x.Department))Your Syntax:Advantages:
Disadvantages:
Proposed Alternative:Advantages:
Disadvantages:
I will define later list of API examples for all window functions and we can choose better syntax for definition. |
|
@sdanyliv some thoughts on pros/cons: Re. my proposal I would add advantages:(3) Aggregate lambda is identical whether used on a groupby or on a window. (4) Concept of window (which set of rows are passed to function) is clearly separated from configuration of function (receiver) itself. (5) Function name at the end is consistent with groupby, and C#/LINQ in general (which is the opposite of your disadvantage 2, not quite agree on this one). (6) All windowed functions can be discovered as extensions of (7) Formatting tools such as CSharpier will format better: // Your last example as formatted by Prettier:
// My proposal
Sql.Over()
.PartitionBy(x.Country)
.OrderBy(x.Date)
.Sum(x.Amount, o => o.Distinct().Filter(x.Amount > 0))
// This PR
Sql.Functions.Sum(
x.Amount,
f =>
f.Distinct()
.Filter(x.Amount > 0)
.Over()
.PartitionBy(x.Country)
.OrderBy(x.Date)
)Re. disadvantages of my proposal:
Debatable, I don't quite agree: see advantage 5 above (consistency with groupby / LINQ / C# in general).
Can you clarify what you mean? I don't get it.
I don't get this one either. The SQL window is defined in
I am not sure why having a frame into a window makes Re. your proposal advantages:
I don't understand how having different options in the same aggregate function, depending on whether it's called on a group or a window, is "unified". In fact, I mention the exact opposite of this as advantage 3. above.
Do you have a concrete example? I am not aware of any aggregate-like functions that imposes requirements on its window. Re. your disadvantages
If we want an explicitly named function with my syntax, it's as easy as
Re. your comment below on LAG and LEADI don't get the issue?
It would be chained after // OVER (ORDER BY age RANGE BETWEEN 10 PRECEDING AND 5 FOLLOWING)
Over().OrderBy(x.Age).Range(10, 5);
What do you mean? I tested the following in PostgreSQL 17 and it works: -- no order? not useful but works
select lead(n) over ()
from Test
-- range?
select lead(n) over (order by n range 1 preceding)
from Test |
|
@jods4, sorry, but is hard to follow your answer, add citations to numbers. Show more examples (full examples) than words. Define range in your proposed syntax. For example LAG, LEAD do not allow range definition. |
|
@sdanyliv no worry, there's a lot to process! I have edited my post above, hopefully it's easier to read now. |
|
Postgres 17 introduces new functionality. Oracle and other database providers currently don't support frame definitions in these window functions. The syntax you've provided for the Frame is the simplest case, defining unbounded ranges clearly: OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) EXCLUDE CURRENT ROWConsider also this example below, which is syntactically valid from a compiler perspective but difficult to understand clearly: var query =
from t in db.Some
from ss in (
from ss in db.Some2
let o = Sql.Over()
let partition = o.PartitionBy(ss.Some2Field)
let ordering = partition.OrderBy(t.SomeField)
select ordering
)
select new
{
sum = ss.RowsRange
.Between.CurrentRow.And.Unbounded
.Exclude.CurrentRow
.Sum(x => x.SomeValue)
};Just want to mention that I don't like that we cannot control this process. Something may work, but something will fail - additional code, additional tests. |
Yes. This is why I feel we shouldn't go to crazy lengths to model specific RDBMS support in C# apis. Let's reflect the SQL standard.
I guess this will be the same regardless of which design we pick?
Exclusion could be an enum overload, similar to how we handle So for your example: // OVER (ORDER BY t ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) EXCLUDE CURRENT ROW
Sql.Over()
.OrderBy(t)
.Rows(null, 0, Exclude.CurrentRow)
Okay that's crazy, I didn't even consider it.
|
I'm not sure about the compiler feature you're referring to, but the proposed implementation won't compile. Anyway, I like idea about frame range (writing in prosed syntax) Sql.Functions.Sum(x. Some, f => f.Distinct().Over()
.PartitionBy(x.Some)
.OrderBy(x.Some)
.Rows(null, 0)
// .Range(null, 0)
// .Groups(null, 0)
.ExcludeCurrentRow()
// .ExcludeNoOthers()
// .ExcludeGroup()
// .ExcludeTies()
)Looking at syntax and https://www.sqlite.org/windowfunctions.html - do we handle all cases? |
|
I think that's what I meant: linq2db builder should refuse to compile invalid |
|
@jods4, check updated comment. I understand your point, but I'm still hesitant - I'm strongly inclined to prevent compiling bad functions. Let me first create a table of the functions we plan to implement, and then we'll discuss further. |
We need an overload that takes only the frame start For ORDER BY, PostgreSQL supports custom comparators with |
|
In SQL, the OVER clause is the key element that defines a Window Function, as specified in the official SQL documentation: Based on this, our API should reflect this fundamental structure. So, our Window Function API should always start with Everything else - Ranking Functions, Aggregate Functions, Analytic Functions - should be placed between these two constructs. This way, we can clearly separate regular Aggregate Functions from Window Aggregate Functions, avoiding confusion in usage. According to the specification, the OVER clause consists of: OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)This means that an API like: Over().PartitionBy().OrderBy()would be problematic because the entire construct must return a column value, and there's no clear way to bind the method chain to anything meaningful. Alternatively, we could make the chain end with something like: Over().PartitionBy().OrderBy().Value()but that introduces unnecessary complexity (say hello to Builder pattern), which may not be ideal in this case. Given these constraints, we have two viable options: Lambda-based configuration: Over(x => x.PartitionBy(...).OrderBy(...).RowOrRange(...))This approach is concise and consistent with other fluent APIs. It avoids ambiguity in interpreting Over(x => x), ensuring clarity in the function's purpose. Multiple lambda overloads for PartitionBy, OrderBy, etc: Over(x => x, x => x)This would require multiple overloads to cover different cases. But this one introduces ambiguity: Over(x => x) // Is this PartitionBy or OrderBy?Resolving this ambiguity would either require additional overloads or explicit method naming, both of which complicate API usage. Given the above considerations, the most reasonable and practical API design would be: Sql.Window.[SqlFunction()].Over(x => x.[PartitionBy(...)].[OrderBy(...)].[RowOrRange(...)]) |
|
@igor-tkachev, let's look at corner cases // SUM(DISTINCT some) OVER (...)
Sql.Window.Sum(t.Value, f => f.Distinct).Over(f => f.[PartitionBy(...)].[OrderBy(...)].[RowOrRange(...)])With one lambda for everything Sql.Window.Sum(t.Value, f => f.Distinct.Over().[PartitionBy(...)].[OrderBy(...)].[RowOrRange(...)])With window definition let wnd = Sql.Window.DefineWindow(f => f.[PartitionBy(...)].[OrderBy(...)].[RowOrRange(...)]);
sum = Sql.Window.Sum(t.Value, f => f.Distinct.Over(wnd))Can you catch these cases? |
|
A single lambda looks like a workable option but is less intuitive. As mentioned earlier, the OVER clause is the key element in defining Window Functions, so it makes sense to highlight it explicitly in the API. There are already inconsistencies in your example: f => f.Distinct.Over()Here, Keeping |
|
Two funsies I encountered at work today, which should be accounted for in the syntax (those at least work in Oracle). Postgre doesn't support this, but SQL standard defines an option I believe
It can be applied to MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV. (EDIT: that's what Oracle docs say, but in practice I also apply it to other functions such as |
|
IMHO the first For the second, I would be tempted to create a Sql.Over()
.PartitionBy(country)
.KeepLastBy(year, Order.NullsLast).ThenByDesc(population)
.Min(gdp)
// MIN(gdp) KEEP (DENSE_RANK LAST ORDER BY year NULLS LAST, population DESC) OVER (PARTITION BY country) |
|
@jods4, I avoid putting the function name at the end of a window function definition because it can lead to incorrect syntax that the compiler won't catch. For me, enforcing strict syntax that prevents misuse is more important than readability. |
|
@sdanyliv didn't we go over this?
|
|
If you want a real-world complex query to compare syntax and results, I coded this today: db.PartialStatementFile
.Merge()
.Using(
PendingFiles()
.Where(x => x.LastPageId == null)
.Select(x => new
{
x.Id,
LastPageId = Sql.Ext.Max(x.Id)
.KeepLast().OrderBy(x.IsLastPage)
.Over().PartitionBy(x.BankSwift, x.AccountNumber, x.StatementId)
.ToValue(),
HasLastPage = Sql.Ext.Max(x.IsLastPage)
.Over().PartitionBy(x.BankSwift, x.AccountNumber, x.StatementId)
.ToValue(),
MaxPage = Sql.Ext.Max(x.PageNumber)
.Over().PartitionBy(x.BankSwift, x.AccountNumber, x.StatementId)
.ToValue(),
PageCount = Sql.Ext.Count()
.Over().PartitionBy(x.BankSwift, x.AccountNumber, x.StatementId)
.ToValue(),
})
.Where(x => x.HasLastPage && x.PageCount >= x.MaxPage)
)
.On((stmt, g) => stmt.Id == g.Id)
.UpdateWhenMatched((stmt, g) => new()
{
LastPageId = g.LastPageId
})
.Merge();Resulting query (slightly reformatted): MERGE INTO PartialStatementFile Target
USING (
SELECT
x.Id,
x.LastPageId
FROM
(
SELECT
f.Id,
MAX(f.Id) KEEP (DENSE_RANK LAST ORDER BY f.IsLastPage) OVER(PARTITION BY f.BankSwift, f.AccountNumber, f.StatementId) as LastPageId
MAX(f.IsLastPage) OVER(PARTITION BY f.BankSwift, f.AccountNumber, f.StatementId) as HasLastPage,
MAX(f.PageNumber) OVER(PARTITION BY f.BankSwift, f.AccountNumber, f.StatementId) as MaxPage,
COUNT(*) OVER(PARTITION BY f.BankSwift, f.AccountNumber, f.StatementId) as PageCount,
FROM
PartialStatementFile f
WHERE
Nvl2(f.MergeFileId, NULL, f.Received) < :before AND
f.LastPageId IS NULL
) x
WHERE
x.HasLastPage = 1 AND x.PageCount >= x.MaxPage
) Source
ON (Target.Id = Source.Id)
WHEN MATCHED THEN UPDATE SET
Target.LastPageId = Source.LastPageIdMy suggested syntax for those, based on previous discussions: LastPageId = Sql.Over(wnd).KeepLastBy(x.IsLastPage).Max(x.Id) |
|
Feature request based on example above: it would be great in RDBMS that don't support named WINDOW yet, if linq2db could emulate the feature, simply by repeating the OVER(..) definition. You can see in my complex example above that I wrote multiple times Even though Oracle doesn't support |
|
Please focus solely on the syntax—don’t try to cover everything at once. We can define anything later; the key is figuring out how. Here are my concerns:
|
What is an example of an incorrect window function?
I agree on some drawbacks, which is why I now think that not everything must be chained fluently. |
# Conflicts: # Source/LinqToDB/Linq/Builder/SequenceHelper.cs # Source/LinqToDB/Linq/Translation/ProviderMemberTranslatorDefault.cs
Implemented Features
DistinctBy.Current Implementation (preliminary, subject to changes, frame boundaries not yet implemented)
Aggregate Functions Examples
Open Questions
Specifying Arguments
COUNT
COUNT() OVER(...)Sql.Window.Count(f => f.PartitionBy(x.Some))COUNT(x.Some) OVER(...)Sql.Window.Count(x.Some, f => f.PartitionBy(x.Some))COUNT(DISTINCT x.Some) OVER(...)1.
Sql.Window.CountDistinct(x.Some, f => f.PartitionBy(x.Some))2.
Sql.Window.Count(f => f.DistinctBy(x.Some).PartitionBy(x.Some))3.
Sql.Window.Count(a => a.Distinct(x.Some), f => f.PartitionBy(x.Some))SUM, AVG, etc.
SUM(x.Some) OVER(...)Sql.Window.Sum(x.Some, f => f.PartitionBy(x.Some))SUM(DISTINCT x.Some) OVER(...)1.
Sql.Window.SumDistinct(x.Some, f => f.PartitionBy(x.Some))(Note: multiple overloads required)2.
Sql.Window.Sum(a => a.Distinct(x.Some), f => f.PartitionBy(x.Some))Recommended syntax (preferred for clarity and extensibility):
Specifying Frames
SQL Frame Syntax:
[ROWS | RANGE | GROUPS] frame_start [frame_exclusion] [ROWS | RANGE | GROUPS] BETWEEN frame_start AND frame_end [frame_exclusion] frame_exclusion: EXCLUDE CURRENT ROW EXCLUDE GROUP EXCLUDE TIES EXCLUDE NO OTHERSSQL Examples:
Current Proposed C# Syntax:
Currently proposed (open to discussion):
Alternate possibilities:
Seeking Feedback
Please share your thoughts on the proposed syntaxes and indicate your preference, especially considering clarity, consistency, and extensibility.