Windowing functions in recursive CTE

Written on Jul, 13, 2019

Today we will see an interesting case of incompatibility between MS SQL Server 2017 and PostgreSQL 9.6 (and different versions as well). Let’s start with this code:

WITH dummy AS(
    SELECT 1 AS rowValue, 0 AS phase
    UNION ALL
    SELECT 2 AS rowValue, 0 AS phase
),
solution AS (
    SELECT * FROM dummy
),
solution2 AS(
    SELECT
        SUM(rowValue) OVER (PARTITION BY phase) AS rowValue,
        phase + 1 AS phase
    FROM solution
    WHERE phase = 0
)
SELECT *
FROM solution2
WHERE phase = 1

WITH dummy AS(

SELECT 1 AS rowValue, 0 AS phase

UNION ALL

SELECT 2 AS rowValue, 0 AS phase

solution AS (

SELECT * FROM dummy

solution2 AS(

SELECT

SUM(rowValue) OVER (PARTITION BY phase) AS rowValue,

phase + 1 AS phase

FROM solution

WHERE phase = 0

)

SELECT *

FROM solution2

WHERE phase = 1

We emulate a recursive CTE. We have two columns in source dataset, we want to…

Machine Learning Part 4 — Linear regression in T-SQL

Written on Nov, 10, 2018

This is the fourth part of the ML series. For your convenience you can find other parts in the table of contents in Part 1 – Linear regression in MXNet This time we are going to implement linear regression as a function. This…

Machine Learning Part 2 — Linear regression in SQL

Written on Oct, 27, 2018

This is the second part of the ML series. For your convenience you can find other parts in the table of contents in Part 1 – Linear regression in MXNet Image that you have only a data warehouse with SQL capabilities to train…

Tag Archives

Windowing functions in recursive CTE

Machine Learning Part 4 — Linear regression in T-SQL

Machine Learning Part 2 — Linear regression in SQL

Recent Posts

Categories

Archive

Posts