Обсуждение: Fwd: TSQL To Postgres - Unpivot/Union All

Поиск

Список

Период

Сортировка

Fwd: TSQL To Postgres - Unpivot/Union All

От

Anthony Apollis

Дата:

20 июля 2023 г., 15:17:42

I am trying to convert this TSQL:

INSERT INTO temp_FieldFlowsFact

SELECT [Account],[Calendar day],[Financial year],[Period]
,CASE
WHEN [Metric] like '%R12M%' THEN 'R12M'
WHEN [Metric] like '%R6M%' THEN 'R6M'
WHEN [Metric] like '%R3M%' THEN 'R3M'
ELSE 'Periodic'
END AS [Period type]
,[Metric],[Metric Value]

FROM
(
----Calculating Rolling FTRs
SELECT [Account], [Calendar day] ,[Financial year],[Period]

,[Issue]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Issues]
,SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Issues]

,[Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers In]
,SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers In]

,[Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Transfers Out]
,SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Transfers Out]

,[Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING) AS [R12M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING) AS [R6M Return]
,SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING) AS [R3M Return]

,ROUND(ISNULL(([Transfers Out]+[Return])/NULLIF(([Issue]+[Transfers In]),0),0)*100,2) AS [FTR%] --Calculating FTR%

,ROUND(ISNULL(( SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
)

/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)

),0),0)*100,2) AS [R12M FTR%] --Calculating R12M FTR%

,ROUND(ISNULL(( SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 11 PRECEDING)
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)
)

/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 5 PRECEDING)

),0),0)*100,2) AS [R6M FTR%] --Calculating R6M FTR%

,ROUND(ISNULL(( SUM([Transfers Out]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)
+
SUM([Return]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)
)

/NULLIF((
SUM([Issue]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)
+
SUM([Transfers In]) OVER (PARTITION BY [Account] ORDER BY [Calendar day] ROWS 2 PRECEDING)

),0),0)*100,2) AS [R3M FTR%] ---Calculating R3M FTR%

FROM #temp_FlowsFact) AS FACT

--WHERE [Account]='SA11'
UNPIVOT
( [Metric Value] FOR [Metric] IN (
[Issue],[R12M Issues],[R6M Issues],[R3M Issues],[Transfers In],[R12M Transfers In]
,[R6M Transfers In],[R3M Transfers In],[Transfers Out],[R12M Transfers Out],[R6M Transfers Out]
,[R3M Transfers Out],[Return],[R12M Return],[R6M Return],[R3M Return],[FTR%],[R12M FTR%],[R6M FTR%],[R3M FTR%]
)
)AS unpvt
--WHERE [Account] ='AE25' AND [Metric] like '%FTR%'
ORDER BY [Calendar day]

To Postgres. The above code gives outputs for columns:

The Postgres i used: INSERT INTO temp_FieldFlowsFact

