A certain table stores records of personnel from external sources entering and leaving a building by swiping their cards in recent days. When a person enters and exits a building, there are usually N pairs of records, with one in and one out for each pair. Sometimes the data is not standardized or paired, with only one in or one out, or multiple consecutive ins or outs.

Now we need to convert each pair of records from rows to columns and turn them into a single record. Unmatched data should be converted into a separate record, and the blank part should be filled with null.

We can first group by person and building, and then divide every 2 adjacent rows within the group into a small group and transpose each small group. But after SQL grouping, it must aggregate immediately, and subsets cannot be kept to group again. This requires a shift in thinking and using multi-layer nested window functions to bypass this problem, which is difficult to code. Grouping by adjacent IN/OUT states is another hassle, requiring the use of multiple layers of PARTITION to generate accumulated values for grouping. What's even more troublesome is that it must immediately aggregate after grouping, and the grouped data cannot be further transposed, which is still cumbersome.

After SPL grouping, the grouped subsets can be retained, and conditional ordered grouping is also supported. It is convenient to divide a pair of IN/OUT records into a group, and adjacent references are also used in this process. SPL can continue to keep grouped subsets, making it easier to transpose each group.

A1： Load data from the database and sort it by username, building, timestamp.

A2： Use the group function to group by person and building, but do not aggregate.

A3： For each group of data in A2, divide each group of IN and OUT (including individual IN and OUT) into a small group, and merge the large group to leave only the small group. The option @i of the group function indicates grouping by condition. ~ is the current group, and [-1] represents the previous record.

A4： Convert each group of data in A3 from rows to columns, union each group and leave only records. The pivot function is used for transposition, and the column names after transposition can be specified as IN and OUT to ensure that null is automatically filled in case of missing data.

Question source：https://stackoverflow.com/questions/78319976/postgresql-how-to-calculate-swipe-in-and-swipe-out-time