I have a table with Forecast, Budget, & Actuals. I need to grab the latest data based on the month which is prefixed in the d_period field (Ex: Jan, Feb, Mar, Apr, etc.). The query should always default to the latest month (no relation to current month – the upload varies so it could be 2 months ago, 4 months
Tag: trino
Get JSON object keys as array in Presto/Trino
I have JSON data like this in one of my columns and I would like to run a query that returns the keys as an array Answer Convert your JSON into a MAP and then use map_keys(): Use json_parse() if your JSON column is of type VARCHAR Output: _col0 bar, foo foo
Aggregation with column selecting element based on list of priorities
I would like to aggregate a list of elements where one column is the urgency. I would like to get a row for each item and take the highest “Urgency” observed for that item based on a list (or mapping). Item Urgency A Normal A Low A High B Normal B Low C High Expected output: Item Urgency A High
Presto – Convert Table into Map
I would like to convert the below table into a map with multiple key/value pairs label_a label_b 1 2 3 4 5 6 into {‘label_a’ -> 1, ‘label_b’ -> 2} {‘label_a’ -> 3, ‘label_b’ -> 4} {‘label_a’ -> 5, ‘label_b’ -> 6} What would be the easiest way to do this? All the results I’ve found are scala/non-presto solutions and
generate date range between min and max dates Athena presto SQL sequence error
I’m attempting to generate a series of dates in Presto SQL (Athena) using unnest and sequence something similair to generate_series in postgres. my table looks like I’m aiming for an output as follows I’ve attempted to use the following query to achieve this – however I get an error when trying to unnest my date sequence which yields the following
SQL/Presto: how to choose rows if the values match with another table’s
I have 2 tables: table 1: table 2: I want to add a column for table 2 such that if cnt in table1 for a task is the same as cnt2 for a task in table2. If there is no match, mark it as ‘no match’ desired result: I started from a query like the one below to pick tasks
SQL/Presto: how to rank within a subgroup of each group
I have a table like the following: i want to rank user_id within subgroup of each group by the score and then by time (earlier better) each user_id gets. so the desired output is Answer Use rank(): Actually, I’m not sure if higher scores are better than lower ones, so you might want score asc.
count and sum in case statement
What is the difference below if I use case instead of sum? I believe I would get the same output? Thanks! Answer The three are equivalent. All of them count the number of rows that meet the particular condition (salary > 100000). All return 0/1 and would not return NULL values for the column. From a performance perspective, all should
WHERE followed by ON clause
What is the semantic difference between the following queries? To me both seemed similar until I executed them on presto. Is this something specific to presto or am I missing something in the SQL standard? Form 1: All conditions specified in the ON clause. Form 2: Some conditions specified in the WHERE clause instead. Form 1 results in some rows
presto sql filter part of the string java
I have a string in col1 select col1 from table “blah blah 123 blah iterate 866 loss blah blah” how to filter string between iterate and loss and just show sum of all such filtered numbers ? Answer You can use regexp_extract to extract the number within that string, convert the values to a numeric type and sum them up: