User-Defined Ranking / Analytical Functions in SQL Server 2008

Question

I plan to migrate the data warehouse to SQL Server 2008 and try to find ways to replicate LAG, LEAD, FIRST_VALUE, and LAST_VALUE analytic functions from Oracle to SQL Server 2008. They are not included in SQL Server 2008, although the main mechanism for windowed analytic functions (for example, ROW_NUMBER, RANK and DENSE_RANK are present).

For these functions, you can achieve the same function by creating a subquery that assigns a row number to each row using ROW_NUMBER and then self-joins this query to find related rows with nearby row numbers (for LAG and LEAD), or row number 1 (for FIRST_VALUE )

I expect that performing self-connections will distract the operation's efficiency: but I don't yet have SQL Server to verify this. Therefore, without evaluating the performance, I wonder if there is a more efficient workaround that avoids self-connections.

By reviewing the documentation for user-defined aggregate functions , it is possible that the same code structure can be used to provide custom analytic functions.

So my question is: can you add an OVER () clause after a custom aggregate function to call it as an analytic function?

If so, is the Terminate () method called once per line? Is there anything special needed to get the strings sent to your UDF in the order specified in the OVER () clause?

Answer 1

, udfs.

UDFS, , ( ). , , APPLY, .

, Oracle . .

, Oracle , .

, SQL Server 2005+ FIRST_VALUE ( ) .

, FIRST_VALUE 2, . UDF agg, , , FIRST_VALUE .

;WITH CTE AS
(
    SELECT
        department_id, last_name, salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS ranking
    FROM employees
    WHERE department_id = 90
)
SELECT
    c1.department_id, c1.last_name, c1.salary,
    c2.last_name as Poorest
FROM
    CTE c1
    CROSS JOIN
    (SELECT last_name FROM CTE WHERE Ranking = 1) c2
ORDER BY
    c1.employee_id

Answer 2

SQL- SSAS; FirstNonEmpty, LastNonEmpty, FirstChild, LastChild. SQL-; . . , .