I have a query with many CASE statements that runs for a very long time due to the number of rows. In my research I have not found a solution yet. Is there a way to write the CASE statements more efficiently and with better performance? database: Oracle table_a table table_y are all the same table where I SEL…
calculation with undefined number of rows
I would like to add a calculation taking into account we could have one, two, three or a lot of more rows. We can get all the data using the relations but I am a bit stuck because the number of relation are undefined. For example, as a source: Source Table id amount debt relation rn 123 250 225 NULL
Splitting up events that occur over the day boundary
I have a table of events with a start time and an end time, with some events that have a start time before midnight and an end time after midnight. I’d like to produce output that splits up these events at the midnight barrier so they can be counted toward their respective date. The above table could be…
Athena/Presto : Unnest 2 arrays with left join
So I have 2 Json arrays that need unnesting, and joining based on a key within the json structure. In theory is easy, but without having a ‘left join unnest’ functionality, it all becomes messy. I have achieved what I want, by grouping the results; but I also have concerns that it is doing 2 cross…
User Defined Column name in select statement in Hivesql
I need to create user defined column name like below Postgresql query into HiveSql. Could you please help me on this. Answer Use backticks: But it is not possible to preserve case due to Hive limitation. Resulted column name will be in lower case: total customers See this answer: https://stackoverflow.com/a/5…
Oracle SQL – need to flip values, but don’t know how
This is the output, but I need those values changed. I have ID primary keys for both – kasutaja and riistvara, but I don’t know how to match kasutaja ID 1 with riistvara ID 2 and vice versa. And the output should be like this: The R_NIMETUS and SEERIA_NR fields are different on my output what I ge…
Database search results with context
I’ve built a system for archiving, aggregating, and indexing my IM history across different platforms (e.g. MSN Messenger, Hangouts, WhatsApp). The data is currently stored in an SQLite3 database, and relevant parts of the schema look something like this: There are also NOT NULL constraints on all colum…
Select everyone with the same ips
There is a table of users users: ETC… It is necessary to select all users with 1 ip. I tried to do it, it doesn’t work, not what I need to choose. Answer For MySQL 5.x use
Find Unique Count Postgresql query into hivesql
I want to get unique customer counts. I have reference of postgresql query. Could you please convert this query into HiveSql Answer Use case expressions: One more method for counting distinct is size(collect_set()):
How to use over – partition by query in SQL in order to get the current, average, and maximum value?
I have this table where it shows a point that was being done by a device at an area and specific location. I want to get the current, average and also maximum points grouped by area and also location. If I choose any day, the current qty will show the quantity of the latest working date. Meanwhile, average qu…