SQL window functions provide extremely powerful and useful features that let you work with observations more easily. Different types explained here.

TiDB supports the SQL Mode ONLY_FULL_GROUP_BY, and when enabled TiDB will refuse queries with ambiguous non-aggregated columns. For example, this query is illegal with ONLY_FULL_GROUP_BY enabled ...

ObjList with group by functions is unrolled into fields. So the tight aggregation loop does not have to get individual functions from the ObjList. This yields small, but consistent performance ...

Part 1 of this article introduced four new T-SQL functions available in SQL Server 2012. Part 2 has the rest.