implement slice_tail and slice_head

[mgacc0]

Would it be possible to implement slice_tail.duckplyr_df, slice_head.duckplyr_df, slice_min.duckplyr_df, slice_max.duckplyr_df using ROW_NUMBER() OVER?

For example:

CREATE TABLE df1 AS (
    SELECT * FROM (
        VALUES
            (1, 'A', '2023-01-01'::DATE),
            (2, 'A', '2023-01-02'::DATE),
            (3, 'A', '2023-01-03'::DATE),
            (4, 'B', '2023-01-02'::DATE),
            (5, 'B', '2023-01-03'::DATE),
            (6, 'C', '2023-01-03'::DATE)
    ) AS t(id, group_id, date)
);

slice_max(df1, order_by = date, by = group_id) as:

WITH RankedRows AS (
    SELECT *, ROW_NUMBER() OVER () AS rn
    FROM df1
),
RankedRowsInGroup AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY date DESC) AS grn
    FROM RankedRows
)
SELECT * EXCLUDE(rn, grn) FROM RankedRowsInGroup
WHERE grn = 1
ORDER BY rn;

┌───────┬──────────┬────────────┐
│  id   │ group_id │    date    │
│ int32 │ varchar  │    date    │
├───────┼──────────┼────────────┤
│     3 │ A        │ 2023-01-03 │
│     5 │ B        │ 2023-01-03 │
│     6 │ C        │ 2023-01-03 │
└───────┴──────────┴────────────┘

 
 
slice_head(df1, n = 1, by = group_id) as:

WITH RankedRows AS (
    SELECT *, ROW_NUMBER() OVER () AS rn
    FROM df1
),
RankedRowsInGroup AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY group_id) AS grn
    FROM RankedRows
)
SELECT * EXCLUDE(rn, grn) FROM RankedRowsInGroup
WHERE grn = 1
ORDER BY rn;

┌───────┬──────────┬────────────┐
│  id   │ group_id │    date    │
│ int32 │ varchar  │    date    │
├───────┼──────────┼────────────┤
│     1 │ A        │ 2023-01-01 │
│     4 │ B        │ 2023-01-02 │
│     6 │ C        │ 2023-01-03 │
└───────┴──────────┴────────────┘

[krlmlr]

Thanks. I think this might be possible, would have to translate this into relational. The ROW_NUMBER() is a rel_project(), perhaps with a relexpr_window(), the WHERE is a rel_filter(), the ORDER BY is a rel_order() . If you want to take a stab, search for d = row_number(), .by = g in test-rel_api.R for a similar example (mutate(d = row_number(), .by = g)) using the relational API.