对于 BigQuery 标准 SQL
#standardSQL
WITH worklog AS (
SELECT 'ABC_1' AS ID, 'ABC' AS ResourceID, 'CREATE' AS Action, 10 AS COUNT UNION ALL
SELECT 'ABC_2', 'ABC', 'UPDATE', 8 UNION ALL
SELECT 'ABC_3', 'ABC', 'UPDATE', 4 UNION ALL
SELECT 'ABC_4', 'ABC', 'DELETE', 4 UNION ALL
SELECT 'DEF_1', 'DEF', 'CREATE', 10 UNION ALL
SELECT 'DEF_2', 'DEF', 'DELETE', 10 UNION ALL
SELECT 'GHJ_1', 'GHJ', 'CREATE', 10 UNION ALL
SELECT 'KLM_1', 'KLM', 'CREATE', 10 UNION ALL
SELECT 'KLM_2', 'KLM', 'UPDATE', 5
)
SELECT * EXCEPT(Last)
FROM (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ResourceID ORDER BY ID DESC) AS Last
FROM worklog
WHERE Action != 'DELETE'
)
WHERE Last = 1
-- ORDER BY ID