I have the following tables and code which were in a SQL Server database and im rewriting in Postgres
However there is no equivalent of Outer Apply in Postgres
My code as follows:
Table Definition and Data Pouplation
CREATE TEMP TABLE MultiplierList
(
ID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
Multiplier INT
);
INSERT INTO MultiplierList (Multiplier)
VALUES (2),
(4),
(8),
(16),
(32),
(64),
(128),
(256),
(512),
(1024),
(2048),
(4096),
(8192),
(16384)
Insert into readings(DeviceChannelID, si, timestamp)
Values
('a06fd7d9-9748-4dd3-9054-f65b21704ab1',1.92264,'2022-05-20 13:55:23.000000'),
('afef08f8-10f9-4a45-8881-6180bb43ea7f',3.304008,'2022-05-20 13:55:23.000000'),
What im trying to recode is the following SQL
SELECT FLOOR
(
(ROW_NUMBER() OVER (PARTITION BY CR.DeviceChannelID, ML.ID ORDER BY CR.TimeStamp) - 1) /
Ml.Multiplier)
+ 1
AS Grp,
CR.DeviceChannelID,
CR.Si,
CR.TimeStamp,
ml.ID AS Level
FROM MultiplierList ml
OUTER APPLY Readings CR
But there is no postgres equivalent of Outer apply and I want to do is apply Mutiplier List to each of my records in Readings so i get 28 records (14 for each reading)
Desired Result Output
Grp DeviceChannelID Si TimeStamp Level
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 1
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 2
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 3
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 4
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 5
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 6
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 7
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 8
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 9
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 10
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 11
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 12
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 13
1 AFEF08F8-10F9-4A45-8881-6180BB43EA7F 3.3 2022-11-26 11:50:06.000 14
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 1
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 2
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 3
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 4
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 5
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 6
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 7
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 8
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 9
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 10
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 11
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 12
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 13
1 A06FD7D9-9748-4DD3-9054-F65B21704AB1 2.76 2022-11-26 11:50:06.000 14