I have a table with events, where start_dt is a start and end_dt an end of the event. This table is partitioned by dt column derived from the end_dt. This means that events that start before and end after midnight are present only in one partition. What I need to do is to split each event into as many rows
Tag: presto
Mapping two columns into one column in Athena
I have data in the Athena something like this: However, the table owner changed the provider to provider_new. Thus, after 2020-08-01 the provider returns to NULL. Here is my query: How can I map these two columns to one? Thanks in advance. Answer Do you want coalesce()? coalesce() returns the first non-null value of the arguments it is given.
presto replace multiple characters
I have a string and would like to remove a set of characters. Is there a better way than chaining multiple replace()? I came up with the following but it requires a replace() call for each character: This is a minimal example and I have more characters to escape so I was looking for a less verbose way of achieving
Split array(bigint) to multiple rows of bigint
I’m querying some data (SQL, presto), each item can be the parent or child of another. The parent IDs and the child IDs are stored in the row for that primary ID as an array(bigint). Each task could be the child of multiple parents. It looks something like: I want a list of all of the parent Ids and each
REGEX Extract Amount Without Currency
SELECT ocr_text, bucket, REGEXP_EXTRACT(‘-?[0-9]+(.[0-9]+)?’, ocr_text) FROM temp I am trying to extract amounts from a string that will not have currency present. Any number that does …
Returning Null Value – Coalesce and ISNULL
I may be having a brain fog, but if I have below: Which gives me the value of invalid for all fields in my info column that have an empty space, if I used ISNULL instead of COALESCE would it give me the same result? I cannot access a sql engine currently so just wanted to sanity check. Thanks! Answer
how do we check if an element in the array exists and if it has a value of “true” in Presto
The data in the array looks like XXXX:”False”. I used element_at(array_name,’Field’) and contains(map_keys(array_name),’Field’). They only check if the element is present in the array. But if the ‘Field’ has a value of ‘true’, How do i check that value? Answer Assuming that you have a map at hand, and that you want to check the value of of a given
SQL find max date based on a non null other column
I have a table like so: And I would like to return an entire row containing the uniqueID, scandatetime, and latest scanfacilityname (i.e., max scandatetime where scanfacilityname is not null). I have tried the following query: But im not sure if this is correct/if I need the coalesce. Answer You can use the max_by function: See the doc. There’s no
Presto – how is there an alternative to to_char like postgresql?
I need to build a query in presto that could look back the trailing 70 days, the table I am working with is storing the dates in the format of ‘YYYYMMDD’. in postgresql, I can just simply write the …
How to convert data into this form in SQL :
Input: I have to Convert data from input to output. where we trying if for id freq is n then create n rows of that id. Output: Answer In Presto, one option uses sequence() and a lateral join to generate the rows: