Search code examples
sqlprestotrino

Presto SQL Exploding Array to rows (but randomly sampled 100 rows)

I have a huge table with an array column. I know the code to explode array to rows, however, I only want to explode the array to get 100 rows and stop - trying to save on compute cost of the query. Any suggestions?

SELECT
    *
FROM (
    SELECT
        u.ids AS final_id,
        ROW_NUMBER() OVER (
            ORDER BY
                random()
        ) AS seqnum
    FROM raw
    CROSS JOIN UNNEST(raw.ids) u(ids)
    WHERE
        u.ids IS NOT NULL
    GROUP BY
        1
)
WHERE
    seqnum <= 100;
Solution

  • By adding the LIMIT 100 statement after the WHERE clause in the subquery, you can restrict the number of rows returned to 100. This will help save on compute costs by avoiding unnecessary processing of additional rows.