I noticed that I could not find the sql statement in the official Pig documentation, only at https://issues.apache.org/jira/browse/PIG-2482 . I tried the statement out and seems to work pretty well for my use case.
With apologies if either the issue is well known or I didn't properly read the documentation. Thanks!