I am trying to group rows on a postgres query. I have one row of data that has a field with a line number in it. The next 3 rows are null. I want to set them to the same value as the first row. Then the 5th row repeats the process. There may be 1 or more rows with null as the line number.
Here is a sample of the data with a simple query to pull the rows I want, ordered by line_num:
item_id | ordered_qty | unit | line_num | group_line_num | code |
---|---|---|---|---|---|
NP4484140T | 11 | PST^ | 7 | 7 | |
COL48LPT | 6 | PST | 8 | null | NP4484140T |
COL48EPT | 4 | PST | 9 | null | NP4484140T |
COL48BPT | 1 | PST | 10 | null | NP4484140T |
VP5578135T | 1 | PST^ | 52 | 52 | |
ONTP48CPT | 1 | PST | 53 | null | VP5578135T |
I want group_line_num to be 7 where it is null in the first set and 52 on the 4th null. The groups can also be created based on where the unit changes with the PST^ being the defining line of the group. I have tried using lag but since the number of null lines is variable I am not sure how to make it work.
The described results can be easily achieved using MAX
as a window function.
The following code establishes a table of example values:
CREATE TABLE examples (
item_id TEXT NOT NULL,
ordered_qty INTEGER NOT NULL,
unit TEXT NOT NULL,
line_num INTEGER NOT NULL,
group_line_num INTEGER,
code TEXT
);
INSERT INTO
examples (
item_id,
ordered_qty,
unit,
line_num,
group_line_num,
code
)
VALUES
('NP4484140T', 11, 'PST^', 7, 7, ''),
('COL48LPT', 6, 'PST', 8, NULL, 'NP4484140T'),
('COL48EPT', 4, 'PST', 9, NULL, 'NP4484140T'),
('COL48BPT', 1, 'PST', 10, NULL, 'NP4484140T'),
('VP5578135T', 1, 'PST^', 52, 52, ''),
('ONTP48CPT', 1, 'PST', 53, NULL, 'VP5578135T');
To determine the group_line_num
using it's most recent non-NULL value, run the following:
SELECT
item_id,
ordered_qty,
unit,
line_num,
MAX(group_line_num) OVER (ORDER BY line_num) AS group_line_num,
code
FROM
examples
ORDER BY
line_num;
The following query derives group_line_num
from the most recent line_num
associated with unit = 'PST^'
:
SELECT
item_id,
ordered_qty,
unit,
line_num,
MAX(line_num) FILTER (WHERE unit = 'PST^') OVER (ORDER BY line_num) AS group_line_num,
code
FROM
examples
ORDER BY
line_num;
The two queries return identical results:
item_id | ordered_qty | unit | line_num | group_line_num | code |
---|---|---|---|---|---|
NP4484140T | 11 | PST^ | 7 | 7 | null |
COL48LPT | 6 | PST | 8 | 7 | NP4484140T |
COL48EPT | 4 | PST | 9 | 7 | NP4484140T |
COL48BPT | 1 | PST | 10 | 7 | NP4484140T |
VP5578135T | 1 | PST^ | 52 | 52 | |
ONTP48CPT | 1 | PST | 53 | 52 | VP5578135T |