Trying to achieve equivalent of OuterApply in Postgres

65 Views Asked by At

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

0

There are 0 best solutions below