SELECT "Account", "Calendar day", "Financial year", "Period",
CASE
WHEN "Metric" LIKE '%R12M%' THEN 'R12M'
WHEN "Metric" LIKE '%R6M%' THEN 'R6M'
WHEN "Metric" LIKE '%R3M%' THEN 'R3M'
ELSE 'Periodic'
END AS "Period type",
"Metric", "Metric Value"
FROM (
-- Calculating Rolling FTRs
SELECT "Account", "Calendar day", "Financial year", "Period",
"Issue",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Issues",
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Issues",
"Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers In",
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers In",
"Transfers Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Transfers Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Transfers Out",
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Transfers Out",
"Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) AS "R12M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) AS "R6M Return",
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS "R3M Return",
ROUND(COALESCE(("Transfers Out" + "Return") / NULLIF(("Issue" + "Transfers In"), 0), 0) * 100, 2) AS "FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R12M FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R6M FTR%",
ROUND(COALESCE((
SUM("Transfers Out") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
SUM("Return") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
) /
NULLIF((
SUM("Issue") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) +
SUM("Transfers In") OVER (PARTITION BY "Account" ORDER BY "Calendar day" ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
), 0), 0) * 100, 2) AS "R3M FTR%"
FROM temp_FlowsFact
) AS FACT
UNION ALL
SELECT "Account", "Calendar day", "Financial year", "Period",
CASE
WHEN "Metric" = 'Issue' THEN 'Issue'
WHEN "Metric" = 'R12M Issues' THEN 'R12M Issues'
WHEN "Metric" = 'R6M Issues' THEN 'R6M Issues'
WHEN "Metric" = 'R3M Issues' THEN 'R3M Issues'
WHEN "Metric" = 'Transfers In' THEN 'Transfers In'
WHEN "Metric" = 'R12M Transfers In' THEN 'R12M Transfers In'
WHEN "Metric" = 'R6M Transfers In' THEN 'R6M Transfers In'
WHEN "Metric" = 'R3M Transfers In' THEN 'R3M Transfers In'
WHEN "Metric" = 'Transfers Out' THEN 'Transfers Out'
WHEN "Metric" = 'R12M Transfers Out' THEN 'R12M Transfers Out'
WHEN "Metric" = 'R6M Transfers Out' THEN 'R6M Transfers Out'
WHEN "Metric" = 'R3M Transfers Out' THEN 'R3M Transfers Out'
WHEN "Metric" = 'Return' THEN 'Return'
WHEN "Metric" = 'R12M Return' THEN 'R12M Return'
WHEN "Metric" = 'R6M Return' THEN 'R6M Return'
WHEN "Metric" = 'R3M Return' THEN 'R3M Return'
WHEN "Metric" = 'FTR%' THEN 'FTR%'
WHEN "Metric" = 'R12M FTR%' THEN 'R12M FTR%'
WHEN "Metric" = 'R6M FTR%' THEN 'R6M FTR%'
WHEN "Metric" = 'R3M FTR%' THEN 'R3M FTR%'
END AS "Period type",
"Metric Value", "Metric"
FROM temp_FlowsFact
WHERE "Metric" IN (
'Issue', 'R12M Issues', 'R6M Issues', 'R3M Issues', 'Transfers In', 'R12M Transfers In',
'R6M Transfers In', 'R3M Transfers In', 'Transfers Out', 'R12M Transfers Out', 'R6M Transfers Out',
'R3M Transfers Out', 'Return', 'R12M Return', 'R6M Return', 'R3M Return', 'FTR%', 'R12M FTR%', 'R6M FTR%', 'R3M FTR%'
)
ORDER BY "Calendar day";

Does not give me outputs for columns: Metric and Pricing Channel:

What am i doing wrong? Please assist?

Вложения

Re: TSQL To Postgres - Unpivot/Union All

От

"David G. Johnston"

Дата:

20 июля 2023 г., 15:48:22

On Thursday, July 20, 2023, Anthony Apollis <anthony.apollis@gmail.com> wrote:

I am trying to convert this TSQL:

This doesn’t really seem relevant all that relevant at this point. Given some input data either the query produces the expected result or it doesn’t.

What am i doing wrong? Please assist?

You need to provide more info - specifically the input data mentioned above. Try making a reproducer on this fiddle site.

https://dbfiddle.uk/btGcOH30

David J.

Re: TSQL To Postgres - Unpivot/Union All

От

Geoff Winkless

Дата:

20 июля 2023 г., 16:58:06

On Thu, 20 Jul 2023 at 13:17, Anthony Apollis <anthony.apollis@gmail.com> wrote:

The Postgres i used: INSERT INTO temp_FieldFlowsFact
SELECT "Account", "Calendar day", "Financial year", "Period",

[snip]

At the very least, include a column list in your INSERT statement. We have no way of checking where any of your results are going.

Geoff

Fwd: TSQL To Postgres - Unpivot/Union All

От

Anthony Apollis

Дата:

20 июля 2023 г., 17:28:33

---------- Forwarded message ---------
From: Anthony Apollis <anthony.apollis@gmail.com>
Date: Thu, 20 Jul 2023 at 16:07
Subject: Re: TSQL To Postgres - Unpivot/Union All
To: Geoff Winkless <pgsqladmin@geoff.dj>, David G. Johnston <david.g.johnston@gmail.com>

I am attaching my TSQL and Postgres SQL:

On Thu, 20 Jul 2023 at 15:58, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

On Thu, 20 Jul 2023 at 13:17, Anthony Apollis <anthony.apollis@gmail.com> wrote:
The Postgres i used: INSERT INTO temp_FieldFlowsFact
SELECT "Account", "Calendar day", "Financial year", "Period",

[snip]

At the very least, include a column list in your INSERT statement. We have no way of checking where any of your results are going.

Geoff

Вложения

Re: TSQL To Postgres - Unpivot/Union All

От

Geoff Winkless

Дата:

20 июля 2023 г., 17:51:35

On Thu, 20 Jul 2023 at 15:28, Anthony Apollis <anthony.apollis@gmail.com> wrote:
> I am attaching my TSQL and Postgres SQL:

You're still missing some CREATEs, for example for temp_FieldFlowsFact.

Even assuming your columns list is correct, I would still (and as a
matter of habit) include the target column list in your INSERT
statements, if only to make your own life easier, but especially
because any later changes to the tables could end up shifting the
position of the columns.

Certainly it will be easier for anyone else trying to disentangle the SQL later.

As an aside, can you clarify whether you mean temporal tables (and are
you using a temporal tables extension)? Or should that read
"temporary"?

Geoff

Re: Fwd: TSQL To Postgres - Unpivot/Union All

От

Thomas Kellerer

Дата:

20 июля 2023 г., 18:07:31

Anthony Apollis schrieb am 20.07.2023 um 14:17:
> Does not give me outputs for columns: Metric and Pricing Channel:
> image.png
>
> What am i doing wrong? Please assist?

UNPIVOT can be done using a LATERAL sub-query with a VALUES clause:

https://blog.sql-workbench.eu/post/unpivot-with-postgres/

Re: TSQL To Postgres - Unpivot/Union All

От

Anthony Apollis

Дата:

20 июля 2023 г., 18:47:08

CREATE TEMP TABLE temp_FieldFlowsFact (
Account varchar(20),
"Calendar day" DATE,
"Financial year" varchar(5),
Period varchar(8),
"Period type" varchar(10),
Metric varchar(50),
"Metric Value" float
)

It was created as a temp table in SQLServer, but it does not have to be in Postgres. Thanks

On Thu, 20 Jul 2023 at 16:51, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

On Thu, 20 Jul 2023 at 15:28, Anthony Apollis <anthony.apollis@gmail.com> wrote:
> I am attaching my TSQL and Postgres SQL:

You're still missing some CREATEs, for example for temp_FieldFlowsFact.

Even assuming your columns list is correct, I would still (and as a
matter of habit) include the target column list in your INSERT
statements, if only to make your own life easier, but especially
because any later changes to the tables could end up shifting the
position of the columns.

Certainly it will be easier for anyone else trying to disentangle the SQL later.

As an aside, can you clarify whether you mean temporal tables (and are
you using a temporal tables extension)? Or should that read
"temporary"?

Geoff

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Обсуждение: Fwd: TSQL To Postgres - Unpivot/Union All

Fwd: TSQL To Postgres - Unpivot/Union All

Вложения

Re: TSQL To Postgres - Unpivot/Union All

Re: TSQL To Postgres - Unpivot/Union All

Fwd: TSQL To Postgres - Unpivot/Union All

Вложения

Re: TSQL To Postgres - Unpivot/Union All

Re: Fwd: TSQL To Postgres - Unpivot/Union All

Re: TSQL To Postgres - Unpivot/Union All