Sql-server – Want to perform a function on a returned table on subset of rows

functionsself-joinsql serversql-server-2012

I have a query that takes 10 minutes to run (1.4 million rows and 15-ish joins), so I think the time isn't terrible.

What I need from this is to effectively INNER JOIN on itself an additional column based on a key of 'n' other columns. This key would be selecting 3 specific rows and performing a mathematical function to produce one result. This result would be joined onto the 3 rows in the final column:

enter image description here

I know I could (and have with smaller queries) just run the query again with a group by and join it on to the original, but this (I believe) would double the query time.

Is there a standard way to do this that I am just not aware of?

Completely stumped by this one, and have had to resort to asking a question on here (after searching). Bear with me as I am self taught!

Best Answer

Below is an example of how you might use the FIRST_VALUE and LAST_VALUE Windows Functions to calculate your Gain_loss column and then use a CASE expression to give you a Classification

SET NOCOUNT ON
declare @Table table (Col1 Char(1), Col2 Char(2), Col3 Char(2), [Year] int, [Year+n] int, Result int)
insert into @Table 
    (Col1, Col2, Col3, [Year], [Year+n], Result) 
    values 
        ('A', 'UK', '1Z', 2015, 2, 30),
        ('A', 'UK', '1Z', 2015, 1, 20), 
        ('A', 'UK', '1Z', 2015, 0, 10), 
        ('A', 'UK', '1Y', 2015, 2, 30),
        ('A', 'UK', '1Y', 2015, 1, 35),
        ('A', 'UK', '1Y', 2015, 0, 40)

--Use the FIRST_VALUE and LAST_VALUE Window Functions to calculate the Gain_loss
SELECT Col1
    ,Col2
    ,Col3
    ,[YEAR]
    ,[Year+n]
    ,Result
    ,LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER by Col1, Col2, Col3, [Year], [Year+n] 
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - 
    FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n]) 
    AS Gain_loss
    ,CASE
        WHEN 
            LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n] 
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - 
            FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n])  > 0 THEN 'Up'
        WHEN
            LAST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3 
                            ORDER BY Col1, Col2, Col3, [Year], [Year+n] 
                            ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - 
            FIRST_VALUE(Result) OVER(PARTITION BY Col1, Col2, Col3 
                                ORDER BY Col1, Col2, Col3, [Year], [Year+n]) < 0 THEN 'Down'
        ELSE 'NoChange' 
    END AS Classification
FROM @Table
ORDER BY Col1, Col2, Col3 DESC, [YEAR], [Year+n]

That produces the following results (which appears to match your desired results)

| Col1 | Col2 | Col3 | YEAR | Year+n | Result | Gain_loss | Classification |
|------|------|------|------|--------|--------|-----------|----------------|
| A    | UK   | 1Z   | 2015 | 0      | 10     | 20        | Up             |
| A    | UK   | 1Z   | 2015 | 1      | 20     | 20        | Up             |
| A    | UK   | 1Z   | 2015 | 2      | 30     | 20        | Up             |
| A    | UK   | 1Y   | 2015 | 0      | 40     | -10       | Down           |
| A    | UK   | 1Y   | 2015 | 1      | 35     | -10       | Down           |
| A    | UK   | 1Y   | 2015 | 2      | 30     | -10       | Down           |

To learn more about SQL Server Window Functions, check out:

SELECT - OVER Clause (Transact-SQL)

How to Use Microsoft SQL Server 2012's Window Functions